How ORACLE SQL TUNING ADVISOR is used

The main steps used by SQL Tunning Advisor:

1 Setting up Tunning task

2 Execute Task

3 showing tunning results

4 Follow the recommendations to run the corresponding tuning method

Here's how to do it in this order:

1 Setting up test tables and indexes

Sql> CREATE TABLE test_sql_advisor as SELECT owner,object_name,object_id from Dba_objects;

Table created

Sql> Select COUNT (*) from Test_sql_advisor;

COUNT (*)
----------
757229

2 Authorized SYSDBA Permissions Login

Sql> GRANT ADVISOR to Noap;

Grant succeeded
Sql> GRANT select_catalog_role to Noap;

Grant succeeded
Sql> GRANT EXECUTE on Dbms_sqltune to Noap;

Grant succeeded

3 CREATE TASK

You can create tuning tasks from the following:
-SQL statement selected by SQL identifier from the cursor cache
-SQL Tuning Set containing multiple statements
-Text of a single SQL statement
-SQL statement selected by SQL identifier from the Automatic Workload Repository.

The above translation can be understood to establish tuning tasks in several ways:

1 Create a task by obtaining a sql_id from the cursor cache to specify the SQL statement
2 SQL Tuning collection includes multiple statements to establish a task
3 The text of a single SQL statement to create a task
4 Create a task by using the corresponding sql_id in the AWR to get the SQL statement

The main use of Dbms_sqltune is to set up a task. Create_tuning_task This function, the function exists rewritten, the following posted out interface
1 creating tasks based on SQL text

--------------------create_tuning_task-sql Text Format------------------

--NAME:

--Create_tuning_task-crate a tuning task in order to tune a single SQL

--Statement (SQL text format)

--

--DESCRIPTION

--This function was called to prepare the tuning of a single statement

--Given its text.

-The function mainly creates an advisor task and sets its parameters.

--

--PARAMETERS:

--Sql_text (in)-text of a SQL statement

--Bind_list (in)-a set of bind values

--user_name (in)-the username for who the statement would be tuned

--scope (in)-Tuning scope (limited/comprehensive)

--Time_limit (in)-maximum duration in second for the tuning session

--Task_name (in)-Optional tuning Task Name

--Description (in)-Maximum of the SQL tuning session description

--

--RETURNS:

--SQL tuning Task unique Name

--

--EXCEPTIONS:

--To IS done

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

FUNCTION Create_tuning_task (

Sql_text in CLOB,

Bind_list in Sql_binds: = NULL,

User_name in VARCHAR2: = NULL,

Scope in VARCHAR2: = Scope_comprehensive,

Time_limit in Number: = Time_limit_default,

Task_name in VARCHAR2: = NULL,

Description in VARCHAR2: = NULL)

RETURN VARCHAR2;

2 building tasks based on sql_id
---------------------create_tuning_task-sql_id Format--------------------

--NAME:

--create_tuning_task-sql_id format

--

--DESCRIPTION

--This function was called to prepare the tuning of a single statement

--From the Cursor Cache given its identifier.

-The function mainly creates an advisor task and sets its parameters.

--

--PARAMETERS:

--sql_id (in)-identifier of the statement

--Plan_hash_value (in)-hash value of the SQL execution plan

--scope (in)-Tuning scope (limited/comprehensive)

--Time_limit (in)-Maximum tuning duration in second

--Task_name (in)-Optional tuning Task Name

--Description (in)-Maximum of the SQL tuning session description

--

--RETURNS:

--SQL tuning Task unique Name

--

--EXCEPTIONS:

--To IS done

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

FUNCTION Create_tuning_task (

sql_id in VARCHAR2,

Plan_hash_value in Number: = NULL,

Scope in VARCHAR2: = Scope_comprehensive,

Time_limit in Number: = Time_limit_default,

Task_name in VARCHAR2: = NULL,

Description in VARCHAR2: = NULL)

RETURN VARCHAR2;

3 Setting up tasks based on awr snapshot interval and corresponding sql_id

--------------create_tuning_task-workload repository Format--------------

--NAME:

--create_tuning_task-workload repository format

--

--DESCRIPTION

--This function was called to prepare the tuning of a single statement

--from the workload repository given a range of snapshot identifiers.

-The function mainly creates an advisor task and sets its parameters.

--

--PARAMETERS:

--Begin_snap (in)-Begin snapshot Identifier

--End_snap (in)-End snapshot identifier

--sql_id (in)-identifier of the statement

--Plan_hash_value (in)-Plan hash value

--scope (in)-Tuning scope (limited/comprehensive)

--Time_limit (in)-maximum duration in second for tuning

--Task_name (in)-Optional tuning Task Name

--Description (in)-Maximum of the SQL tuning session description

--

--RETURNS:

--SQL tuning Task unique Name

--

--EXCEPTIONS:

--To IS done

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

FUNCTION Create_tuning_task (

Begin_snap in number,

End_snap in number,

sql_id in VARCHAR2,

Plan_hash_value in Number: = NULL,

Scope in VARCHAR2: = Scope_comprehensive,

Time_limit in Number: = Time_limit_default,

Task_name in VARCHAR2: = NULL,

Description in VARCHAR2: = NULL)

RETURN VARCHAR2;

And of course there are functions based on sqlset, and SQL Performance Analyzer (SPA) task to set up this topic.

The setup of sample is based on the SQL text to do the experiment, as shown below

DECLARE
My_task_name VARCHAR2 (30);
My_sqltext CLOB;
BEGIN
My_sqltext: = ' SELECT * from test_object_ttx WHERE object_id =: BND ';
My_task_name: = Dbms_sqltune. Create_tuning_task (Sql_text = My_sqltext,
Bind_list=>sql_binds (ANYDATA. Convertnumber (9)),
user_name = ' Noap ',
Scope=> ' comprehensive ',
Time_limit = 60,
Task_name = ' Sql_tuning_test ',
Description=> ' TUNING TASK '
);

END;

The established state is initial because it hasn't been executed yet.

4 EXECUTE TASK

Sql> BEGIN Dbms_sqltune. Execute_tuning_task (' sql_tuning_test '); END;
2/

PL/SQL procedure successfully completed

sql> SELECT status from user_advisor_tasks WHERE task_name = ' sql_tuning_test ';

STATUS
-----------
Completed

5 Query Recommendations Results

SELECT Dbms_sqltune. Report_tuning_task (' Sql_tuning_test ') from DUAL;

The statement is a result of a CLOB field click Self-view to optimize SQL based on the corresponding recommendations

General Information Sections
-------------------------------------------------------------------------------
Tuning Task Name:sql_tuning_test
Tuning Task Owner:noap
Workload Type:single SQL Statement
Scope:comprehensive
Time Limit (seconds): 60
Completion status:completed
Started at:06/24/2011 12:45:20
Completed at:06/24/2011 12:45:22
-------------------------------------------------------------------------------
Schema Name:noap
SQL id:5k6fk8cynf60x
SQL Text:select * from test_sql_advisor WHERE object_id =: BND
-------------------------------------------------------------------------------
Findings section (1 finding)
-------------------------------------------------------------------------------
1-statistics finding
---------------------
The table "Noap" has not been parsed. " Test_sql_advisor ".
Recommendation
--------------
-Consider collecting optimizer statistics for this table.
Execute dbms_stats.gather_table_stats (ownname = ' Noap ', tabname =
' Test_sql_advisor ', estimate_percent =
Dbms_stats. Auto_sample_size, method_opt = ' for all COLUMNS SIZE
AUTO ');
Rationale
---------
In order to select a good execution plan, the optimizer needs the most up-to-date statistics for this table.
-------------------------------------------------------------------------------
EXPLAIN Plans Section
-------------------------------------------------------------------------------
1-original
-----------
Plan Hash value:719217330
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |     |    1 |     96 | 2 (0) | 00:03:18 |
|  1 | TABLE ACCESS by INDEX rowid|     Test_sql_advisor |    1 |     96 | 2 (0) | 00:03:18 |
|* 2 | INDEX RANGE SCAN |     Test_sql_advisor_idx |       1 |     | 1 (0) | 00:01:39 |
----------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-access ("object_id" =:bnd)
-------------------------------------------------------------------------------

6 How to delete a task

BEGIN dbms_sqltune.drop_tuning_task (' sql_tuning_test '); END;

7 views that can be used

SELECT * from User_advisor_tasks T WHERE task_name= ' sql_tuning_test ';
SELECT * from Dba_sqltune_statistics
SELECT * from Dba_sqltune_binds
SELECT * from Dba_sqltune_plans WHERE task_id=13009

How ORACLE SQL TUNING ADVISOR is used