在10g中，Oracle推出了自己的SQL最佳化協助工具輔助: SQL最佳化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保證最佳化器是CBO模式下。但是我認為使用這種工具，僅適合完全不懂SQL的調優的人群，不要認為工具能解決好問題。SQL說到底是表達的是一個業務，工具怎麼可能理解業務。SQL調優還是要用autotrace,10046,10053,display_cursor這些優秀的工具做診斷，然後根據業務和所具備的oracle基礎的知識進行調優，這是最好的方法。今天在這裡玩這個工具只是玩票。
1.建立資料，故意不建索引，然後做查詢
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;
經過時間: 00: 00: 00.07
執行計畫
----------------------------------------------------------
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")
統計資訊
----------------------------------------------------------
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.通過DBMS_SQLTUNE包的CREATE_TUNING_TASK來建立一個最佳化任務，然後通過DBMS_SQLTUNE.EXECUTE_TUNING_TASK來執行調優任務，產生調優建議。
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', -- 必須大寫
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;
/
官方文檔解析：
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.檢查最佳化任務的狀態
SQL> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;
TASK_NAME ADVISOR_NAME STATUS
------------------------------ ------------------------------ -----------
tuning_sql_test SQL Tuning Advisor COMPLETED
4.查看最佳化結果
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 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)
--------------------------------------------------
通過建立一個或多個索引可以
Recommendation (estimated benefit: 100%)
----------------------------------------
-考慮運行可以改進物理方案設計的 Access Advi
create index TEST.IDX$$_0C890001 on TEST.TEST1('OBJECT_ID');
-考慮運行可以改進物理方案設計的 Access Advi
create index TEST.IDX$$_0C890002 on TEST.TEST2('OBJECT_ID');
Rationale
---------
建立推薦的索引可以顯著地改進此語句的執行計畫。但是, 使用典型的
可能比單個語句更可取。通過這種方法可以獲得全面的索引建
-------------------------------------------------------------------------------
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.驗證一下
根據advisor的建議進行調優，1410降到204，是乎有點效果。
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;
經過時間: 00: 00: 00.06
執行計畫
----------------------------------------------------------
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")
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
240 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.刪除最佳化任務
SQL> exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
