Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.2.3 - User Guide

Tutorial: Inspect SGA

Inspect SGA retrieves executed SQL statements from Oracle's System Global Area or currently running SQL statements from Oracle's open cursor. Once you retrieve the statements, Inspect SGA displays the statements and their run time statistics so you can identify resource intensive statements in your database environment.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To retrieve a previously executed SQL statement

  1. Select the Inspect SGA tab in the main window.

    Note: To retrieve previously executed SQL statements, you must have privileges to view SYS.V_$SQLAREA and either SYS.V_$SQLTEXT_WITH_NEWLINES or SYS.V_$SQLTEXT.

  2. Click to select a group or click to create a new group in the Group list.

  3. Click . The Add Inspect SGA Job wizard displays.

  4. Complete the following fields in the wizard:

    General Information Page Description

    Job type

    Select the Executed SQL from SQL Area option.
    Collecting Criteria Page Description

    Collecting Criteria

    Select the Top n records option and enter the number of records to display.

    First by

    Click and select the statistic to use to extract SQL statements if you are not displaying all records.

    Note: A large SGA increases processing time.

    Collection Time Page Description

    Collection Time

    Select the Start collecting when you click the Inspect button option.
  5. Click to retrieve the SQL statements and run time statistics.

  6. Select a statement that requires optimization in the SQL Statistics pane and click to send to Batch Optimize SQL or click the arrow beside and select an option.

    Tip: You can add an Inspect SGA job in Batch Optimize to optimize all the SQL statements in the collection.

 

Tutorial: Analyze Impact

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 PI 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 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 to move it to the Indexed Columns list.
  7. Click 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 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 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 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. See "Review Impact Analysis Results" in the online help for more information.
    • If the analysis determines that non of the specified SQL were impacted, the log page displays and reports "No impacted SQL found."

Tip: Use and to navigate back and forth between the pages of a tab.

 

Tutorial: Analyze Index Impact

You can analyze the impact of new indexes on your SQL statement's execution plans before you physically create the indexes on your database. You can create indexes in Optimize SQL or Optimize Indexes and then send the index or indexes to Analyze Impact to evaluate the impact on your SQL workload.

Use the Optimize SQL module to generate index alternatives for a single SQL statement. Or use the Optimize Indexes module to generate index alternatives for a SQL workload or group of SQL statements.

To send indexes to Analyze Impact from Optimize SQL

  1. Generate index alternatives using the Index Generation feature in Optimize SQL. See Tutorial: Generate Indexes in Optimize SQL for more information.

  2. Select the virtual index alternative you want to use for the analysis in the Alternatives pane in Optimize SQL.

  3. Click in Optimize SQL. A new Analyze Impact session opens.

  4. The Indexing Change Details page is populated with the new index that you sent from the Optimize SQL session. Use this page to modify the index, if necessary. See "Analyze the Impact of Additional Indexes" in the online help for more information.

  5. When you have finished modifying the index, click to start collecting the SQL to evaluate. See "Collect SQL and Run an Impact Analysis" in the online help for more information.

To send indexes to Analyze Impact from Optimize Indexes

  1. Generate index alternatives in Optimize Indexes. See "About Optimize Indexes" in the online help for more information.
  2. In the Optimize Indexes session, select the Results tab.
  3. On the Results page, in the Results Summary pane, click Send to Analyze Impact. A new Analyze Impact session opens.
  4. The Indexing Change Details page is populated with the new indexes that you sent from the Optimize Indexes session. Use this page to modify the index or indexes, if necessary. See "Analyze the Impact of Additional Indexes" in the online help for more information.
  5. When you have finished modifying the indexes, click to start collecting the SQL to evaluate. See "Collect SQL and Run an Impact Analysis" in the online help for more information.

 

Tutorial: Manage Outlines

Outline Management displays stored outlines deployed using SQL Rewrite mode in Optimize SQL.

To manage outlines

  1. Select the Manage Plans tab in the main window.

    Tip: Select the Show Manage Plans checkbox on the Manage Plans options page to display the Manage Plans tab in the main window.

  2. Click Manage Plans. The Create a New Manage Plans Session window displays.

  3. Select a connection to use. 

  4. Select the Outlines Management tab.

  5. Select a category in the Category/Outline pane.

    You can delete or rename the selected category.

  6. Select a stored outline from the category node.

    You can move, rename, or reset the Used Flag on the selected stored outline.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating