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 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. |
| 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. |
| 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. |
| Test for Scalability | Uses Benchmark Factory™ to simulate potential workload conditions to test SQL statement performance. |
| Incorporate Best Practices | Incorporates common best practices techniques to improve database performance. |
| Plan Control Mode | Description |
| Generate Execution Plan Alternatives | Generates execution plan alternatives for your SQL statements without changing the original source code. |
| Deploy Baselines |
Creates baselines from the execution plan alternatives and deploys these baselines to ensure optimal database performance. |
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.
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.
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.
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.
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.
Manage Plans organizes stored baselines and outlines used to improve SQL statement performance.
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:
|
| 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. |
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 |
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 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_$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 |
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
