Chatta subito con l'assistenza
Chat con il supporto

SQL Optimizer for Oracle 10.0 - 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

Trace Setup

SQL Optimizer can retrieve trace statistics each time you execute a SQL statement in Optimize SQL or Optimize Indexes. The Oracle SQL tracing function writes the statistics to a log file on the server. You must set up the file access method for retrieving the statistics from the log file before viewing this information.

Notes:

  • Your user account must have ALTER SESSION privileges to enable the trace function in Oracle.

  • By default, Oracle stores the log file in the location specified by the Oracle USER_DUMP_DEST parameter. You can change this location in the init.ora.file.
  • This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set trace setup options

  1. Click in the main menu.

  2. Select General | Trace Setup.

  3. Review the following for additional information:

    Trace Setup Options

    Description

    Select database connection for Oracle trace log

    Click to select a connection for the trace setup.

    Tip: Click to open the Connection Manager to create a new connection. See Connect to the Database for more information.

    Access Method

    Description

    Select the access method

    Select one of the following:

    • File Transfer Protocol (FTP)—The FTP connection must already exist for the Oracle instance for you to connect.
    • Network File Server (NFS)—For remote NFS connections, the trace file location is different than the USER_DUMP_DEST location.
    • UTL_File—To use this file access method, the UTL_FILE package must be installed on the Oracle instance and valid directory specification parameters must exist in the init.ora.file for USER_DUMP_DEST and UTL_FILE_DIR.

    Note: If you are connected to an Oracle 9i (or later) database, when configuring the UTL_FILE access method, SQL Optimizer recommends using a DIRECTORY object to access the file directory.

 

 

Execution Plan

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

To set execution plan options

  1. Click in the main menu.

  2. Select General | Execution Plan.

  3. Specify options for the display of execution plans. Review the following for additional information:

    TABLE ACCESS FULL warning threshold Specify a table size threshold at which the TABLE ACCESS FULL operation is highlighted in red.
    Color

    Select the color for individual items in the execution plan.

    Font Select the font settings fthe individual items in the execution plan.

    Note: The execution plan displays in several panes. Use this page to specify color and font options to apply globally to all Execution Plan panes. To customize display options for individual panes, right-click the execution plan and select Plan Options .

 

Related Topics

About Execution Plans

Test for Scalability

To set test for scalability options

  1. Click in the main menu.

  2. Select General | Test for Scalability.

  3. Review the following for additional information:

    Minimum number of virtual users

    Enter the minimum number of virtual users to use for scalability testing.

    Range: 1 to 1,000,000,000

    Maximum number of virtual users

    Enter the maximum number of virtual users to use for scalability testing.

    Range: 1 to 1,000,000,000

    Step value of virtual users

    Enter the number of virtual users to increase by for each step of scalability testing.

    Range: 1 to 1,000,000,000

    Distribution Model

    Select a latency think time distribution model:

    • None—Does not insert a latency think time between executions.
    • Absolute—Uses the value entered in the Duration field as the latency think time between executions.
    • Uniform—Uses a random duration between 1 millisecond and the value entered in the Duration field as the latency think time.
    • Negative Exponential—Uses a random duration based on a mathematical model weighted towards lower latency think times. The value entered in the Duration field is used for calculating think times.
    • Normal—Uses a random duration based on a mathematical model weighted towards average latency think times. The value entered in the Duration field is used for calculating think times.
    • Poisson—Uses a random duration based on a mathematical model weighted towards discrete latency think times. The value entered in the Duration field is used for calculating think times.
    Duration (milliseconds)

    Enter a duration in milliseconds.

    Note: How SQL Optimizer uses the value you enter depends on the distribution model selected.

    Execute Each SQL Statement by

    Select one of the following:

    • Duration (seconds)—Enter the duration of the scalability test in seconds.

    • Number of times—Enter the number of times to execute the SQL statement.

      Range: 1 to 100

 

Related Topics

Test for Scalability (Optimize SQL)

Test for Scalability (Batch Optimize)

Directory Setup

To set directory setup options

  1. Click in the main menu.

  2. Select General | Directory Setup.

  3. Specify data directory location or locations. Review the following for additional information:

    Group / Option Description
    Application Data Directory

    Select a location for the SQL Optimizer application data directory which stores connection profiles, layout files, log files, and other data.

    Use this area to store all data directories in one location. Each module's data files are grouped by sub-directory.

    Note: The default location is in the user's Roaming directory.

    Use default—Resets the location to the primary default location, which is in the user's Roaming directory.

    Use local default—Sets the location to the optional default location, which is in the user's Local directory.

    Note: If you change the location of the application data directory, you must restart SQL Optimizer for the change to take effect.

    Data Directories

    Use this area to specify data directory locations for each module, individually.

    Note: Click Show Details to display the options.

    Use Application Data Directory—Select this option to use the location specified above under Application Data Directory. After selecting this option, the text boxes for individual data directories are disabled.

    Optimize SQL data directory

    Select the directory to store data files created using Optimize SQL.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Optimize SQL Data\

    Batch Optimize SQL data directory

    Select the directory to store data files created using Batch Optimize SQL.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Batch Optimize SQL Data

    Scan SQL data directory

    Select the directory to store data files created using Scan SQL.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Scan SQL Data

    Inspect SGA data directory

    Select the directory to store data files created using Inspect SGA.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Inspect SGA Data

    Analyze Impact data directory

    Select the directory to store data files created using Analyze Impact.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Analyze Impact Data

    Optimize Indexes data directory

    Select the directory to store data files created using Optimize Indexes.

    Default: C:\Users\<user>\AppData\Roaming\Quest Software\Quest SQL Optimizer for Oracle\<version>\Optimize Indexes Data

    Note:

    • It is not advisable to change the directory location once you create data files because SQL Optimizer will not move existing files to the new directory location.

 

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione