Oracle10g新特性：使用DBMS_SQLTUNE最佳化SQL
發表於: 2007.04.30 10:51
分類: Oracle
出處: http://ningoo.itpub.net/post/2149/284636
---------------------------------------------------------------[@more@]
在Oracle10g之前，最佳化SQL是個比較費力的技術活，不停的分析執行計劃，加hint，分析統計資訊等等，當然也有SQL調優輔助工具可以使用，只是要麼價格昂貴，要麼效果不佳。在10g中，Oracle推出了自己的SQL最佳化輔助工具，這就是新的DBMS_SQLTUNE包。執行DBMS_SQLTUNE包進行sql最佳化需要有advisor的許可權：
advisor to ning;
Grant succeeded.
使用DBMS_SQLTUNE包進行SQL最佳化，大致可以分為四個步驟：
建立最佳化任務
執行最佳化任務
顯示最佳化任務的結果
按照建議執行最佳化
一、建立最佳化任務
使用dbms_sqltune.create_tuning_task函式來建立最佳化任務，該引數的宣告如下：
程式碼:--------------------------------------------------------------------------------
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
---------------------- ------------- ------ --------
SQLSET_NAME VARCHAR2 IN
BASIC_FILTER VARCHAR2 IN DEFAULT
OBJECT_FILTER VARCHAR2 IN DEFAULT
RANK1 VARCHAR2 IN DEFAULT
RANK2 VARCHAR2 IN DEFAULT
RANK3 VARCHAR2 IN DEFAULT
RESULT_PERCENTAGE NUMBER IN DEFAULT
RESULT_LIMIT NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
PLAN_FILTER VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
--------------------------------------------------------------------------------
具體每個引數的含義請參考Oracle官方文件的說明。函式的返回值為建立的任務名。下面我們建立一個叫sql_tuning_test的任務：
程式碼:--------------------------------------------------------------------------------
2 my_task_name VARCHAR2 (30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'SELECT e.last_name, d.department_name, d.department_id
6 FROM employees e, departments d
7 WHERE e.department_id = d.department_id
8 AND d.department_id = :bnd';
9 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
10 bind_list => sql_binds (anydata.convertnumber (9)),
11 user_name => 'NING',
12 scope => 'COMPREHENSIVE',
13 time_limit => 60,
14 task_name => 'sql_tuning_test',
15 description => 'Tuning Task'
16 );
17 END;
18 /
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
二、執行最佳化任務
透過呼叫dbms_sqltune.execute_tuning_task過程來執行前面建立好的最佳化任務。
程式碼:--------------------------------------------------------------------------------
PROCEDURE EXECUTE_TUNING_TASK
Argument Name Type In/Out Default?
----------------- ------------- ------ --------
TASK_NAME VARCHAR2 IN
dbms_sqltune.execute_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
三、檢查最佳化任務的狀態
透過檢視user_advisor_tasks/dba_advisor_tasks檢視可以檢視最佳化任務的當前狀態。
程式碼:--------------------------------------------------------------------------------
status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test';
STATUS
-----------
COMPLETED
--------------------------------------------------------------------------------
四、得到最佳化任務執行的結果
透過dbms_sqltune.report_tning_task函式可以獲得最佳化任務的結果。
程式碼:--------------------------------------------------------------------------------
LONG 999999
serveroutput on size 999999
LINESIZE 100
DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_test
Tuning Task Owner : NING
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 04/30/2007 10:42:48
Completed at : 04/30/2007 10:42:49
Number of Statistic Findings : 2
Number of SQL Profile Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: NING
SQL ID : 6rbh5v8smjpdq
SQL Text : SELECT e.last_name, d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = :bnd
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "NING"."DEPARTMENTS" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'NING', tabname =>
'DEPARTMENTS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Table "NING"."EMPLOYEES" was not analyzed.
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'NING', tabname =>
'EMPLOYEES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_test',
replace => TRUE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2052257371
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 22 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 26 | 22 (5)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 7 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 1 | 19 | 18 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - filter("D"."DEPARTMENT_ID"=:BND)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
3 - filter("E"."DEPARTMENT_ID"=:BND)
2- Using SQL Profile
--------------------
Plan hash value: 1782137809
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 22 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 26 | 22 (5)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 19 | 18 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 7 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - filter("E"."DEPARTMENT_ID"=:BND)
3 - filter("D"."DEPARTMENT_ID"=:BND)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------
然後我們就可以根據Recommendation部分的建議來執行最佳化操作了。
五、刪除最佳化任務
透過呼叫dbms_sqltuen.drop_tuning_task可以刪除已經存在的最佳化任務
程式碼:--------------------------------------------------------------------------------
dbms_sqltune.drop_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
(需要引用, 請註明出處: http://ningoo.itpub.net)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1017065/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用dbms_sqltune進行SQL優化SQL優化
- 使用dbms_sqltune獲得SQL調整建議SQL
- 使用dbms_sqltune調優sql的步驟SQL
- Oracle12c中SQL最佳化（SQL TUNING）新特性之SQL計劃指令OracleSQL
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- (轉)oracle10g新特性之 flashbackOracle
- oracle10g新特性——物化檢視Oracle
- DBMS_SQLTUNE優化SQLSQL優化
- 【10g SQL新特性】q-quote使用SQL
- ZT oracle10g新特性——物化檢視Oracle
- Oracle10g新特性——RMAN(轉來的)Oracle
- 轉載：Oracle10g新特性——審計Oracle
- Oracle10g新特性之stream流配置Oracle
- Oracle 11g 中SQL效能最佳化新特性之SQL效能分析器（SQLPA）OracleSQL
- 利用DBMS_SQLTUNE優化SQLSQL優化
- Oracle12c中SQL最佳化新特性之自動重最佳化（automatic reoptimization）OracleSQL
- oracle10G新特性之ASM的應用OracleASM
- Oracle10g新特性:Recycle Bin回收站Oracle
- Oracle10g新特性——RMAN(轉來的)——2Oracle
- SQL調優工具包DBMS_SQLTUNE的使用方法SQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- ORACLE10g新特性——全域性HASH分割槽索引Oracle索引
- oracle11g中SQL最佳化新特性之Adaptive Cursor Sharing (ACS)OracleSQLAPT
- DBMS_SQLTUNE使用方法SQL
- dbms_sqltune包的使用SQL
- SQL優化----dbms_sqltune詳解(1)SQL優化
- DBMS_SQLTUNE詳解(2)---sql_profileSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- 23C新特性SQL防火牆 （SQL Firewall）SQL防火牆
- 掌握SQL Monitor這些特性，SQL最佳化將如有神助！SQL
- Oracle10g新特性——利用RMAN遷移表空間Oracle
- oracle10G新特性之段顧問的應用Oracle
- SQL Query Result Cache的使用和配置--Oracle 11G新特性SQLOracle
- oracle10G新特性之資料泵匯出/匯入Oracle
- Oracle10g新特性——利用外部表解除安裝資料Oracle
- Oracle10g新特性——利用RMAN遷移表空間（二）Oracle
- Oracle10g新特性——利用RMAN遷移表空間（一）Oracle
- Oracle10g新特性——記錄DML錯誤日誌（三）Oracle