Steps by steps to run SQL Tuning Advisor in Oracle
As we all know running a slow query is a major performance issue in the database also has the biggest challenge for DBA to resolve this issue.
Resolving the slowness issue we are using an Oracle tool called SQL Advisor.
When you run SQL advisor for the recommendation against any sql_id or Sql_query, It provides the recommendation to tune long-running SQL query.
Below are the steps to execute SQL tunning advisory.
1. Create SQL Tuning Task:-
Create SQL tuning task for the specific SQL_ID.
SQL> declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> 'ccd88nkav4nzv',TASK_NAME => 'SQL_TUNNING_TASK_ccd88nkav4nzv');
end;
/
2. Check Status
Check the status of created SQL tunning task:
SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'SQL_TUNNING_TASK_ccd88nkav4nzv';
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
TASK_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- -----------
SQL_TUNNING_TASK_ccd88nkav4nzv INITIAL
3. Execute SQL
Execute the created SQL tunning task:
SQL> exec dbms_sqltune.execute_tuning_task (TASK_NAME => 'SQL_TUNNING_TASK_ccd88nkav4nzv');
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
PL/SQL procedure successfully completed.
4. Check Status
Check the status after executing the SQL tuning task:
SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'SQL_TUNNING_TASK_ccd88nkav4nzv';
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
TASK_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- -----------
SQL_TUNNING_TASK_ccd88nkav4nzv Completed
5. Fetch SQL tuning advisor report.
Run the Below Query to get the SQL tunning advisory report:
SQL> SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNNING_TASK_ccd88nkav4nzv') AS recommendations FROM dual;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
Below are the sample output of the SQL tuning Advisor
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORDLTION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNNING_TASK_ccd88nkav4nzv
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/06/2021 08:56:35
Completed at : 01/06/2021 09:14:27
-------------------------------------------------------------------------------
ScheDL Name: HR
SQL ID : ccd88nkav4nzv
SQL Text : select 'DEPARTMENT_ID,DEPARTMENT_CODE from dual
union all
select 'DEPARTMENT_Details'||','||
EMPLOYEE_ID ||','||
'EMPLOYEE' ||','||
RECOMMENDATIONS
--------------------------------------------------------------------------------
'1'
from EMPLOYEE
where DEPARTMENT.DEPARTMENT_NAME(DEPARTMENT_ID) = 'BUS'
and available = '1'
and DEPARTMENT.CODE(DEPARTMENT_ID) = 'N'
and DEPARTMENT.CODE(DEPARTMENT_ID,HR.DEPARTMENT_CODE('N')) = 'N'
and DEPARTMENT.DEPARTMENT_ID(DEPARTMENT_ID,DEPARTMENT.DEPARTMENT_CODE(
'N')) = 'N'
and
not exists (select * from DEPARTMENT where DEPARTMENT_pidm =
DEPARTMENT_ID and DEPARTMENT__code in ('20', '40')
and (DEPARTMENT_credit_hr >= .5 or
DEPARTMENT_levl_code = 'HR')
and DEPARTMENT_term_code >=
DEPARTMENT.DEPARTMENT_CODE('N')
and DEPARTMENT__code in (select EMPLOYEE_code
from EMPLOYEE where EMPLOYEE_code = 'Y')
)
RECOMMENDATIONS
--------------------------------------------------------------------------------
and not exists (select * from EMPLOYEE where EMPLOYEE_pidm
= DEPARTMENT_ID and EMPLOYEE_from_date < SYSDATE
and EMPLOYEE_status is null and
EMPLOYEE_code = 'DL'
and (substr(EMPLOYEE_code,1,5) in
('95945','95946','95949','95959','95960','95975','95977','95986',
'95602','95924','96160','96161','96162')
or EMPLOYEE_code like '895%')
)
and exists (select * from DEPARTMENT where DEPARTMENT_pidm =
DEPARTMENT_ID and DEPARTMENT__code in ('10', '30', '80')
and (DEPARTMENT_credit_hr >= .5 or
DEPARTMENT_levl_code = 'HR')
and DEPARTMENT_term_code >= (select
min(DEPARTMENT_code) from DEPARTMENT where DEPARTMENT_end_date > sysdate)
--DEPARTMENT.DEPARTMENT_CODE('N')
and DEPARTMENT__code in (select EMPLOYEE_code
from EMPLOYEE where EMPLOYEE_code = 'Y')
)
RECOMMENDATIONS
--------------------------------------------------------------------------------
)
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estiDLted benefit: 57.36%)
------------------------------------------
- Consider running the Access Advisor to improve the physical scheDL design
or creating the recommended index.
create index HR.IDX$$_981700001 on
HR.EMPLOYEE("EMPLOYEE_code","EMPLOYEE_CODE");
- Consider running the Access Advisor to improve the physical scheDL design
or creating the recommended index.
create index HR.IDX$$_981700003 on
RECOMMENDATIONS
--------------------------------------------------------------------------------
HR.EMPLOYEE("AVAILABLE","DEPARTMENT_ID",'EMPLOYEE
BUS,'||"EMPLOYEE_ID"||','||'EMPLOYEE'||','||'1');
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index DLintenaHRe overhead and additional space consumption.
2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical perforDLHRe data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed inforDLtion on each
plan.
id plan hash last seen elapsed (s) origin note
RECOMMENDATIONS
--------------------------------------------------------------------------------
-- ---------- -------------------- ------------ --------------- --------------
--
1 1128296975 2021-01-05/09:00:58 26.876 AWR
2 1831885506 2021-01-05/09:00:58 32.152 AWR
InforDLtion
-----------
- Because no execution history for the Original Plan was found, the SQL
Tuning Advisor could not determine if any of these execution plans are
superior to it. However, if you know that one alternative plan is better
than the Original Plan, you can create a SQL plan baseline for it. This
will instruct the Oracle optimizer to pick it over any other choices in
the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name =>
'SQL_TUNNING_TASK_8uvwhd4w2sqwe', owner_name => 'SYS',
plan_hash_value => xxxxxxxx);
-------------------------------------------------------------------------------
RECOMMENDATIONS
--------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3149499791
--------------------------------------------------------------------------------
------------------------------------
| Id | Operation | Name | Row
s | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------------------
| 0 | SELECT STATEMENT | |
2 | 152 | 399 (2)| 00:00:01 |
| 1 | UNION-ALL | |
| | | |
| 2 | FAST DUAL | |
1 | | 2 (0)| 00:00:01 |
| 3 | HASH UNIQUE | |
1 | 76 | 397 (2)| 00:00:01 |
RECOMMENDATIONS
--------------------------------------------------------------------------------
|* 4 | FILTER | |
| | | |
| 5 | NESTED LOOPS ANTI | |
1 | 76 | 389 (2)| 00:00:01 |
| 6 | NESTED LOOPS | |
1 | 48 | 386 (2)| 00:00:01 |
| 7 | NESTED LOOPS | |
1 | 43 | 385 (2)| 00:00:01 |
|* 8 | DLT_VIEW ACCESS FULL | EMPLOYEE |
1 | 18 | 382 (2)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT |
1 | 25 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | DEPARTMENT_KEYEX3 |
1 | | 2 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | |
1 | 15 | | |
|* 12 | VIEW | index$_join$_007 | 1
26 | 1890 | 2 (0)| 00:00:01 |
|* 13 | HASH JOIN | |
| | | |
|* 14 | INDEX RANGE SCAN | DEPARTMENT_END_DATEEX | 1
RECOMMENDATIONS
--------------------------------------------------------------------------------
26 | 1890 | 1 (0)| 00:00:01 |
| 15 | INDEX FAST FULL SCAN | PK_DEPARTMENT | 1
26 | 1890 | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE |
1 | 5 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_EMPLOYEE |
1 | | 0 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEE | 23
84 | 66752 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | EMPLOYEE_KEYEX2 |
1 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | |
1 | 30 | 5 (0)| 00:00:01 |
| 21 | NESTED LOOPS | |
1 | 30 | 5 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPARTMENT |
1 | 25 | 4 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | DEPARTMENT_KEYEX3 |
1 | | 3 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_EMPLOYEE |
1 | | 0 (0)| 00:00:01 |
RECOMMENDATIONS
--------------------------------------------------------------------------------
|* 25 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE |
1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------------------
Predicate InforDLtion (identified by operation id):
---------------------------------------------------
4 - filter( NOT EXISTS (SELECT 0 FROM "HR"."DEPARTMENT" "DEPARTMENT","HR"."
EMPLOYEE" "EMPLOYEE" WHERE
"DEPARTMENT__CODE"="EMPLOYEE_CODE" AND "EMPLOYEE_IHRL_SECT_ENR
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
Very nicely explained
Thanks, Eswar for the feedback!
All of your positive feedback motivates us to work even harder! 🙂
Keep following our posts and sharing your feedback with us.
Regards,
Team DBsGuru.
This was super helpful. Thanks
Excellent, Thanks for summarizing the Details for generating SQL Tuning advisor report!