SAP Note 2410208 - Collect Explain Plan of a Prepared Statement.

[koken]

Symptom


For performance analysis you want to collect the ExplainPlan of a prepared statement.
Reason and Prerequisites


Reason:

SAP HANA database can use parameter aware optimization to optimize the execution plan of a prepared statement further during first execution based on the set of parameters given to it. ExplainPlan only prepares the SQL statement but does not execute it and hence does not necessarily reflect the execution plan after parameter aware optimization.

Prerequisites:

• SAP HANA database all Revisions
• Prepared statement

Solution


You can utilize the SQL Plan Cache to get the plan after parameter aware optimization.

• If there is already a valid SQL Plan Cache entry for the SQL user you are executing the ExplainPlan with, you can collect the ExplainPlan from SQL Plan Cache via the following steps.

1. 1. select plan_id from m_sql_plan_cache where statement_hash = '<hash>';
   2. explain plan set statement_name = '<some_name>' for sql plan cache entry <plan_id>;
   3. select * from explain_plan_table where statement_name = '<some_name>';
   4. delete from sys.explain_plan_table where statement_name = '<some_name>';

• If you execute the explain plan with a SQL user which does not have a valid SQL Plan Cache entry or you have a different statement string due to white spaces or line feeds please execute the following steps:

1. 1. Paste the SQL statement in SAP HANA Studio SQL console
   2. Make sure there is a semicolon at the end of the statement, to prevent a line feed at the end
   3. Prepare the statement by hitting Execute (F8)
   4. Paste the values into their corresponding parameter fields
   5. Execute the statement by hitting again Execute (F8)
   6. After the query finished execute the Explain Plan of the SQL from the same SQL console

Remarks:

• OPTIMIZER_ADMIN privilege is needed for the first option.
• There are other options to identify the <plan_id>, e.g. a LIKE search on the STATEMENT_STRING. If searching via the STATEMENT_STRING, you must consider exact matches including white spaces and line feeds.
• To check if there is a valid SQL Plan Cache entry you can look up the IS_VALID column of M_SQL_PLAN_CACHE.
• If you followed the second procedure above and forgot the semicolon at the end there can be two entries for your statement in the SQL Plan Cache. They will have different statement hashes and one will have EXECUTION_COUNT 0. In this case there is probably a line feed at the end of the statement which was not considered for the ExplainPlan but for the execution or vice versa, leading to different statement hashes and hence to different SQL Plan Cache entries. The ExplainPlan will reflect the execution plan without parameter aware optimization only in this case.