SQL Tuning Advisor
SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements.
SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack.
Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.
Tuning recommendations include:
• Collection of object statistics
• Creation of indexes
• Rewriting SQL statements
• Creation of SQL profiles
• Creation of SQL plan baselines
SQL Tuning Advisor Architecture
Automatic Tuning Optimizer is the central tool used by SQL Tuning Advisor. The advisor can receive SQL statements as input from multiple sources,
analyze these statements using the optimizer, and then make recommendations.
The following figure shows the basic architecture of SQL Tuning Advisor.
Input to SQL Tuning Advisor
Input for SQL Tuning Advisor can come from several sources, including ADDM, AWR, the shared SQL area, and SQL tuning sets.
SQL Tuning Advisor uses its input sources as follows:
• Automatic Database Diagnostic Monitor (ADDM)
The primary input source for SQL Tuning Advisor is ADDM (pronounced Adam). By default, ADDM runs proactively once every hour.
To identify performance problems involving high-load SQL statements, ADDM analyzes key statistics gathered by Automatic Workload Repository (AWR) over the last hour .
If a high-load SQL statement is identified, then ADDM recommends running SQL Tuning Advisor on the SQL.
• AWR
AWR takes regular snapshots of system activity, including high-load SQL statements ranked by relevant statistics, such as CPU consumption and wait time.
You can view the AWR and manually identify high-load SQL statements. You can run SQL Tuning Advisor on these statements,
although Oracle Database automatically performs this work as part of automatic SQL tuning. By default, AWR retains data for the last eight days.
You can locate and tune any high-load SQL that ran within the retention period of AWR using this technique.
• Shared SQL area
The database uses the shared SQL area to tune recent SQL statements that have yet to be captured in AWR.
The shared SQL area and AWR provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows,
which by default is at least 8 days.
• SQL tuning set
A SQL tuning set (STS) is a database object that stores SQL statements along with their execution context.
An STS can include SQL statements that are yet to be deployed, with the goal of measuring their individual performance,
or identifying the ones whose performance falls short of expectation. When a set of SQL statements serve as input, the database must first constructs and use an STS.
Automatic Tuning Optimizer Analyses
The following graphic depicts the different types of analysis that Automatic Tuning Optimizer performs.
A=> Statistical Analysis
The optimizer relies on statistics to generate execution plans.
If these statistics are stale or missing, then the optimizer can generate suboptimal plans.
Automatic Tuning Optimizer checks for missing or stale statistics, and recommends gathering fresh statistics if needed.
Object statistics
The optimizer checks the statistics for each object referenced in the query.
System statistics
The cost of smart scans depends on the system statistics I/O seek time, multiblock read count, and I/O transfer speed.
If gathering these statistics would improve the plan, then SQL Tuning Advisor recommends accepting a SQL profile.
B=> SQL Profiling
SQL profiling is the verification by the Automatic Tuning Optimizer of its own estimates.
By reviewing execution history and testing the SQL, the optimizer can ensure that it has the most accurate information available to generate execution plans.
SQL Profile Implementation
If the optimizer generates auxiliary information during statistical analysis or SQL profiling, then the optimizer recommends implementing a SQL profile.
When SQL Tuning Advisor is run on demand, the user must choose whether to implement the SQL profile.
When the Automatic SQL Tuning task is configured to implement SQL profiles automatically,
advisor behavior depends on the setting of the ACCEPT_SQL_PROFILE tuning task parameter:
-> If set to true, then the advisor implements SQL profiles automatically.
-> If set to false, then user intervention is required.
-> If set to AUTO (default), then the setting is true when at least one SQL statement exists with a SQL profile, and false when this condition is not satisfied.
C=> Access Path Analysis
An access path is the means by which the database retrieves data.
Automatic Tuning Optimizer explores whether a new index can significantly enhance query performance and recommends either of the following:
Creating an index
Index recommendations are specific to the SQL statement processed by SQL Tuning Advisor.
Sometimes a new index provides a quick solution to the performance problem associated with a single SQL statement.
Running SQL Access Advisor
Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload,
it also recommends running SQL Access Advisor on the SQL statement along with a representative SQL workload.
SQL Access Advisor examines the effect of creating an index on the SQL workload before making recommendations.
D=> SQL Structural Analysis
During structural analysis, Automatic Tuning Optimizer tries to identify syntactic, semantic, or design problems that can lead to suboptimal performance.
The goal is to identify poorly written SQL statements and to advise you how to restructure them.
Automatic Tuning Optimizer identifies the following categories of structural problems:
Inefficient use of SQL constructors
A suboptimally performing statement may be using NOT IN instead of NOT EXISTS, or UNION instead of UNION ALL. The UNION operator, as opposed to the UNION ALL operator,
uses a unique sort to ensure that no duplicate rows are in the result set. If you know that two queries do not return duplicates, then use UNION ALL.
Data type mismatches
If the indexed column and the compared value have a data type mismatch, then the database does not use the index because of the implicit data type conversion.
Also, the database must expend additional resources converting data types, and some SQL statements may fail because data values do not convert correctly.
Common mistakes include columns that contain numeric data but are never used for arithmetic operations: telephone numbers, credit card numbers, and check numbers.
To avoid poor cardinality estimates, suboptimal plans, and ORA-01722 errors, developers must ensure that bind variables are type VARCHAR2 and not numbers.
Design mistakes
A classic example of a design mistake is a missing join condition. If n is the number of tables in a query block, then n-1 join conditions must exist to avoid a Cartesian product.
E=> Alternative Plan Analysis
While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement.
If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.
SQL Tuning Advisor Operation
You can run SQL Tuning Advisor automatically or on demand. You can also run the advisor on a local or remote database.
Configure SQL Tuning Advisor to run automatically using DBMS_AUTO_SQLTUNE, or on demand using DBMS_SQLTUNE.
The methods of invocation differ as follows:
=> Automatically
You can configure SQL Tuning Advisor to run during nightly system maintenance windows. When run by AUTOTASK,
the advisor is known as Automatic SQL Tuning Advisor and performs automatic SQL tuning.
=> On-Demand
In on-demand SQL tuning, you manually invoke SQL Tuning Advisor to diagnose and fix SQL-related performance problems after they have been discovered.
SQL Tuning Advisor uses Automatic Tuning Optimizer to perform its analysis. This optimization is "automatic" because the optimizer analyzes the SQL instead of the user.
Do not confuse Automatic Tuning Optimizer with automatic SQL tuning, which in this document refers only to the work performed by the Automatic SQL Tuning task.
1=> Managing the Automatic SQL Tuning Task
=> To identify SQL performance problems proactively, configuring SQL Tuning Advisor as an automated task is a simple solution.
=> The task processes selected high-load SQL statements from AWR that qualify as tuning candidates.
=> By default, the Automatic SQL Tuning task runs for in a nightly maintenance window.
=> This is PART OF AUTO MAINTENANCE TASK
Basic Tasks for Automatic SQL Tuning
This section explains the basic tasks in running SQL Tuning Advisor as an automatic task.
The following graphic shows the basic workflow.
A=> Enabling and Disabling the Automatic SQL Tuning Task from the Command Line
We have the following options:
Run the ENABLE or DISABLE procedure in the DBMS_AUTO_TASK_ADMIN PL/SQL package.
This package is the recommended command-line technique. For both the ENABLE and DISABLE procedures, you can specify a particular maintenance window with the window_name parameter.
Set the STATISTICS_LEVEL initialization parameter to BASIC to disable collection of all advisories and statistics, including Automatic SQL Tuning Advisor.
Because monitoring and many automatic features are disabled, Oracle strongly recommends that you do not set STATISTICS_LEVEL to BASIC.
SQL> COL CLIENT_NAME FORMAT a20
SELECT CLIENT_NAME, STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'sql tuning advisor';SQL> SQL> 2 3
CLIENT_NAME STATUS
-------------------- --------
sql tuning advisor ENABLED
B=> Configuring the Automatic SQL Tuning Task Using the Command Line
The DBMS_AUTO_SQLTUNE package enables you to configure automatic SQL tuning by specifying the task parameters using the SET_AUTO_TUNING_TASK_PARAMETER procedure.
Because the task is owned by SYS, only SYS can set task parameters.
The ACCEPT_SQL_PROFILE tuning task parameter specifies whether to implement SQL profiles automatically (true) or require user intervention (false).
The default is AUTO, which means true if at least one SQL statement exists with a SQL profile and false if this condition is not satisfied.
SQL> COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a10
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE ( (TASK_NAME = 'SYS_AUTO_SQL_TUNING_TASK') AND
( (PARAMETER_NAME LIKE '%PROFILE%') OR
(PARAMETER_NAME = 'LOCAL_TIME_LIMIT') OR
(PARAMETER_NAME = 'EXECUTION_DAYS_TO_EXPIRE') ) );
SQL> SQL> SQL> 2 3 4 5 6
PARAMETER_NAME VALUE
------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE 30
LOCAL_TIME_LIMIT 1200
ACCEPT_SQL_PROFILES FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES 10000
SQL>
Setting parameters
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
, parameter => parameter_name
, value => value
);
END;
/
C=> Viewing Automatic SQL Tuning Reports
To generate a SQL tuning report as a CLOB, execute the DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK function.
You can store the CLOB in a variable and then print the variable to view the report.
2=> Running SQL Tuning Advisor On Demand
We can run SQL Tuning Advisor on demand.
On-demand SQL tuning is defined as any invocation of SQL Tuning Advisor that does not result from the Automatic SQL Tuning task.
Typically, you invoke SQL Tuning Advisor to run ADDM proactively, or to tune SQL statement reactively when users complain about suboptimal performance.
Command-Line Interface to On-Demand SQL Tuning
We can run SQL Tuning Advisor using procedures in the DBMS_SQLTUNE package.
Basic Tasks in On-Demand SQL Tuning
The following graphic shows the basic workflow when using the PL/SQL APIs.
Now I will walk through Basic steps for creating / executing / reporting Tuning task as on demand.
A=> Creating a SQL Tuning Task
To create a SQL tuning task executes the DBMS_SQLTUNE.CREATE_TUNING_TASK function.
We can create tuning tasks from any of the following (INPUT FOR SQL TUNING TASK)
The text of a single SQL statement
A SQL tuning set containing multiple statements
A SQL statement selected by SQL identifier from the shared SQL area
A SQL statement selected by SQL identifier from AWR
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * FROM employees e, locations l, departments d WHERE e.department_id = d.department_id
AND l.location_id = d.location_id AND e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext
, bind_list => sql_binds(anydata.ConvertNumber(9000))
, user_name => 'HR'
, scope => 'COMPREHENSIVE'
, time_limit => 60
, task_name => 'STA_FOR_SQL1'
, description => 'This is tunning task for SQL Qury only'
);
END;
/
B=> Configuring a SQL Tuning Task
Execute the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER function to configure basic options for tuning task
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'STA_FOR_SQL1'
, parameter => 'TIME_LIMIT'
, value => 120
);
END;
/
We can use USER_ADVISOR_PARAMETERS to check parameter values for Task as below.
SQL> COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a15
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM USER_ADVISOR_PARAMETERS
WHERE TASK_NAME = 'STA_FOR_SQL1'
AND PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;
SQL> SQL> SQL> 2 3 4 5
PARAMETER_NAME VALUE
------------------------- ---------------
DAYS_TO_EXPIRE 30
DEFAULT_EXECUTION_TYPE TUNE SQL
EXECUTION_DAYS_TO_EXPIRE UNLIMITED
JOURNALING INFORMATION
MODE COMPREHENSIVE
SQL_LIMIT -1
SQL_PERCENTAGE 1
TARGET_OBJECTS 1
TEST_EXECUTE AUTO
TIME_LIMIT 60
10 rows selected.
SQL>
C=> Executing a SQL Tuning Task
To execute Tuning Task we can use DBMS_SQLTUNE.EXECUTE_TUNING_TASK as below. And we can get status for the same from USER_ADVISOR_LOG
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_FOR_SQL1');
END;
/
COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG;
D=> Monitoring a SQL Tuning Task
We can monitor the status from below Views
SELECT STATUS FROM USER_ADVISOR_TASKS WHERE TASK_NAME = 'STA_FOR_SQL1';
E=> Displaying the Results of a SQL Tuning Task
To report the results of a tuning task, use the DBMS_SQLTUNE.REPORT_TUNING_TASK function.
The report contains all the findings and recommendations of SQL Tuning Advisor.
SQL> SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_FOR_SQL1' )
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : STA_FOR_SQL1
Tuning Task Owner : ABHI_TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/26/2022 01:01:43
Completed at : 01/26/2022 01:01:45
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : fzf61z52zh4sk
SQL Text : SELECT /*+ ORDERED */ * FROM employees e, locations l,
departments d WHERE e.department_id = d.department_id AND
l.location_id = d.location_id AND e.employee_id < :bnd
Bind Variables :
1 - (NUMBER):9000
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 22.58%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'STA_FOR_SQL1',
task_owner => 'ABHI_TEST', replace => TRUE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .001695 .000173 89.79 %
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
CPU Time (s): .000835 0 100 %
User I/O Time (s): .001336 0 100 %
Buffer Gets: 18 14 22.22 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 4096 0 100 %
Physical Write Bytes: 0 0
Rows Processed: 106 106
Fetches: 106 106
Executions: 1 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
----------------------------------------------------------------------------------------------------
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 3 of the
execution plan.
Recommendation
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_FOR_SQL1')
