Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.3.3 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

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: source code, an Oracle Automatic Workload Repository (AWR), or the Oracle System Global Area (SGA).

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.

    • 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.

 

Related Topics

About Analyze Impact

 

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 Topics

About Managing Plans

Deploy Outlines

Manage Outlines

About Us

About Us

We are more than just a name

We are on a quest to make your information technology work harder for you. That is why we build community-driven software solutions that help you spend less time on IT administration and more time on business innovation. We help you modernize your data center, get you to the cloud quicker and provide the expertise, security and accessibility you need to grow your data-driven business. Combined with Quest’s invitation to the global community to be a part of its innovation, and our firm commitment to ensuring customer satisfaction, we continue to deliver solutions that have a real impact on our customers today and leave a legacy we are proud of. We are challenging the status quo by transforming into a new software company. And as your partner, we work tirelessly to make sure your information technology is designed for you and by you. This is our mission, and we are in this together. Welcome to a new Quest. You are invited to Join the Innovation.

Our brand, our vision. Together.

Our logo reflects our story: innovation, community and support. An important part of this story begins with the letter Q. It is a perfect circle, representing our commitment to technological precision and strength. The space in the Q itself symbolizes our need to add the missing piece—you—to the community, to the new Quest.

Contact Quest

For sales or other inquiries, visit www.quest.com/contact.

Technical Support Resources

Technical support is available to Quest customers with a valid maintenance contract and customers who have trial versions. You can access the Quest Support Portal at https://support.quest.com.

The Support Portal provides self-help tools you can use to solve problems quickly and independently, 24 hours a day, 365 days a year. The Support Portal enables you to:

  • Submit and manage a Service Request
  • View Knowledge Base articles
  • Sign up for product notifications
  • Download software and technical documentation
  • View how-to-videos
  • Engage in community discussions
  • Chat with support engineers online
  • View services to assist you with your product

SQL Optimizer Community

You can also find help and additional information at the SQL Optimizer community at:

https://blog.toadworld.com/tag/sql-optimizer-for-oracle

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating