SQL Optimizer for Oracle 9.2.2 - User Guide

Introduction to SQL Optimizer

About SQL Optimizer for Oracle

SQL Optimizer for Oracle® automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer for Oracle® analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA). Once SQL Optimizer identifies problematic SQL statements, it optimizes the SQL and provides replacement code that includes the optimized statement.

SQL Optimizer also provides a complete index optimization and plan change analysis solution. It provides index recommendations for multiple SQL statements or a SQL workload, simulates index impact analysis, and generates SQL execution plan alternatives.

SQL Optimizer consists of the following modules:

Optimize SQL

Optimize SQL includes a SQL Rewrite mode and a Plan Control mode.

SQL Rewrite Mode Description
Optimize SQL Statements

Uses SQL Optimizer's Artificial Intelligence engine to execute SQL syntax rules and apply Oracle optimization hints to create semantically equivalent SQL statement alternatives. In addition, you can create user-defined alternatives to test with your database environment. See "About Optimizing SQL" in the online help for more information.

Test Run SQL Alternatives

Test run SQL statement alternatives to view execution statistics. This provides execution times that allow you to identify the best SQL statement for your database environment. See "Execute Scenarios" in the online help for more information.

Generate Index Alternatives

Analyzes SQL statement syntax and database structure to provide index alternatives that improve performance. SQL Optimizer uses virtual indexes to generate alternatives without physically creating the indexes on your database. See "About Index Generation" in the online help for more information.

Test for Scalability Uses Benchmark Factory™ to simulate potential workload conditions to test SQL statement performance. See "Test for Scalability" in the online help for more information.
Incorporate Best Practices Incorporates common best practices techniques to improve database performance. See "Best Practices" in the online help for more information.
Plan Control Mode Description
Generate Execution Plan Alternatives Generates execution plan alternatives for your SQL statements without changing the original source code. See "Generate Execution Plan Alternatives" in the online help for more information.
Deploy Baselines

Creates baselines from the execution plan alternatives and deploys these baselines to ensure optimal database performance. See "Deploy Baselines" in the online help for more information.

Optimize Indexes

Optimize Indexes analyzes a SQL workload or any group of SQL statements and determines the best index set for the workload or group of statements. See "About Optimize Indexes" in the online help for more information.

Batch Optimize

Batch Optimize submits files, database objects, SQL text, or statements stored in a Foglight Performance Investigator repository for batch processing. Batch Optimize scans and extracts the SQL statements, optimizes the statements, and tests the alternatives to find the best performing SQL statements for your database environment. See "About Batch Optimize" in the online help for more information.

Scan SQL

Scan SQL identifies problematic SQL statements in your source code and database objects without execution. Scan SQL then analyzes the problematic SQL statements and categorizes them according to performance levels. See "About Scanning SQL" in the online help for more information.

Inspect SGA

Inspect SGA analyzes SQL statements from Oracle's SGA. You can specify the criteria used to retrieve SQL statements and execution statistics to review SQL performance. See "About Inspect SGA" in the online help for more information.

Analyze Impact

Use Analyze Impact to evaluate the impact on a SQL workload from database changes by tracking execution plan and Oracle cost changes for SQL statements. You can run an impact analysis to estimate the performance impacts from parameter changes and new indexing. You can also run a comparison of two different databases that run the same application. See "About Analyze Impact" in the online help for more information.

Manage Plans

Manage Plans organizes stored baselines and outlines used to improve SQL statement performance. See "About Managing Plans" in the online help for more information.

SQL Optimization Workflow

The SQL Optimization workflow ensures that your SQL statements perform optimally in your database environment.

Procedure Description
Identify Problematic SQL

Batch Optimize extracts embedded SQL statements from your database objects. After extracting the statements, it analyzes execution plan operations and identifies potential performance bottlenecks.

Notes:

  • You can use Inspect SGA to capture dynamic SQL statements. Save the captured dynamic SQL statements into an inspector file and use Batch Optimize to extract the statements.

  • You can also use Scan SQL to extract embedded SQL statements.
Optimize SQL Statements

Once Batch Optimize identifies problematic SQL statements, it automatically optimizes these statements and generates alternatives with unique execution plans. Batch Optimize generates the alternatives by analyzing SQL statement syntax and database structure. You can also use hints during the optimization process.

Note: You can also use SQL Rewrite mode in Optimize SQL to optimize SQL statements extracted with Scan SQL.

Test Run SQL Alternatives

After Batch Optimize generates alternatives, it will automatically test run the alternatives and provide you with the best statement for your database environment.

Note: Since Batch Optimize automates the SQL optimization process, you are only provided with the best alternative statement it generates. You can send your statement to Optimize SQL to view all statement alternatives available.

Compare SQL Alternatives

Batch Optimize compares the SQL text and execution plans of your original SQL statement and the best alternative.

Note: If you send your statement to Optimize SQL, you can compare your original SQL statement with any of the statement alternatives available.

Replace Problematic SQL Statements Batch Optimize creates a script that you can use to replace your original source code.
Generate Index Alternatives

In addition to optimizing SQL statements, you can generate index alternatives for your statement in Optimize SQL. You can generate new indexes for a group of SQL statements or for a SQL workload using Optimize Indexes.

Generate Execution Plan Alternatives You can use Plan Control mode in Optimize SQL to generate execution plan alternatives for your SQL statements without changing the original source code. Plan Control mode finds the best execution plan for your SQL statement and deploys it as a baseline.
Analyze Performance Changes Analyze Impact evaluates what impact certain changes, such as indexing and parameter changes, will have on SQL statement performance.

 

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

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 Foglight PI Repository

Requires access to the following tables:

QUEST_SC_ACTION_DIM

QUEST_SC_CLIENT_INFO_DIM

QUEST_SC_MODULE_DIM

QUEST_SC_SQL_STAT_FACT

QUEST_SC_SQL_SYNTAX_DIM

QUEST_CTRL_PYRAMID_LEVELS

QUEST_DB_USER_DIM

QUEST_INSTANCE_DIM

QUEST_PROGRAM_DIM

QUEST_TIME_DIM

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 Foglight PI Repository

Requires access to the following tables:

QUEST_SC_ACTION_DIM

QUEST_SC_CLIENT_INFO_DIM

QUEST_SC_MODULE_DIM

QUEST_SC_SQL_STAT_FACT

QUEST_SC_SQL_SYNTAX_DIM

QUEST_CTRL_PYRAMID_LEVELS

QUEST_DB_USER_DIM

QUEST_INSTANCE_DIM

QUEST_PROGRAM_DIM

QUEST_TIME_DIM

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

Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents