How can time information in the SQL cache (M_SQL_PLAN_CACHE) be interpreted?
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 time and client time
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
Usually long EXECUTION_OPEN or EXECUTION_FETCH times are caused by retrieving the data.
From a SQL tuning perspective the most important information is the total elapsed time of the SQL statement which is the sum of preparation time and execution time.
Sign up for STechies
All the site contents are Copyright © www.stechies.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies. The site www.stechies.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.stechies.com or the content authors.