How can we collect ExplainPlan of a prepared statement for analysing the performance?
Solution
User can utilize the SQL Plan Cache in order to get plan after parameter aware optimization.
Option 1) If already there is a valid SQL Plan Cache entry existing for the SQL user you are executing the ExplainPlan with, then you can collect the ExplainPlan from SQL Plan Cache performing the steps below:
- First select a plan_id from m_sql_plan_cache where the statement_hash = '<hash>';
- Now explain the plan set statement_name = '<some_name>' for sql plan cache entry <plan_id>;
- Now please select * from explain_plan_table where statement_name = '<some_name>';
- At last delete from sys.explain_plan_table where statement_name = '<some_name>';
Option 2) But If the user execute the explain plan with a SQL user with no valid SQL Plan Cache entry or different statement string due to white spaces or line feeds then please follow the steps below:
- First user should paste the SQL statement in the SQL console of SAP HANA Studio.
Note: Please make sure that there is a semicolon at the end of the statement in order to avoid line feed at the end.
- Now Execute (F8) in order to prepare the statement
- Now paste the value inside their corresponding parameter fields
- At last after finishing the query please execute the Explain Plan the SQL from the same SQL console
Note
- For the first option user need the OPTIMIZER_ADMIN privilege
- User can also opt for other option in order to identify the <plan_id>, for example LIKE search on the STATEMENT_STRING ie if user is searching via STATEMENT_STRING then he must consider the exact matches including line feeds and white spaces
- In order to check whether there is a valid SQL Plan Cache entry user can look up the IS_VALID column of M_SQL_PLAN_CACHE.
- If you forgot the semicolon while following the second option the at the end there can be two entries for your statement in the SQL Plan Cache. Both the entries will have different statement hashes and one will have EXECUTION_COUNT 0. Now in this case there will probably a 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. Only in this type of cases the ExplainPlan will reflect the execution plan without parameter aware optimization.