To generate an inspected SQL report
Use Analyze Impact to evaluate the impact that a change can have on a group of SQL statements or a SQL workload. The types of changes Analyze Impact will allow you to evaluate include: the addition of indexes and changes to database parameters. You can also run a comparison of two different databases that run the same application.
To evaluate the impact on a SQL workload, SQL Optimizer identifies changes to each SQL statement's execution plan resulting from the specified database change.
You can collect the SQL workload from one of several sources: source code, an Oracle Automatic Workload Repository (AWR), or the Oracle System Global Area (SGA).
The following is a summary of the Analyze Impact procedure/workflow:
Step one. Select the type of change to analyze (create new session).
Step two. Specify change and analysis criteria.
Step three. Collect the SQL workload to evaluate.
Step four. Review collected SQL (optional).
Step five. Run the analysis
Step six. Review results
Step one. To create an Analyze Impact session, begin by selecting the type of change you want to analyze.
To create a new Analyze Impact session
Select Analyze Impact.
Select the type of change you want to evaluate. Select from the following:
|Additional Indexes||Select to evaluate the impact of adding indexes. You can create indexes or evaluate indexes created previously in an Optimize Indexes session.|
|Parameter Change||Select to evaluate the impact of changing database parameters.|
|Compare Databases||Select to compare two databases running the same application.|
|Custom Script||Select this option to use a custom script to create the change that you want to analyze.|
Specify analysis criteria. See one of the following topics (depending on the change you selected):
Step two. If you selected to evaluate the impact of additional indexes, specify criteria for the new indexes.
You can use Analyze Impact to create new indexes to evaluate or you can evaluate indexes created previously in the Optimize Indexes module. See Send Virtual Indexes to Analyze Impact (Optimize Indexes) for more information.
To analyze the impact of Additional Indexes
Specify the remaining index criteria for the selected index. Review the following for additional information:
|Index Type||Select an index type.|
|Advanced Options||Click to select from additional options for the selected index.|
|Select to add key compression. If you select Compress Key, you must also include the number of columns to compress in the Columns field.|
You can manage the list of indexes using the following toolbar buttons:
|Click to add a new index.|
||Click to easily rename the selected index.|
|Click to delete the selected index.|
||Click to delete all indexes.|
|Click to display the index criteria in a card view.|
|Click to display the index criteria in a table view.|
Go to Step three. See Collect SQL and Run an Impact Analysis.
Tip: If you used Optimize Indexes to find new indexes to optimize database performance, you can send those indexes directly from your Optimize Indexes session to a new Analyze Impact session. See Send Virtual Indexes to Analyze Impact (Optimize Indexes) for more information.