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.