sql tuning advisor(STA) 建議 建立sql profile
sql tuning advisor(STA)，是Oracle 10g起 提供的SQL自動最佳化建議工具。sql profile是用來穩定穩定（或者說固化）sql的執行計劃的工具。它是對outline功能的增強。
今天帶來一個 STA 推薦使用 sql profile 來最佳化sql的例子。
環境準備：
建立測試表
蒐集統計資訊
執行SQL，最佳化器選擇了 Hash join。估計的成本 是611， 邏輯讀是2192。
用HINT 強制做 Nested Loop方式。成本估算為7956 （大於Hash Join 的611，難怪沒走 NL方式）。但是實際上，邏輯讀1187（比 HJ的 2192 的少）。說明，最佳化器估算錯誤。這裡是因為，t1.object_name like '%T1%'，Oracle對like的估算是 按照表行數的 5%。這裡過高的估計了，t1表返回的記錄數，也就過高估計巢狀的次數。
處理方法，用sql tuning advisor(STA)來搞定它。
用sys使用者來查一下 帶最佳化sql的 id
sys使用者呼叫STA
檢視最佳化成果
安裝STA建議，來建立sql profile
檢查測試結果。安裝sql profile固化的執行計劃來執行。而且，現在的成本估算比較客觀（417， 低於 Hash join的 611）
新的問題： 如果，替換like後面的字串，sql profile就失效了。換成 t1.object_name like '%T2%'後，有走Hash join了
新問題的處理的方法。sys使用者重新建立sql profile，force_match=>true強制匹配
處理過後
今天帶來一個 STA 推薦使用 sql profile 來最佳化sql的例子。
環境準備：
建立測試表
點選(此處)摺疊或開啟
-
scott@ORCL>create table t1 as select * from dba_objects;
-
-
Table created.
-
-
scott@ORCL>create table t2 as select * from dba_objects;
-
-
Table created.
-
-
scott@ORCL>create index ind_t2 on t2(object_id);
-
- Index created.
點選(此處)摺疊或開啟
-
scott@ORCL> exec dbms_stats.gather_table_stats(user,\'t1\',cascade=>true,method_opt=>\'for all indexed columns size auto\');
-
-
PL/SQL procedure successfully completed.
-
-
scott@ORCL>exec dbms_stats.gather_table_stats(user,\'t2\',cascade=>true,method_opt=>\'for all indexed columns size auto\');
-
- PL/SQL procedure successfully completed.
點選(此處)摺疊或開啟
-
scott@ORCL>set autotrace traceonly
-
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;
-
-
51 rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1838229974
-
-
---------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
-
|* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
-
|* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
-
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
-
---------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
-
NOT NULL)
-
-
-
Statistics
-
----------------------------------------------------------
-
1 recursive calls
-
0 db block gets
-
2192 consistent gets
-
0 physical reads
-
0 redo size
-
5305 bytes sent via SQL*Net to client
-
556 bytes received via SQL*Net from client
-
5 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
- 51 rows processed
點選(此處)摺疊或開啟
-
scott@ORCL>select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
-
2 from t1,t2
-
3 where t1.object_name like \'%T1%\'
-
4 and t1.object_id=t2.object_id;
-
-
51 rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1386590592
-
-
---------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 3824 | 407K| 7956 (1)| 00:01:36 |
-
| 1 | NESTED LOOPS | | | | | |
-
| 2 | NESTED LOOPS | | 3824 | 407K| 7956 (1)| 00:01:36 |
-
|* 3 | TABLE ACCESS FULL | T1 | 3824 | 365K| 305 (1)| 00:00:04 |
-
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
-
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
-
---------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
-
NULL)
-
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
-
-
Statistics
-
----------------------------------------------------------
-
1 recursive calls
-
0 db block gets
-
1187 consistent gets
-
0 physical reads
-
0 redo size
-
5305 bytes sent via SQL*Net to client
-
556 bytes received via SQL*Net from client
-
5 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
- 51 rows processed
用sys使用者來查一下 帶最佳化sql的 id
點選(此處)摺疊或開啟
-
sys@ORCL>select sql_id, sql_text from v$sql where sql_text like \'select t1.*,t2.owner%\';
-
-
SQL_ID
-
-------------
-
SQL_TEXT
-
-----------------------------------------------------------------------------------------------
-
4zbqykx89yc8v
- select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id
sys使用者呼叫STA
點選(此處)摺疊或開啟
-
var tuning_task varchar2(100); -- 建立最佳化任務時，返回的任務名 用繫結變數儲存
-
-
BEGIN
-
:tuning_task := dbms_sqltune.create_tuning_task(sql_id => \'4zbqykx89yc8v\'); -- 建立最佳化任務，輸入待最佳化sql的id
-
dbms_sqltune.execute_tuning_task(:tuning_task); -- 執行最佳化任務
-
dbms_output.put_line(:tuning_task);
-
END;
- /
檢視最佳化成果
點選(此處)摺疊或開啟
-
sys@ORCL>SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;
-
-
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
-
--------------------------------------------------------------------------------
-
GENERAL INFORMATION SECTION
-
-------------------------------------------------------------------------------
-
Tuning Task Name : TASK_567
-
Tuning Task Owner : SYS
-
Workload Type : Single SQL Statement
-
Scope : COMPREHENSIVE
-
Time Limit(seconds): 1800
-
Completion Status : COMPLETED
-
Started at : 06/03/2014 02:43:02
-
Completed at : 06/03/2014 02:43:06
-
-
-------------------------------------------------------------------------------
-
Schema Name: SCOTT
-
SQL ID : 4zbqykx89yc8v
-
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\'
-
and t1.object_id=t2.object_id
-
-
-------------------------------------------------------------------------------
-
FINDINGS SECTION (1 finding)
-
-------------------------------------------------------------------------------
-
-
1- SQL Profile Finding (see explain plans section below)
-
--------------------------------------------------------
-
A potentially better execution plan was found for this statement.
-
-
Recommendation (estimated benefit: 46.23%) -- STA給出的建議 建立 sql profile
-
------------------------------------------
-
- Consider accepting the recommended SQL profile.
-
execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\',
-
task_owner => \'SYS\', replace => TRUE);
-
-
Validation results
-
------------------
-
The SQL profile was tested by executing both its plan and the original plan
-
and measuring their respective execution statistics. A plan may have been
-
only partially executed if the other could be run to completion in less time.
-
-
Original Plan With SQL Profile % Improved
-
------------- ---------------- ----------
-
Completion Status: COMPLETE COMPLETE
-
Elapsed Time (s): .069781 .061671 11.62 %
-
CPU Time (s): .069689 .06179 11.33 %
-
User I/O Time (s): 0 0
-
Buffer Gets: 2188 1174 46.34 %
-
Physical Read Requests: 0 0
-
Physical Write Requests: 0 0
-
Physical Read Bytes: 0 0
-
Physical Write Bytes: 0 0
-
Rows Processed: 51 51
-
Fetches: 51 51
-
Executions: 1 1
-
-
Notes
-
-----
-
1. Statistics for the original plan were averaged over 10 executions.
-
2. Statistics for the SQL profile plan were averaged over 10 executions.
-
-
-------------------------------------------------------------------------------
-
EXPLAIN PLANS SECTION
-
-------------------------------------------------------------------------------
-
-
1- Original With Adjusted Cost
-
------------------------------
-
Plan hash value: 1838229974
-
-
---------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 56 | 6104 | 611 (1)| 00:00:08 |
-
|* 1 | HASH JOIN | | 56 | 6104 | 611 (1)| 00:00:08 |
-
|* 2 | TABLE ACCESS FULL| T1 | 56 | 5488 | 305 (1)| 00:00:04 |
-
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
-
---------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
-
NOT NULL)
-
-
2- Using SQL Profile -- sql profile 固化的執行計劃 走NL
-
--------------------
-
Plan hash value: 1386590592
-
- --------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
-
e |
- --------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:
-
00:06 |
-
| 1 | NESTED LOOPS | | | | |
-
|
-
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:
-
00:06 |
-
-
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
-
--------------------------------------------------------------------------------
-
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:
-
00:04 |
-
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:
-
00:01 |
-
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:
-
00:01 |
- --------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
-
NULL)
-
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
點選(此處)摺疊或開啟
-
sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\', task_owner => \'SYS\', replace => TRUE);
-
- PL/SQL procedure successfully completed.
檢查測試結果。安裝sql profile固化的執行計劃來執行。而且，現在的成本估算比較客觀（417， 低於 Hash join的 611）
點選(此處)摺疊或開啟
-
scott@ORCL>set autotrace traceonly
-
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;
-
-
51 rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1386590592
-
-
---------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
-
| 1 | NESTED LOOPS | | | | | |
-
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
-
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
-
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
-
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
-
---------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
-
NULL)
-
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
-
Note
-
-----
-
- SQL profile \"SYS_SQLPROF_01466078fe610000\" used for this statement -- SQL profile 生效了，按照固化的執行計劃走
-
-
-
Statistics
-
----------------------------------------------------------
-
35 recursive calls
-
0 db block gets
-
1198 consistent gets
-
1 physical reads
-
0 redo size
-
5305 bytes sent via SQL*Net to client
-
556 bytes received via SQL*Net from client
-
5 SQL*Net roundtrips to/from client
-
1 sorts (memory)
-
0 sorts (disk)
- 51 rows processed
新的問題： 如果，替換like後面的字串，sql profile就失效了。換成 t1.object_name like '%T2%'後，有走Hash join了
點選(此處)摺疊或開啟
-
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;
-
-
124 rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1838229974
-
-
---------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
-
|* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
-
|* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
-
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
-
---------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS
-
NOT NULL)
-
-
-
Statistics
-
----------------------------------------------------------
-
1 recursive calls
-
0 db block gets
-
2197 consistent gets
-
0 physical reads
-
0 redo size
-
9356 bytes sent via SQL*Net to client
-
611 bytes received via SQL*Net from client
-
10 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
- 124 rows processed
新問題的處理的方法。sys使用者重新建立sql profile，force_match=>true強制匹配
點選(此處)摺疊或開啟
-
sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name =>\'TASK_567\', task_owner=>\'SYS\', replace=>TRUE,force_match=>true);
-
- PL/SQL procedure successfully completed.
處理過後
點選(此處)摺疊或開啟
-
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;
-
-
124 rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1386590592
-
-
---------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
-
| 1 | NESTED LOOPS | | | | | |
-
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
-
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
-
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
-
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
-
---------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
-
NULL)
-
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
-
-
Note
-
-----
-
- SQL profile \"SYS_SQLPROF_0146607cb3e40001\" used for this statement -- 這裡 SQL profile又生效了
-
-
-
Statistics
-
----------------------------------------------------------
-
6 recursive calls
-
0 db block gets
-
1253 consistent gets
-
1 physical reads
-
0 redo size
-
9356 bytes sent via SQL*Net to client
-
611 bytes received via SQL*Net from client
-
10 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
- 124 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12238525/viewspace-1174894/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Oracle SQL最佳化之sql tuning advisor(STA)OracleSQL
- 在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQLSQL優化
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- sql tuning task和sql profileSQL
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- SQL TUNING ADVISORSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- sql tuning advisor和sql access advisor區別SQL
- 手工執行sql tuning advisor和sql access advisorSQL
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle實用sql(3)--sql tuning advisorOracleSQL
- SQL Tuning Advisor簡介SQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- 在EM中使用SQL Access Advisor（SQL訪問建議）優化SQLSQL優化
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Guideline of SQL Tuning AdvisorGUIIDESQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Sql Tuning Advisor的大致過程測試！SQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- sql profileSQL
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- 【kingsql分享】Oracle 10G強大的SQL優化工具：SQL Tuning AdvisorSQLOracle 10g優化
- sql tuningSQL
- sql profile使用SQL
- Oracle OCP 1Z0 053 Q253(SQL Tuning Advisor)OracleSQL
- Oracle OCP IZ0-053 Q46(SQL Tuning Advisor Limited)OracleSQLMIT
- Automatic SQL Tuning and SQL ProfilesSQL