In 10 Gb, Oracle launched its own auxiliary SQL optimization tool: SQL optimizer (SQL Tuning Advisor: STA), which is a new DBMS_SQLTUNE package. To use the STA, make sure that the optimizer is in CBO mode. However, I think this tool is only suitable for those who do not fully understand SQL optimization. In the end, SQL statements express a business. How can tools understand the business. For SQL optimization, it is best to use excellent tools such as autotrace, 10046, 10053, and display_cursor for diagnosis, and then perform optimization based on the business and the basic knowledge of oracle. Playing this tool is just a ticket today.
1. Create data, deliberately do not create an index, and then perform a 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;
Used time: 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 ")
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1410 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL * Net to client
385 bytes encoded ed 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 using CREATE_TUNING_TASK of the DBMS_SQLTUNE package, and execute the optimization task using DBMS_SQLTUNE.EXECUTE_TUNING_TASK to generate optimization suggestions.
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', -- uppercase is required
Scope => 'computing ',
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 analysis:
SQL _text The text of a SQL statement
User_name The username for whom the statement is 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 256 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 ')
Bytes ------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
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)
--------------------------------------------------
You can create one or more Indexes
Recommendation (estimated benefit: 100%)
----------------------------------------
-Consider the Access Advi that can improve the physical solution design
Create index TEST. IDX $ _ 0C890001 on TEST. TEST1 ('object _ id ');
-Consider the Access Advi that can improve the physical solution design
Create index TEST. IDX $ _ 0C890002 on TEST. TEST2 ('object _ id ');
Rationale
---------
Creating recommended indexes can significantly improve the execution plan of this statement. However, the typical
It may be more desirable than a single statement. Using this method, you can obtain comprehensive index creation
-------------------------------------------------------------------------------
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
Bytes -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")
-------------------------------------------------------------------------------
5. Verify
Tuning Based on advisor recommendations, with 1410 down to 204, is almost a bit effective.
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;
Used time: 00: 00: 00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1069114244
Bytes -------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
240 consistent gets
226 physical reads
0 redo size
410 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6. Delete optimization tasks
SQL> exec dbms_sqltune.drop_tuning_task ('tuning _ SQL _test ');
