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)
-------------------------------------------------------------------------------