The Analyze Impact module saves the execution plan for SQL statements so that you can track changes to the database environment such as: parallel processing, database system changes, data growth, database migration, database version upgrade, development to production deployment, data reorganization, index changes, virtual index simulation, database re-design, rule-based to cost-based optimizing, and any other database environment change. Analyze Impact shows how these changes affect the execution plans. It enables you to save and compare the execution plans before and after these changes so that you can see how these changes impact the performance of your SQL statements.
Analyze Impact stores the SQL statements and their execution plan information in this directory on your PC:
C:\Documents and Settings\user\Application Data\Quest Software\version\Quest SQL Optimizer for Oracle\Analyzer Data
The location of the directory can be changed in the Options window under the Analyzer page.
1. Click the Analyze Impact tab.
2. In the Group
list, click and select a group or click to create a new group.
SQL Tab – Entering a SQL statement
The SQL statements that you save in the Analyze Impact are listed under the SQL tab located at the bottom left of the Analyze Impact window. You may save a SQL statement from another module (see Save SQL Statements to the Analyze Impact Tutorial) or from within Analyze Impact module.
3. Click the SQL tab at the bottom left corner of the window.
4. Right-click in the SQL pane and select Add SQL to open the Add SQL wizard.
5. On the General page, enter a name for your SQL statement in the Name box.
6. If you would like to create a new folder to store the SQL statement, click .
7. In the pane at the bottom of the Add SQL page, click the folder where you want to store the SQL statement.
8. On the SQL Information page, enter your SQL statement.
9. Click to check the syntax and retrieve the execution plan.
Quest SQL Optimizer for Oracle User Guide Use Quest SQL Optimizer
40
10. Click Finish to save the SQL.
Analyzer Tab – Analyzing Execution Plan Changes
You must have saved a SQL statement before you can perform any comparison in the Analyze Impact. (See Save SQL Statements to the Analyze Impact Tutorial or SQL Tab, step 3.)
The Analyzer tab can contain more than one Analyzer. Every Analyzer is an individual unit that contains the analysis of different SQL statements’ execution plans. Every Analyzer can have one or more SQL statements grouped in what is called a SQL Repository. For the SQL statements in the SQL Repository, you can take one or more execution plan snapshots under different database environments.
11. Click the Analyzer tab at the bottom left corner of the window.
12. Right-click in the Analyzer pane and select Add Analyzer to open the Add Analyzer wizard.
13. On the General page, enter a name for your Analyzer in the Name box.
14. If you would like to create a new folder to store the Analyzer, click .
15. In the pane at the bottom of the General page, select the folder where you want to store the Analyzer.
16. On the SQL page, select the SQL statements you would like to add to the Analyzer from your predefined SQL statements, or you may add a statement to this Analyzer by clicking
in the right corner of this window.
17. On the Plan Snapshot page, the first time you are on this page, it brings up the Add Baseline Snapshot wizard. This snapshot of the SQL statements and execution plans is used as the comparison for all other snapshots in the Analyzer.
After the first execution plan is saved, if you would like to add another SQL snapshot to this Analyzer, click under the Plan Snapshot page.
18. In the Add Baseline Snapshot or Add Plan Snapshot wizard under the General page, enter a name for this Snapshot in the Name box.
19. Under the Connection Information page, select the options for retrieving your execution plan.
l Copy existing execution plan from SQL
Use the execution plan that was saved with the SQL statement at the time that statement was saved to the Analyze Impact.
l Current connection
Use Quest SQL Optimizer
Get the execution plan from the current logon that you are using.
l New connection
Get the execution plan with a different user logon.
20. If you would like to run one or more of the Oracle scripts, under the Script page, select the script you would like to use. If you select to use scripts, the pre-script will be executed after connecting to the database and before getting the execution plan. After retrieving the execution plan, if there is a post-script it will be executed. You can use scripts to change your database environment. For instance, you can use a pre-script to change some of the dynamic init.ora parameters such as optimizer_mode, and then use the post-script to reset the parameter to its original value. (See Script Tab, step 26.)
Note: You can add a new Oracle script by clicking . 21. Click Finish to close the Add Baseline or Plan Snapshot wizard.
22. To add more snapshots, repeat from step 17.
23. Click Finish to close the Add Analyzer Wizard. The Analyzer makes the connection to the database and retrieves the execution plan under the different snapshots.
24. The Plan Generation Summary window displays information regarding the retrieval of the execution plans. There you can see if the retrieval is successful or if an error occurred.
Click OK to close the window.
25. To review the Plan Analyzer summary, see View the Execution Plan Changes, step 34.
Script Tab – Creating Scripts for Analyzing Configuration Changes 26. Click the Script tab at the bottom left corner of the window.
27. Right-click in the Script pane and select Add Script to open the Add Script wizard.
28. On the General page, enter a name for your Script in the Name box.
29. One the Scripts page, you will see an area for a Pre-Script and a Post-Script.
30. In the Pre-Script area, enter the Oracle commands to be executed before the execution plan is retrieved.
31. In the Post-Script area, enter the Oracle commands to be executed after the execution plan is retrieved.
32. In order to check your script in a pre-selected database, click . This will allow you to verify that you your scripts execute.
33. Click Finish to save the scripts.
Quest SQL Optimizer for Oracle User Guide Use Quest SQL Optimizer
42
View the Execution Plan Changes
34. Click the Analyzer tab at the bottom left corner of the window.
35. Expand the tree in the left pane to find the SQL statement for which you would like to analysis the various execution plans.
36. To view the SQL text and the execution plans for each snapshot, click the name you gave the SQL statement in the left pane.
37. In the right pane, you will see the SQL statement with tabs at the bottom of the pane to display the execution plan and SQL classification for each snapshot. This interface allows you to compare side by side the execution plan for the SQL statement under different snapshots. It also displays the SQL Classification associated with every execution plan.
38. To view a summary of the performance change from the SQL statement in the Baseline snapshot and a comparison snapshot, click the name of the comparison snapshot in the left pane.
39. In the right pane, you will see a table (in the center pane) that summarizes the comparison of the Baseline execution plan and the snapshot execution plan. It shows the Baseline Snapshot Oracle Cost, the Snapshot Oracle Cost, Cost difference (Snapshot cost – Baseline cost), YES/NO Plan Changed, SQL Classification for the Baseline Snapshot, SQL Classification for the Snapshot, and what type of change occurs,
Improved/Unchanged/Degraded.
From here you can identify the SQL statements that may experience performance improvement (Oracle cost in the comparison snapshot is smaller than the cost in the Baseline snapshot), or performance degradation (Oracle cost in comparison snapshot is higher than the cost in the Baseline snapshot). You can also analyze how the complexity of the execution plan changes between snapshots by comparing changes in the SQL Classification.
Note: Oracle only provides a cost for the execution plan for SQL statements under the cost-based optimizer. If no cost is displayed, this means that the SQL statement is using the rule-based optimizer.
40. At the top right pane, you can see a graph or a summary of Oracle cost changes, which indicate overall performance improvement or degradation. The summary also displays overall performance improvement or degradation of the SQL Classification. Clicking any portion of one of these graphs will highlight the statements that are being represented in these graphs in the table summary discussed in the previous point (step 39).