Thursday, March 31, 2016

SQL Tuning Advisor for Single SQL Statement.

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>

No comments:

Post a Comment