How to use Oracle's Dbms_sqltune package to execute SQL Tuning Advisor for SQL self-tuning

How to use Oracle's Dbms_sqltune package to execute SQL Tuning Advisor for SQL self-tuning

1 ". Here is a simple example to illustrate the use of Dbms_sqltune
Start by running a SQL that you want to tune, and then get the Sqlid

Sql> SELECT * from V$sqltext where sql_text like ' select * from dual% ';

ADDRESS hash_value sql_id command_type PIECE sql_text
---------------- ---------- ------------- ------------ ---------- -------------------------------------------------- --------------
0000000069BC2BE0 942515969 a5ks9fhw2v9s1 3 0 SELECT * from dual

1 row selected.

2 ". Execute SQLTRPT Script

Two kinds of data are recorded by default in Sqltrpt
Expensive SQL in the cursor cache
Expensive SQL in the workload repository
Of course we can also manually enter other SQL that we want to adjust

Sql> @?/rdbms/admin/sqltrpt

Expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sql_id ELAPSED sql_text_fragment
------------- ---------- -------------------------------------------------------
B6USRG82HWSA3 97.69 call Dbms_stats.gather_database_stats_job_proc ()
6GVCH1XU9CA3G 38.88 DECLARE Job Binary_integer: =: job; Next_date Date: =:
cvn54b7yz0s8u 21.34 Select/*+ Index (idl_ub1$ i_idl_ub11) +*/piece#,length
Dbvkky621gqtr 16.22 SELECT/*+ Parallel */Extractvalue (VALUE (T), '/select_
3ktacv9r56b51 9.68 Select Owner#,name,namespace,remoteowner,linkname,p_tim
Ga9j9xk5cy9s0 7.01 Select/*+ Index (idl_sb4$ i_idl_sb41) +*/piece#,length
39M4SX9K63BA2 6.09 Select/*+ Index (idl_ub2$ i_idl_ub21) +*/piece#,length
8swypbbr0m372 5.90 Select Order#,columns,types from access$ where d_obj#=:
db78fxqxwxt7r 5.62 Select/*+ rule */bucket, endpoint, col#, Epvalue from
G5M0BNVYY37B1 5.38 Select sql_id, Plan_hash_value, bucket_id, begin
424h0nf7bhqzd 5.02 SELECT sqlset_row (sql_id, Force_matching_signature,

sql_id ELAPSED sql_text_fragment
------------- ---------- -------------------------------------------------------
32hbap2vtmf53 4.31 Select Position#,sequence#,level#,argument,type#,charse
9s0xa5dgvuq55 4.29 DECLARE Job Binary_integer: =: job; Next_date Timestam
d4taszv1bpc0w 4.02 DECLARE CNT number;   Bid number; Eid
96G93HNTRZJTR 3.78 Select/*+ Rule */bucket_cnt, row_cnt, cache_cnt, NULL

Expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sql_id ELAPSED
------------- ----------
Sql_text_fragment
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------
B6USRG82HWSA3 198.03
Call Dbms_stats.gather_database_stats_job_proc ()

6GVCH1XU9CA3G 169.58
DECLARE job Binary_integer: =: job; Next_date Date: =:

1JQCPQF8FPDR8 139.13
Select COUNT (*) from dba_objects A, dba_objects b where

sql_id ELAPSED
------------- ----------
Sql_text_fragment
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------
cvn54b7yz0s8u 82.99
Select/*+ Index (idl_ub1$ i_idl_ub11) +*/piece#,length

F6CZ4N8Y72XDC 63.29
SELECT space_usage_kbytes from V$sysaux_occupants WH

6mcpb06rctk0x 44.62
Call Dbms_space.auto_space_advisor_job_proc ()

sql_id ELAPSED
------------- ----------
Sql_text_fragment
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------
3ktacv9r56b51 42.79
Select Owner#,name,namespace,remoteowner,linkname,p_tim

12a2xbmwn5v6z 39.87
Select owner, segment_name, blocks from dba_segments WH

05S9358MM6VRR 37.59
Begin Dbms_feature_usage_internal.exec_db_usage_samplin

sql_id ELAPSED
------------- ----------
Sql_text_fragment
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------
5zruc4v6y32f9 33.12
DECLARE job Binary_integer: =: job; Next_date Timestam

Dbvkky621gqtr 31.66
SELECT/*+ Parallel */Extractvalue (VALUE (T), '/select_

63N9PWUTT8YZW 28.03
MERGE/*+ dynamic_sampling (ST 4) Dynamic_sampling_est_c

sql_id ELAPSED
------------- ----------
Sql_text_fragment
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------
7xa8wfych4mad 27.86
SELECT SUM (blocks) from X$kewx_segments WHERE segment

8swypbbr0m372 26.81
Select Order#,columns,types from access$ where d_obj#=:

db78fxqxwxt7r 26.37
Select/*+ Rule */bucket, endpoint, col#, Epvalue from

Specify the SQL ID
~~~~~~~~~~~~~~~~~~
Enter value for SQLID:A5KS9FHW2V9S1

SQL Id SPECIFIED:A5KS9FHW2V9S1

Tune the SQL-----------------------------------------------here for the SQL Tuning Advisor recommendations
~~~~~~~~~~~~

General Information Sections
-------------------------------------------------------------------------------
Tuning Task name:task_219
Tuning Task Owner:sys
Workload Type:single SQL Statement
Scope:comprehensive
Time Limit (seconds): 1800
Completion status:completed
Started at:05/17/2014 17:07:54
Completed at:05/17/2014 17:07:54

-------------------------------------------------------------------------------
Schema Name:sys

SQL ID:A5KS9FHW2V9S1
SQL Text:select * from dual

-------------------------------------------------------------------------------
There is no recommendations to improve the statement.

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

Note: In the production environment has not tested, do not know how the effect of SQL Tuning Advisor, this needs to be then verified under!

How to use Oracle's Dbms_sqltune package to execute SQL Tuning Advisor for SQL self-tuning