HOW TO GENERATE SQL TUNING ADVISOR REPORT IN ORACLE DATABASE

Choose the sql to be tuned

kIsH@Xhydra<>select sql_text from v$sql where sql_id='5qmm8vw5dvy8b';

SQL_TEXT
--------------------------------------------------------------------------------
select xdate from kish.xtbl where xid between 4001021 and 10000000

Create the tuning task using the below procedure

kIsH@Xhydra<>DECLARE
          v_tune_task_id varchar2(50);
          BEGIN
                  v_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                  sql_id => '5qmm8vw5dvy8b',
                  time_limit => 86400,
                  task_name => '5qmm8vw5dvy8b_tt',
                  description => 'Tuning advisory for 5qmm8vw5dvy8b');
                  DBMS_OUTPUT.PUT_LINE('v_tune_task_id: '|| v_tune_task_id);
          END;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.

Check the advisor parameters that can be used for configuration of the tuning task from dba_advisor_parameters

kIsH@Xhydra<>select PARAMETER_NAME,PARAMETER_VALUE from dba_advisor_parameters where TASK_NAME='5qmm8vw5dvy8b_tt';

PARAMETER_NAME       PARAMETER_VALUE
-------------------- --------------------
DAYS_TO_EXPIRE       30
END_SNAPSHOT         UNUSED
END_TIME             UNUSED
INSTANCE             UNUSED
JOURNALING           INFORMATION
MODE                 COMPREHENSIVE
START_SNAPSHOT       UNUSED
START_TIME           UNUSED
TARGET_OBJECTS       1
TIME_LIMIT           86400
DEFAULT_EXECUTION_TY TUNE SQL
PE

CON_DBID_MAPPING     UNUSED
ORA_EM_PARAM1        UNUSED
ORA_EM_PARAM2        UNUSED
ORA_EM_PARAM3        UNUSED
ORA_EM_PARAM4        UNUSED
ORA_EM_PARAM5        UNUSED
ORA_EM_PARAM6        UNUSED
ORA_EM_PARAM7        UNUSED
ORA_EM_PARAM8        UNUSED
ORA_EM_PARAM9        UNUSED
ORA_EM_PARAM10       UNUSED
EXECUTION_DAYS_TO_EX UNLIMITED
PIRE

REMOTE_SOURCE        UNUSED
USERNAME             UNUSED
BASIC_FILTER         UNUSED
PLAN_FILTER          UNUSED
RANK_MEASURE1        UNUSED
RANK_MEASURE2        UNUSED
RANK_MEASURE3        UNUSED
SQL_PERCENTAGE       1
SQL_LIMIT            -1
RESUME_FILTER        UNUSED
LOCAL_TIME_LIMIT     UNUSED
TEST_EXECUTE         AUTO
APPLY_CAPTURED_COMPI UNUSED
LENV

DATABASE_LINK_TO     UNUSED

37 rows selected.

Execute the tuning task which was created earlier

kIsH@Xhydra<>BEGIN
  2          DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '5qmm8vw5dvy8b_tt');
  3          END;
  4          /

PL/SQL procedure successfully completed.

Typical views which are related to tuning tasks

kIsH@Xhydra<>desc dba_advisor_tasks
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 TASK_ID                                   NOT NULL NUMBER
 TASK_NAME                                          VARCHAR2(128)
 DESCRIPTION                                        VARCHAR2(256)
 ADVISOR_NAME                                       VARCHAR2(128)
 CREATED                                   NOT NULL DATE
 LAST_MODIFIED                             NOT NULL DATE
 PARENT_TASK_ID                                     NUMBER
 PARENT_RXEC_ID                                     NUMBER
 LAST_EXECUTION                                     VARCHAR2(128)
 EXECUTION_TYPE                                     VARCHAR2(128)
 EXECUTION_TYPE#                                    NUMBER
 EXECUTION_DESCRIPTION                              VARCHAR2(256)
 EXECUTION_START                                    DATE
 EXECUTION_END                                      DATE
 STATUS                                             VARCHAR2(11)
 STATUS_MESSAGE                                     VARCHAR2(4000)
 PCT_COMPLETION_TIME                                NUMBER
 PROGRESS_METRIC                                    NUMBER
 METRIC_UNITS                                       VARCHAR2(64)
 ACTIVITY_COUNTER                                   NUMBER
 RECOMMENDATION_COUNT                               NUMBER
 ERROR_MESSAGE                                      VARCHAR2(4000)
 SOURCE                                             VARCHAR2(128)
 HOW_CREATED                                        VARCHAR2(30)
 READ_ONLY                                          VARCHAR2(5)
 SYSTEM_TASK                                        VARCHAR2(5)
 ADVISOR_ID                                NOT NULL NUMBER
 STATUS#                                            NUMBER

kIsH@Xhydra<>desc v$advisor_progress
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 USERNAME                                           VARCHAR2(128)
 OPNAME                                             VARCHAR2(64)
 ADVISOR_NAME                                       VARCHAR2(64)
 TASK_ID                                            NUMBER
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 BENEFIT_SOFAR                                      NUMBER
 BENEFIT_MAX                                        NUMBER
 FINDINGS                                           NUMBER
 RECOMMENDATIONS                                    NUMBER
 TIME_REMAINING                                     NUMBER
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 ELAPSED_SECONDS                                    NUMBER
 ADVISOR_METRIC1                                    NUMBER
 METRIC1_DESC                                       VARCHAR2(64)
 EXECUTION_TYPE                                     VARCHAR2(64)
 CON_ID                                             NUMBER

Check the advisor log, if the tuning task is completed

kIsH@Xhydra<>col STATUS_MESSAGE for a20
kIsH@Xhydra<>select OWNER,TASK_ID,TASK_NAME,STATUS,STATUS_MESSAGE from dba_advisor_log where TASK_NAME='5qmm8vw5dvy8b_tt';

OWNER                      TASK_ID TASK_NAME            STATUS               STATUS_MESSAGE
-------------------- ------------- -------------------- -------------------- --------------------
SYS                           1633 5qmm8vw5dvy8b_tt     COMPLETED

Generate the tuning advisor recommendations for tuning

kIsH@Xhydra<>set long 100000000
kIsH@Xhydra<>select DBMS_SQLTUNE.REPORT_TUNING_TASK('5qmm8vw5dvy8b_tt') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('5QMM8VW5DVY8B_TT')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : 5qmm8vw5dvy8b_tt
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 86400
Completion Status  : COMPLETED
Started at         : 10/26/2022 22:49:53
Completed at       : 10/26/2022 22:49:53

-------------------------------------------------------------------------------
Schema Name   : SYS
Container Name: CDB$ROOT
SQL ID        : 5qmm8vw5dvy8b
SQL Text      : select xdate from kish.xtbl where xid between 4001021 and
                10000000

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

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 70.43%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index KISH.IDX$$_06610001 on KISH.XTBL("XID","XDATE");

  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 maintenance overhead and additional space consumption.

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

1- Original
-----------
Plan hash value: 1420452506

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   425K|  5818K|  5485   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| XTBL |   425K|  5818K|  5485   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("XID"<=10000000 AND "XID">=4001021)

2- Using New Indices
--------------------
Plan hash value: 1675568796

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |   425K|  5818K|  1622   (1)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_06610001 |   425K|  5818K|  1622   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("XID">=4001021 AND "XID"<=10000000)

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

Leave a Reply