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 that using this tool is only suitable for people who don't understand SQL at all, and don't think that tools can solve problems. SQL is ultimately an expression of a business, how the tool might understand the business. SQL tuning is the best way to diagnose with autotrace,10046,10053,display_cursor these excellent tools and then tune based on the knowledge of the business and the Oracle Foundation that you have. playing this tool here today is just a ensemble.
1. Create the data, deliberately do not build the index, and then do the query
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
Execution 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 through the create_tuning_task of the Dbms_sqltune package, and then through the Dbms_sqltune. Execute_tuning_task to perform 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 running Access advi that can improve the physical schema design
CREATE INDEX TEST. idx$$_0c890001 on TEST. TEST1 (' object_id ');
-Consider running Access advi that can improve the physical schema design
CREATE INDEX TEST. idx$$_0c890002 on TEST. TEST2 (' object_id ');
Rationale
---------
Creating a recommended index can significantly improve the execution plan for this statement. However, using a typical
may be preferable than a single statement. This approach allows for comprehensive indexing
-------------------------------------------------------------------------------
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 According to 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
Execution 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 ');
