First Use SQL Tuning recommendation Tool--sql Tuning Advisor

in 10g, Oracle launched its own SQL optimization aids :  SQL Optimizer (SQL Tuning Advisor :STA) , it is the new Dbms_sqltune package.

Use the STA to make sure that the optimizer is in CBO mode. but I think the use of such tools, only for the people who do not understand the tuning of SQL, do not feel that the tool can solve the problem.

SQL says exactly what is expressed is a business, how the tool can understand the business. SQL tuning still needs to be diagnosed with autotrace,10046,10053,display_cursor these excellent tools. It is then best to tune based on the knowledge of the business and the Oracle Foundation that is available. Playing this tool here today is just ensemble.

1. Create the data. Useful do not build indexes and then do queries

Sql> CREATE TABLE Test1 as SELECT * from Dba_objects;
Sql> CREATE TABLE Test2 as SELECT * from Dba_objects;
sql> exec dbms_stats.gather_table_stats (user, ' test1 ');
sql> exec dbms_stats.gather_table_stats (user, ' test2 ');
Sql> Set Timing on
Sql> Set Autotrace traceonly
Sql> Select COUNT (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id;
Time used: 00:00:00.07
Run a plan
----------------------------------------------------------
Plan Hash value:2544416891
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |   10 | 298 (1) | 00:00:04 |
|  1 |       SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |       HASH JOIN | |   50981 |   497k| 298 (1) | 00:00:04 |
|    3 | TABLE ACCESS full| TEST1 |   50982 |   248k| 149 (1) | 00:00:02 |
|    4 | TABLE ACCESS full| TEST2 |   50983 |   248k| 149 (1) | 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
1410 consistent gets
0 physical Reads
0 Redo Size
410 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
Sql> set Autotrace off


2. Create an optimization task by dbms_sqltune the create_tuning_task of the package. And then through Dbms_sqltune. Execute_tuning_task to run tuning tasks and generate tuning recommendations.
Sql> DECLARE
My_task_name VARCHAR2 (300);
My_sqltext CLOB;
BEGIN
My_sqltext: = ' SELECT count (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id ';
My_task_name: = Dbms_sqltune. Create_tuning_task (
Sql_text = My_sqltext,
user_name = ' TEST ',--must be capitalized
Scope = ' comprehensive ',
Time_limit = 10,
Task_name = ' Tuning_sql_test ',
Description = ' Task to tune a query on a specified table ');
Dbms_sqltune. Execute_tuning_task (task_name = ' tuning_sql_test ');
END;
/

Official document parsing:
Sql_text the text of a SQL statement
User_name the username for whom the statement are to be tuned
Scope Tuning scope (limited/comprehensive)
Time_limit the maximum duration in seconds for the tuning session
Task_name an optional tuning task name
Description A task of the SQL tuning session to A maximum of characters

3. Check the status of the optimization task
Sql> select Task_name,advisor_name,status from User_advisor_tasks;
Task_name Advisor_name STATUS
------------------------------ ------------------------------ -----------
Tuning_sql_test SQL Tuning Advisor completed

4. View Optimization Results
Sql> Set Longchunksize 999999
Sql> set serveroutput on size 999999
Sql> Set Long 999999
Sql> Select Dbms_sqltune.report_tuning_task (' Tuning_sql_test ') from dual;
Dbms_sqltune. Report_tuning_task (' Tuning_sql_test ')
------------------------------------------------------------------------------------------------
General Information Sections
-------------------------------------------------------------------------------
Tuning Task Name:tuning_sql_test
Tuning Task Owner:test
Scope:comprehensive
Time Limit (seconds): 10
Completion status:completed
Started at:05/23/2014 08:50:40
Completed at:05/23/2014 08:50:41
Number of Index findings:1
-------------------------------------------------------------------------------
Schema Name:test
SQL Id:afjq3us3nf5dt
SQL text:select Count (*) from test1 T1, test2 t2 where t1.object_id =
t2.object_id
-------------------------------------------------------------------------------
Findings section (1 finding)
-------------------------------------------------------------------------------
1-index finding (see Explain plans section below)
--------------------------------------------------
By creating one or more indexes, you can
Recommendation (estimated benefit:100%)
----------------------------------------
-Consider implementing an Access Advi that improves the design of the physical scheme
CREATE INDEX TEST. idx$$_0c890001 on TEST. TEST1 (' object_id ');

-Consider implementing an Access Advi that improves the design of the physical scheme
CREATE INDEX TEST. idx$$_0c890002 on TEST. TEST2 (' object_id ');

Rationale
---------
Creating a recommended index can significantly improve the run schedule for this statement.

However, using a typical
may be preferable than a single statement. This way, you can get a comprehensive index
-------------------------------------------------------------------------------
EXPLAIN Plans Section
-------------------------------------------------------------------------------
1-original
-----------
Plan Hash value:2544416891
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |   10 | 298 (1) | 00:00:04 |
|  1 |       SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |       HASH JOIN | |   50981 |   497k| 298 (1) | 00:00:04 |
|    3 | TABLE ACCESS full| TEST1 |   50982 |   248k| 149 (1) | 00:00:02 |
|    4 | TABLE ACCESS full| TEST2 |   50983 |   248k| 149 (1) | 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")

2-using New Indices
--------------------
Plan Hash value:3060659111
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 |                SELECT STATEMENT |     |    1 |    10 | 53 (2) | 00:00:01 |
|  1 |                SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |                HASH JOIN | |   50981 |    497k| 53 (2) | 00:00:01 |
|    3 | INDEX FAST Full scan| idx$$_0c890001 |   50982 |    248k| 26 (0) | 00:00:01 |
|    4 | INDEX FAST Full scan| idx$$_0c890002 |   50983 |    248k| 26 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
-------------------------------------------------------------------------------

5. Verify

tuning based on Advisor recommendations, 1410 to 204, it's a bit of a effect.

Sql>create index ind_t1_object_id on test1 (object_id);

Sql> CREATE index ind_t2_object_id on test2 (object_id);

Sql> Set Autotrace traceonly
Sql> Select COUNT (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id;
Time used: 00:00:00.06
Run a plan
----------------------------------------------------------
Plan Hash value:1069114244
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 |                  SELECT STATEMENT |     |    1 |    10 | 51 (2) | 00:00:01 |
|  1 |                  SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |                  HASH JOIN | |   50981 |    497k| 51 (2) | 00:00:01 |
|    3 | INDEX FAST Full scan| ind_t1_object_id |   50982 |    248k| 25 (0) | 00:00:01 |
|    4 | INDEX FAST Full scan| ind_t2_object_id |   50983 |    248k| 25 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
Consistent gets
226 physical Reads
0 Redo Size
410 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

6. Delete the optimization task
sql> exec dbms_sqltune.drop_tuning_task (' tuning_sql_test ');

First Use SQL Tuning recommendation Tool--sql Tuning Advisor