SQL Tuning Advisor help us to find out the problem of SQL Statement Execution. SQL Tuning Advisory can run for single SQL Statement using SQL_ID. You can find SQL_ID from GV$SQLAREA View of GV$SQL View. After finding SQL ID perform the following steps for running SQL Tuning Advisory.
1. Create SQL Tuning Advisor Task.
DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_id => '5yshyjm21hhfs',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'STA:5yshyjm21hhfs',
description => '5yshyjm21hhfs');
END;
2. Executing SQL Tuning Task.
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:5yshyjm21hhfs');
3. The Result of SQL Tuning Advisory.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:5yshyjm21hhfs') from dual;
Exercise
==========================
SQL> DECLARE
MY_TASK_NAME VARCHAR2(30);
BEGIN
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => '5yshyjm21hhfs',SCOPE => 'COMPREHENSIVE',TIME_LIMIT => 60,TASK_NAME => 'sql_stat:5yshyjm21hhfs',DESCRIPTION => '5yshyjm21hhfs');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_stat:5yshyjm21hhfs');
PL/SQL procedure successfully completed.
SQL>
SQL> SET LONG 10000
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_stat:5yshyjm21hhfs') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_STAT:5YSHYJM21HHFS')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_stat:5yshyjm21hhfs
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 03/31/2016 17:01:41
Completed at : 03/31/2016 17:01:42
-------------------------------------------------------------------------------
Schema Name: RBL_SUPPORT
SQL ID : 5yshyjm21hhfs
SQL Text : SELECT ACC_NUMBER, ACC_DATE, TRAN_AMOUNT
FROM ACNUM P,
AC_BAL T
WHERE
P.ACC_NUMBER=T.ACC_NUMBER
AND ACC_KEY = :1
AND ACC_DATE BETWEEN
DFU_GET_CURR_MONTH_STARTDATE(:ACC_KEY, CASE WHEN
NVL(P.TRAN_VALUE_DT,:V_CURRENT_DATE)<:V_CURRENT_DATE THEN
P.TRAN_VALUE_DT ELSE :V_CURRENT_DATE END) AND
DFU_GET_CURR_MONTH_ENDTDATE(:ACC_KEY,DFU_GET_CURR_MONTH_STA
RTDATE(:ACC_KEY, CASE WHEN
NVL(P.TRAN_VALUE_DT,:V_CURRENT_DATE)<:V_CURRENT_DATE THEN
P.TRAN_VALUE_DT ELSE :V_CURRENT_DATE END))
AND ACC_DATE <= :6
Bind Variables :
1 - (NUMBER):1
2 - (NUMBER):1
3 - (DATE):03/31/2016 00:00:00
4 - (DATE):03/31/2016 00:00:00
5 - (DATE):03/31/2016 00:00:00
6 - (NUMBER):1
7 - (NUMBER):1
8 - (DATE):03/31/2016 00:00:00
9 - (DATE):03/31/2016 00:00:00
10 - (DATE):03/31/2016 00:00:00
11 - (DATE):03/31/2016 00:00:00
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- --------------
--
1 2650109619 2016-03-31/16:11:43 48.718 Cursor Cache
Information
-----------
- 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_stat:5yshyjm21hhfs', owner_name => 'SYS', plan_hash_value =>
xxxxxxxx);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2489994495
--------------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 1 | 47
| 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 47
| 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 47
| 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ACNUM | 1 | 22
| 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AC_BAL | 1 |
| 2 (0)| 00:00:01 |
|* 5 | MAT_VIEW ACCESS BY INDEX ROWID| AC_BAL | 1 | 25
| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DFU_GET_CURR_MONTH_STARTDATE"(:ACC_KEY,CASE WHEN
NVL("P"."TRAN_VALUE_DT",:V_CURRENT_DATE)<:V_CURRENT_DATE THEN "P".
"TRAN_VALUE_DT" ELSE
:V_CURRENT_DATE END )<=:6)
4 - access("P"."ACC_NUMBER"="T"."ACC_NUMBER" AND
"ACC_DATE">="DFU_GET_CURR_MONTH_STARTDATE"(:ACC_KEY,C
ASE WHEN
NVL("P"."TRAN_VALUE_DT",:V_CURRENT_DATE)<:V_CURRENT_DATE THEN "P".
"TRAN_VALUE_DT" ELSE
:V_CURRENT_DATE END ) AND "ACC_DATE"<=:6)
filter("ACC_DATE"<="DFU_GET_CURR_MONTH_ENDTDATE"(:ACC_KEY,"D
FU_GET_CURR_MONTH_
STARTDATE"(:ACC_KEY,CASE WHEN NVL("P"."TRAN_VALUE_DT",:V_CU
RRENT_DATE)<:V_CURRENT_DATE
THEN "P"."TRAN_VALUE_DT" ELSE :V_CURRENT_DATE END )))
5 - filter("ACC_KEY"=:1)
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :2650109619
Executions :1229
Elapsed Time :48.718 sec
CPU Time :30.149 sec
Buffer Gets :3196590
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
--------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 1 |
47 | 8592 (1)| 00:01:44 |
| 1 | NESTED LOOPS | | 1 |
47 | 8592 (1)| 00:01:44 |
| 2 | NESTED LOOPS | | 327K|
47 | 8592 (1)| 00:01:44 |
|* 3 | TABLE ACCESS FULL | ACNUM | 1 |
22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | AC_BAL_ENT_BRAN | 327K|
| 1647 (1)| 00:00:20 |
|* 5 | MAT_VIEW ACCESS BY INDEX ROWID| AC_BAL | 1 |
25 | 8590 (1)| 00:01:44 |
--------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DFU_GET_CURR_MONTH_STARTDATE"(:ACC_KEY,CASE WHEN
NVL("P"."TRAN_VALUE_DT",:V_CURRENT_DATE)<:V_CURRENT_DATE THEN "P".
"TRAN_VALUE_DT" ELSE :V_CURRENT_DATE
END )<=:6)
4 - access("ACC_KEY"=:1)
5 - filter("ACC_DATE"<=:6 AND "P"."ACC_NUMBER"="T"."TRAN_INTE
RNAL_ACNUM" AND
"ACC_DATE">="DFU_GET_CURR_MONTH_STARTDATE"(:ACC_KEY,C
ASE WHEN
NVL("P"."TRAN_VALUE_DT",:V_CURRENT_DATE)<:V_CURRENT_DATE THEN "P".
"TRAN_VALUE_DT" ELSE :V_CURRENT_DATE
END ) AND "ACC_DATE"<="DFU_GET_CURR_MONTH_ENDTDATE"(:ENTITY
_NUMBER,"DFU_GET_CURR_MONTH_STARTDATE
"(:ACC_KEY,CASE WHEN NVL("P"."TRAN_VALUE_DT",:V_CURRENT_DAT
E)<:V_CURRENT_DATE THEN
"P"."TRAN_VALUE_DT" ELSE :V_CURRENT_DATE END )))
-------------------------------------------------------------------------------
SQL>
