Introduction
This document is about the usage of the DB2 Explain Plan analysis tool provided within the DBA Cockpit which provides a very good and easy to use method to perform query analysis.
Initial Analysis
To find out the worst performing queries – we can navigate to the following place :
On Double Clicking on the SQL Cache , we get the following screen . In this screen we can input various selection items based on our system observations .
For example , if we see long sequential run times on SWW* tables in SM66 , we can enter as below.
If we do not enter anything specific , it will tell about the overall system.
In the next screen , we can sort by Average Execution Time
Actual Analysis Part
In the above screen – we need to highlight the query where we want to do analysis and then click on INDEX ADVISOR. This will open a new as below . Click on the recommended index button as highlighted
It shows the existing indexes + the indexes which are recommended to be created.
Please note that this is just an initial recommendation and testing needs to be done before actual implementations in the system.
Near the EXPLAIN Button – we have the option to view the explain plan of the query based on the using the existing / recommended or all indexes in the system.
Based on the selection criterion – the explain plan and the cost of the query will be shown.
For our demonstration purpose , we will take one of the query tuning example : Please see the below explain plan with the estimated cost and the important points highlighted.
It is effectively doing a full table scan on the table and is having a high execution time . Please note that TIMERON is a unit to measure the execution time and not equal to normal time like seconds etc.
If we check on the recommended index for this query – we will see that there is a recommendation of a new index
Here the recommended index is SWN_NOTIF~2 . Using this index , we can check the explain plan :
Please note the cost highlighted in YELLOW above and compare with the earlier explain plan. It shows that the cost has reduced drastically and also it is going for an index scan ( IXSCAN )
In this way the Index Analyzer feature in DBA Cockpit is very helpful in getting recommendations for new indexes on poorly performing SAP Queries.
Another advantage is checking the query performance in different database
For example – we have a poorly performing query in PROD Environment. We need to check in QA Environment if the query is being executed using the same Explain PLAN.
In such scenario – we can simply take the query from the PROD and put in the QA Environment in this location .
Two things we need to change when putting the PROD Query in the NON PROD Environment .
At the end of the query highlighted in BLUE above are the System SID and the Schema name .
Hence suppose prod query has PRD , SAPPRD —-> Before executing the query in the NON PROD , make it QAT , SAPQAT .
Remember : If you have performed a System Copy , then the Schema in the QA Environment will be same as PROD ( In this case – SAPPRD ) . So no need to change.
In this way , the Index Advisor can be easily used for the initial level for query tuning in DB2 Databases.
Thanks for sharing Rishi.
regards
kartik
Thanks Kartik !!