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.
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.
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;
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.
Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.