Chat now with support
Chat mit Support

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

Additional Resources

 

Learn More and Connect with the Community

Use the following links to videos, blogs, and community resources where you can learn more about SQL Optimizer for Oracle®, find tips and tricks for using SQL Optimizer, and get answers to questions.

 

 

 

Database Privileges

Oracle database privileges limit access for individual users. The following list summarizes the functions in SQL Optimizer that require specific Oracle database privileges.

Module

Functionality

Privilege
All Modules

Trace setup options:

Enable collection of Oracle trace statistics

Requires ALTER SESSION privileges.

Requires access to the following views:

SYS.V_$SESSION

SYS.V_$PROCESS

All Modules Retrieve DBMS_XPLAN Requires access to the SYS.DBMS_XPLAN package.
All Modules General If the Oracle init parameter O7_DICTIONARY_ACCESSIBILITY for Oracle 8 or later is set to false, you cannot access objects under SYS even if you have SELECT ANY TABLE privileges. In this case, you need SELECT ANY DICTIONARY privileges or SELECT_CATALOG_ROLE to access the objects under SYS.
Optimize SQL (SQL Rewrite) Alter session parameters for executing SQL

Requires access to SYS.V_$PARAMETER view.

Generate virtual indexes Requires Oracle 8i or later.
Check existing translation for your SQL

Requires Oracle 12c or later.

Requires access to the SYS.ALL_SQL_TRANSLATIONS view.

Create SQL Translation Profile

Requires Oracle 12c or later.

Requires CREATE SQL TRANSLATION PROFILE privileges.

Deploy outlines

Requires Oracle 8i or later.

Requires CREATE ANY OUTLINE and DROP ANY OUTLINE privileges.

Requires access and UPDATE privileges to the following views:

OUTLN.OL$HINTS

OUTLN.OL$

OUTLN.OL$NODES

Register SQL Translation

Requires Oracle 12c or later.

Requires access to the SYS.DBMS_SQL_TRANSLATOR package.

Requires access to the SYS.ALL_SQL_TRANSLATION_PROFILES view.

Optimize SQL (Plan Control) Open database connection Requires Oracle 11g or later.
Retrieve execution plans, generate alternative plans

Requires ADMINISTER SQL MANAGEMENT OBJECT privileges.

Requires access to the following packages:

SYS.DBMS_SQL

SYS.DBMS_SPM

SYS.DBMS_XPLAN

Requires access to the following views:

SYS.DBA_SQL_PLAN_BASELINES

SYS.V_$SQLAREA

SYS.V_$SQLTEXT_WITH_ NEWLINES

Optimize SQL and Batch Optimize SQL

Execution method option:

Run on server setting

 Requires access to the SYS.DBMS_SQL package.
Retrieve run-time statistics

Requires access to the following views:

SYS.V_$MYSTAT

SYS.V_$STATNAME

SYS.V_$PARAMETER

Retrieve actual plan

Requires ALTER SESSION privileges.

Requires access to the SYS.DBMS_XPLAN package.

Requires access to the following views:

SYS.V_$SQLAREA

SYS.V_$ SQL_PLAN_STATISTICS_ALL

SYS.V_$SESSION

Capture bind values from database

Requires Oracle 10g or later.

Requires access to the following views:

SYS.V_$SQLAREA

SYS.V_$SQL_BIND_CAPTURE

Optimize Indexes Recommend indexes

Requires Oracle 8i or later.

Requires access to the SYS.V_$SESSION view.

Access the AWR

Requires Oracle 10g or later.

Requires access to the following system views:

SYS.DBA_HIST_SNAPSHOT

SYS.DBA_HIST_SQLTEXT

SYS.DBA_HIST_SQLSTAT

Display the control information for the Workload Repository

Requires access to the following system view:

SYS. DBA_HIST_WR_CONTROL

Display the SQL summary for the Workload Repository

Requires access to the following system view:

SYS. DBA_HIST_SQL_SUMMARY

Access the SGA Requires access to the SYS.V_$SQLAREA view.

Inspect SGA

SQL to collect: Executed SQL from SQL area 

Requires access to the following views:

SYS.V_$SQLAREA

SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_$SQLTEXT depending on your version of Oracle)

Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or later.

SQL to collect: Currently executing SQL

Requires access to the following views:

SYS.V_$OPEN_CURSOR

SYS.V_$SESSION

SYS.V_$SQLAREA

SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_$SQLTEXT depending on your version of Oracle)

Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or later.

Flush Oracle shared pool

Requires ALTER SYSTEM privileges.

Execution plan information

Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or later.

Monitor by session Requires access to the SYS.V_$SESSION view.

Manage Plans

(Baselines Management)

 Open a Baselines Management session or database connection Requires Oracle 11g or later.
View baselines Requires access to the SYS.DBA_SQL_PLAN_BASELINES view.
View baseline plans

Requires access to the SYS.DBA_SQL_PLAN_BASELINES view.

Requires access to the SYS.DBMS_XPLAN package.

Import, export, and migrate baselines

Requires ADMINISTER SQL MANAGEMENT OBJECT and CREATE TABLE privileges.

Requires access to the SYS.DBA_SQL_PLAN_BASELINES view.

Requires access to the SYS.DBMS_SPM package.

View and modify baseline configuration values

Obtaining the Oracle parameter values

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

and OPTIMIZER_USE_SQL_PLAN_BASELINES

Requires ADMINISTER SQL MANAGEMENT OBJECT and ALTER SYSTEM privileges.

Requires access to the SYS.DBMS_SPM package.

Requires access to the following views:

SYS.V_$SYSTEM_PARAMETER

SYS.DBA_SQL_MANAGEMENT_CONFIG

SYS.DBA_DATA_FILES

Manage Plans

(Outlines Management)

 Open an Outlines Management session or database connection Requires Oracle 8i or later.
View outlines Requires OUTLN.OL$HINTS (SELECT only) and OUTLN.OL$ (SELECT only) privileges.
Modify outline configuration values Requires ALTER SYSTEM and OUTLN.OL$ (SELECT only) privileges.
Modify outlines

Requires DROP ANY OUTLINE and ALTER ANY OUTLINE privileges.

Requires access to the SYS.OUTLN_PKG package.

Analyze Impact

 

 Virtual indexes for Index Impact

Requires Oracle 8i or later.

Requires access to the following system view:

SYS.V_$SESSION

Altering session parameters for Parameter Impact

Requires access to the following system view:

SYS.V_$PARAMETER

Access AWR

Requires Oracle 10g or later.

Requires access to the following system views:

SYS.DBA_HIST_SNAPSHOT

SYS.DBA_HIST_SQLTEXT

SYS.DBA_HIST_SQLSTAT

Display the control information for the Workload Repository

Requires access to the following system view:

SYS. DBA_HIST_WR_CONTROL

Display the SQL summary for the Workload Repository

Requires access to the following system view:

SYS. DBA_HIST_SQL_SUMMARY

Access SGA

Requires access to the following system view:

SYS.V_$SQLAREA

Display database information for Compare Database Impact

Requires access to the following system views:

SYS.PRODUCT_COMPONENTS_VERSION

SYS.V_$PARAMETER

SYS.NLS_SESSION_PARAMETERS

SYS.NLS_INSTANCE_PARAMETERS

SYS.NLS_DATABASE_PARAMETERS

SYS.V_$SGAINFO

SYS.V_$DATABASE

SYS.V_$INSTANCE

SYS.V_$STATISTICS_LEVEL

SYS.V_$OPTION

 

Related Topic

Database Privileges Script

Connect to the Database

Database Privileges Script

The following script provides the commands required to grant the Oracle database privileges needed for various functions in SQL Optimizer.

/* This is a sample script for granting all the privileges required by the SQL Optimizer user. */

/* In order to grant privileges on SYS objects, you must login with the SYS user */

/* Please verify the script below before executing. */

 

/* Create a new role */

create role sqloptimizer_user;

 

/* For the Run on Server function */

grant EXECUTE on SYS.DBMS_SQL to sqloptimizer_user;

 

/* For retrieving run time statistics */

grant SELECT on SYS.V_$MYSTAT to sqloptimizer_user;

grant SELECT on SYS.V_$STATNAME to sqloptimizer_user;

 

/* For retrieving trace statistics */

grant SELECT on SYS.V_$PARAMETER to sqloptimizer_user;

 

/* For trace setup */

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

grant SELECT on SYS.V_$PROCESS to sqloptimizer_user;

 

/* For retrieving actual plan */

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

grant SELECT on SYS.V_$ SQL_PLAN_STATISTICS_ALL to sqloptimizer_user;

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_XPLAN to sqloptimizer_user;

grant ALTER SESSION to sqloptimizer_user;

 

/* For capturing bind values from database */

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

grant SELECT on SYS.V_$SQL_BIND_CAPTURE to sqloptimizer_user;

 

/* For altering session parameters */

grant SELECT on SYS.V_$PARAMETER to sqloptimizer_user;

 

/* For deploying stored outlines */

grant CREATE ANY OUTLINE to sqloptimizer_user;

grant DROP ANY OUTLINE to sqloptimizer_user;

grant SELECT, UPDATE on OUTLN.OL$HINTS to sqloptimizer_user;

grant SELECT, UPDATE on OUTLN.OL$ to sqloptimizer_user;

grant SELECT, UPDATE on OUTLN.OL$NODES to sqloptimizer_user;

 

/* For generating plans in Plan Control session */

grant ADMINISTER SQL MANAGEMENT OBJECT to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_SPM to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_XPLAN to sqloptimizer_user;

grant SELECT on SYS.DBA_SQL_PLAN_BASELINES to sqloptimizer_user;

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to sqloptimizer_user;

 

/* For inspecting executed SQL from SGA */

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to sqloptimizer_user;

 

/* For inspecting currently executing SQL from SGA */

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to sqloptimizer_user;

grant SELECT on SYS.V_$OPEN_CURSOR to sqloptimizer_user;

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

 

/* For inspecting currently executing SQL from SGA and monitor by session*/

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

 

/*For retrieving the plan information (applicable to Oracle 9 or later) */

grant SELECT on SYS.V_$SQL_PLAN to sqloptimizer_user;

 

/* For flushing the shared pool */

grant ALTER SYSTEM to sqloptimizer_user;

 

/* For viewing baselines and their plans */

grant SELECT on SYS.DBA_SQL_PLAN_BASELINES to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_XPLAN to sqloptimizer_user;

 

/* For exporting, importing and migrating baselines */

grant ADMINISTER SQL MANAGEMENT OBJECT to sqloptimizer_user;

grant CREATE TABLE to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_SPM to sqloptimizer_user;

grant SELECT on SYS.DBA_SQL_PLAN_BASELINES to sqloptimizer_user;

 

/* For viewing and modifying baseline configuration values */

grant ADMINISTER SQL MANAGEMENT OBJECT to sqloptimizer_user;

grant ALTER SYSTEM to sqloptimizer_user;

grant EXECUTE on SYS.DBMS_SPM to sqloptimizer_user;

grant SELECT on SYS.V_$SYSTEM_PARAMETER to sqloptimizer_user;

grant SELECT on SYS.DBA_SQL_MANAGEMENT_CONFIG to sqloptimizer_user;

grant SELECT on SYS.DBA_DATA_FILES to sqloptimizer_user;

 

/* For managing stored outlines */

grant DROP ANY OUTLINE to sqloptimizer_user;

grant ALTER ANY OUTLINE to sqloptimizer_user;

grant EXECUTE on SYS.OUTLN_PKG to sqloptimizer_user;

grant SELECT on OUTLN.OL$HINTS to sqloptimizer_user;

grant SELECT on OUTLN.OL$ to sqloptimizer_user;

 

/* For recommending indexes in Optimize Indexes */

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

 

/* For collecting SQL from AWR in Optimize Indexes */

grant SELECT on SYS.DBA_HIST_SNAPSHOT to sqloptimizer_user;

grant SELECT on SYS.DBA_HIST_SQLTEXT to sqloptimizer_user;

grant SELECT on SYS.DBA_HIST_SQLSTAT to sqloptimizer_user;

 

/* For Optimize Indexes, Analyze Impact displaying control information from AWR */

grant SELECT on SYS.DBA_HIST_WR_CONTROL to sqloptimizer_user;

 

/* For Optimize Indexes, Analyze Impact displaying SQL summary from AWR */

grant SELECT on SYS.DBA_HIST_SQL_SUMMARY to sqloptimizer_user;

 

/* For collecting SQL from SGA in Optimize Indexes */

grant SELECT on SYS.V_$SQLAREA to sqloptimizer_user;

 

/* For Analyze Impact index impact */

grant SELECT on SYS.V_$SESSION to sqloptimizer_user;

 

/* For Analyze Impact index impact, parameter impact */

grant SELECT on SYS.V_$PARAMETER to sqloptimizer_user;

 

/* For Analyze Impact compare database impact */

grant SELECT on SYS.PRODUCT_COMPONENTS_VERSION to sqloptimizer_user;

grant SELECT on SYS.V_$PARAMETER to sqloptimizer_user;

grant SELECT on SYS.V_$SGAINFO to sqloptimizer_user;

grant SELECT on SYS.V_$DATABASE to sqloptimizer_user;

grant SELECT on SYS.V_$INSTANCE to sqloptimizer_user;

grant SELECT on SYS.V_$STATISTICS_LEVEL to sqloptimizer_user;

grant SELECT on SYS.V_$OPTION to sqloptimizer_user;

grant SELECT on SYS.NLS_SESSION_PARAMETERS to sqloptimizer_user;

grant SELECT on SYS.NLS_INSTANCE_PARAMETERS to sqloptimizer_user;

grant SELECT on SYS.NLS_DATABASE_PARAMETERS to sqloptimizer_user;

 

/* Grant to the role: SELECT_CATALOG_ROLE */

grant SELECT_CATALOG_ROLE to sqloptimizer_user;

 

/* For retrieving DBMS_XPLAN */

grant EXECUTE on SYS.DBMS_XPLAN to sqloptimizer_user;

 

/* Grant the role to a user */

grant sqloptimizer_user to &username;

 

/* For create a SQL Translation Profile to use in the SQL Translation window */

grant CREATE SQL TRANSLATION PROFILE to sqloptimizer_user;

 

 

Related Topic

Database Privileges

Connect to the Database

You can use the Connection Manager to create a new connection or connect to an existing connection. SQL Optimizer supports TNS, Direct, or Oracle Lightweight Directory Access Protocol (LDAP) connections.

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

To create a connection

  1. Select Connection | Open Connection Manager.

  2. Enter a user name and password.

  3. Specify the connection properties. Review the following for additional information:

    TNS Tab Description

    Database

    Select an Oracle database for the connection.

    Tip: You can also enter an easy connect string in the following format: host:port\service_name.

    Connect as

    Select whether to connect as either SYSDBA or SYSOPER if you have the appropriate permissions. Otherwise, select NORMAL.

    Connect Using

    Select the appropriate Oracle Home.

    Direct Tab Description

    Host

    		 Enter the system name for the server on which the database resides.

    Port

    		 Enter the port number for the server on which the database resides.

    Service Name/SID

    Enter either the Service Name or SID of the database for the connection.

    Note: You can find the SID value in the registry under each installed home.

    LDAP Tab Description

    LDAP Descriptor

    Enter the database name for the connection or click to select a database name.

    Notes:

    • SQL Optimizer supports Oracle names directory services. This support includes both Oracle OID and Microsoft Active Directory servers.

    • Instant Client LDAP support requires the LDAP DDL that Oracle does not install by default. To use this feature, you must install the ORALDAPCLNT10.DDL in the same location as the oci.dll file.

    • For Instant Client LDAP support, TNSNAMES.ora, LDAP.ora, and SQLNET.ora must exist in the same location specified by the TNS_ADMIN system variable.

  4. Click Create to save the connection and immediately connect to the database.

Notes:

  • The Connection Manager stores connections created in other Quest Software products when you launch SQL Optimizer from these products.

  • Right-click an existing connection and select Remove Selected Entry to remove the connection.
  • To re-establish lost connections, select Connection | Test Connections (Reconnect). SQL Optimizer checks existing connections and re-establishes any that were lost.
  • Oracle automatically terminates inactive sessions after 15 to 60 minutes. Users with the ServiceAdministrator role have the ability to set the time limit.

View Database Privileges

You can view a list of privileges required to use SQL Optimizer for each database connection listed in the Connection Manager.

To view required/missing privileges for a connection

  1. Select Connection | Open Connection Manager.
  2. In the Connection Manager, locate your connection in the list and then click inside the cell in the Privileges column for that connection.

  3. A drop-down pane opens listing the missing or required privileges for that connection. Click on the privilege link to display a description of the privilege. For a complete list of Oracle database privileges required to use SQL Optimizer, see Database Privileges.

    Note: Missing privileges are displayed for open connections. Required privileges are displayed for closed connections.

 

Related Topics

Inspect SGA Connection

Optimize SQL Connection

Manage Outlines Connection

Database Privileges

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen