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.
- SQL Optimizer for Oracle Community - Ask questions, join the discussion, and find videos and user guides.
- SQL Optimizer for Oracle Blog - Read about new features, learn SQL tuning techniques for specific situations/environments, and gain new insights and strategies.
- SQL Optimizer for Oracle Forum - Join the discussion.
- Toad for Oracle Blog - Learn more about Toad for Oracle.
- Technical Documentation (Quest Support Portal) - Find documentation for current and earlier versions of SQL Optimizer.
- Toad World - Visit other Toad Communities, including Toad for Oracle®. Find DBMS and SQL knowledge, find software downloads, and find answers to your database 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 | |
| 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. |
| 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_$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 |
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;
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
-
Select Connection | Open Connection Manager.
- Enter a user name and password.
-
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.
-
-
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
- Select Connection | Open Connection Manager.
- In the Connection Manager, locate your connection in the list and then click inside the cell in the Privileges column for that connection.
-
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.