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.

You can collect the SQL workload from one of several sources: an Oracle Automatic Workload Repository (AWR), Foglight PA Repository, Oracle System Global Area (SGA), or from source code.

To perform an impact analysis

  1. Select Analyze Impact.
  2. Select the type of change you want to analyze. For this tutorial, select Additional Indexes.
  3. Select the database connection and schema to use.
  4. Click icon_AI_NewIndex to add a new index.
  5. Enter a new index name or use the default.
  6. In the Select Index Columns and Options pane, select the table containing the columns you want to index. The table's columns display in the lower pane. Select a column and click icon_PickRtArrow to move it to the Indexed Columns list.
  7. Click icon_AI_FunctionIndexCreate.png-550x0 to create a functional index.
  8. 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.

    Compress Key

    Select to add key compression. If you select Compress Key, you must also include the number of columns to compress in the Columns field.

     

  9. Click B_AI_NextSelectSQL to start collecting the SQL to evaluate.
  10. Select the source from which you want to collect SQL. For this tutorial, select SGA.
  11. On the SQL collection page, click the edit link beside the parsing schema name. All the available parsing schemas are displayed in the graph. Select one of the parsing schemas by clicking it’s bar in the graph. Click the bar a second time to deselect it.
  12. Click the edit link beside Module. Notice that all available modules (for the selected schema) display in the graph. You can filter the SQL to collect by selecting a particular module or you can select all to collect SQL from all modules.

    Note: Use the mouse pointer to hover over a bar in the graph to display a descriptive tooltip containing SQL workload details to help you select a workload.

  13. Click the edit link beside Action. Notice that all available actions (for the selected schema) display in the graph. You can filter the SQL to collect by selecting a particular action or you can select all to collect SQL from all actions.
  14. When you are finished selecting a SQL workload, click B_AI_CollectSQL to start the collection process.
  15. SQL Optimizer collects the specified SQL statements. The SQL page opens displaying the list of SQL statements collected.
  16. In the SQL Workload pane, select a SQL statement to display the statement text and the parsed execution plan.
  17. You can remove a SQL statement from the workload by deselecting (clearing) the checkbox in the Include column.
  18. When you are satisfied with the SQL workload, click B_AI_RunImpactAnalysis to start the analysis process.
  19. When the analysis process is finished, one of the following will display, depending on the analysis results:

    • If an impact was found, the Impacts page displays.
    • If the analysis determines that non of the specified SQL were impacted, the log page displays and reports "No impacted SQL found."

About the Author

Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

Start the discussion at forums.toadworld.com