- SAP Managed Tags:
Introduction
This blog post series describes a concept on how to monitor your HANA system and consumption of SAP Datasphere. Starting from an analysis of the HANA system resource situation it will enable you to break down the resource consumption into types like reporting via Ina/MDS, Task Handling, Open SQL and more.
The target audience is the central SAP Datasphere Administrator (global perspective) or a key user of an implementation project, who likes to monitor his resource consumption (project perspective).
To facilitate the usage, this blog post provide a "How to Read" and "How to Interpret" section. The first one explains the figures displayed, the second one provides a proposal on how to rate and the results in your daily work.
HANA System - Overall Consumption
For any performance and resource optimization it is important to understand the overall system situation. Especially if you have several projects in parallel you need total figures of workload and tools to identify critical time slots.
As this kind of monitoring should include gradual "changes over time" as well as checks like "what is the resource situation now or yesterday?" there is the challenge of aggregating the detailed memory and CPU snapshots created by SAP HANA into a level suitable for daily or regular usages.
Below you see a proposal for display of CPU consumption:
Let us go through the data preparation and aggregation step-by-step to understand the figures:
Every measurement of CPU consumption is converted into a percentage of total available.
Example: The snapshot provides the information that during the measured time interval, the CPU was busy for 3secs and idle for 7sec. In this case we a usage of 30% (busy / (busy + idle)).
Assuming a measurement interval of 1min, this would still create 1440 data points per day. Too much for an "easy to use" level. Hence we further total the count of snapshots into 10% segments.
How to Read:
On the 23rd of May, there were 16 snapshots (= Minutes) with a CPU consumption between 70% and 80%.
How to Interpret:
Start with a threshold assumption of acceptable CPU consumption. Consider potential future growth and (if applicable) development tasks that you like to save guard. As an example I will use 70%.
Then you could describe a healthy state like this: "if we are below 70% for the majority of the day we are in a healthy state". For majority you could select another threshold like "The consumption should exceed 70% for not more than an hour.
With these assumption you could further simplify the sample to feed a tile (for check of today or yesterday) or, if interested in the changes over time, create a chart showing the count of above 70%.
Note: In addition, I would recommend to check the Out-of-Memory and Admission Control (Rejections) figures. They can be found in SAP Datasphere -> System Monitor.
If all aspects (CPU, Memory, Out-of-Memory and Admission Control) are rated as OK, we assume that the system overall is an healthy state.
Break Down into Spaces and Types (Ina, Open SQL, ...)
Assuming that you either like to reduce the resource consumption or that you have found peak times where the resource consumption is too high. A next step is to perform a breakdown of these resource consumptions.
The leading idea of the proposal is to annotate the schema name. Mapping a schema name to a space id provides you a grouping by space id and hence allow you to assign the resource consumption to space related activities.
We apply the annotation to the HANA statistic view M_EXPENSIVE_STATEMENTS. As this view contains columns like session variables and statement string, we can introduce an additional grouping by usage type: is the resource consumption inferred by tasks, Ina-MDS, OpenSQL and more.
Note: The view M_EXPENSIVE_STATEMENTS only captures a subset of requests. Hence the CPU consumption display is only a lower limit and hence only an indication. An example: many small requests could in total consume the same CPU as one expensive call. In the statistics below you will just see the one expensive call and miss the impact of the bunch of small request.
Note: Verify that you have enabled the M_EXPENSIVE_STATEMENTS traces and defined proper thresholds. The settings can be found in SAP Datasphere: System => Configuration => Monitoring
Group Expensive Statements by Space Id
Using the space-schema mapping table SPACE_SCHEMAS_V_EXT, we can group schemas by Space Id. As shown in the screenshot below, we can assign the CPU time captured in M_EXPENSIVE_STATEMENTS per schema to the related SAP Datasphere Space.
Note that there is a Space Id = Null. This indicates, that these schema names are not directly related to a space. Still, they could be related to SAP Datasphere like e.g. the DWC_TENTANT_OWNER.
Once you have identified an expensive space, you like to drill down into the next level of detail - understand where the resources are consumed. The column SCHEMA_TYPE provides you this next level of detail.
The following usage types are provided by a heuristic based on the user-, schema- and application name. They allow the assignment of entries of M_EXPENSIVE_STATEMENTS to several categories.
- Space-Task
- Space-Ina
- Space-Open-Sql
- Space-Others
- non-Space-Analysis-User
- non-Space-SDI
- non-Space-Others
How to Read:
Example 1: In the space SAP_CONTENT we have active OpenSQL user. The CPU time consumed by them is 1,8 secs.
Example 2: We have not space related CPU consumption - this is indicated by space id = Null. In the sample the below we see SDI related activities. These schemas are generated by e.g. Agent and not per space.
How to Interpret:
First of all it is "just" an overview in which space/schema which activities are ongoing. From a central perspective, this could be used to identify the contribution to resource consumption and, if rated as too high, approach the project/space responsible person.
When you have defined usages for spaces, e.g. one for task handling and a seperate one for reporting via SAC, the overview can be used to validate if the project is complied with the plan. If you see reporting activities in a space dedicated for task processing, approach the project and ask for clarification.
Note: The schema/user property being of type Open SQL is not exposed to the monitoring space as of now. Hence I have introduced a heuristic based on the current naming conventions for generated schema for a given space (Schema Name = <Space Id>#<Prefix>_<UID>). This fails for some old spaces, here the <Space Id># is missing.
Identify and Resolve: You could notify this situation in the space-schema mapping listed above. The space SAP_ADMIN is related to a space SAP_ADMIN#INA_<UID>. This follows the current naming convention. the space CONTENT_SPACE is related to space SPM_<UID> - here your see the missing leading space id. In case, adjust the SQL provided as part of this blog post and add this combination to the defined rules.
How to Implement:
The views require a monitoring space - either the SAP_ADMIN space or the one selected in System -> Configuration -> Monitoring.
Copy the content into a *.json file and import it into your monitoring space of choice.
HANA System - Overall Consumption
The CSN File containing the table and view definitions.
Select and copy the whole line, e.g. via shift-cursor down or triple click.
{ "definitions": { "SAP_TCT_HOST_LOAD_V_R_01": { "kind": "entity", "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "TIME_STAMP": { "@EndUserText.label": "Timestamp [UTC]", "type": "cds.Timestamp" }, "DATE_TIME": { "isCalculated": true, "@EndUserText.label": "Date-Time", "type": "cds.Date" }, "DATE": { "isCalculated": true, "@EndUserText.label": "Date", "type": "cds.Date" }, "HOUR": { "isCalculated": true, "@EndUserText.label": "Hour", "type": "cds.String", "length": 100 }, "DATE_HOUR": { "isCalculated": true, "@EndUserText.label": "Date-Hour", "type": "cds.Date" }, "WEEKDAY": { "isCalculated": true, "@EndUserText.label": "Weekday", "type": "cds.Integer" }, "WEEK": { "isCalculated": true, "@EndUserText.label": "Week", "type": "cds.Integer" }, "HANA_TOTAL_GB": { "@EndUserText.label": "HANA Total [GB]", "precision": 5, "scale": 2, "type": "cds.Decimal", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "HANA_USED_GB": { "@EndUserText.label": "HANA Used [GB]", "precision": 5, "scale": 2, "type": "cds.Decimal", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "HANA_USED_PCT_SEGMENT": { "@EndUserText.label": "Memory Used [%]", "type": "cds.String", "length": 100 }, "HANA_USED_PCT_GROUP": { "@EndUserText.label": "Memory Used [g-y-r]", "type": "cds.String", "length": 100 }, "CPU_TOTAL_S": { "@EndUserText.label": "CPU Total [sec]", "precision": 5, "scale": 2, "type": "cds.Decimal", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "CPU_BUSY_S": { "@EndUserText.label": "CPU Busy [sec]", "precision": 5, "scale": 2, "type": "cds.Decimal", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "CPU_BUSY_PCT_SEGMENT": { "@EndUserText.label": "CPU Busy [%]", "type": "cds.String", "length": 100 }, "RECORD_COUNT_ALL": { "@EndUserText.label": "Count", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } } }, "query": { "SELECT": { "from": { "SELECT": { "from": { "ref": [ "HOST_RESOURCE_UTILIZATION_STATISTICS" ] }, "columns": [ { "ref": [ "HOST" ] }, { "ref": [ "SNAPSHOT_ID" ], "as": "TIME_STAMP" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "SNAPSHOT_ID" ] }, { "val": 1 }, { "val": 19 } ] }, { "val": "YYYY-MM-DD HH:MI:SS" } ], "as": "DATE_TIME" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "SNAPSHOT_ID" ] }, { "val": 1 }, { "val": 13 } ] }, { "val": "YYYY-MM-DD HH" } ], "as": "DATE_HOUR" }, { "func": "TO_DATE", "args": [ { "ref": [ "SNAPSHOT_ID" ] } ], "as": "DATE" }, { "func": "WEEKDAY", "args": [ { "ref": [ "SNAPSHOT_ID" ] } ], "as": "WEEKDAY" }, { "func": "WEEK", "args": [ { "ref": [ "SNAPSHOT_ID" ] } ], "as": "WEEK" }, { "func": "HOUR", "args": [ { "ref": [ "SNAPSHOT_ID" ] } ], "as": "HOUR" }, { "xpr": [ { "ref": [ "ALLOCATION_LIMIT" ] }, "/", { "val": 1024 }, "/", { "val": 1024 }, "/", { "val": 1024 } ], "as": "ALLOC_LIM_GB" }, { "xpr": [ { "ref": [ "INSTANCE_TOTAL_MEMORY_USED_SIZE" ] }, "/", { "val": 1024 }, "/", { "val": 1024 }, "/", { "val": 1024 } ], "as": "HANA_MEM_USED_GB" }, { "xpr": [ { "func": "TO_DECIMAL", "args": [ { "xpr": [ { "ref": [ "INSTANCE_TOTAL_MEMORY_USED_SIZE" ] }, "/", { "ref": [ "ALLOCATION_LIMIT" ] }, "*", { "val": 10 } ] }, { "val": 10 }, { "val": 0 } ] }, "*", { "val": 10 } ], "as": "HANA_MEM_SEG" }, { "xpr": [ { "xpr": [ { "ref": [ "TOTAL_CPU_SYSTEM_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_USER_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_WIO_TIME_DELTA" ] } ] }, "/", { "val": 1000 }, "/", { "val": 1000 } ], "as": "CPU_BUSY_S" }, { "xpr": [ { "xpr": [ { "ref": [ "TOTAL_CPU_IDLE_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_SYSTEM_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_USER_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_WIO_TIME_DELTA" ] } ] }, "/", { "val": 1000 }, "/", { "val": 1000 } ], "as": "CPU_TOTAL_S" }, { "xpr": [ { "func": "TO_DECIMAL", "args": [ { "xpr": [ { "xpr": [ { "ref": [ "TOTAL_CPU_SYSTEM_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_USER_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_WIO_TIME_DELTA" ] } ] }, "/", { "xpr": [ { "ref": [ "TOTAL_CPU_IDLE_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_SYSTEM_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_USER_TIME_DELTA" ] }, "+", { "ref": [ "TOTAL_CPU_WIO_TIME_DELTA" ] } ] }, "*", { "val": 10 } ] }, { "val": 10 }, { "val": 0 } ] }, "*", { "val": 10 } ], "as": "HANA_CPU_SEG" } ] }, "as": "base" }, "columns": [ { "ref": [ "HOST" ] }, { "ref": [ "TIME_STAMP" ] }, { "ref": [ "DATE_TIME" ] }, { "ref": [ "DATE" ] }, { "ref": [ "HOUR" ] }, { "ref": [ "DATE_HOUR" ] }, { "ref": [ "WEEKDAY" ] }, { "ref": [ "WEEK" ] }, { "func": "TO_DECIMAL", "args": [ { "ref": [ "ALLOC_LIM_GB" ] }, { "val": 10 }, { "val": 2 } ], "as": "HANA_TOTAL_GB" }, { "func": "TO_DECIMAL", "args": [ { "ref": [ "HANA_MEM_USED_GB" ] }, { "val": 10 }, { "val": 2 } ], "as": "HANA_USED_GB" }, { "xpr": [ { "ref": [ "HANA_MEM_SEG" ] }, "||", { "val": " < " }, "||", { "ref": [ "HANA_MEM_SEG" ] }, "+", { "val": 10 }, "||", { "val": " %" } ], "as": "HANA_USED_PCT_SEGMENT" }, { "xpr": [ "case", "when", { "ref": [ "HANA_MEM_SEG" ] }, "<", { "val": 70 }, "then", { "val": " < 70" }, "when", { "ref": [ "HANA_MEM_SEG" ] }, ">=", { "val": 90 }, "then", { "val": ">= 90" }, "else", { "val": "70 < 90" }, "end" ], "as": "HANA_USED_PCT_GROUP" }, { "func": "TO_DECIMAL", "args": [ { "ref": [ "CPU_TOTAL_S" ] }, { "val": 10 }, { "val": 2 } ], "as": "CPU_TOTAL_S" }, { "func": "TO_DECIMAL", "args": [ { "ref": [ "CPU_BUSY_S" ] }, { "val": 10 }, { "val": 2 } ], "as": "CPU_BUSY_S" }, { "xpr": [ { "ref": [ "HANA_CPU_SEG" ] }, "||", { "val": " < " }, "||", { "ref": [ "HANA_CPU_SEG" ] }, "+", { "val": 10 }, "||", { "val": " %" } ], "as": "CPU_BUSY_PCT_SEGMENT" }, { "val": 1, "as": "RECORD_COUNT_ALL" } ] } }, "@EndUserText.label": "Monitoring: HANA CPU and Memory Overview", "@ObjectModel.modelingPattern": { "#": "ANALYTICAL_FACT" }, "@ObjectModel.supportedCapabilities": [ { "#": "ANALYTICAL_FACT" }, { "#": "ANALYTICAL_PROVIDER" } ], "@DataWarehouse.consumption.external": true, "@DataWarehouse.sqlEditor.query": "/*\n HANA System - CPU and Memory Consumption - Overview\n ---------------------------------------------------\n When to use: Looking for an overview of CPU and Memory consumption.\n What is shows: The memory and cpu figures that hana captures every minute, are converted in to relative consumption (in percentage).\n The percentages are grouped in 10% segments and a count is displayed\n \n SAMPLE OUTPUT: \n \n \t CPU Busy [%]\t | 0 < 10 %\t10 < 20 %\t20 < 30 %\t30 < 40 %\t40 < 50 %\n --------------------------------------------------------------------------------------------------------------\n Host name\t Date |\n 12345678-abcd-abcd-abcd-1234512345\t May 3, 2023\t | 1394\t 32\t 6\t 1\t 1\n\t May 4, 2023\t | 1371\t 41\t 6\t 14\t 6\n\n Example: On the May 3, we have 32 measurements (= Minutes), where the CPU consumption was 10% to 20% of the overall available CPU capacitry.\n \n The view is based on view: HOST_RESOURCE_UTILIZATION_STATISTICS\n https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/fbd82f0303c44e35a6bbb12cb... -- host name(s)\n \"HOST\",\n \n -- time columns for reporting\n \"TIME_STAMP\",\n\t\"DATE_TIME\",\n\t\"DATE\",\n\t\"HOUR\",\n\t\"DATE_HOUR\",\n\t\"WEEKDAY\",\n\t\"WEEK\",\n\t\n -- Memory figures\n\tTO_DECIMAL(\"ALLOC_LIM_GB\", 10, 2) as \"HANA_TOTAL_GB\",\n\tTO_DECIMAL(\"HANA_MEM_USED_GB\", 10, 2) as \"HANA_USED_GB\",\n\t\"HANA_MEM_SEG\" || ' < ' || \"HANA_MEM_SEG\" + 10 || ' %' as HANA_USED_PCT_SEGMENT,\n\t\n\t-- Count based on thresholds (proposed values 70% and 90%)\n\t-- Usage proposal: traffic light, adjust threshholds to your specific situation or introduce parameters\n\tcase\n\t when \"HANA_MEM_SEG\" < 70 then ' < 70'\n\t when \"HANA_MEM_SEG\" >= 90 then '>= 90'\n\t else '70 < 90' \n\tend as HANA_USED_PCT_GROUP,\n\t\n -- CPU figures\n\tTO_DECIMAL(\"CPU_TOTAL_S\", 10, 2) as \"CPU_TOTAL_S\",\n\tTO_DECIMAL(\"CPU_BUSY_S\", 10, 2) as \"CPU_BUSY_S\",\n\t\"HANA_CPU_SEG\" || ' < ' || \"HANA_CPU_SEG\" + 10 || ' %' as CPU_BUSY_PCT_SEGMENT,\n\t\n\t1 as RECORD_COUNT_ALL\n\t\n FROM (\n \n\t\tSELECT \n\t\t -- host name\n\t\t \"HOST\",\n\t\t \n -- time columns for reporting\n SNAPSHOT_ID \"TIME_STAMP\",\n\t\t\tTO_SECONDDATE(SUBSTRING(SNAPSHOT_ID, 1, 19), 'YYYY-MM-DD HH:MI:SS') as \"DATE_TIME\",\n\t\t\tTO_SECONDDATE(SUBSTRING(SNAPSHOT_ID, 1, 13), 'YYYY-MM-DD HH') as \"DATE_HOUR\",\n \tTO_DATE(\"SNAPSHOT_ID\") as \"DATE\",\n \tWEEKDAY(\"SNAPSHOT_ID\") as \"WEEKDAY\",\n \tWEEK(\"SNAPSHOT_ID\") as \"WEEK\",\n \tHOUR(\"SNAPSHOT_ID\") as \"HOUR\", \n \n -- HANA memory figures: available and in-use\n\t\t\tALLOCATION_LIMIT / 1024 / 1024 / 1024 as ALLOC_LIM_GB,\n\t\t\tINSTANCE_TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024 as HANA_MEM_USED_GB, \n\t\t\t\n -- Memory consumption in percentage, rounded into full 10% segments: e.g. 85% => 80%\n\t\t\tTO_DECIMAL(INSTANCE_TOTAL_MEMORY_USED_SIZE / ALLOCATION_LIMIT * 10, 10, 0) * 10 as HANA_MEM_SEG, \n\n -- CPU figures: available and in-use\n (TOTAL_CPU_SYSTEM_TIME_DELTA + TOTAL_CPU_USER_TIME_DELTA + TOTAL_CPU_WIO_TIME_DELTA) / 1000 / 1000 as CPU_BUSY_S, \n (TOTAL_CPU_IDLE_TIME_DELTA + TOTAL_CPU_SYSTEM_TIME_DELTA + TOTAL_CPU_USER_TIME_DELTA + TOTAL_CPU_WIO_TIME_DELTA) / 1000 / 1000 as CPU_TOTAL_S, \n\t\t\t\n -- CPU consumption in percentage, rounded into full 10% segments: e.g. 85% => 80%\n\t\t\tTO_DECIMAL((TOTAL_CPU_SYSTEM_TIME_DELTA + TOTAL_CPU_USER_TIME_DELTA + TOTAL_CPU_WIO_TIME_DELTA) / (TOTAL_CPU_IDLE_TIME_DELTA + TOTAL_CPU_SYSTEM_TIME_DELTA + TOTAL_CPU_USER_TIME_DELTA + TOTAL_CPU_WIO_TIME_DELTA) * 10, 10, 0) * 10 as HANA_CPU_SEG\n\t\t\t\n\t\tFROM \"HOST_RESOURCE_UTILIZATION_STATISTICS\"\n\t\t\n\t) AS base" }, "HOST_RESOURCE_UTILIZATION_STATISTICS": { "kind": "entity", "elements": { "SNAPSHOT_ID": { "type": "cds.Timestamp", "@EndUserText.label": "Timestamp (UTC) identifying when the statistics service performed a data collection. Data collections scheduled for the same time have the same snapshot ID." }, "SERVER_TIMESTAMP": { "type": "cds.Timestamp", "@EndUserText.label": "Timestamp (local server time) identifying when a data collection actually occurred on the server." }, "INDEX": { "type": "cds.String", "@EndUserText.label": "Unique identifier for a row of the result set that a particular snapshot of a data collection represents.", "length": 76 }, "TOTAL_CPU_IDLE_TIME_DELTA": { "type": "cds.Integer64", "@EndUserText.label": "Total CPU idle time" }, "TOTAL_CPU_SYSTEM_TIME_DELTA": { "type": "cds.Integer64", "@EndUserText.label": "Total CPU system time" }, "TOTAL_CPU_USER_TIME_DELTA": { "type": "cds.Integer64", "@EndUserText.label": "Total CPU user time" }, "TOTAL_CPU_WIO_TIME_DELTA": { "type": "cds.Integer64", "@EndUserText.label": "Total IO time" }, "SNAPSHOT_DELTA": { "type": "cds.Integer64", "@EndUserText.label": "Time elapsed" }, "ACTIVE_ASYNC_IO_COUNT": { "type": "cds.Integer64", "@EndUserText.label": "Number of asynchronous input/output requests on the host. Kernel param.: fs.ai-nr. See async_io_limit in M_HOST_INFORMATION" }, "ALLOCATION_LIMIT": { "type": "cds.Integer64", "@EndUserText.label": "Allocation limit for all processes" }, "FAST_RESTART_MEMORY_FILE_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Size of the memory pool dedicated for FRO (fast restart option) owned by all SAP HANA processes" }, "FAST_RESTART_MEMORY_MAPPED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Amount of memory from the memory pool dedicated for FRO (fast restart option) that is currently mapped to SAP HANA processes" }, "FAST_RESTART_MEMORY_PEAK_FILE_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Peak memory from the memory pool dedicated for FRO (fast restart option) that was owned by SAP HANA processes since the start of the instance (sample based value)" }, "FAST_RESTART_MEMORY_PEAK_MAPPED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Peak memory from the memory pool dedicated for FRO (fast restart option) that was mapped to SAP HANA processes since the start of the instance (sample based value)" }, "FREE_PHYSICAL_MEMORY": { "type": "cds.Integer64", "@EndUserText.label": "Free physical memory on the host" }, "FREE_SWAP_SPACE": { "type": "cds.Integer64", "@EndUserText.label": "Free swap memory on the host" }, "HOST": { "type": "cds.String", "@EndUserText.label": "Host name", "length": 64 }, "INSTANCE_CODE_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Code size, including shared libraries of SAP HANA processes" }, "INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Shared memory size of SAP HANA processes" }, "INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Size of the memory pool for all SAP HANA processes" }, "INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Peak memory from the memory pool that was in use by SAP HANA processes since start of the instance (sample based value)" }, "INSTANCE_TOTAL_MEMORY_USED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Amount of memory from the memory pool that is currently in actual use by SAP HANA processes" }, "OPEN_FILE_COUNT": { "type": "cds.Integer64", "@EndUserText.label": "Number of allocated file handles on the host. Kernel param.: fs.file-nr. See open_file_limit in M_HOST_INFORMATION" }, "PERSISTENT_MEMORY_FILE_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Size of the memory pool dedicated for persistent memory owned by all SAP HANA processes" }, "PERSISTENT_MEMORY_MAPPED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Amount of memory from the memory pool dedicated for persistent memory that is currently mapped to SAP HANA processes" }, "PERSISTENT_MEMORY_PEAK_FILE_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Peak memory from the memory pool dedicated for persistent memory that was owned by SAP HANA processes since the start of the instance (sample based value)" }, "PERSISTENT_MEMORY_PEAK_MAPPED_SIZE": { "type": "cds.Integer64", "@EndUserText.label": "Peak memory from the memory pool dedicated for persistent memory that was mapped to SAP HANA processes since the start of the instance (sample based value)" }, "SITE_ID": { "type": "cds.Integer", "@EndUserText.label": "ID identifying the site from which the data were collected." }, "SYS_TIMESTAMP": { "type": "cds.Timestamp", "@EndUserText.label": "Host timestamp in local time zone" }, "USED_PHYSICAL_MEMORY": { "type": "cds.Integer64", "@EndUserText.label": "Used physical memory on the host" }, "USED_SWAP_SPACE": { "type": "cds.Integer64", "@EndUserText.label": "Used swap memory on the host" }, "UTC_TIMESTAMP": { "type": "cds.Timestamp", "@EndUserText.label": "Host timestamp in UTC" } }, "@DataWarehouse.external.schema": "_SYS_STATISTICS", "@DataWarehouse.external.entity": "HOST_RESOURCE_UTILIZATION_STATISTICS", "@EndUserText.label": "HOST_RESOURCE_UTILIZATION_STATISTICS" } }, "version": { "csn": "1.0" }, "meta": { "creator": "CDS Compiler v1.19.2" }, "$version": "1.0"}HANA System - Break Down into Usage Types
The CSN File containing the table and view definitions.
Select and copy the whole line, e.g. via shift-cursor down or triple click.
{ "definitions": { "SAP_TCT_EXP_STATM_V_R_01": { "kind": "entity", "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "PORT": { "@EndUserText.label": "Internal port", "type": "cds.Integer" }, "SCHEMA_NAME": { "@EndUserText.label": "Schema name", "type": "cds.String", "length": 256 }, "SPACE_ID": { "@EndUserText.label": "Space Id", "type": "cds.String", "length": 64, "notNull": true }, "IS_SPACE_SCHEMA": { "@EndUserText.label": "Is Space Schema", "type": "cds.Boolean" }, "IS_TASK": { "@EndUserText.label": "Is Task", "type": "cds.Boolean" }, "IS_INA_CALL": { "@EndUserText.label": "Is Ina Call", "type": "cds.Boolean" }, "IS_SCHEMA_OPEN_SQL": { "@EndUserText.label": "Is Schema Open Sql", "type": "cds.Boolean" }, "SCHEMA_TYPE": { "@EndUserText.label": "Schema Type", "type": "cds.String", "length": 100 }, "IS_SDI": { "@EndUserText.label": "Is SDI", "type": "cds.Boolean" }, "IS_SCHEMA_ANALYSIS": { "@EndUserText.label": "Is Schema Analysis", "type": "cds.Boolean" }, "CONNECTION_ID": { "@EndUserText.label": "Connection ID", "type": "cds.Integer" }, "TRANSACTION_ID": { "@EndUserText.label": "Transaction object ID", "type": "cds.Integer" }, "UPDATE_TRANSACTION_ID": { "@EndUserText.label": "Write transaction ID", "type": "cds.Integer64" }, "STATEMENT_ID": { "@EndUserText.label": "Statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_EXECUTION_ID": { "@EndUserText.label": "Execution id of statement", "type": "cds.Integer64" }, "PARENT_STATEMENT_ID": { "@EndUserText.label": "Parent statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_HASH": { "@EndUserText.label": "Statement Hash", "type": "cds.String", "length": 32 }, "DB_USER": { "@EndUserText.label": "User name", "type": "cds.String", "length": 256 }, "APP_USER": { "@EndUserText.label": "Application user name", "type": "cds.String", "length": 256 }, "START_TIME": { "@EndUserText.label": "Statement start time", "type": "cds.Timestamp" }, "DURATION_MICROSEC": { "@EndUserText.label": "Duration [us]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "OBJECT_NAME": { "@EndUserText.label": "Related objects", "type": "cds.String", "length": 5000 }, "OPERATION": { "@EndUserText.label": "Type of operation", "type": "cds.String", "length": 5000 }, "RECORDS": { "@EndUserText.label": "Number of records", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_STRING": { "@EndUserText.label": "Statement string", "type": "cds.LargeString" }, "PARAMETERS": { "@EndUserText.label": "Parameters", "type": "cds.String", "length": 5000 }, "ERROR_CODE": { "@EndUserText.label": "Error code", "type": "cds.Integer" }, "ERROR_TEXT": { "@EndUserText.label": "Error message", "type": "cds.String", "length": 5000 }, "LOCK_WAIT_COUNT": { "@EndUserText.label": "Accumulated lock wait count", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "LOCK_WAIT_DURATION": { "@EndUserText.label": "Accumulated lock wait duration", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "ALLOC_MEM_SIZE_ROWSTORE": { "@EndUserText.label": "Allocated memory size for row store", "type": "cds.Integer64" }, "ALLOC_MEM_SIZE_COLSTORE": { "@EndUserText.label": "Allocated memory size for column store", "type": "cds.Integer64" }, "MEMORY_SIZE": { "@EndUserText.label": "Peak memory [Byte]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "REUSED_MEMORY_SIZE": { "@EndUserText.label": "Memory reused from cached data structures, 0 if caches empty", "type": "cds.Integer64" }, "CPU_TIME": { "@EndUserText.label": "CPU time [us]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "PASSPORT_ROOT_CONTEXT_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying source of request", "type": "cds.String", "length": 32 }, "PASSPORT_TRANSACTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying business transaction", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying connection", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_COUNTER": { "@EndUserText.label": "SAP EPP Passport connection counter", "type": "cds.Integer64" }, "STATEMENT_START_TIME": { "@EndUserText.label": "Time when the first OPERATION of a statement execution started. All OPERATIONs of a statement execution share the same STATEMENT_START_TIME.", "type": "cds.Timestamp" }, "APPLICATION_SOURCE": { "@EndUserText.label": "Application Source", "type": "cds.String", "length": 256 }, "APPLICATION_NAME": { "@EndUserText.label": "Application Name", "type": "cds.String", "length": 256 }, "NETWORK_MESSAGE_ID": { "@EndUserText.label": "Client message id", "type": "cds.Integer64" }, "WORKLOAD_CLASS_NAME": { "@EndUserText.label": "Effective workload class", "type": "cds.String", "length": 256 }, "PRIORITY": { "@EndUserText.label": "Effective statement priority", "type": "cds.Integer" }, "STATEMENT_THREAD_LIMIT": { "@EndUserText.label": "Effective statement thread limit", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_MEMORY_LIMIT": { "@EndUserText.label": "Effective statement memory limit", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "SESSION_VARIABLES": { "@EndUserText.label": "Session variables", "type": "cds.LargeString" }, "TABLE_TYPES": { "@EndUserText.label": "Table types", "type": "cds.String", "length": 32 }, "ROW_COUNT": { "@EndUserText.label": "ROW_COUNT", "type": "cds.Integer" } }, "query": { "SELECT": { "from": { "ref": [ "SAP_TCT_EXP_STATM_V_H_01" ] }, "columns": [ { "ref": [ "HOST" ] }, { "ref": [ "PORT" ] }, { "ref": [ "SCHEMA_NAME" ] }, { "ref": [ "SPACE_ID" ] }, { "ref": [ "IS_SPACE_SCHEMA" ] }, { "ref": [ "IS_TASK" ] }, { "ref": [ "IS_INA_CALL" ] }, { "ref": [ "IS_SCHEMA_OPEN_SQL" ] }, { "xpr": [ "case", "when", { "ref": [ "IS_SPACE_SCHEMA" ] }, "=", { "val": "true" }, "then", "case", "when", { "ref": [ "IS_TASK" ] }, "=", { "val": "true" }, "then", { "val": "Space-Task" }, "when", { "ref": [ "IS_INA_CALL" ] }, "=", { "val": "true" }, "then", { "val": "Space-Ina" }, "when", { "ref": [ "IS_SCHEMA_OPEN_SQL" ] }, "=", { "val": "true" }, "then", { "val": "Space-Open-Sql" }, "else", { "val": "Space-Others" }, "end", "else", "case", "when", { "ref": [ "IS_SDI" ] }, "=", { "val": "true" }, "then", { "val": "non-Space SDI" }, "when", { "ref": [ "IS_SCHEMA_ANALYSIS" ] }, "=", { "val": "true" }, "then", { "val": "non-Space Analysis User" }, "else", { "val": "non-Space-Others" }, "end", "end" ], "as": "SCHEMA_TYPE" }, { "ref": [ "IS_SDI" ] }, { "ref": [ "IS_SCHEMA_ANALYSIS" ] }, { "ref": [ "CONNECTION_ID" ] }, { "ref": [ "TRANSACTION_ID" ] }, { "ref": [ "UPDATE_TRANSACTION_ID" ] }, { "ref": [ "STATEMENT_ID" ] }, { "ref": [ "STATEMENT_EXECUTION_ID" ] }, { "ref": [ "PARENT_STATEMENT_ID" ] }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "DB_USER" ] }, { "ref": [ "APP_USER" ] }, { "ref": [ "START_TIME" ] }, { "ref": [ "DURATION_MICROSEC" ] }, { "ref": [ "OBJECT_NAME" ] }, { "ref": [ "OPERATION" ] }, { "ref": [ "RECORDS" ] }, { "ref": [ "STATEMENT_STRING" ] }, { "ref": [ "PARAMETERS" ] }, { "ref": [ "ERROR_CODE" ] }, { "ref": [ "ERROR_TEXT" ] }, { "ref": [ "LOCK_WAIT_COUNT" ] }, { "ref": [ "LOCK_WAIT_DURATION" ] }, { "ref": [ "ALLOC_MEM_SIZE_ROWSTORE" ] }, { "ref": [ "ALLOC_MEM_SIZE_COLSTORE" ] }, { "ref": [ "MEMORY_SIZE" ] }, { "ref": [ "REUSED_MEMORY_SIZE" ] }, { "ref": [ "CPU_TIME" ] }, { "ref": [ "PASSPORT_ROOT_CONTEXT_ID" ] }, { "ref": [ "PASSPORT_TRANSACTION_ID" ] }, { "ref": [ "PASSPORT_CONNECTION_ID" ] }, { "ref": [ "PASSPORT_CONNECTION_COUNTER" ] }, { "ref": [ "STATEMENT_START_TIME" ] }, { "ref": [ "APPLICATION_SOURCE" ] }, { "ref": [ "APPLICATION_NAME" ] }, { "ref": [ "NETWORK_MESSAGE_ID" ] }, { "ref": [ "WORKLOAD_CLASS_NAME" ] }, { "ref": [ "PRIORITY" ] }, { "ref": [ "STATEMENT_THREAD_LIMIT" ] }, { "ref": [ "STATEMENT_MEMORY_LIMIT" ] }, { "ref": [ "SESSION_VARIABLES" ] }, { "ref": [ "TABLE_TYPES" ] }, { "ref": [ "ROW_COUNT" ] } ] } }, "@EndUserText.label": "SAP_TCT_EXP_STATM_V_R_01", "@ObjectModel.modelingPattern": { "#": "ANALYTICAL_FACT" }, "@ObjectModel.supportedCapabilities": [ { "#": "ANALYTICAL_FACT" }, { "#": "ANALYTICAL_PROVIDER" } ], "@DataWarehouse.consumption.external": true, "@DataWarehouse.sqlEditor.query": "SELECT \"HOST\",\n\t\"PORT\",\n\t\"SCHEMA_NAME\",\n\t\n\t\"SPACE_ID\",\n\t\"IS_SPACE_SCHEMA\",\n\t\"IS_TASK\",\n\t\"IS_INA_CALL\",\n\t\"IS_SCHEMA_OPEN_SQL\",\n\t\n\tcase\n\t when \"IS_SPACE_SCHEMA\" = 'true' then\n\t case\n\t when \"IS_TASK\" = 'true' then 'Space-Task'\n\t when \"IS_INA_CALL\" = 'true' then 'Space-Ina'\n\t when \"IS_SCHEMA_OPEN_SQL\" = 'true' then 'Space-Open-Sql'\n\t else 'Space-Others' \n\t end \n\t else \n\t case \n\t when \"IS_SDI\" = 'true' then 'non-Space SDI'\n\t when \"IS_SCHEMA_ANALYSIS\" = 'true' then 'non-Space Analysis User'\n\t else 'non-Space-Others' \n\t end \n\t end as \"SCHEMA_TYPE\",\n\t \n\t\"IS_SDI\",\n\t\"IS_SCHEMA_ANALYSIS\",\n\t\n\t\"CONNECTION_ID\",\n\t\"TRANSACTION_ID\",\n\t\"UPDATE_TRANSACTION_ID\",\n\t\"STATEMENT_ID\",\n\t\"STATEMENT_EXECUTION_ID\",\n\t\"PARENT_STATEMENT_ID\",\n\t\"STATEMENT_HASH\",\n\t\"DB_USER\",\n\t\"APP_USER\",\n\t\"START_TIME\",\n\t\"DURATION_MICROSEC\",\n\t\"OBJECT_NAME\",\n\t\"OPERATION\",\n\t\"RECORDS\",\n\t\"STATEMENT_STRING\",\n\t\"PARAMETERS\",\n\t\"ERROR_CODE\",\n\t\"ERROR_TEXT\",\n\t\"LOCK_WAIT_COUNT\",\n\t\"LOCK_WAIT_DURATION\",\n\t\"ALLOC_MEM_SIZE_ROWSTORE\",\n\t\"ALLOC_MEM_SIZE_COLSTORE\",\n\t\"MEMORY_SIZE\",\n\t\"REUSED_MEMORY_SIZE\",\n\t\"CPU_TIME\",\n\t\"PASSPORT_ROOT_CONTEXT_ID\",\n\t\"PASSPORT_TRANSACTION_ID\",\n\t\"PASSPORT_CONNECTION_ID\",\n\t\"PASSPORT_CONNECTION_COUNTER\",\n\t\"STATEMENT_START_TIME\",\n\t\"APPLICATION_SOURCE\",\n\t\"APPLICATION_NAME\",\n\t\"NETWORK_MESSAGE_ID\",\n\t\"WORKLOAD_CLASS_NAME\",\n\t\"PRIORITY\",\n\t\"STATEMENT_THREAD_LIMIT\",\n\t\"STATEMENT_MEMORY_LIMIT\",\n\t\"SESSION_VARIABLES\",\n\t\"TABLE_TYPES\",\n\t\"ROW_COUNT\"\nFROM \"SAP_TCT_EXP_STATM_V_H_01\"" }, "SPACE_SCHEMAS_V_EXT": { "kind": "entity", "elements": { "SPACE_ID": { "@EndUserText.label": "SPACE_ID", "type": "cds.String", "length": 64, "notNull": true }, "SCHEMA_NAME": { "@EndUserText.label": "SCHEMA_NAME", "type": "cds.String", "length": 256, "notNull": true } }, "@EndUserText.label": "SPACE_SCHEMAS_V_EXT", "@ObjectModel.modelingPattern": { "#": "DATA_STRUCTURE" }, "@ObjectModel.supportedCapabilities": [ { "#": "DATA_STRUCTURE" } ], "@DataWarehouse.external.schema": "DWC_GLOBAL", "@DataWarehouse.external.entity": "SPACE_SCHEMAS_V_EXT" }, "SAP_TCT_SCHEMA_V_H_01": { "kind": "entity", "elements": { "SPACE_ID": { "@EndUserText.label": "SPACE_ID", "type": "cds.String", "length": 64, "notNull": true }, "SCHEMA_NAME": { "@EndUserText.label": "SCHEMA_NAME", "type": "cds.String", "length": 256, "notNull": true }, "SCHEMA_TYPE": { "@EndUserText.label": "SCHEMA_TYPE", "type": "cds.String", "length": 100 } }, "query": { "SELECT": { "from": { "ref": [ "SPACE_SCHEMAS_V_EXT" ] }, "columns": [ { "ref": [ "SPACE_ID" ] }, { "ref": [ "SCHEMA_NAME" ] }, { "xpr": [ "case", "when", { "ref": [ "SPACE_ID" ] }, "<>", { "ref": [ "SCHEMA_NAME" ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#INA_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#INA_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#BL_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#BL_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#DIS_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#DIS_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#SAC_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#SAC_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#SPM_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#SPM_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#WRG_" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "#WRG_" } ] } ] } ] }, "and", { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "$TEC" } ] }, "<>", { "func": "left", "args": [ { "ref": [ "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "func": "concat", "args": [ { "ref": [ "SPACE_ID" ] }, { "val": "$TEC" } ] } ] } ] }, "then", { "val": "OPEN_SQL" }, "else", { "val": "OTHER" }, "end" ], "as": "SCHEMA_TYPE" } ] } }, "@EndUserText.label": "SAP_TCT_SCHEMA_V_H_01", "@ObjectModel.modelingPattern": { "#": "DATA_STRUCTURE" }, "@ObjectModel.supportedCapabilities": [ { "#": "DATA_STRUCTURE" } ], "@DataWarehouse.consumption.external": false, "@DataWarehouse.sqlEditor.query": "/*\n The table SPACE_SCHEMAS_V_EXT provides a list of schemas that are used in the space context. The list includes\n all schemas generated by SAP Datasphere for a space (like Ina/MDS, DataFlow, Spacemanagement, etc.) and in \n addition all Open SQL Schemas. It doesn't contain schemas without space dependencies like Database Analysis User, \n tenant management, SDI Activities and more or schemas not related to SAP Datasphere.\n \n SCHEMA_TYPE: Based on the naming convention for generated schemas, a heuristic is implemented to \n determine a type - list of values: OPEN_SQL, OTHERS:\n \n - OPEN_SQL: As there is currently no table for the schema type,\n the heuristic assumes OPEN SQL if it doesn't comply with any naming convention. \n \n Known limitations: \n Older spaces doesn't follow the naming convention and will be tagged as OPEN SQL. In this\n case you might want to add them manually.\n\n Documentation for SPACE_SCHEMAS_V_EXT: \n https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/4ab45090c5684ebf8765757a1d... \n\n \"SPACE_ID\",\n\t\"SCHEMA_NAME\",\n\t\n -- heuristic for OPEN SQL Schema\t\n\tcase\n when \"SPACE_ID\" <> \"SCHEMA_NAME\"\n and concat(\"SPACE_ID\",'#INA_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#INA_'))) \n and concat(\"SPACE_ID\",'#BL_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#BL_' ))) \n and concat(\"SPACE_ID\",'#DIS_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#DIS_'))) \n and concat(\"SPACE_ID\",'#SAC_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#SAC_'))) \n and concat(\"SPACE_ID\",'#SPM_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#SPM_'))) \n and concat(\"SPACE_ID\",'#WRG_') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'#WRG_'))) \n and concat(\"SPACE_ID\",'$TEC') <> left(\"SCHEMA_NAME\",length(concat(\"SPACE_ID\",'$TEC' ))) \n then 'OPEN_SQL'\n else 'OTHER' end as \"SCHEMA_TYPE\"\t\n\t\nFROM \"SPACE_SCHEMAS_V_EXT\"" }, "M_EXPENSIVE_STATEMENTS": { "kind": "entity", "@ObjectModel.supportedCapabilities": [ { "#": "DATA_STRUCTURE" } ], "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "PORT": { "@EndUserText.label": "Internal port", "type": "cds.Integer" }, "SCHEMA_NAME": { "@EndUserText.label": "Schema name", "type": "cds.String", "length": 256 }, "CONNECTION_ID": { "@EndUserText.label": "Connection ID", "type": "cds.Integer" }, "TRANSACTION_ID": { "@EndUserText.label": "Transaction object ID", "type": "cds.Integer" }, "UPDATE_TRANSACTION_ID": { "@EndUserText.label": "Write transaction ID (This number is ever increasing)", "type": "cds.Integer64" }, "STATEMENT_ID": { "@EndUserText.label": "Statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_EXECUTION_ID": { "@EndUserText.label": "Execution id of statement", "type": "cds.Integer64" }, "PARENT_STATEMENT_ID": { "@EndUserText.label": "Parent statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_HASH": { "@EndUserText.label": "Unique identifier for an SQL string", "type": "cds.String", "length": 32 }, "DB_USER": { "@EndUserText.label": "User name", "type": "cds.String", "length": 256 }, "APP_USER": { "@EndUserText.label": "Application user name", "type": "cds.String", "length": 256 }, "START_TIME": { "@EndUserText.label": "Statement start time", "type": "cds.Timestamp" }, "DURATION_MICROSEC": { "@EndUserText.label": "Statement duration", "type": "cds.Integer64" }, "OBJECT_NAME": { "@EndUserText.label": "Related objects", "type": "cds.String", "length": 5000 }, "OPERATION": { "@EndUserText.label": "Type of operation: e.g. prepare, execute, fetch, close", "type": "cds.String", "length": 5000 }, "RECORDS": { "@EndUserText.label": "Number of records", "type": "cds.Integer64" }, "STATEMENT_STRING": { "@EndUserText.label": "Statement string", "type": "cds.LargeString" }, "PARAMETERS": { "@EndUserText.label": "Parameters", "type": "cds.String", "length": 5000 }, "ERROR_CODE": { "@EndUserText.label": "Error code", "type": "cds.Integer" }, "ERROR_TEXT": { "@EndUserText.label": "Error message", "type": "cds.String", "length": 5000 }, "LOCK_WAIT_COUNT": { "@EndUserText.label": "Accumulated lock wait count", "type": "cds.Integer64" }, "LOCK_WAIT_DURATION": { "@EndUserText.label": "Accumulated lock wait duration", "type": "cds.Integer64" }, "ALLOC_MEM_SIZE_ROWSTORE": { "@EndUserText.label": "Allocated memory size for row store", "type": "cds.Integer64" }, "ALLOC_MEM_SIZE_COLSTORE": { "@EndUserText.label": "Allocated memory size for column store", "type": "cds.Integer64" }, "MEMORY_SIZE": { "@EndUserText.label": "Peak memory reached during statement execution. The maximum peak among all involved services is shown.", "type": "cds.Integer64" }, "REUSED_MEMORY_SIZE": { "@EndUserText.label": "Memory reused from cached data structures, 0 if caches empty", "type": "cds.Integer64" }, "CPU_TIME": { "@EndUserText.label": "CPU time (us) consumed to compute the statement", "type": "cds.Integer64" }, "PASSPORT_ROOT_CONTEXT_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying source of request", "type": "cds.String", "length": 32 }, "PASSPORT_TRANSACTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying business transaction", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying connection", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_COUNTER": { "@EndUserText.label": "SAP EPP Passport connection counter", "type": "cds.Integer64" }, "STATEMENT_START_TIME": { "@EndUserText.label": "Time when the first OPERATION of a statement execution started. All OPERATIONs of a statement execution share the same STATEMENT_START_TIME.", "type": "cds.Timestamp" }, "APPLICATION_SOURCE": { "@EndUserText.label": "Application can define from which source file SAP HANA is called. Usage is up to the application. Value also displayed in M_PREPARED_STATEMENTS.APPLICATION_SOURCE", "type": "cds.String", "length": 256 }, "APPLICATION_NAME": { "@EndUserText.label": "Name of the application", "type": "cds.String", "length": 256 }, "NETWORK_MESSAGE_ID": { "@EndUserText.label": "The client message id on the physical server connection, also found in M_SQL_CLIENT_NETWORK_IO. MESSAGE_ID", "type": "cds.Integer64" }, "WORKLOAD_CLASS_NAME": { "@EndUserText.label": "Name of effective workload class", "type": "cds.String", "length": 256 }, "PRIORITY": { "@EndUserText.label": "Effective statement priority", "type": "cds.Integer" }, "STATEMENT_THREAD_LIMIT": { "@EndUserText.label": "Effective statement thread limit", "type": "cds.Integer" }, "STATEMENT_MEMORY_LIMIT": { "@EndUserText.label": "Effective statement memory limit", "type": "cds.Integer" }, "SESSION_VARIABLES": { "@EndUserText.label": "Session variables", "type": "cds.LargeString" }, "TABLE_TYPES": { "@EndUserText.label": "Table types referenced by the statement, represented by a combination of values: ROW, COLUMN, VIRTUAL", "type": "cds.String", "length": 32 } }, "@EndUserText.label": "M_EXPENSIVE_STATEMENTS", "@ObjectModel.modelingPattern": { "#": "DATA_STRUCTURE" }, "@DataWarehouse.external.schema": "SYS", "@DataWarehouse.external.entity": "M_EXPENSIVE_STATEMENTS" }, "SAP_TCT_EXP_STATM_V_H_01": { "kind": "entity", "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "PORT": { "@EndUserText.label": "Internal port", "type": "cds.Integer" }, "SCHEMA_NAME": { "@EndUserText.label": "Schema name", "type": "cds.String", "length": 256 }, "SPACE_ID": { "@EndUserText.label": "Space Id", "type": "cds.String", "length": 64, "notNull": true }, "IS_SPACE_SCHEMA": { "@EndUserText.label": "Is Space Schema", "type": "cds.Boolean" }, "IS_TASK": { "@EndUserText.label": "IS_TASK", "type": "cds.Boolean" }, "IS_INA_CALL": { "@EndUserText.label": "IS_INA_CALL", "type": "cds.Boolean" }, "IS_SDI": { "@EndUserText.label": "IS_SDI", "type": "cds.Boolean" }, "IS_SCHEMA_ANALYSIS": { "@EndUserText.label": "IS_SCHEMA_ANALYSIS", "type": "cds.Boolean" }, "IS_SCHEMA_OPEN_SQL": { "@EndUserText.label": "IS_SCHEMA_OPEN_SQL", "type": "cds.Boolean" }, "CONNECTION_ID": { "@EndUserText.label": "Connection ID", "type": "cds.Integer" }, "TRANSACTION_ID": { "@EndUserText.label": "Transaction object ID", "type": "cds.Integer" }, "UPDATE_TRANSACTION_ID": { "@EndUserText.label": "Write transaction ID", "type": "cds.Integer64" }, "STATEMENT_ID": { "@EndUserText.label": "Statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_EXECUTION_ID": { "@EndUserText.label": "Execution id of statement", "type": "cds.Integer64" }, "PARENT_STATEMENT_ID": { "@EndUserText.label": "Parent statement ID", "type": "cds.String", "length": 20 }, "STATEMENT_HASH": { "@EndUserText.label": "Statement Hash", "type": "cds.String", "length": 32 }, "DB_USER": { "@EndUserText.label": "User name", "type": "cds.String", "length": 256 }, "APP_USER": { "@EndUserText.label": "Application user name", "type": "cds.String", "length": 256 }, "START_TIME": { "@EndUserText.label": "Statement start time", "type": "cds.Timestamp" }, "DURATION_MICROSEC": { "@EndUserText.label": "Duration [us]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "OBJECT_NAME": { "@EndUserText.label": "Related objects", "type": "cds.String", "length": 5000 }, "OPERATION": { "@EndUserText.label": "Type of operation", "type": "cds.String", "length": 5000 }, "RECORDS": { "@EndUserText.label": "Number of records", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_STRING": { "@EndUserText.label": "Statement string", "type": "cds.LargeString" }, "PARAMETERS": { "@EndUserText.label": "Parameters", "type": "cds.String", "length": 5000 }, "ERROR_CODE": { "@EndUserText.label": "Error code", "type": "cds.Integer" }, "ERROR_TEXT": { "@EndUserText.label": "Error message", "type": "cds.String", "length": 5000 }, "LOCK_WAIT_COUNT": { "@EndUserText.label": "Accumulated lock wait count", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "LOCK_WAIT_DURATION": { "@EndUserText.label": "Accumulated lock wait duration", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "ALLOC_MEM_SIZE_ROWSTORE": { "@EndUserText.label": "Allocated memory size for row store", "type": "cds.Integer64" }, "ALLOC_MEM_SIZE_COLSTORE": { "@EndUserText.label": "Allocated memory size for column store", "type": "cds.Integer64" }, "MEMORY_SIZE": { "@EndUserText.label": "Peak memory [Byte]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "REUSED_MEMORY_SIZE": { "@EndUserText.label": "Memory reused from cached data structures, 0 if caches empty", "type": "cds.Integer64" }, "CPU_TIME": { "@EndUserText.label": "CPU time [us]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "PASSPORT_ROOT_CONTEXT_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying source of request", "type": "cds.String", "length": 32 }, "PASSPORT_TRANSACTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying business transaction", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_ID": { "@EndUserText.label": "SAP EPP Passport GUID identifying connection", "type": "cds.String", "length": 32 }, "PASSPORT_CONNECTION_COUNTER": { "@EndUserText.label": "SAP EPP Passport connection counter", "type": "cds.Integer64" }, "STATEMENT_START_TIME": { "@EndUserText.label": "Time when the first OPERATION of a statement execution started. All OPERATIONs of a statement execution share the same STATEMENT_START_TIME.", "type": "cds.Timestamp" }, "APPLICATION_SOURCE": { "@EndUserText.label": "Application Source", "type": "cds.String", "length": 256 }, "APPLICATION_NAME": { "@EndUserText.label": "Application Name", "type": "cds.String", "length": 256 }, "NETWORK_MESSAGE_ID": { "@EndUserText.label": "Client message id", "type": "cds.Integer64" }, "WORKLOAD_CLASS_NAME": { "@EndUserText.label": "Effective workload class", "type": "cds.String", "length": 256 }, "PRIORITY": { "@EndUserText.label": "Effective statement priority", "type": "cds.Integer" }, "STATEMENT_THREAD_LIMIT": { "@EndUserText.label": "Effective statement thread limit", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_MEMORY_LIMIT": { "@EndUserText.label": "Effective statement memory limit", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "SESSION_VARIABLES": { "@EndUserText.label": "Session variables", "type": "cds.LargeString" }, "TABLE_TYPES": { "@EndUserText.label": "Table types", "type": "cds.String", "length": 32 }, "ROW_COUNT": { "@EndUserText.label": "ROW_COUNT", "type": "cds.Integer" } }, "query": { "SELECT": { "from": { "join": "left", "args": [ { "ref": [ "M_EXPENSIVE_STATEMENTS" ], "as": "exp" }, { "SELECT": { "from": { "ref": [ "SAP_TCT_SCHEMA_V_H_01" ] }, "columns": [ { "ref": [ "SCHEMA_NAME" ] }, { "ref": [ "SPACE_ID" ] }, { "ref": [ "SCHEMA_TYPE" ] } ] }, "as": "space" } ], "on": [ { "ref": [ "exp", "SCHEMA_NAME" ] }, "=", { "ref": [ "space", "SCHEMA_NAME" ] } ] }, "columns": [ { "ref": [ "HOST" ] }, { "ref": [ "PORT" ] }, { "ref": [ "exp", "SCHEMA_NAME" ] }, { "ref": [ "space", "SPACE_ID" ] }, { "xpr": [ "case", "when", { "ref": [ "space", "SPACE_ID" ] }, "is", "null", "then", { "val": false }, "else", { "val": true }, "end" ], "as": "IS_SPACE_SCHEMA" }, { "xpr": [ "case", "when", { "func": "locate", "args": [ { "ref": [ "SESSION_VARIABLES" ] }, { "val": "$$DWC_PROCEDURE_RUN$$" } ] }, "=", { "val": 0 }, "then", { "val": false }, "else", { "val": true }, "end" ], "as": "IS_TASK" }, { "xpr": [ "case", "when", { "func": "locate", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "CALL SYS.EXECUTE_MDS" } ] }, "=", { "val": 1 }, "then", { "val": true }, "else", { "val": false }, "end" ], "as": "IS_INA_CALL" }, { "xpr": [ "case", "when", { "ref": [ "APPLICATION_NAME" ] }, "=", { "val": "SAP_SDI_HDI" }, "then", { "val": true }, "else", { "val": false }, "end" ], "as": "IS_SDI" }, { "xpr": [ "case", "when", { "val": "DWCDBUSER#" }, "=", { "func": "left", "args": [ { "ref": [ "exp", "SCHEMA_NAME" ] }, { "func": "length", "args": [ { "val": "DWCDBUSER#" } ] } ] }, "then", { "val": true }, "else", { "val": false }, "end" ], "as": "IS_SCHEMA_ANALYSIS" }, { "xpr": [ "case", "when", { "ref": [ "space", "SCHEMA_TYPE" ] }, "=", { "val": "OPEN_SQL" }, "then", { "val": true }, "else", { "val": false }, "end" ], "as": "IS_SCHEMA_OPEN_SQL" }, { "ref": [ "CONNECTION_ID" ] }, { "ref": [ "TRANSACTION_ID" ] }, { "ref": [ "UPDATE_TRANSACTION_ID" ] }, { "ref": [ "STATEMENT_ID" ] }, { "ref": [ "STATEMENT_EXECUTION_ID" ] }, { "ref": [ "PARENT_STATEMENT_ID" ] }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "DB_USER" ] }, { "ref": [ "APP_USER" ] }, { "ref": [ "START_TIME" ] }, { "ref": [ "DURATION_MICROSEC" ] }, { "ref": [ "OBJECT_NAME" ] }, { "ref": [ "OPERATION" ] }, { "ref": [ "RECORDS" ] }, { "ref": [ "STATEMENT_STRING" ] }, { "ref": [ "PARAMETERS" ] }, { "ref": [ "ERROR_CODE" ] }, { "ref": [ "ERROR_TEXT" ] }, { "ref": [ "LOCK_WAIT_COUNT" ] }, { "ref": [ "LOCK_WAIT_DURATION" ] }, { "ref": [ "ALLOC_MEM_SIZE_ROWSTORE" ] }, { "ref": [ "ALLOC_MEM_SIZE_COLSTORE" ] }, { "ref": [ "MEMORY_SIZE" ] }, { "ref": [ "REUSED_MEMORY_SIZE" ] }, { "ref": [ "CPU_TIME" ] }, { "ref": [ "PASSPORT_ROOT_CONTEXT_ID" ] }, { "ref": [ "PASSPORT_TRANSACTION_ID" ] }, { "ref": [ "PASSPORT_CONNECTION_ID" ] }, { "ref": [ "PASSPORT_CONNECTION_COUNTER" ] }, { "ref": [ "STATEMENT_START_TIME" ] }, { "ref": [ "APPLICATION_SOURCE" ] }, { "ref": [ "APPLICATION_NAME" ] }, { "ref": [ "NETWORK_MESSAGE_ID" ] }, { "ref": [ "WORKLOAD_CLASS_NAME" ] }, { "ref": [ "PRIORITY" ] }, { "ref": [ "STATEMENT_THREAD_LIMIT" ] }, { "ref": [ "STATEMENT_MEMORY_LIMIT" ] }, { "ref": [ "SESSION_VARIABLES" ] }, { "ref": [ "TABLE_TYPES" ] }, { "val": 1, "as": "ROW_COUNT" } ] } }, "@EndUserText.label": "SAP_TCT_EXP_STATM_V_H_01", "@ObjectModel.modelingPattern": { "#": "ANALYTICAL_FACT" }, "@ObjectModel.supportedCapabilities": [ { "#": "ANALYTICAL_FACT" }, { "#": "ANALYTICAL_PROVIDER" } ], "@DataWarehouse.consumption.external": true, "@DataWarehouse.sqlEditor.query": "/*\n Usage: This view adds SAP Datasphere related information to the expensive statement view. \n \n This view adds the following columns to the M_EXPENSIVE_STATEMENTS view:\n \n SPACE_ID : If the schema is related to a Datasphere Space, this column contains the space id - otherwise Null\n Schemas related to Open SQL users of SAP Datasphere are included as well.\n \n IS_SPACE_SCHEMA : Boolean to enable filtering for space and non-space related schemas\n IS_TASK : Entry is related to SAP Datasphere Task Execution (e.g. View Persistence)\n IS_INA : Entry is triggered by an Ina/MDS call (e.g. SAP Analytics Cloud)\n \n Not space related attributes:\n \n IS_SDI : Heuristic - based on the application we map the entry to SDI / non-SDI\n IS_ANALYSIS_USER : Heuristic - based on leading string 'DWCDBUSER' we map the entry to Analysis User\n \n ROW_COUNT : Use to count the rows, default measure for reporting\n*/\nSELECT \n \"HOST\",\n\t\"PORT\",\n\t\n\texp.\"SCHEMA_NAME\",\n\tspace.\"SPACE_ID\",\n\tCASE WHEN space.\"SPACE_ID\" IS NULL THEN FALSE ELSE TRUE END AS \"IS_SPACE_SCHEMA\",\n\tCASE WHEN locate(\"SESSION_VARIABLES\", '$$DWC_PROCEDURE_RUN$$') = 0 THEN FALSE ELSE TRUE END AS \"IS_TASK\",\n\tCASE WHEN locate(\"STATEMENT_STRING\", 'CALL SYS.EXECUTE_MDS') = 1 THEN TRUE ELSE FALSE END AS \"IS_INA_CALL\",\n\tCASE WHEN \"APPLICATION_NAME\" = 'SAP_SDI_HDI' THEN TRUE ELSE FALSE END AS \"IS_SDI\",\n\tCASE WHEN 'DWCDBUSER#' = left(exp.\"SCHEMA_NAME\", length('DWCDBUSER#')) THEN TRUE ELSE FALSE END AS \"IS_SCHEMA_ANALYSIS\", \n\tCASE\n WHEN space.\"SCHEMA_TYPE\" = 'OPEN_SQL' THEN TRUE \n ELSE FALSE END as \"IS_SCHEMA_OPEN_SQL\",\n \n\t\"CONNECTION_ID\",\n\t\"TRANSACTION_ID\",\n\t\"UPDATE_TRANSACTION_ID\",\n\t\"STATEMENT_ID\",\n\t\"STATEMENT_EXECUTION_ID\",\n\t\"PARENT_STATEMENT_ID\",\n\t\"STATEMENT_HASH\",\n\t\"DB_USER\",\n\t\"APP_USER\",\n\t\"START_TIME\",\n\t\"DURATION_MICROSEC\",\n\t\"OBJECT_NAME\",\n\t\"OPERATION\",\n\t\"RECORDS\",\n\t\"STATEMENT_STRING\",\n\t\"PARAMETERS\",\n\t\"ERROR_CODE\",\n\t\"ERROR_TEXT\",\n\t\"LOCK_WAIT_COUNT\",\n\t\"LOCK_WAIT_DURATION\",\n\t\"ALLOC_MEM_SIZE_ROWSTORE\",\n\t\"ALLOC_MEM_SIZE_COLSTORE\",\n\t\"MEMORY_SIZE\",\n\t\"REUSED_MEMORY_SIZE\",\n\t\"CPU_TIME\",\n\t\"PASSPORT_ROOT_CONTEXT_ID\",\n\t\"PASSPORT_TRANSACTION_ID\",\n\t\"PASSPORT_CONNECTION_ID\",\n\t\"PASSPORT_CONNECTION_COUNTER\",\n\t\"STATEMENT_START_TIME\",\n\t\"APPLICATION_SOURCE\",\n\t\"APPLICATION_NAME\",\n\t\"NETWORK_MESSAGE_ID\",\n\t\"WORKLOAD_CLASS_NAME\",\n\t\"PRIORITY\",\n\t\"STATEMENT_THREAD_LIMIT\",\n\t\"STATEMENT_MEMORY_LIMIT\",\n\t\"SESSION_VARIABLES\",\n\t\"TABLE_TYPES\",\n\t1 AS \"ROW_COUNT\"\nFROM \"M_EXPENSIVE_STATEMENTS\" AS exp\n\tLEFT JOIN (\n\t\tSELECT \"SCHEMA_NAME\",\n\t\t\t \"SPACE_ID\",\n\t\t\t \"SCHEMA_TYPE\"\n\t\tFROM \"SAP_TCT_SCHEMA_V_H_01\"\n\t) AS space ON exp.\"SCHEMA_NAME\" = space.\"SCHEMA_NAME\"" } }, "version": { "csn": "1.0" }, "meta": { "creator": "CDS Compiler v1.19.2" }, "$version": "1.0"}The views can be directly used in the preview of SAP Datasphere or e.g. via SAP Analytics Cloud.
Conclusion
Following this blog post supports you in monitoring your HANA system and to judge if the system is in a healthy state. Although, the blog post only shows samples dealing with the CPU time, other measures like peak memory are part of the views as well.
If you like to get into even more details, you might want to check out the blog posts related to task monitoring (an updated providing CPU and Memory figures in on its way). Another blog post planned to be release soon will support your in analyzing Ina/MDS requests.
Looking forward to hear your feedback and proposals for improvement. If you identify additional helpful rules to map schema names or users to specific schema or usage type, please contact me directly for an evaluation.
I’d be happy to hear your thoughts, ideas and comments on this blog post. Let me know in the comments!
3 Comments
