SELECT * FROM "SYS"."M_CACHE_ENTRIES"
WHERE "CACHE_ID" = 'MDS' AND "COMPONENT" = 'CUBE'
{ "definitions": { "SAP_TCT_INA_V_R_01": { "kind": "entity", "elements": { "APP_USER": { "@EndUserText.label": "Application User", "type": "cds.String", "length": 256 }, "DB_USER": { "@EndUserText.label": "DB User", "type": "cds.String", "length": 256 }, "START_TIME": { "@EndUserText.label": "Time", "type": "cds.Timestamp", "key": true, "notNull": true }, "DATE": { "isCalculated": true, "@EndUserText.label": "Date", "type": "cds.Date" }, "WEEKDAY": { "isCalculated": true, "@EndUserText.label": "Weekday", "type": "cds.Integer" }, "WEEK": { "isCalculated": true, "@EndUserText.label": "Week", "type": "cds.Integer" }, "HOUR": { "isCalculated": true, "@EndUserText.label": "Hour", "type": "cds.Integer" }, "DATE_TIME": { "isCalculated": true, "@EndUserText.label": "Date-Time", "type": "cds.DateTime" }, "DATE_HOUR": { "isCalculated": true, "@EndUserText.label": "Date-Hour", "type": "cds.DateTime" }, "EXECUTION_COUNT": { "isCalculated": true, "@EndUserText.label": "Execution Count", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "EXECUTION_TYPE": { "isCalculated": true, "@EndUserText.label": "Execution-Type", "type": "cds.String", "length": 256 }, "STORY_NAME": { "isCalculated": true, "@EndUserText.label": "Story Name", "type": "cds.String", "length": 256 }, "STORY_ID": { "isCalculated": true, "@EndUserText.label": "Story Id", "type": "cds.String", "length": 256 }, "WIDGET_ID": { "isCalculated": true, "@EndUserText.label": "Widget Id", "type": "cds.String", "length": 256 }, "DATA_SOURCE_NAME": { "isCalculated": true, "@EndUserText.label": "Data Source", "type": "cds.String", "length": 256 }, "DATA_SOURCE_SCHEMA": { "isCalculated": true, "@EndUserText.label": "Data Source Schema", "type": "cds.String", "length": 256 }, "DATA_SOURCE_INSTANCE_ID": { "isCalculated": true, "@EndUserText.label": "Data Source Instance Id", "type": "cds.String", "length": 256 }, "RUNTIME": { "@EndUserText.label": "Runtime [ms]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "RUNTIME_SEG_SECS": { "@EndUserText.label": "Runtime Seg.[secs]", "type": "cds.String", "length": 100 }, "RUNTIME_SEG_LOG": { "@EndUserText.label": "Runtime Seq.[ms/log]", "type": "cds.String", "length": 100, "@Analytics.dimension": true }, "PEAK_MEMORY_KB": { "isCalculated": true, "@EndUserText.label": "Peak Memory [Kb]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "PEAK_MEM_SEG_KB_LOG": { "@EndUserText.label": "Peak Memory [Kb/log]", "type": "cds.String", "length": 100 }, "ROW_COUNT": { "isCalculated": true, "@EndUserText.label": "Count", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_HASH": { "@EndUserText.label": "Statement Hash", "type": "cds.String", "length": 32 }, "STATEMENT_STRING": { "@EndUserText.label": "Statement String", "type": "cds.LargeString" } }, "query": { "SELECT": { "from": { "SELECT": { "from": { "ref": [ "M_MULTIDIMENSIONAL_STATEMENT_S" ], "as": "base" }, "columns": [ { "ref": [ "APPLICATION_USER_NAME" ], "as": "APP_USER" }, { "ref": [ "USER_NAME" ], "as": "DB_USER" }, { "ref": [ "LAST_EXECUTION_TIMESTAMP" ], "as": "START_TIME" }, { "func": "TO_DATE", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "DATE" }, { "func": "WEEKDAY", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "WEEKDAY" }, { "func": "WEEK", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "WEEK" }, { "func": "HOUR", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "HOUR" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] }, { "val": 1 }, { "val": 19 } ] }, { "val": "YYYY-MM-DD HH:MI:SS" } ], "as": "DATE_TIME" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] }, { "val": 1 }, { "val": 13 } ] }, { "val": "YYYY-MM-DD HH" } ], "as": "DATE_HOUR" }, { "func": "to_int", "args": [ { "ref": [ "EXECUTION_COUNT" ] } ], "as": "EXECUTION_COUNT" }, { "xpr": [ "case", "when", { "val": "{\"Analytics\":{\"Actions\":" }, "=", { "func": "left", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": 24 } ] }, "then", { "val": "Analytics-Actions" }, "else", { "func": "substr_after", "args": [ { "func": "substr_before", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\":" } ] }, { "val": "\"" } ] }, "end" ], "as": "EXECUTION_TYPE" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"StoryName\":\"" } ] }, { "val": "\"" } ], "as": "STORY_NAME" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"StoryId\":\"" } ] }, { "val": "\"" } ], "as": "STORY_ID" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"WidgetId\":[" } ] }, { "val": "]" } ], "as": "WIDGET_ID" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":" } ] }, { "val": "\"ObjectName\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"ObjectName\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_NAME" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":" } ] }, { "val": "\"SchemaName\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"SchemaName\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_SCHEMA" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"InstanceId\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_INSTANCE_ID" }, { "ref": [ "TOTAL_EXECUTION_TIME" ], "as": "RUNTIME" }, { "func": "to_int", "args": [ { "xpr": [ { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "/", { "val": 1000 } ] } ], "as": "RUNTIME_SEG_SECS" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "when", { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] }, "<", { "val": 0.477 }, "then", { "val": 3 }, "when", { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] }, "<", { "val": 0.6989 }, "then", { "val": 5 }, "else", { "val": 10 }, "end" ], "as": "RUNTIME_SEG_LOG_DELTA" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "else", { "func": "to_int", "args": [ { "func": "power", "args": [ { "val": 10 }, { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] } ] } ] }, "end" ], "as": "RUNTIME_SEG_LOG" }, { "func": "to_bigint", "args": [ { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ], "as": "PEAK_MEMORY_KB" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "when", { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] }, "<", { "val": 0.477 }, "then", { "val": 3 }, "when", { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] }, "<", { "val": 0.6989 }, "then", { "val": 5 }, "else", { "val": 10 }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG_DELTA" }, { "xpr": [ "case", "when", { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "else", { "func": "to_int", "args": [ { "func": "power", "args": [ { "val": 10 }, { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] } ] } ] }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG" }, { "val": 1, "as": "ROW_COUNT" }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "STATEMENT_STRING" ] } ] }, "as": "Subselect1" }, "columns": [ { "ref": [ "APP_USER" ] }, { "ref": [ "DB_USER" ] }, { "ref": [ "START_TIME" ] }, { "ref": [ "DATE" ] }, { "ref": [ "WEEKDAY" ] }, { "ref": [ "WEEK" ] }, { "ref": [ "HOUR" ] }, { "ref": [ "DATE_TIME" ] }, { "ref": [ "DATE_HOUR" ] }, { "ref": [ "EXECUTION_COUNT" ] }, { "ref": [ "EXECUTION_TYPE" ] }, { "ref": [ "STORY_NAME" ] }, { "ref": [ "STORY_ID" ] }, { "ref": [ "WIDGET_ID" ] }, { "ref": [ "DATA_SOURCE_NAME" ] }, { "ref": [ "DATA_SOURCE_SCHEMA" ] }, { "ref": [ "DATA_SOURCE_INSTANCE_ID" ] }, { "ref": [ "RUNTIME" ] }, { "xpr": [ { "ref": [ "RUNTIME_SEG_SECS" ] }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_SECS" ] }, "+", { "val": 1 } ], "as": "RUNTIME_SEG_SECS" }, { "xpr": [ "case", "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 3 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 3 }, "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 5 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 3 }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 5 }, "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 10 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 5 }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 10 }, "else", { "val": "0" }, "end" ], "as": "RUNTIME_SEG_LOG" }, { "ref": [ "PEAK_MEMORY_KB" ] }, { "xpr": [ "case", "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 3 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 3 }, "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 5 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 3 }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 5 }, "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 10 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 5 }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 10 }, "else", { "val": "0" }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG" }, { "ref": [ "ROW_COUNT" ] }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "STATEMENT_STRING" ] } ] } }, "@EndUserText.label": "SAP_TCT_INA_V_R_01", "@ObjectModel.modelingPattern": { "#": "ANALYTICAL_FACT" }, "@ObjectModel.supportedCapabilities": [ { "#": "ANALYTICAL_FACT" }, { "#": "ANALYTICAL_PROVIDER" } ], "@DataWarehouse.consumption.external": true, "@DataWarehouse.sqlEditor.query": "/*\n\n View Name: SAP_TCT_INA_INA_V_R_01\n\n Docu : Source View M_MULTIDIMENSIONAL_STATEMENT_STATISTICS\n https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/5b04f05f501f4f91aea202f139... This view parses the ina request and extracts the following properties: \n - Ina/MDS Requests Grouped per Date and Hour \n - Ina/MDS Requests Grouped by Type \n - Runtime Segmentation \n - Name of the Story (currently only provided for Analytics and Batch Calls)\n - Name of the first data source found (for batch, several data sources are possible)\n \n For performance reasons we used the string parsing instead of hana json statements.\n \n This works best with the MDS setting to write unique records ( MDS key name is \"statistics_unique_entry\" ) and a \n max rowcount increased to a limit of 100.000 (MDS key name is \"max_statistics_entry_count\")\n\n Example for setting the parameters\n ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System' ) SET\n ('mds', '<mds key name>') = 'value' WITH RECONFIGURE;\n \n This configuration can be set in SAP Datasphere as \"Trace\" for MDS: navigate to system => configuration => monitoring. \n\n*/\n\nSELECT \n \"APP_USER\",\n \"DB_USER\",\n \"START_TIME\",\n \"DATE\",\n \"WEEKDAY\",\n \"WEEK\",\n \"HOUR\",\n \"DATE_TIME\",\n \"DATE_HOUR\",\n \n \"EXECUTION_COUNT\",\n \"EXECUTION_TYPE\",\n \n \"STORY_NAME\",\n \"STORY_ID\",\n \"WIDGET_ID\",\n \n \"DATA_SOURCE_NAME\",\n \"DATA_SOURCE_SCHEMA\",\n \"DATA_SOURCE_INSTANCE_ID\",\n \n -- Runtime: raw value in ms, and segmentation in full seconds and log-scale (ms)\n \"RUNTIME\",\n \"RUNTIME_SEG_SECS\" || ' < ' || \"RUNTIME_SEG_SECS\" + 1 as \"RUNTIME_SEG_SECS\",\n case \n when \"RUNTIME_SEG_LOG_DELTA\" = 3 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" || ' < ' || \"RUNTIME_SEG_LOG\" * 3 \n when \"RUNTIME_SEG_LOG_DELTA\" = 5 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" * 3 || ' < ' || \"RUNTIME_SEG_LOG\" * 5 \n when \"RUNTIME_SEG_LOG_DELTA\" = 10 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" * 5 || ' < ' || \"RUNTIME_SEG_LOG\" * 10\n else '0' end as \"RUNTIME_SEG_LOG\",\n \n -- Memory: raw value and logarithmic segmentation\n \"PEAK_MEMORY_KB\",\n case \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 3 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 3 \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 5 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" * 3 || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 5 \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 10 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" * 5 || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 10\n else '0' end as \"PEAK_MEM_SEG_KB_LOG\",\n\n \"ROW_COUNT\",\n \"STATEMENT_HASH\",\n \"STATEMENT_STRING\"\n\nFROM (\n\n SELECT \n \n \"APPLICATION_USER_NAME\" as \"APP_USER\",\n \"USER_NAME\" as \"DB_USER\",\n \n \"LAST_EXECUTION_TIMESTAMP\" as \"START_TIME\",\n \n \tTO_DATE(\"LAST_EXECUTION_TIMESTAMP\") as \"DATE\",\n \tWEEKDAY(\"LAST_EXECUTION_TIMESTAMP\") as \"WEEKDAY\",\n \tWEEK(\"LAST_EXECUTION_TIMESTAMP\") as \"WEEK\",\n \tHOUR(\"LAST_EXECUTION_TIMESTAMP\") as \"HOUR\", \t\n \n \tTO_SECONDDATE(SUBSTRING(\"LAST_EXECUTION_TIMESTAMP\", 1, 19), 'YYYY-MM-DD HH:MI:SS') as \"DATE_TIME\",\n \tTO_SECONDDATE(SUBSTRING(\"LAST_EXECUTION_TIMESTAMP\", 1, 13), 'YYYY-MM-DD HH') as \"DATE_HOUR\",\n \n -- per default, the statistic figures are aggregated per identical mds statement\n -- If mds tracing is enabled, execution_count for a record is always 1 (no aggregation takes place)\n \tto_int(\"EXECUTION_COUNT\") as \"EXECUTION_COUNT\",\n \t\n -- request type: e.g. METADATA, ANALYTICS\n case\n when '{\"Analytics\":{\"Actions\":' = left(\"STATEMENT_STRING\",24) then 'Analytics-Actions'\n else substr_after(substr_before(\"STATEMENT_STRING\", '\":'), '\"') \n end as EXECUTION_TYPE,\n \n -- storyName/Id/Widget\n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"StoryName\":\"'),'\"') STORY_NAME,\n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"StoryId\":\"'),'\"') STORY_ID , \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"WidgetId\":['),']') as WIDGET_ID , \n \n -- use native string operations to save performance over json_value/json_query:\n concat( \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":'),'\"ObjectName\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"ObjectName\":\"'),'\"')\n ) as DATA_SOURCE_NAME,\n \n concat( \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":'),'\"SchemaName\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"SchemaName\":\"'),'\"')\n ) as DATA_SOURCE_SCHEMA,\n \n concat( \n substr_before(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"InstanceId\":\"'),'\"')\n ) as DATA_SOURCE_INSTANCE_ID,\n \n -- runtime and segments for reporting\n \"TOTAL_EXECUTION_TIME\" as \"RUNTIME\",\n \n \tto_int(\"TOTAL_EXECUTION_TIME\" / 1000) as \"RUNTIME_SEG_SECS\",\n \tcase \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t when log(10,\"TOTAL_EXECUTION_TIME\") - to_int(log(10,\"TOTAL_EXECUTION_TIME\")) < 0.477 then 3 \n \t when log(10,\"TOTAL_EXECUTION_TIME\") - to_int(log(10,\"TOTAL_EXECUTION_TIME\")) < 0.6989 then 5\n \t else 10 end as \"RUNTIME_SEG_LOG_DELTA\",\n \n case \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t else to_int(power(10,to_int(log(10,\"TOTAL_EXECUTION_TIME\")))) end as \"RUNTIME_SEG_LOG\",\n \n to_bigint(\"MAX_EXECUTION_MEMORY_SIZE\" / 1024 ) as \"PEAK_MEMORY_KB\",\n \n case \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t when log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024) - to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024)) < 0.477 then 3 \n \t when log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024) - to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024)) < 0.6989 then 5\n \t else 10 end as \"PEAK_MEM_SEG_KB_LOG_DELTA\",\n \n case \n \t when \"MAX_EXECUTION_MEMORY_SIZE\" = 0 then 0\n \t else to_int(power(10,to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\"/1024)))) end as \"PEAK_MEM_SEG_KB_LOG\",\n \n 1 as \"ROW_COUNT\",\n \"STATEMENT_HASH\",\n \"STATEMENT_STRING\"\n \n FROM \"M_MULTIDIMENSIONAL_STATEMENT_S\" as base )" }, "M_MULTIDIMENSIONAL_STATEMENT_S": { "kind": "entity", "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "PORT": { "@EndUserText.label": "Internal port", "type": "cds.Integer" }, "STATEMENT_STRING": { "@EndUserText.label": "Statement string", "type": "cds.LargeString" }, "STATEMENT_HASH": { "@EndUserText.label": "Statement hash", "type": "cds.String", "length": 32 }, "USER_NAME": { "@EndUserText.label": "User name", "type": "cds.String", "length": 256 }, "APPLICATION_USER_NAME": { "@EndUserText.label": "Application user name", "type": "cds.String", "length": 256 }, "APPLICATION_NAME": { "@EndUserText.label": "Application name", "type": "cds.String", "length": 256 }, "STATEMENT_TYPE": { "@EndUserText.label": "Statement type which can be either INA or MDX", "type": "cds.String", "length": 4 }, "LAST_CONNECTION_ID": { "@EndUserText.label": "Last connection ID that executed the statement", "type": "cds.Integer" }, "LAST_EXECUTION_TIMESTAMP": { "@EndUserText.label": "Last execution timestamp", "type": "cds.Timestamp" }, "LAST_METADATA_READ_DURATION": { "@EndUserText.label": "Last time spent in metadata read", "type": "cds.Integer64" }, "LAST_QUERY_PREPARATION_DURATION": { "@EndUserText.label": "Last time spent in query preparation", "type": "cds.Integer64" }, "LAST_PLAN_EXECUTION_DURATION": { "@EndUserText.label": "Last time spent in plan execution", "type": "cds.Integer64" }, "LAST_POST_PROCESSING_DURATION": { "@EndUserText.label": "Last time spent in post processing", "type": "cds.Integer64" }, "LAST_CUBE_PROCESSING_DURATION": { "@EndUserText.label": "Last time spent in cube processing", "type": "cds.Integer64" }, "LAST_PLAN_EXECUTION_RESULTSET_SIZE": { "@EndUserText.label": "Last size of the intermediate result returned by plan execution in bytes", "type": "cds.Integer64" }, "LAST_RESULTSET_SIZE": { "@EndUserText.label": "Last size of the resultset in bytes", "type": "cds.Integer64" }, "LAST_RESULTSET_CELL_COUNT": { "@EndUserText.label": "Last number of cells in the resultset", "type": "cds.Integer64" }, "LAST_HIERARCHY_COUNT": { "@EndUserText.label": "Last number of hierarchies", "type": "cds.Integer" }, "LAST_CACHED_HIERARCHY_COUNT": { "@EndUserText.label": "Last number of hierarchy cache hit", "type": "cds.Integer" }, "LAST_CALCULATION_ENTITY_COUNT": { "@EndUserText.label": "Last number of calculation entities", "type": "cds.Integer" }, "LAST_CALCULATION_ENTITY_GROUP_COUNT": { "@EndUserText.label": "Last number of calculation entity groups", "type": "cds.Integer" }, "LAST_DRILL_DIMENSION_COUNT": { "@EndUserText.label": "Last number of dimensions in drill", "type": "cds.Integer" }, "LAST_AGGREGATION_DIMENSION_COUNT": { "@EndUserText.label": "Last number of dimensions in aggregation", "type": "cds.Integer" }, "LAST_SERIALIZED_CUBE_SIZE": { "@EndUserText.label": "Last size of the serialized cube in bytes", "type": "cds.Integer64" }, "LAST_REQUEST_QUEUE_COUNT": { "@EndUserText.label": "Last number of jobs waiting in the request queue", "type": "cds.Integer64" }, "LAST_EXECUTION_STATUS": { "@EndUserText.label": "Last execution status of the statement", "type": "cds.String", "length": 16 }, "LAST_PERFORMANCE_DATA": { "@EndUserText.label": "Last performance metrics", "type": "cds.LargeString" }, "EXECUTION_COUNT": { "@EndUserText.label": "Execution count", "type": "cds.Integer" }, "TOTAL_METADATA_CACHE_HIT_COUNT": { "@EndUserText.label": "Total number of metadata cache hit", "type": "cds.Integer" }, "TOTAL_DATA_CACHE_HIT_COUNT": { "@EndUserText.label": "Total number of data cache hit", "type": "cds.Integer" }, "TOTAL_EXECUTION_TIME": { "@EndUserText.label": "Total time of execution", "type": "cds.Integer64" }, "AVG_EXECUTION_TIME": { "@EndUserText.label": "Average time of execution", "type": "cds.Integer64" }, "MIN_EXECUTION_TIME": { "@EndUserText.label": "Minimum time of execution", "type": "cds.Integer64" }, "MAX_EXECUTION_TIME": { "@EndUserText.label": "Maximum time of execution", "type": "cds.Integer64" }, "MAX_CALLED_THREAD_COUNT": { "@EndUserText.label": "Maximum thread count", "type": "cds.Integer" }, "MAX_EXECUTION_MEMORY_SIZE": { "@EndUserText.label": "Maximum memory size", "type": "cds.Integer64" } }, "@EndUserText.label": "M_MULTIDIMENSIONAL_STATEMENT_STATISTICS", "@ObjectModel.modelingPattern": { "#": "DATA_STRUCTURE" }, "@ObjectModel.supportedCapabilities": [ { "#": "DATA_STRUCTURE" } ], "@DataWarehouse.external.schema": "SYS", "@DataWarehouse.external.entity": "M_MULTIDIMENSIONAL_STATEMENT_STATISTICS" } }, "version": { "csn": "1.0" }, "meta": { "creator": "CDS Compiler v1.19.2" }, "$version": "1.0"}
WHERE START_TIME >
( SELECT
CASE WHEN MAX(START_TIME) IS NULL
THEN TO_DATE(0)
ELSE MAX(START_TIME) END
FROM HIST_TCT_INA_V_R_01 )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
20 | |
9 | |
9 | |
7 | |
7 | |
7 | |
7 | |
6 | |
5 | |
5 |