What is HANA SQL Plan Cache ?

SQL PLAN CAHE:
The SQL PLAN CACHE stores plans generated by previous SQL statement executions. The plan cache is used by the SAP HANA database to speed up query exectution if the same SQL statement is executed again.The plan cache also collects some statistics regarding plan preparation and execution

SQL STATEMENT:

ALTER SYSTEM CLEAR SQL PLAN CACHE

The ALTER SYSTEM CLEAR SQL PLAN CACHE statement removes all the SQL plans that are not currently executing from the plan cache. The command also removes all plans having reference count of '0' from the plan cache and resets all the statistics of the remaining plans. Lastly the command also reset the contents of M_SQL_PLAN_CACHE_OVERVIEW monitoring view.






Note: The following new columns have been added to the SQL plan cache, which can be viewed on the Performance tab of the Administration editor in HANA Studio.

  • TOTAL_EXECUTION_MEMORY_SIZE
  • MAX_EXECUTION_MEMORY_SIZE
  • AVG_EXECUTION_MEMORY_SIZE
  • MIN_EXECUTION_MEMORY_SIZE
  • TOTAL_EXECUTION_REUSED_MEMORY_SIZE
  • MAX_EXECUTION_REUSED_MEMORY_SIZE
  • AVG_EXECUTION_REUSED_MEMORY_SIZE
  • MIN_EXECUTION_REUSED_MEMORY_SIZE
  • TOTAL_EXECUTION_CPU_TIME
  • MAX_EXECUTION_CPU_TIME
  • AVG_EXECUTION_CPU_TIME
  • MIN_EXECUTION_CPU_TIME


Tags: M_SQL_PLAN_CACHE, M_SQL_PLAN_CACHE_OVERVIEW,  TOTAL_EXECUTION_MEMORY_SIZE, MAX_EXECUTION_MEMORY_SIZE, AVG_EXECUTION_MEMORY_SIZE, MIN_EXECUTION_MEMORY_SIZE, , TOTAL_EXECUTION_REUSED_MEMORY_SIZE, MAX_EXECUTION_REUSED_MEMORY_SIZE, AVG_EXECUTION_REUSED_MEMORY_SIZE, MIN_EXECUTION_REUSED_MEMORY_SIZE, TOTAL_EXECUTION_CPU_TIME, MAX_EXECUTION_CPU_TIME, AVG_EXECUTION_CPU_TIME, MIN_EXECUTION_CPU_TIME,SQL Plan Cache and Expensive Statements sub-tabs of the Performance tab, Timeline view is now available in HANA, 
Share this article :

Post a Comment

 
Copyright © 2011. SAP HANA TUTORIALS FREE - S/4 HANA - All Rights Reserved