Toad World Blog

Batch Optimize SQL

Jan 10, 2013 11:00:00 PM by Steve Hilker

To batch optimize SQL

  1. Select the Batch Optimize tab in the main window.

  2. Click Add Code to Optimize in the Batch Job List pane and select All Types. The Add Batch Optimize Jobs window displays.

  3. Review the following for additional information:

    Connection Page Description

    Connection

    Click 4812.TB_DownArrow to select a previously created database connection.

    Tips:

    • Click TB_Ellipses to open the Connection Manager to create a new connection.

    • You can select an alternative connection for executing the SQL statement alternatives Batch Optimize generates.

    Database Objects Page Description

    Database objects

    Select a schema, database object type, or individual database object, and then click icon_PickRtArrow to add the object.

    Tip:

    • Click TB_Ellipses to browse for database objects.

    • Your database privileges determine if you can scan all selected database objects.

    Execute using schema

    Click 4812.TB_DownArrow and select an alternative schema for executing the SQL statement alternatives.
    Source Code Page Description

    Source code type

    Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming source code to indicate the source code type for the file or directory you want to scan.

    Add by file

    Click TB_AddSourceCode and browse to the files you want to add.

    Add by directory

    Click TB_AddSourceCode and browse to the directories you want to add.

    Note: Select the Include Sub-directory checkbox to scan sub-directories.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Execute using schema

    Click 4812.TB_DownArrow and select an alternative schema for executing the SQL statement alternatives.
    SQL Text Page Description

    SQL text

    Enter SQL statement text.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Execute using schema

    Click 4812.TB_DownArrow and select an alternative schema for executing the SQL statement alternatives.
    Scan SQL Page Description

    Group

    Select the Scanner group that contains the SQL statements you want to scan.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Execute using schema

    Click 4812.TB_DownArrow and select an alternative schema for executing the SQL statement alternatives.
    Inspect SGA Page Description

    Group

    Select the Inspector group that contains the SQL statements you want to scan.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Execute using schema

    Click 4812.TB_DownArrow and select an alternative schema for executing the SQL statement alternatives.

    Foglight Performance Analysis for Oracle Page

    Description

    Select a database to search for the repository used to store captured SQL

    Click 4812.TB_DownArrow to select a previously created database connection, and then click Check for PA Repository to locate the repository.

    Tip: Click TB_Ellipses to open the Connection Manager to create a new connection.

    Note: Batch Optimize helps you manage jobs by organizing them into batches. Use the Batch Info page to create a new batch or to add the current job to an existing batch.

     

  4. Click Finish to start batch optimization.

    Batch Optimize scans the job you created, classifies and optimizes the statements, and executes the SQL statement alternatives it generates.

    Notes: 

    • Scanning starts automatically if you select the Automatically start extracting SQL when job is added checkbox in the Batch Optimize options page. Batch Optimize selects this checkbox by default.

    • Batch Optimize selects the SQL statement to optimize based on the classification types selected in the Batch Optimize options page. Batch Optimize selects Problematic and Complex SQL classification types by default.

    • Batch Optimize executes SQL alternatives it generates based on the statement types selected in the Batch Optimize options page. Batch Optimize selects SELECT statements by default.

  5. Select Batch List in the Batch Job List pane to view information about the jobs you created.

    The Batch List pane sorts information about your jobs by batches. Additional information displays in the Jobs Improved pane.

  6. Select a batch from the batch list node to see details for the batch in the Job List pane.

    The Job List pane displays the type of job, job status, and time of improvement for each job in the batch. Additional information displays in the SQL Classification and Cost and Elapsed Time Comparison panes for a selected job.

    Tip: Select a job in the Job List pane and click TB_OptimizeScript to generate a replacement script with the optimized SQL statement.

  7. Select a job from the batch node to see details of the job.

    The SQL List pane displays SQL classification information for the SQL statements in the job you select. The Original SQL Text and Best Alternative SQL Text panes allow you to compare your original SQL statement with the best alternative Batch Optimize generated.

    Tip: Select a SQL statement in the SQL List pane and click TB_SendtoOptimizer_BatchOptimize to send the statement to Optimize SQL and view all SQL alternatives.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki Tutorial

Steve Hilker

Written by 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.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.