Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

M_SQL_PLAN_CACHE: Interpret Time Information in SQL cache

|| 0

M_SQL_PLAN_CACHE: Interpret Time Information in SQL cache
Stechies

Hello Experts,

How can time information in the SQL cache (M_SQL_PLAN_CACHE) be interpreted?

Thanks in advance.

SOLUTION

Tables like M_SQL_PLAN_CACHE, M_SQL_PLAN_CACHE_RESET and HOST_SQL_PLAN_CACHE contain several Time-Related columns and it is not always clear how to interpret them:

Time column type Description
CURSOR

Contains the overall cursor time including SAP HANA server execution time and client time

Preparation time isn't included

Mainly applies to SELECT and CALL operations, can be 0 for others (e.g. DML, DDL)

If the client performs other tasks between fetches of data, the cursor time can be much higher than the SAP HANA server time.

This can result in MVCC issues because old versions of data need to be kept until the execution is finished.

EXECUTION

Contains the execution time (open + fetch + lock wait + close) on SAP HANA server side, does not include table load and preparation time

EXECUTION_OPEN

Contains the open time on SAP HANA server side

Includes the actual retrieval of data in case of column store accesses with early materialization

EXECUTION_FETCH

Contains the fetch time on SAP HANA server side

Includes the actual retrieval of data in case of row store accesses or late materialization

EXECUTION_CLOSE Contains the close time on SAP HANA server side
TABLE_LOAD Contains the table load time during preparation, is part of the preparation time
PREPARATION Contains the preparation time
LOCK_WAIT

Contains the transaction lock wait time, internal locks are not included


Advertisement
Advertisement
Advertisement