EDUCAÇÃO E TECNOLOGIA

SAP Data Warehouse Cloud: M_EXPENSIVE_STATEMENTS and Task Executions

Data Warehouse Cloud allows you to configure the thresholds for capturing expensive statements (help page).

Here you see a screenshot with the delivered default:

This is a very helpful functionality – unfortunately something important is missing: How do I map the captured statements to the related activity in DWC? The view M_EXPENSIVE_STATEMENTS ( help page ) doesn’t contain any DWC related information.

Lukilly this is not 100% true: There is a session variable that is added when the DWC task framework is the initiator. This is the case when e.g. performing a view persistence or remote table replication. The name of the variable is ‘$$DWC_PROCEDURE_RUN$$’.

The following statement would return all entries that are related to the task framework:

SELECT * FROM M_EXPENSIVE_STATEMENTS WHERE "SESSION_VARIABLES" LIKE '%$$DWC_PROCEDURE_RUN$$%'

Even better – the value of the variable is the task id. By using the view TASK_LOGS_V_EXT delivered by DWC ( help page ) you can navigate to the related space and object id.

Here is a screenshot of the defintion of the view: per task log id you can get the space, where the tasks has been defined and the application_id indicates if it is a e.g. a view persistence or remote table replication.

The only remaining tasks is now to join these two tables by the task id – and we have a basis for helpful reports where you can group the expensive statements by the DWC artefacts.

Implementing the Reporting Views

Below you find two views – the first one contains the join of the two views. The second one has specific columns to serve the reporting requirements.

Create now an SQL view with the name TASK_EXP_STATM_V_H_01 in your monitoring space (help page):

/* help page for M_EXPENSIVE_STATEMENTS: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20af736e751910148162e2ab1982f035.html help page to configure threshhold for expensive statements in DWC: https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/9cd0691c44a74f2aa47b52f615f74433.html This view searches for entries in the M_EXPENSIVE_STATEMENTS that are related to DWC DataIntegration. This clasification is based on an internal property of the SESSION_VARIABLES -> $$DWC_PROCEDURE_RUN$$ */ SELECT "DB_USER", "SCHEMA_NAME", "APP_USER", "EXP_ST_START_TIME", "DURATION_MICROSEC", "OBJECT_NAME", "OPERATION", "RECORDS", "STATEMENT_STRING", "PARAMETERS", "ERROR_CODE", "ERROR_TEXT", "MEMORY_SIZE", "REUSED_MEMORY_SIZE", "CPU_TIME", "STATEMENT_START_TIME", "APPLICATION_SOURCE", "APPLICATION_NAME", "NETWORK_MESSAGE_ID", "WORKLOAD_CLASS_NAME", "PRIORITY", "STATEMENT_THREAD_LIMIT", "STATEMENT_MEMORY_LIMIT", "SESSION_VARIABLES", "TABLE_TYPES", "TASK_LOGS_V_EXT"."TASK_LOG_ID" TASK_LOG_ID, "TASK_LOGS_V_EXT"."SPACE_ID" SPACE_ID, "TASK_LOGS_V_EXT"."APPLICATION_ID" APPLICATION_ID, "TASK_LOGS_V_EXT"."ACTIVITY" ACTIVITY, "TASK_LOGS_V_EXT"."OBJECT_ID" OBJECT_ID
FROM ( SELECT "DB_USER", "SCHEMA_NAME", "APP_USER", "START_TIME" EXP_ST_START_TIME, "DURATION_MICROSEC", "OBJECT_NAME", "OPERATION", "RECORDS", "STATEMENT_STRING", "PARAMETERS", "ERROR_CODE", "ERROR_TEXT", "MEMORY_SIZE", "REUSED_MEMORY_SIZE", "CPU_TIME", "STATEMENT_START_TIME", "APPLICATION_SOURCE", "APPLICATION_NAME", "NETWORK_MESSAGE_ID", "WORKLOAD_CLASS_NAME", "PRIORITY", "STATEMENT_THREAD_LIMIT", "STATEMENT_MEMORY_LIMIT", "SESSION_VARIABLES", "TABLE_TYPES", -- JSON_VALUE has issues with $$ in the property name substr_before(substr_after(SESSION_VARIABLES, '"$$DWC_PROCEDURE_RUN$$": "'), '"') TASK_LOG_ID FROM "M_EXPENSIVE_STATEMENTS" -- if the session is initiated by the task framework, it contains the property TASK_EXECUTION_ID WHERE "SESSION_VARIABLES" LIKE '%$$DWC_PROCEDURE_RUN$$%' ) EXP_STATEMENTS_TASK_RELATED LEFT JOIN "TASK_LOGS_V_EXT" ON "TASK_LOGS_V_EXT"."TASK_LOG_ID" = EXP_STATEMENTS_TASK_RELATED."TASK_LOG_ID" 

For the reporting, create a second view (of type analytical dataset for SAC) with the SQL below.

SELECT "DB_USER", "SCHEMA_NAME", "APP_USER", "EXP_ST_START_TIME", "DURATION_MICROSEC" / 1000 DURATION_MS, "DURATION_MICROSEC" / 1000 / 1000 DURATION_SEC, "OBJECT_NAME", "OPERATION", "RECORDS", "STATEMENT_STRING", "PARAMETERS", "ERROR_CODE", "ERROR_TEXT", 1 ROW_COUNT, to_int(round("MEMORY_SIZE" / 1024 / 1024)) MEMORY_SIZE_MB, to_int(round("MEMORY_SIZE" / 1024 / 1024 / 1024)) MEMORY_SIZE_GB, "CPU_TIME" / 1000 CPU_TIME_MS, "CPU_TIME" / 1000 / 1000 CPU_TIME_SEC, "STATEMENT_START_TIME", "APPLICATION_NAME", "WORKLOAD_CLASS_NAME", "STATEMENT_THREAD_LIMIT", "STATEMENT_MEMORY_LIMIT", "TASK_LOG_ID", "SPACE_ID", "APPLICATION_ID", "ACTIVITY", "OBJECT_ID"
FROM "TASK_EXP_STATM_V_H_01"

and set the following dimensions as the measures:

  • DURATION_MS
  • MEMORY_SIZE_MB
  • CPU_TIME_MS
  • ROW_COUNT

The related dimensions DURATION_SECS, MEMORY_SIZE_GB and CPU_TIME_SEC should be kept as normal dimension/attributes.

Reporting Scenarios

List all Tasks With Peak Memory Greater  than 10 GB

This is pretty straight forward. We have the dimension MEMORY_SIZE_GB that contains the peak memory in GB. Just add a filter greater than 10 by the following steps:

Navigate to the filter section in SAC (after you have inserted a table using the DWC view you just created) and select the Peak Memory (GB) (Range) from the dropdown:

In the following dialog, select greater than with a value 10.

Combine the memory and runtime figures from expensive statements with the modeling artefacts of DWC provide a great help in monitoring and understanding the impact of the tasks in terms of system resources. Especially during first productive usage it is highly recommended to evaluate the impact and to take measure to reduce or distribute the load to avoid overload situations.

I’d be happy to hear your thoughts, ideas and comments on this monitoring topic. Let me know in the comments!