SQL Tuning Advisor is used for tuning SQL statements.
This is run to implement a ADDM performance finding.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.
To run the SQL Tuning Advisor do the following:
1. On the Home Page, under Related Links, click Advisor Central, then click SQL Tuning Advisor. The SQL Tuning Advisor Links page appears.
2. The advisor can be run on one of the following sources:
Top SQL—These consist of recently active top SQL statements from the cursor cache (Spot SQL) or historical top SQL from the AWR (Period SQL).
SQL Tuning Sets—These consist of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from any SQL workload.
3. For example, you can select Top SQL. The Top SQL page appears. This page has two tabs, Spot SQL and Period SQL. Spot lists recent top SQL from the cursor cache, while Period SQL lists historical top SQL captured in the AWR. You must select an interval to analyze by dragging the shaded box over the period. You then select one or more SQL statements to analyze during the selected period.
4. Click Run SQL Tuning Advisor. The SQL Tuning Options page appears showing the SQL statements in the interval. Give your task a name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the task. Click OK.
5. Navigate back to the Advisor Central page. The status of Advisor Tasks is listed under this heading in the results section. You must wait until your task status is COMPLETED. You can check the status by clicking your browser's Refresh button. Then, select your task and click View Result.
6. The SQL Tuning Result page appears. To view recommendations, select the SQL statement and click View Recommendations. The recommendation can include one or more of the following:
Create an index to offer alternate, faster access paths to the query optimizer.
Accept SQL profile, which contains additional SQL statistics specific to the statement that enables the query optimizer to generate a significantly better execution plan.
Gather optimizer statistics on objects with stale or no statistics.
Advice on how to rewrite a query for better performance.
SQL Advisor in Oracle 10g
Another great feature of Oracle 10G that allow you to tune SQL. Now you don't need to tune SQL statement manually. This new feature
does it for you.
SQL Tuning Advisor using DBMS_SQLTUNE package and very simple to use.
The example below shows how to use SQL advisor.
1. Grant following access to the user that is going to run this new tool. In the example below SCOTT is the owner of the schema.
GRANT ADVISOR TO USER1;
GRANT SELECT_CATALOG_ROLE TO USER1;
GRANT EXECUTE ON DBMS_SQLTUNE TO USER1;
2. Create the tuning task
DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := 'SELECT * from TAB1 where empno = 1200';
task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => 'USER1',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_test1',
description => 'This is a tuning task on TAB1 table');
END;
/
Some time you may have queries that might take longer than the time that you have specified in the "time_limit" parameter. If this is the case then remove this parameter.
NOTE: You can not create more than one task with the same name. If this is the case then drop the existing task or use a different name.
2.1 To view the existing task for the user run the following statement.
select task_name from dba_advisor_log where owner = 'SCOTT';
3. Execute the tuning task
Execute dbms_sqltune.Execute_tuning_task (task_name => 'sql_tuning_task_test1');
3.1 You can check the status of the task using following query.
select status from dba_advisor_log where task_name='sql_tuning_task_test1';
4. Now view the Recommendation
set linesize 100
set long 1000
set longchunksize 1000
SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_TEST1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task_test1
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/22/2006 15:33:13
Completed at : 06/22/2006 15:33:14
-------------------------------------------------------------------------------
SQL ID : ad1489724nqpn
SQL Text: SELECT * from TAB1 where VALUE=18902;
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "USER1"."TAB1" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'USER1', tabname =>'TAB1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Based on this information, you can decide what actions are necessary to tune the SQL.
11g AUTOMATIC SQL TUNING
As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
• AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
• The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
• Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
• The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.
-- Enable
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- Disable
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.
The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
• ACCEPT_SQL_PROFILES - Automatically accept SQL profiles (default FALSE).
• MAX_SQL_PROFILES_PER_EXEC - The maximum number of SQL profiles automatically implemented per run (default 20).
• MAX_AUTO_SQL_PROFILES - The maximum number of automatic SQL profiles allowed on the system (default 10000).
The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
ACCEPT_SQL_PROFILES FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES 10000
3 rows selected.
SQL>
The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/
The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/
SET LONG 1000000
PRINT :l_report
L_REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 31
Current Execution : EXEC_1_25
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 01/16/2008 22:00:06
Completed at : 01/16/2008 22:00:46
Number of Candidate SQLs : 0
Cumulative Elapsed Time of SQL (s) : 0
-------------------------------------------------------------------------------
This is run to implement a ADDM performance finding.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.
To run the SQL Tuning Advisor do the following:
1. On the Home Page, under Related Links, click Advisor Central, then click SQL Tuning Advisor. The SQL Tuning Advisor Links page appears.
2. The advisor can be run on one of the following sources:
Top SQL—These consist of recently active top SQL statements from the cursor cache (Spot SQL) or historical top SQL from the AWR (Period SQL).
SQL Tuning Sets—These consist of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from any SQL workload.
3. For example, you can select Top SQL. The Top SQL page appears. This page has two tabs, Spot SQL and Period SQL. Spot lists recent top SQL from the cursor cache, while Period SQL lists historical top SQL captured in the AWR. You must select an interval to analyze by dragging the shaded box over the period. You then select one or more SQL statements to analyze during the selected period.
4. Click Run SQL Tuning Advisor. The SQL Tuning Options page appears showing the SQL statements in the interval. Give your task a name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the task. Click OK.
5. Navigate back to the Advisor Central page. The status of Advisor Tasks is listed under this heading in the results section. You must wait until your task status is COMPLETED. You can check the status by clicking your browser's Refresh button. Then, select your task and click View Result.
6. The SQL Tuning Result page appears. To view recommendations, select the SQL statement and click View Recommendations. The recommendation can include one or more of the following:
Create an index to offer alternate, faster access paths to the query optimizer.
Accept SQL profile, which contains additional SQL statistics specific to the statement that enables the query optimizer to generate a significantly better execution plan.
Gather optimizer statistics on objects with stale or no statistics.
Advice on how to rewrite a query for better performance.
SQL Advisor in Oracle 10g
Another great feature of Oracle 10G that allow you to tune SQL. Now you don't need to tune SQL statement manually. This new feature
does it for you.
SQL Tuning Advisor using DBMS_SQLTUNE package and very simple to use.
The example below shows how to use SQL advisor.
1. Grant following access to the user that is going to run this new tool. In the example below SCOTT is the owner of the schema.
GRANT ADVISOR TO USER1;
GRANT SELECT_CATALOG_ROLE TO USER1;
GRANT EXECUTE ON DBMS_SQLTUNE TO USER1;
2. Create the tuning task
DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := 'SELECT * from TAB1 where empno = 1200';
task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => 'USER1',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_test1',
description => 'This is a tuning task on TAB1 table');
END;
/
Some time you may have queries that might take longer than the time that you have specified in the "time_limit" parameter. If this is the case then remove this parameter.
NOTE: You can not create more than one task with the same name. If this is the case then drop the existing task or use a different name.
2.1 To view the existing task for the user run the following statement.
select task_name from dba_advisor_log where owner = 'SCOTT';
3. Execute the tuning task
Execute dbms_sqltune.Execute_tuning_task (task_name => 'sql_tuning_task_test1');
3.1 You can check the status of the task using following query.
select status from dba_advisor_log where task_name='sql_tuning_task_test1';
4. Now view the Recommendation
set linesize 100
set long 1000
set longchunksize 1000
SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_TEST1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task_test1
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/22/2006 15:33:13
Completed at : 06/22/2006 15:33:14
-------------------------------------------------------------------------------
SQL ID : ad1489724nqpn
SQL Text: SELECT * from TAB1 where VALUE=18902;
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "USER1"."TAB1" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'USER1', tabname =>'TAB1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Based on this information, you can decide what actions are necessary to tune the SQL.
11g AUTOMATIC SQL TUNING
As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
• AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
• The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
• Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
• The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.
-- Enable
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- Disable
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.
The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
• ACCEPT_SQL_PROFILES - Automatically accept SQL profiles (default FALSE).
• MAX_SQL_PROFILES_PER_EXEC - The maximum number of SQL profiles automatically implemented per run (default 20).
• MAX_AUTO_SQL_PROFILES - The maximum number of automatic SQL profiles allowed on the system (default 10000).
The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
ACCEPT_SQL_PROFILES FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES 10000
3 rows selected.
SQL>
The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/
The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/
SET LONG 1000000
PRINT :l_report
L_REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 31
Current Execution : EXEC_1_25
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 01/16/2008 22:00:06
Completed at : 01/16/2008 22:00:46
Number of Candidate SQLs : 0
Cumulative Elapsed Time of SQL (s) : 0
-------------------------------------------------------------------------------