What is the SQL Tuning Advisor and How to Run SQL Tuning Advisor Manually in Oracle | Oracle Database Performance Tuning Tutorial -10

Mehmet Salih Deveci November 20, 2019 3 Comments

Hi,

I will explain what is the SQL Tuning Advisor and how to use it in this Article.

SQL Tuning Advisor in Oracle

You can read the following article before this.

Optimizer Access Paths & Join Operators ( Hash Join, Nested Loop ) in Oracle | Oracle Database Performance Tuning Tutorial -8

Oracle database has Query optimizer which can perform SQL Tuning automatically. This Query optimizer is running in two modes.

Normal Mode: Optimizer should operate with very limited time, and it must find a good execution in this limited time. This execution plan is not probably the best.

Tuning Mode: Optimizer operates in several minutes to tune SQL Statements, there is no limited time. Oracle optimizer try to find lots of execution plan which is better than the execution plan produced under the normal mode.

There are many advisors who direct DBAs and facilitate their some tasks in the Oracle Database as follows. Using these advisors, you can perform some tasks much more easily.

SQL Tuning Advisor
SQL Access Advisor
Memory Advisor
Undo Advisor
Data Recovery Advisor (DRA)

SQL Tuning Advisor is introduced with Oracle Database 10g and the one of the most popular advisor of the Oracle database for Database Administrator ( DBA ). Because it analyzes high load SQL statements which is called TOP SQLs and offer you some useful recommendations for Tuning.

SQL Tuning Advisor analyzes one or more SQL statements same time, it invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. Some of SQL Tuning Advisor recommendations are as follows.

Collection of Table or Index statistics
New Index creation
Restructuring of the SQL statement
SQL Profile creation

I will explain SQL Tuning advisor usage in this article.

These are just recommendations, not implementation. DBA should probe and analyze these recommendations, if they are appopriate for related SQLs, they can be applied as follows.

Run SQL Tuning Advisor

Now let’s go to run SQL Tuning Advisor for any SQL. You should select this SQL between TOP SQL. If you don’t know which SQL you should tune, then you can generate an AWR report to see TOP SQL.

Read the following article if you don’t know how to generate an AWR report, or what is the AWR report.

How to Generate AWR ( Automatic Workload Repository ) Report via SQL*Plus, Enterprise Manager and Toad in Oracle

I will run SQL Tuning Advisor ( STA ) for SQL_ID: dz3rmaqvta9uq .

Step-1: Firstly Create Tuning task as follows.

variable stmt_task VARCHAR2(64); 
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'dz3rmaqvta9uq',task_name=>'msd_dz3rmaqvta9uq');

This will create tuning task for related SQL_ID if it exist in the Cache. If it doesn’t exist, you should create Tuning task using AWR Snapshow as follows.

SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 43000,
end_snap => 43700,
sql_id => 'dz3rmaqvta9uq',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'msd_dz3rmaqvta9uq',
description => 'Tuning task for statement msd_dz3rmaqvta9uq');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step -2: Check if it exists or not as follows.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='msd_dz3rmaqvta9uq';

STATUS
-----------
COMPLETED

SQL>

Step -3: Once Tuning task is created, you should execute it as follows.

BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'msd_dz3rmaqvta9uq');
end;
/

Step -4: If Execution of Tuning task is completed, you can display the Report as follows.

SET linesize 200
SET LONG 999999999
SET pages 1000
SET longchunksize 20000 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'msd_dz3rmaqvta9uq' ) FROM DUAL;







All content of Tuning task are as follows.

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : msd_dz3rmaqvta9uq
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/19/2019 11:34:47
Completed at       : 11/19/2019 11:35:32

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : dz3rmaqvta9uq
SQL Text   : SELECT TO_NUMBER(NULL) DFNUMBER, TO_NUMBER(NULL) CREATE_SCN,
             BRL.RESETLOGS_CHANGE# RESET_SCN, BRL.RESETLOGS_TIME RESET_TIME,
             TO_NUMBER(NULL) CKP_SCN, TO_DATE(NULL) CKP_TIME, TO_NUMBER(NULL)
             STOP_SCN, BRL.THREAD# LOGTHREAD, BRL.SEQUENCE# LOGSEQUENCE,
             TO_NUMBER(NULL) SETSTAMP, TO_NUMBER(NULL) SETCOUNT,
             BS.COMPLETION_TIME COMPTIME, 0 NBACKUPS, BRL.TERMINAL
             LOGTERMINAL, BRL.NEXT_CHANGE# NEXT_SCN, 0 PLUGGEDRONLY, 0
             PLUGINSCN, 0 PLUGINRLGSCN, TO_DATE(NULL) PLUGINRLGTIME,
             TO_NUMBER(NULL) NEWCREATE_SCN, TO_NUMBER(NULL) NEWRESET_SCN,
             TO_DATE(NULL) NEWRESET_TIME FROM V$BACKUP_REDOLOG BRL, (SELECT
             DISTINCT THREAD#, SEQUENCE#, RESETLOGS_CHANGE#, RESETLOGS_TIME
             FROM V$ARCHIVED_LOG WHERE STATUS = 'A' AND ARCHIVED = 'YES' AND
             STANDBY_DEST = 'NO' AND (:B15 IS NULL OR THREAD# = :B15 ) AND
             (:B14 IS NULL OR SEQUENCE# >= :B14 ) AND (:B13 IS NULL OR
             SEQUENCE# <= :B13 ) AND (:B12 IS NULL OR NEXT_CHANGE# > :B12 )
             AND (:B11 IS NULL OR FIRST_CHANGE# < :B11 ) AND (:B10 IS NULL OR
             NAME LIKE :B10 ) AND (:B9 IS NULL OR NEXT_TIME > :B9 ) AND (:B8
             IS NULL OR FIRST_TIME <= :B8 )) AL, (SELECT DISTINCT
             BS.SET_STAMP, BS.SET_COUNT, BS.COMPLETION_TIME FROM
             V$BACKUP_PIECE BP, V$BACKUP_SET BS WHERE BP.STATUS = 'A' AND
             BS.BACKUP_TYPE = 'L' AND BS.SET_STAMP = BP.SET_STAMP AND
             BS.SET_COUNT = BP.SET_COUNT AND (:B7 IS NULL OR (:B7 != 'B' AND
             :B7 != 'D') OR ((:B7 = 'B' OR :B7 = 'D') AND ((:B6 IS NULL AND
             BS.KEEP_OPTIONS IS NULL) OR (:B6 = BP.TAG AND BS.KEEP_OPTIONS IS
             NOT NULL)))) AND (:B5 IS NULL OR :B5 = BP.DEVICE_TYPE) AND ((:B4
             IS NULL AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) OR
             (BP.HANDLE LIKE :B4 OR BP.HANDLE LIKE :B3 OR BP.HANDLE LIKE :B2
             OR BP.HANDLE LIKE :B1 )) GROUP BY BS.SET_STAMP, BS.SET_COUNT,
             BP.DEVICE_TYPE, BP.COPY#, BS.PIECES, BS.COMPLETION_TIME HAVING
             COUNT(DISTINCT BP.PIECE#) = BS.PIECES) BS WHERE BRL.THREAD# =
             AL.THREAD# AND BRL.SEQUENCE# = AL.SEQUENCE# AND
             BRL.RESETLOGS_CHANGE# = AL.RESETLOGS_CHANGE# AND
             BRL.RESETLOGS_TIME = AL.RESETLOGS_TIME AND BS.SET_STAMP =
             BRL.SET_STAMP AND BS.SET_COUNT = BRL.SET_COUNT UNION ALL SELECT
             TO_NUMBER(NULL) DFNUMBER, TO_NUMBER(NULL) CREATE_SCN,
             XAL.RESETLOGS_CHANGE# RESET_SCN, XAL.RESETLOGS_TIME RESET_TIME,
             TO_NUMBER(NULL) CKP_SCN, TO_DATE(NULL) CKP_TIME, TO_NUMBER(NULL)
             STOP_SCN, XAL.THREAD# LOGTHREAD, XAL.SEQUENCE# LOGSEQUENCE,
             TO_NUMBER(NULL) SETSTAMP, TO_NUMBER(NULL) SETCOUNT,
             XAL.COMPLETION_TIME COMPTIME, 0 NBACKUPS, XAL.TERMINAL
             LOGTERMINAL, XAL.NEXT_CHANGE# NEXT_SCN, 0 PLUGGEDRONLY, 0
             PLUGINSCN, 0 PLUGINRLGSCN, TO_DATE(NULL) PLUGINRLGTIME,
             TO_NUMBER(NULL) NEWCREATE_SCN, TO_NUMBER(NULL) NEWRESET_SCN,
             TO_DATE(NULL) NEWRESET_TIME FROM V$PROXY_ARCHIVEDLOG XAL,
             (SELECT DISTINCT THREAD#, SEQUENCE#, RESETLOGS_CHANGE#,
             RESETLOGS_TIME FROM V$ARCHIVED_LOG WHERE STATUS = 'A' AND
             ARCHIVED = 'YES' AND STANDBY_DEST = 'NO' AND (:B15 IS NULL OR
             THREAD# = :B15 ) AND (:B14 IS NULL OR SEQUENCE# >= :B14 ) AND
             (:B13 IS NULL OR SEQUENCE# <= :B13 ) AND (:B12 IS NULL OR
             NEXT_CHANGE# > :B12 ) AND (:B11 IS NULL OR FIRST_CHANGE# < :B11
             ) AND (:B10 IS NULL OR NAME LIKE :B10 ) AND (:B9 IS NULL OR
             NEXT_TIME > :B9 ) AND (:B8 IS NULL OR FIRST_TIME <= :B8 )) AL
             WHERE XAL.THREAD# = AL.THREAD# AND XAL.SEQUENCE# = AL.SEQUENCE#
             AND XAL.RESETLOGS_CHANGE# = AL.RESETLOGS_CHANGE# AND
             XAL.RESETLOGS_TIME = AL.RESETLOGS_TIME AND XAL.STATUS = 'A' AND
             (:B7 IS NULL OR (:B7 != 'B' AND :B7 != 'D') OR ((:B7 = 'B' OR
             :B7 = 'D') AND ((:B6 IS NULL AND XAL.KEEP_OPTIONS IS NULL) OR
             (:B6 = XAL.TAG AND XAL.KEEP_OPTIONS IS NOT NULL)))) AND (:B5 IS
             NULL OR :B5 = XAL.DEVICE_TYPE) AND ((:B4 IS NULL AND :B3 IS NULL
             AND :B2 IS NULL AND :B1 IS NULL) OR (XAL.HANDLE LIKE :B4 OR
             XAL.HANDLE LIKE :B3 OR XAL.HANDLE LIKE :B2 OR XAL.HANDLE LIKE
             :B1 )) ORDER BY RESET_SCN, RESET_TIME, LOGTHREAD, LOGSEQUENCE,
             LOGTERMINAL DESC, COMPTIME
Bind Variables :
 1 -  (NUMBER):NULL
 2 -  (NUMBER):NULL
 3 -  (NUMBER):NULL
 4 -  (NUMBER):NULL
 5 -  (NUMBER):NULL
 6 -  (NUMBER):NULL
 7 -  (NUMBER):0
 8 -  (NUMBER):0
 9 -  (NUMBER):281474976710656
 10 -  (NUMBER):281474976710656
 11 -  (VARCHAR2(2000)):NULL
 12 -  (VARCHAR2(2000)):NULL
 13 -  (DATE):NULL
 14 -  (DATE):NULL
 15 -  (DATE):NULL
 16 -  (DATE):NULL
 17 -  (VARCHAR2(32)):B
 18 -  (VARCHAR2(32)):B
 19 -  (VARCHAR2(32)):B
 20 -  (VARCHAR2(32)):B
 21 -  (VARCHAR2(32)):B
 22 -  (VARCHAR2(32)):NULL
 23 -  (VARCHAR2(32)):NULL
 24 -  (VARCHAR2(32)):DISK
 25 -  (VARCHAR2(32)):DISK
 26 -  (VARCHAR2(32)):NULL
 27 -  (VARCHAR2(32)):NULL
 28 -  (VARCHAR2(32)):NULL
 29 -  (VARCHAR2(32)):NULL
 30 -  (VARCHAR2(32)):NULL
 31 -  (VARCHAR2(32)):NULL
 32 -  (VARCHAR2(32)):NULL
 33 -  (VARCHAR2(32)):NULL
 34 -  (NUMBER):NULL
 35 -  (NUMBER):NULL
 36 -  (NUMBER):NULL
 37 -  (NUMBER):NULL
 38 -  (NUMBER):NULL
 39 -  (NUMBER):NULL
 40 -  (NUMBER):0
 41 -  (NUMBER):0
 42 -  (NUMBER):281474976710656
 43 -  (NUMBER):281474976710656
 44 -  (VARCHAR2(2000)):NULL
 45 -  (VARCHAR2(2000)):NULL
 46 -  (DATE):NULL
 47 -  (DATE):NULL
 48 -  (DATE):NULL
 49 -  (DATE):NULL
 50 -  (VARCHAR2(32)):B
 51 -  (VARCHAR2(32)):B
 52 -  (VARCHAR2(32)):B
 53 -  (VARCHAR2(32)):B
 54 -  (VARCHAR2(32)):B
 55 -  (VARCHAR2(32)):NULL
 56 -  (VARCHAR2(32)):NULL
 57 -  (VARCHAR2(32)):DISK
 58 -  (VARCHAR2(32)):DISK
 59 -  (VARCHAR2(32)):NULL
 60 -  (VARCHAR2(32)):NULL
 61 -  (VARCHAR2(32)):NULL
 62 -  (VARCHAR2(32)):NULL
 63 -  (VARCHAR2(32)):NULL
 64 -  (VARCHAR2(32)):NULL
 65 -  (VARCHAR2(32)):NULL
 66 -  (VARCHAR2(32)):NULL

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 73.23%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'msd_dz3rmaqvta9uq',
            task_owner => 'SYS', replace => TRUE);

  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:             PARTIAL          COMPLETE
  Elapsed Time (s):           15.252865          3.207375      78.97 %
  CPU Time (s):               14.394812          3.018541      79.03 %
  User I/O Time (s):            .003049           .002106      30.92 %
  Buffer Gets:                      114               114          0 %
  Physical Read Requests:          1263               430      65.95 %
  Physical Write Requests:            0                 0 
  Physical Read Bytes:       1110736896         345833472      68.86 %
  Physical Write Bytes:               0                 0 
  Rows Processed:                     0              4181 
  Fetches:                            0              4181 
  Executions:                         0                 1 

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 523732790

------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |  4182 |   527K|       | 10050  (97)| 00:00:01 |
|   1 |  SORT ORDER BY             |           |  4182 |   527K|  1872K|  9684 (100)| 00:00:01 |
|   2 |   UNION-ALL                |           |       |       |       |            |          |
|   3 |    NESTED LOOPS            |           |  4181 |   526K|       |  9683 (100)| 00:00:01 |
|   4 |     NESTED LOOPS           |           |  4181 |   396K|       |     2 (100)| 00:00:01 |
|   5 |      VIEW                  |           |     3 |   135 |       |     1 (100)| 00:00:01 |
|   6 |       HASH UNIQUE          |           |     3 |   477 |       |     1 (100)| 00:00:01 |
|*  7 |        FIXED TABLE FULL    | X$KCCAL   |  2158 |   335K|       |     0   (0)| 00:00:01 |
|*  8 |      FIXED TABLE FULL      | X$KCCBL   |  1394 | 72488 |       |     0   (0)| 00:00:01 |
|*  9 |     VIEW                   |           |     1 |    32 |       |     2 (100)| 00:00:01 |
|  10 |      SORT UNIQUE           |           |  1404 | 70200 |       |     2 (100)| 00:00:01 |
|* 11 |       FILTER               |           |       |       |       |            |          |
|  12 |        SORT GROUP BY       |           |  1404 | 70200 |       |     2 (100)| 00:00:01 |
|  13 |         VIEW               | VM_NWVW_1 |  3546 |   173K|       |     1 (100)| 00:00:01 |
|  14 |          SORT GROUP BY     |           |  3546 |   512K|       |     1 (100)| 00:00:01 |
|* 15 |           HASH JOIN        |           |  3546 |   512K|       |     0   (0)| 00:00:01 |
|* 16 |            FIXED TABLE FULL| X$KCCBP   |  4566 |   481K|       |     0   (0)| 00:00:01 |
|* 17 |            FIXED TABLE FULL| X$KCCBS   |  3546 |   138K|       |     0   (0)| 00:00:01 |
|  18 |    NESTED LOOPS            |           |     1 |   440 |       |     1 (100)| 00:00:01 |
|* 19 |     FIXED TABLE FULL       | X$KCCPA   |     1 |   395 |       |            |          |
|* 20 |     VIEW                   |           |     1 |    45 |       |     1 (100)| 00:00:01 |
|  21 |      SORT UNIQUE           |           |     3 |   477 |       |     1 (100)| 00:00:01 |
|* 22 |       FIXED TABLE FULL     | X$KCCAL   |  2158 |   335K|       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter((:B15 IS NULL OR "ALTHP"=:B15) AND (:B14 IS NULL OR "ALSEQ">=:B14) AND 
              (:B13 IS NULL OR "ALSEQ"<=:B13) AND (:B10 IS NULL OR "ALNAM" LIKE :B10) AND (:B12 IS 
              NULL OR TO_NUMBER("ALNXS")>:B12) AND (:B11 IS NULL OR TO_NUMBER("ALLOS")<:B11) AND (:B9 
              IS NULL OR TO_DATE("ALNXT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')>:B9) AND 
              (:B8 IS NULL OR TO_DATE("ALLOT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')<=:B8
              ) AND DECODE(BITAND("ALFLG",6145),0,'A',1,'D',2048,'X',4096,'U','?')='A' AND 
              DECODE(BITAND("ALFLG",2),0,'NO','YES')='YES' AND 
              DECODE(BITAND("ALFLG",8),0,'NO','YES')='NO' AND "INST_ID"=USERENV('INSTANCE'))
   8 - filter("BLSEQ"="AL"."SEQUENCE#" AND "BLTHP"="AL"."THREAD#" AND 
              "AL"."RESETLOGS_CHANGE#"=TO_NUMBER("BLRLS") AND TO_DATE("BLRLC",'MM/DD/RR 
              HH24:MI:SS','nls_calendar=''GREGORIAN''')=INTERNAL_FUNCTION("AL"."RESETLOGS_TIME") AND 
              "INST_ID"=USERENV('INSTANCE') AND BITAND("BLFLG",2)<>2)
   9 - filter("BS"."SET_STAMP"="BLBSS" AND "BS"."SET_COUNT"="BLBSC")
  11 - filter("$vm_col_3"=COUNT("$vm_col_1"))
  15 - access("BSBSS"="BPBSS" AND "BSBSC"="BPBSC")
       filter(:B7 IS NULL OR :B7<>'B' AND :B7<>'D' OR (:B7='B' OR :B7='D') AND (:B6 IS 
              NULL AND DECODE(BITAND("BSTYP",1792),256,'LOGS',512,'NOLOGS',1024,'BACKUP_LOGS',NULL) 
              IS NULL OR "BPTAG"=:B6 AND DECODE(BITAND("BSTYP",1792),256,'LOGS',512,'NOLOGS',1024,'BAC
              KUP_LOGS',NULL) IS NOT NULL))
  16 - filter((:B4 IS NULL AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL OR "BPHDL" 
              LIKE :B4 OR "BPHDL" LIKE :B3 OR "BPHDL" LIKE :B2 OR "BPHDL" LIKE :B1) AND 
              DECODE(BITAND("BPFLG",12289),0,'A',1,DECODE(BITAND("BPFLG2",1),1,'F','D'),4096,'X',8192,
              'U','?')='A' AND ("BPDEV"=:B5 OR :B5 IS NULL) AND "INST_ID"=USERENV('INSTANCE'))
  17 - filter(CASE BITAND("BSTYP",16) WHEN 16 THEN 'I' ELSE 
              DECODE(BITAND("BSTYP",11),1,'D',2,'I',8,'L') END ='L' AND "INST_ID"=USERENV('INSTANCE') 
              AND (BITAND("BSTYP",32)<>32 OR BITAND("BSTYP",32768)=32768))
  19 - filter((:B5 IS NULL OR "PCDEV"=:B5) AND (:B7 IS NULL OR :B7<>'B' AND :B7<>'D' OR 
              (:B6 IS NULL AND DECODE(BITAND("PAFLG",1792),256,'LOGS',512,'NOLOGS',1024,'BACKUP_LOGS',
              NULL) IS NULL OR "PCTAG"=:B6 AND DECODE(BITAND("PAFLG",1792),256,'LOGS',512,'NOLOGS',102
              4,'BACKUP_LOGS',NULL) IS NOT NULL) AND (:B7='B' OR :B7='D')) AND 
              "INST_ID"=USERENV('INSTANCE') AND (:B4 IS NULL AND :B3 IS NULL AND :B2 IS NULL AND :B1 
              IS NULL OR "PCHDL" LIKE :B4 OR "PCHDL" LIKE :B3 OR "PCHDL" LIKE :B2 OR "PCHDL" LIKE 
              :B1) AND DECODE(BITAND("PCFLG",7),0,'A',1,'D',2,'X',4,'U','?')='A')
  20 - filter("PATHP"="AL"."THREAD#" AND "PASEQ"="AL"."SEQUENCE#" AND 
              "AL"."RESETLOGS_CHANGE#"=TO_NUMBER("PARLS") AND TO_DATE("PARLC",'MM/DD/RR 
              HH24:MI:SS','nls_calendar=''GREGORIAN''')=INTERNAL_FUNCTION("AL"."RESETLOGS_TIME"))
  22 - filter((:B15 IS NULL OR "ALTHP"=:B15) AND (:B14 IS NULL OR "ALSEQ">=:B14) AND 
              (:B13 IS NULL OR "ALSEQ"<=:B13) AND (:B10 IS NULL OR "ALNAM" LIKE :B10) AND (:B12 IS 
              NULL OR TO_NUMBER("ALNXS")>:B12) AND (:B11 IS NULL OR TO_NUMBER("ALLOS")<:B11) AND (:B9 
              IS NULL OR TO_DATE("ALNXT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')>:B9) AND 
              (:B8 IS NULL OR TO_DATE("ALLOT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')<=:B8
              ) AND DECODE(BITAND("ALFLG",6145),0,'A',1,'D',2048,'X',4096,'U','?')='A' AND 
              DECODE(BITAND("ALFLG",2),0,'NO','YES')='YES' AND 
              DECODE(BITAND("ALFLG",8),0,'NO','YES')='NO' AND "INST_ID"=USERENV('INSTANCE'))

2- Using SQL Profile
--------------------
Plan hash value: 2044880322

------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |  4182 |   527K|       |   372   (2)| 00:00:01 |
|   1 |  SORT ORDER BY             |           |  4182 |   527K|  1872K|     6 (100)| 00:00:01 |
|   2 |   UNION-ALL                |           |       |       |       |            |          |
|*  3 |    HASH JOIN               |           |  4181 |   526K|       |     4 (100)| 00:00:01 |
|   4 |     VIEW                   |           |  1404 | 44928 |       |     2 (100)| 00:00:01 |
|   5 |      HASH UNIQUE           |           |  1404 | 70200 |       |     2 (100)| 00:00:01 |
|*  6 |       FILTER               |           |       |       |       |            |          |
|   7 |        HASH GROUP BY       |           |  1404 | 70200 |       |     2 (100)| 00:00:01 |
|   8 |         VIEW               | VM_NWVW_1 |  3546 |   173K|       |     1 (100)| 00:00:01 |
|   9 |          HASH GROUP BY     |           |  3546 |   512K|       |     1 (100)| 00:00:01 |
|* 10 |           HASH JOIN        |           |  3546 |   512K|       |     0   (0)| 00:00:01 |
|* 11 |            FIXED TABLE FULL| X$KCCBS   |  3546 |   138K|       |     0   (0)| 00:00:01 |
|* 12 |            FIXED TABLE FULL| X$KCCBP   |  4566 |   481K|       |     0   (0)| 00:00:01 |
|* 13 |     HASH JOIN              |           |  4181 |   396K|       |     2 (100)| 00:00:01 |
|  14 |      VIEW                  |           |     3 |   135 |       |     1 (100)| 00:00:01 |
|  15 |       HASH UNIQUE          |           |     3 |   477 |       |     1 (100)| 00:00:01 |
|* 16 |        FIXED TABLE FULL    | X$KCCAL   |  2158 |   335K|       |     0   (0)| 00:00:01 |
|* 17 |      FIXED TABLE FULL      | X$KCCBL   | 96957 |  4923K|       |     0   (0)| 00:00:01 |
|* 18 |    HASH JOIN               |           |     1 |   440 |       |     1 (100)| 00:00:01 |
|* 19 |     FIXED TABLE FULL       | X$KCCPA   |     1 |   395 |       |            |          |
|  20 |     VIEW                   |           |     3 |   135 |       |     1 (100)| 00:00:01 |
|  21 |      HASH UNIQUE           |           |     3 |   477 |       |     1 (100)| 00:00:01 |
|* 22 |       FIXED TABLE FULL     | X$KCCAL   |  2158 |   335K|       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("BS"."SET_STAMP"="BLBSS" AND "BS"."SET_COUNT"="BLBSC")
   6 - filter("$vm_col_3"=COUNT("$vm_col_1"))
  10 - access("BSBSS"="BPBSS" AND "BSBSC"="BPBSC")
       filter(:B7 IS NULL OR :B7<>'B' AND :B7<>'D' OR (:B7='B' OR :B7='D') AND (:B6 IS 
              NULL AND DECODE(BITAND("BSTYP",1792),256,'LOGS',512,'NOLOGS',1024,'BACKUP_LOGS',NULL) 
              IS NULL OR "BPTAG"=:B6 AND DECODE(BITAND("BSTYP",1792),256,'LOGS',512,'NOLOGS',1024,'BAC
              KUP_LOGS',NULL) IS NOT NULL))
  11 - filter(CASE BITAND("BSTYP",16) WHEN 16 THEN 'I' ELSE 
              DECODE(BITAND("BSTYP",11),1,'D',2,'I',8,'L') END ='L' AND "INST_ID"=USERENV('INSTANCE') 
              AND (BITAND("BSTYP",32)<>32 OR BITAND("BSTYP",32768)=32768))
  12 - filter((:B4 IS NULL AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL OR "BPHDL" 
              LIKE :B4 OR "BPHDL" LIKE :B3 OR "BPHDL" LIKE :B2 OR "BPHDL" LIKE :B1) AND 
              DECODE(BITAND("BPFLG",12289),0,'A',1,DECODE(BITAND("BPFLG2",1),1,'F','D'),4096,'X',8192,
              'U','?')='A' AND ("BPDEV"=:B5 OR :B5 IS NULL) AND "INST_ID"=USERENV('INSTANCE'))
  13 - access("BLTHP"="AL"."THREAD#" AND "BLSEQ"="AL"."SEQUENCE#" AND 
              "AL"."RESETLOGS_CHANGE#"=TO_NUMBER("BLRLS") AND TO_DATE("BLRLC",'MM/DD/RR 
              HH24:MI:SS','nls_calendar=''GREGORIAN''')=INTERNAL_FUNCTION("AL"."RESETLOGS_TIME"))
  16 - filter((:B15 IS NULL OR "ALTHP"=:B15) AND (:B14 IS NULL OR "ALSEQ">=:B14) AND 
              (:B13 IS NULL OR "ALSEQ"<=:B13) AND (:B10 IS NULL OR "ALNAM" LIKE :B10) AND (:B12 IS 
              NULL OR TO_NUMBER("ALNXS")>:B12) AND (:B11 IS NULL OR TO_NUMBER("ALLOS")<:B11) AND (:B9 
              IS NULL OR TO_DATE("ALNXT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')>:B9) AND 
              (:B8 IS NULL OR TO_DATE("ALLOT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')<=:B8
              ) AND DECODE(BITAND("ALFLG",6145),0,'A',1,'D',2048,'X',4096,'U','?')='A' AND 
              DECODE(BITAND("ALFLG",2),0,'NO','YES')='YES' AND 
              DECODE(BITAND("ALFLG",8),0,'NO','YES')='NO' AND "INST_ID"=USERENV('INSTANCE'))
  17 - filter("INST_ID"=USERENV('INSTANCE') AND BITAND("BLFLG",2)<>2)
  18 - access("PATHP"="AL"."THREAD#" AND "PASEQ"="AL"."SEQUENCE#" AND 
              "AL"."RESETLOGS_CHANGE#"=TO_NUMBER("PARLS") AND TO_DATE("PARLC",'MM/DD/RR 
              HH24:MI:SS','nls_calendar=''GREGORIAN''')=INTERNAL_FUNCTION("AL"."RESETLOGS_TIME"))
  19 - filter((:B5 IS NULL OR "PCDEV"=:B5) AND (:B7 IS NULL OR :B7<>'B' AND :B7<>'D' OR 
              (:B6 IS NULL AND DECODE(BITAND("PAFLG",1792),256,'LOGS',512,'NOLOGS',1024,'BACKUP_LOGS',
              NULL) IS NULL OR "PCTAG"=:B6 AND DECODE(BITAND("PAFLG",1792),256,'LOGS',512,'NOLOGS',102
              4,'BACKUP_LOGS',NULL) IS NOT NULL) AND (:B7='B' OR :B7='D')) AND 
              "INST_ID"=USERENV('INSTANCE') AND (:B4 IS NULL AND :B3 IS NULL AND :B2 IS NULL AND :B1 
              IS NULL OR "PCHDL" LIKE :B4 OR "PCHDL" LIKE :B3 OR "PCHDL" LIKE :B2 OR "PCHDL" LIKE 
              :B1) AND DECODE(BITAND("PCFLG",7),0,'A',1,'D',2,'X',4,'U','?')='A')
  22 - filter((:B15 IS NULL OR "ALTHP"=:B15) AND (:B14 IS NULL OR "ALSEQ">=:B14) AND 
              (:B13 IS NULL OR "ALSEQ"<=:B13) AND (:B10 IS NULL OR "ALNAM" LIKE :B10) AND (:B12 IS 
              NULL OR TO_NUMBER("ALNXS")>:B12) AND (:B11 IS NULL OR TO_NUMBER("ALLOS")<:B11) AND (:B9 
              IS NULL OR TO_DATE("ALNXT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')>:B9) AND 
              (:B8 IS NULL OR TO_DATE("ALLOT",'MM/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN''')<=:B8
              ) AND DECODE(BITAND("ALFLG",6145),0,'A',1,'D',2048,'X',4096,'U','?')='A' AND 
              DECODE(BITAND("ALFLG",2),0,'NO','YES')='YES' AND 
              DECODE(BITAND("ALFLG",8),0,'NO','YES')='NO' AND "INST_ID"=USERENV('INSTANCE'))

-------------------------------------------------------------------------------

Step -6: You can accept the recommended SQL Profile for changing SQL Plan because estimated benefit: 73.23% for this query. You can disable it after applying, if it is not usable.

execute dbms_sqltune.accept_sql_profile(task_name => 'msd_dz3rmaqvta9uq',task_owner => 'SYS', replace => TRUE);

I will continue to explain Performance tuning tutorial in the next articles.

Next post link about Performance Tuning Tutorial is as follows.

How to Run SQL Tuning Advisor via Enterprise Manager Cloud Control | Oracle Database Performance Tuning Tutorial -13

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial

What is the SQL Tuning Advisor and How to Run SQL Tuning Advisor Manually in Oracle | Oracle Database Performance Tuning Tutorial -10

SQL Tuning Advisor in Oracle

Run SQL Tuning Advisor

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

About Mehmet Salih Deveci

3 comments

Leave a Reply Cancel reply