dbms_sqltune usage example

1.) Find the SQL_ID from v$session.
select sid,serial#,sql_id from v$session where username='THE USER U ARE USING TO RUN THE SQL';
In our case it is d6w2vs1p33qrn.

2.) Now create the tuning task:

set serveroutput on
set long 10000 longchunksize 5000
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'd6w2vs1p33qrn',
scope => 'COMPREHENSIVE',
time_limit => 3000,
task_name => 'd6w2vs1p33qrn_tune',
description => 'Task to tune a query');
END;
/


Errors:DECLARE
*
ERROR at line 1:
ORA-13616: The current user SCOTT has not been granted the ADVISOR privilege.
ORA-06512: at "SYS.DBMS_SQLTUNE", line 791
ORA-06512: at line 4

3.) Grant advisor privilege to the user.

4.) Now execute the sql tune task:

exec dbms_sqltune.execute_tuning_task('d6w2vs1p33qrn_tune');
This is take sometime to run.

5.) Now check what the task has to say:
set long 20000
set longchunksize 20000
set linesize 400
select dbms_sqltune.report_tuning_task('d6w2vs1p33qrn_tune') from dual;


DBMS_SQLTUNE.REPORT_TUNING_TASK('D6W2VS1P33QRN_TUNE')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : d6w2vs1p33qrn_tune
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 3000
Completion Status : COMPLETED
Started at : 09/07/2008 23:51:33
Completed at : 09/08/2008 00:08:44
Number of Statistic Findings : 7
Number of SQL Profile Findings : 1

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d6w2vs1p33qrn
SQL Text : select /*+ ordered INDEX(A,IDX_MV2_ACCT4) PARALLEL(a,2)
INDEX(C,IDX_TAB29) parallel(C,10) INDEX(U,IDX_TAB39)
PARALLEL(U,10) */
C.AD_GRP_ID,
C.ACCT_ID,
C.CRTV_ID,
C.SS_WEIGHT,
C.SS_RANK,
C.SS_ROTATION_FLG,
C.CM_WEIGHT,
C.CM_RANK,
C.CM_ROTATION_FLG,
C.TITLE,
C.DESCR,
C.SHORT_DESCR,
C.DISPLAY_URL,
C.CRTV_TMPLT_ID,
C.EXPLODED_FLG,
C.STATUS,
C.EDIT_STATUS,
C.LANG_ID C_LANG_ID,
C.CRTV_RATING,
pkg_distro_utils.cast_Unsigned(U.URL_ID) as URL_ID,
U.LANG_ID U_LANG_ID,
U.URL_RATING,
A.MARKET_ID,
pkg_distro_utils.get_unixtimestamp(C.LAST_UPD) as LAST_UPD
from am.TAB1 A
inner join TAB2 C on C.ACCT_ID = A.ACCT_ID
inner join TAB3 U
on U.ACCT_ID = C.ACCT_ID and U.URL_ID = C.URL_ID
and U.POD_ID = C.POD_ID --Ticket# 81628
where C.status <> 3
order by C.AD_GRP_ID

-------------------------------------------------------------------------------
FINDINGS SECTION (8 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB39" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB39', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

2- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB32" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB32', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

3- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB35" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB35', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

4- Statistics Finding
---------------------
Optimizer statistics for table "SCOTT"."TAB3" and its indices are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'TAB3', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

5- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB27" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB27', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

6- Statistics Finding
---------------------
Optimizer statistics for index "SCOTT"."IDX_TAB21" are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname =>
'IDX_TAB21', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Rationale
---------
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

7- Statistics Finding
---------------------
Optimizer statistics for table "SCOTT"."TAB2" and its indices are stale.

Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'TAB2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

8- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 60.25%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'd6w2vs1p33qrn_tune', replace => TRUE);

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

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3106191583

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 2812K (1)| 09:22:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 2812K (1)| 09:22:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 56G| 2812K (1)| 09:22:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| 3266M| 2068K (1)| 06:53:37 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,01 | P->P | HASH |
| 9 | PX PARTITION HASH ALL | | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,01 | PCWC | |
| 10 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,03 | PCWP | |
| 12 | PX SEND HASH | :TQ10002 | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | P->P | HASH |
|* 13 | HASH JOIN | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1915K| 20M| | 573 (1)| 00:00:07 | | | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | S->P | BROADCAST |
| 17 | INDEX FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | | |
| 18 | PX PARTITION HASH ALL | | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 4 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB2 | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 36 | Q1,02 | PCWP | |
| 20 | INDEX FULL SCAN | IDX_TAB29 | 139M| | | 15201 (1)| 00:03:03 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

6 - access("U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID" AND "U"."POD_ID"="C"."POD_ID")
13 - access("C"."ACCT_ID"="A"."ACCT_ID")
19 - filter("C"."STATUS"<>3)

2- Original With Adjusted Cost
------------------------------
Plan hash value: 4016845619

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 2812K (1)| 09:22:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 2812K (1)| 09:22:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 56G| 2812K (1)| 09:22:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 2068K (1)| 06:53:37 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| 3266M| 2068K (1)| 06:53:37 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,01 | P->P | HASH |
| 9 | PX PARTITION HASH ALL | | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,01 | PCWC | |
| 10 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,03 | PCWP | |
| 12 | PX SEND HASH | :TQ10002 | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | P->P | HASH |
|* 13 | HASH JOIN | | 92M| 23G| | 1717K (1)| 05:43:31 | | | Q1,02 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1915K| 20M| | 573 (1)| 00:00:07 | | | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | S->P | BROADCAST |
| 17 | INDEX FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 573 (1)| 00:00:07 | | | | | |
| 18 | PX PARTITION HASH ALL | | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 4 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB2 | 92M| 22G| | 1716K (1)| 05:43:23 | 1 | 36 | Q1,02 | PCWP | |
| 20 | INDEX FULL SCAN | IDX_TAB29 | 139M| | | 15201 (1)| 00:03:03 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

6 - access("U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID" AND "U"."POD_ID"="C"."POD_ID")
13 - access("C"."ACCT_ID"="A"."ACCT_ID")
19 - filter("C"."STATUS"<>3)

3- Using SQL Profile
--------------------
Plan hash value: 1947416951

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92M| 26G| | 1117K (1)| 03:43:35 | | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 92M| 26G| | 1117K (1)| 03:43:35 | | | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 92M| 26G| 54G| 1117K (1)| 03:43:35 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,03 | P->P | RANGE |
|* 6 | HASH JOIN BUFFERED | | 92M| 26G| | 447K (1)| 01:29:32 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,03 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | PCWC | |
| 10 | INDEX FAST FULL SCAN | IDX_MV2_ACCT4 | 1915K| 20M| | 351 (2)| 00:00:05 | | | Q1,00 | PCWP | |
|* 11 | HASH JOIN | | 92M| 25G| 2432M| 447K (1)| 01:29:27 | | | Q1,03 | PCWP | |
| 12 | PX JOIN FILTER CREATE | :BF0000 | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,03 | PCWP | |
| 13 | PX RECEIVE | | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,03 | PCWP | |
| 14 | PX SEND HASH | :TQ10001 | 92M| 22G| | 131K (1)| 00:26:18 | | | Q1,01 | P->P | HASH |
| 15 | PX BLOCK ITERATOR | | 92M| 22G| | 131K (1)| 00:26:18 | 1 | 4 | Q1,01 | PCWC | |
|* 16 | TABLE ACCESS FULL | TAB2 | 92M| 22G| | 131K (1)| 00:26:18 | 1 | 36 | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,03 | PCWP | |
| 18 | PX SEND HASH | :TQ10002 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,02 | P->P | HASH |
| 19 | PX JOIN FILTER USE | :BF0000 | 751M| 20G| | 48486 (1)| 00:09:42 | | | Q1,02 | PCWP | |
| 20 | PX PARTITION HASH ALL| | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 4 | Q1,02 | PCWC | |
| 21 | INDEX FULL SCAN | IDX_TAB39 | 751M| 20G| | 48486 (1)| 00:09:42 | 1 | 36 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------

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

6 - access("C"."ACCT_ID"="A"."ACCT_ID")
11 - access("U"."POD_ID"="C"."POD_ID" AND "U"."ACCT_ID"="C"."ACCT_ID" AND "U"."URL_ID"="C"."URL_ID")
16 - filter("C"."STATUS"<>3)

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


6.) Drop the tuning task:
exec dbms_sqltune.DROP_TUNING_TASK('d6w2vs1p33qrn_tune');