What is SQL Tuning Advisor:
The SQL Tuning Advisor is a feature in Oracle Database that analyzes SQL statements and provides recommendations to improve their performance. When you run the SQL Tuning Advisor on a SQL statement or a SQL tuning set, it generates a report known as the SQL Tuning Advisor Report.
The SQL Tuning Advisor Report provides information about the analysis performed by the advisor and presents recommendations for optimizing the SQL statement. The report typically includes the following sections:
Summary: Provides an overview of the analysis and recommendations.
SQL Statement Information: Displays details about the SQL statement, such as its text, execution statistics, and object dependencies.
Access Path Analysis: Evaluates the current execution plan and suggests alternative access paths that may improve performance.
SQL Profile: Suggests creating a SQL profile, which is a set of optimizer statistics and hints that guide the optimizer in generating an efficient execution plan for the SQL statement.
Index Analysis: Examines the existing indexes and proposes new indexes or modifications to existing ones that can enhance query performance.
SQL Restructure: Recommends rephrasing or rewriting the SQL statement to improve performance.
Materialized Views: Suggests creating materialized views that can be used to speed up query execution.
Partitioning Analysis: Recommends partitioning strategies that can improve performance for large tables.
SQL Plan Management: Proposes using SQL Plan Management to maintain stable and efficient execution plans for the SQL statement.
Undo Advisor: Provides recommendations on undo retention settings based on the SQL statement's needs.
The SQL Tuning Advisor Report aims to assist database administrators and developers in optimizing SQL statements and improving overall database performance. It provides specific recommendations tailored to the analyzed SQL statement, allowing users to implement the suggested changes and monitor the performance improvements.
How to generate/run SQL Tuning Advisor report from snap id's 57787 to 59230
1st Method:
This is the problematic sql id:g13kaky245z75
Steps to generate sql-tuning advisor report
sql>spool manual_g13kaky245z75.out
sql>set serveroutput on
DECLARE
l_sql_tune_task_id VARCHAR2(100);
sta_task_count number;
BEGIN
select count(task_name) into sta_task_count from dba_advisor_tasks where task_name='g13kaky245z75_tuning_task';
if sta_task_count > 0 then DBMS_SQLTUNE.drop_TUNING_TASK(task_name=>'g13kaky245z75_tuning_task');
end if;
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=>57787,
end_snap=>59230,
sql_id=>'g13kaky245z75',
scope=>'COMPREHENSIVE',
time_limit=>1200,
task_name=>'g13kaky245z75_tuning_task',
description=>'Tuning task for statement ' || 'g13kaky245z75');
DBMS_OUTPUT.put_line('Tuning Task ' || l_sql_tune_task_id||' created.');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0 then dbms_output.put_line(SQLERRM);
raise;
end if;
END;
/
Declare
sta_task_count number;
BEGIN
select count(task_name) into sta_task_count from dba_advisor_tasks where task_name='g13kaky245z75_tuning_task';
if sta_task_count > 0
then DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'g13kaky245z75_tuning_task');
end if;
END;
/
sql>SET LONG 90000
sql>SET LONGCHUNKSIZE 90000
sql>SET LINESIZE 125
sql>SET PAGESIZE 500
sql>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'g13kaky245z75_tuning_task') from DUAL;
spool off
Once spool file generates you can upload to oracle support for further investigation
note: SQL Tuning Advisor can take up to 20 minutes to run. Please don't interrupt it with ctrl-C
2nd Method:
sql>connect to sqlplus "/as sysdba"
sql>@?/rdbms/admin/sqltrpt.sql
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: g13kaky245z75
It will take 20mins time to generate report
Things to check in sql tuning report
1- Statistics Finding
Recommendation: Here you can see the recommendation from sql tuning advisor for ex: to gather the stats or to create index or to modify the sql query
2- Statistics Finding
Recommendation: Here you can see the recommendation from sql tuning advisor for ex: to gather the stats or to create index or to modify the sql query
3- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile. A SQL plan baseline
corresponding to the plan with the SQL profile will also be created.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_10833',task_owner => 'SYS', replace => TRUE);
Note:Here if you see if the estimated benefit is more that 70% or 80% or 90% you can plug or execute the above sql profile.
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): 7.165564 .001346 99.98 %
CPU Time (s): 7.115895 .001354 99.98 %
User I/O Time (s): 0 0
Buffer Gets: 1296489 90 99.99 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 7 7
Fetches: 7 7
Executions: 1 1
4- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive "UNION" operation was found at line ID 7 of the execution plan.
Recommendation
--------------
- Consider using "UNION ALL" instead of "UNION", if duplicates are allowed
or uniqueness is guaranteed.
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 4268187734
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 504 | 352K (1)| 00:00:14 | | |
| 1 | SORT ORDER BY | | 7 | 504 | 352K (1)| 00:00:14 | | |
| 2 | NESTED LOOPS | | 7 | 504 | 352K (1)| 00:00:14 | | |
| 3 | NESTED LOOPS | | 7 | 504 | 352K (1)| 00:00:14 | | |
2- Using SQL Profile
--------------------
Plan hash value: 864480126
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 504 | 74 (11)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 7 | 504 | 74 (11)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 7 | 504 | 73 (10)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 7 | 504 | 73 (10)| 00:00:01 | | |
ENF OF THE REPORT.........
3rd Method:
Problematic sql id is – g13kaky245z75
1. Create Tuning Task
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'g13kaky245z75',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'g13kaky245z75_tuning_task11',
description => 'Tuning task1 for statement g13kaky245z75');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2. Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g13kaky245z75_tuning_task11');
3. Get the Tuning advisor report.
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('g13kaky245z75_tuning_task11') from dual;
4. Get list of tuning task present in database:
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
5. Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('g13kaky245z75_tuning_task11');
select dbms_sqltune.report_tuning_task(:task_name) as recommendations from dual;
spool off
Accepting New SQL Profile:
If any SQL profile is recommended in the sql tuning advisor report, you can implement and accept it like this:
SQL> exec dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE_22zq47mj49f7w_0105_01', task_owner => 'SYS', replace => TRUE, name => 'sql_profile_for_22zq47mj49f7w');
PL/SQL procedure successfully completed.
Check new profile status.
column name format a30;
column category format a15;
column status format a15;
select name, category, status from dba_sql_profiles where name='SYS_SQLPROF_0384724bddc80002';
NAME CATEGORY STATUS
------------------------------ --------------- ---------------
sql_profile_for_22zq47mj49f7w DEFAULT ENABLED
SQL> select name, category, status,sql_text from dba_sql_profiles where sql_text like '%SELECT /*+ parallel_index(A,4)%';
Creating New SQL Baseline
If you found specific plan is good enough to set the baseline, you can accept the tuning result by this:
SQL> exec dbms_sqltune.create_sql_plan_baseline(task_name => 'SQLTUNE_44zq47mj49f7w_0105_01', owner_name => 'SYS', plan_hash_value => 1283456102);
Drop and Disable the SQL Profile for SQL query if you don’t need it:
How to disable the profile:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'coe_4rfddtqn4gc6c_4057693193',attribute_name=>'STATUS',value=>'DISABLED');
How to enable the profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'coe_b082dj04nhwqn_782800796',attribute_name=>'STATUS',value=>'ENABLED');
How to Drop the profile:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name =>'coe_b082dj04nhwqn_782800796');
Enjoy the work and do like me if you like this article!!!
