【SPM】Oracle如何固定執行計劃
【SPM】Oracle如何固定執行計劃
1.1 BLOG文件結構圖
1.2 前言部分
1.2.1 導讀和注意事項
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 固定執行計劃的常用方法：outline、SQL Profile、SPM（重點）
② coe_xfr_sql_profile.sql指令碼的使用
Tips：
① 若文章程式碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址： （提取碼：ed9b）
② 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
1.2.2 相關參考文章連結
11.2.0.2的SPM的一個bug ：http://blog.itpub.net/26736162/viewspace-1248506/
在10g/11g中如何檢視SQL Profiles資訊：http://blog.itpub.net/26736162/viewspace-2106743/
【OUTLINE】使用Oracle Outline技術暫時鎖定SQL的執行計劃：http://blog.itpub.net/26736162/viewspace-2102180/
1.2.3 本文簡介
本文介紹了oracle在固定執行計劃的過程中常使用的3種方法，outline,SQL Profile和SPM,其中SQL Profile和SPM是重點需要掌握的內容。
---------------------------------------------------------------------------------------------------------------------
第二章 固定執行計劃的三種方法介紹
2.1 outline
2.1.1 outline基礎知識
在實際專案中，通常在開發環境下一些SQL 執行沒有任何問題，而到了生產環境或生產環境的資料量發生較大的變數時，其SQL 的執行效率會異常的慢。此時如果更改SQL ，則可能需要重新修改源程式以及重新編譯程式。如果覺得修改源程式的成本比較大，則可以使用OUTLINE在不改變原應用程式的情況下更改特定SQL 的執行計劃。
OUTLINE的原理是將調好的SQL 的執行計劃(一系列的HINT)存貯起來，然後該執行計劃所對應的SQL 用目前系統那個效率低下的SQL 來替代之。從而使得系統每次執行該SQL 時，都會使用已存貯的執行計劃來執行。因此可以在不改變已有系統SQL 的情況下達到改變其執行計劃的目的。
OUTLINE方式也是透過存貯HINT的方式來達到執行計劃的穩定與改變。
當發現低效SQL之後，可以使用hint最佳化他，對於SQL程式碼可以修改的情況，直接修改SQL程式碼加上hint即可，但是對於SQL程式碼不可修改的情況，Oracle提供了outLine功能來為SQL修改hint，以致執行計劃變更！
?OutLine機制：
Outline儲存了SQL的hint在outline的表中。當執行SQL時，Oracle會使用outline中的hint來為SQL生成執行計劃。
?使用 OutLine的步驟：
（1）生成新SQL和老SQL的2個Outline
（2）交換兩個SQL的提示資訊
（3） ON LOGON觸發器設定session的CATEGORY（自定義類別）
SQL命令列為：SQL> alter session set use_stored_outlines=special;
2.1.2 ouline使用演示
測試過程如下：
SYS@test> create user lhr identified by lhr;
User created.
SYS@test> grant dba to lhr;
Grant succeeded.
SYS@test> grant create any outline,alter any outline,DROP ANY OUTLINE to lhr;
Grant succeeded.
SYS@test> grant all on OL$HINTS to lhr;
Grant succeeded.
SYS@test> conn lhr/lhr
Connected.
LHR@test> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@test> create table TB_LHR_20160518 as select * from dba_tables;
Table created.
LHR@test> create index idx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME);
Index created.
LHR@test> SET AUTOTRACE ON;
LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2186742855
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
72 consistent gets
8 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1750418716
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 31 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 1 | 34 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> set autotrace off;
LHR@test> create or replace outline TB_LHR_20160518_1 on select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
Outline created.
LHR@test> create or replace outline TB_LHR_20160518_2 on select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
Outline created.
LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
NAME USED SQL_TEXT
------------------------------ ------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
TB_LHR_20160518_2 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%';
NAME HINT
------------------------------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
LHR@test> UPDATE OUTLN.OL$ SET OL_NAME=DECODE(OL_NAME,'TB_LHR_20160518_2','TB_LHR_20160518_1','TB_LHR_20160518_1','TB_LHR_20160518_2') WHERE OL_NAME IN ('TB_LHR_20160518_1','TB_LHR_20160518_2');
2 rows updated.
LHR@test> commit;
Commit complete.
LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
NAME USED SQL_TEXT
------------------------------ ------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
TB_LHR_20160518_2 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%';
NAME HINT
------------------------------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
LHR@test> SET AUTOTRACE ON;
LHR@test> alter system set use_stored_outlines=true;
System altered.
LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1750418716
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89 | 3026 | 31 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 89 | 3026 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- outline "TB_LHR_20160518_2" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
125 consistent gets
0 physical reads
624 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2186742855
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89 | 3026 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 89 | 3026 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 36 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- outline "TB_LHR_20160518_1" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
24 consistent gets
0 physical reads
584 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test>
2.2 SQL Profile
2.2.1 SQL Profile基礎知識
在oracle 11g的後續版本中，use_stored_outlines這個引數已經不存在了。意味著我們不能像以前的版本中使用create outline的方式來為一個sql建立hint，然後使用store outline來固定執行計劃這種方式了.
SQL Profile就是為某一SQL語句提供除了系統統計資訊、物件（表和索引等）統計資訊之外的其他資訊，比如執行環境、額外的更準確的統計資訊，以幫助最佳化器為SQL語句選擇更適合的執行計劃。SQL Profiles可以說是Outlines的進化。Outlines能夠實現的功能SQL Profiles也完全能夠實現，而SQL Profiles具有Outlines不具備的最佳化，最重要的有二點：
① SQL Profiles更容易生成、更改和控制。
② SQL Profiles在對SQL語句的支援上做得更好，也就是適用範圍更廣。
使用SQL Profiles兩個目的：
（一） 鎖定或者說是穩定執行計劃。
（二） 在不能修改應用中的SQL的情況下使SQL語句按指定的執行計劃執行。
10g之前有outlines,10g之後sql profile作為新特性之一出現。如果針對非繫結變數的sql,outlines則力不從心。sql profile最大的優點是在不修改sql語句和會話執行環境的情況下去最佳化sql的執行效率,適合無法在應用程式中修改sql時.
SQL Profile對以下型別語句有效：
SELECT語句；
UPDATE語句；
INSERT語句（僅當使用SELECT子句時有效）；
DELETE語句；
CREATE語句（僅當使用SELECT子句時有效）；
MERGE語句（僅當作UPDATE和INSERT操作時有效）。
另外，使用SQL Profile還必須有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系統許可權。
2.2.2 SQL Profile使用演示
有2種生成SQL Profile的方法，手動和採用STA來生成。
2.2.2.1 SQL Profile使用示例--手工建立SQL Profile
建立測試表，根據DBA_OBJECTS建立，OBJECT_ID上有索引
LHR@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> Create table TB_LHR_20160525 as select * from dba_objects;
Table created.
LHR@dlhr> create index IND_TB_LHR_ID on TB_LHR_20160525(object_id);
Index created.
檢視SQL預設執行計劃,走了索引，透過指定outline可以獲取到系統為我們生成的hint
LHR@dlhr> explain plan for select * from TB_LHR_20160525 where object_id= :a;
Explained.
LHR@dlhr> select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4254050152
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 886 | 179K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160525 | 886 | 179K| 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TB_LHR_ID | 354 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160525"@"SEL$1" ("TB_LHR_20160525"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
如果我們想讓它走全表掃描，首先獲取全表掃描HINT
LHR@dlhr> explain plan for select /*+ full(TB_LHR_20160525) */* from TB_LHR_20160525 where object_id= :a;
Explained.
LHR@dlhr> select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 345881005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 886 | 179K| 352 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160525 | 886 | 179K| 352 (2)| 00:00:05 |
-------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.
可以看到全表掃描的hint已經為我們生成了，我們選取必要的hint就OK了，其他的可以不要，使用sql profile
LHR@dlhr> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1")'); ----------從上面Outline Data部分獲取到的HINT
5 dbms_sqltune.import_sql_profile('select * from TB_LHR_20160525 where object_id= :a', ----------SQL語句部分
6 v_hints,
7 'TB_LHR_20160525', --------PROFILE 的名字
8 force_match => true);
9 end;
10 /
PL/SQL procedure successfully completed.
檢視是否生效，已經生效了：
LHR@dlhr> explain plan for select * from TB_LHR_20160525 where object_id= :a;
Explained.
LHR@dlhr> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 345881005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 886 | 179K| 352 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160525 | 886 | 179K| 352 (2)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:A))
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL profile "TB_LHR_20160525" used for this statement
18 rows selected.
LHR@dlhr> SELECT b.name,d.sql_text, extractvalue(value(h),'.') as hints
2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,
3 SYS.SQLOBJ$ B,
4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
5 '/outline_data/hint'))) h
6 where a.signature = b.signature
7 and a.category = b.category
8 and a.obj_type = b.obj_type
9 and a.plan_id = b.plan_id
10 and a.signature=d.signature
11 and D.name = 'TB_LHR_20160525';
NAME SQL_TEXT HINTS
------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------
TB_LHR_20160525 select * from TB_LHR_20160525 where object_id= :a FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1")
LHR@dlhr>
一、 使用coe_xfr_sql_profile.sql指令碼生成sqlprof_attr資料
最麻煩的sqlprof_attr('FULL(t1@SEL$1)')是這裡的格式如何寫.在mos上的文章note 215187.1中的sqlt.zip的目錄utl中提供了指令碼coe_xfr_sql_profile.sql可以生成這些資訊.
1.建立測試表和資料
SYS@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> create table scott.test as select * from dba_objects;
Table created.
LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);
PL/SQL procedure successfully completed.
LHR@dlhr> update scott.test set object_id=10 where object_id>10;
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID;
OBJECT_ID COUNT(1)
---------- ----------
6 1
7 1
5 1
8 1
3 1
2 1
10 87076
4 1
9 1
9 rows selected.
2.執行查詢語句
--執行原有的查詢語句,檢視執行計劃發現走索引,實際上這時表中大部分行的object_id都已經被更新為10,所以走索引是不合理的.
LHR@dlhr>
LHR@dlhr> set autot traceonly explain stat
LHR@dlhr>
LHR@dlhr> select * from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13060 consistent gets
0 physical reads
0 redo size
9855485 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6973 consistent gets
0 physical reads
0 redo size
4159482 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
3.查詢上面兩個語句的sql_id,plan_hash_value
LHR@dlhr> set autot off
LHR@dlhr>
LHR@dlhr> col sql_text format a100
LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql
2 where sql_text like 'select * from scott.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------------------------------------- ------------- ---------------
select * from scott.test where object_id=10 cpk9jsg2qt52r 3384190782
LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql
2 where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------------------------------------- ------------- ---------------
select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 217508114
4.把coe_xfr_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。
5.對上面的兩個sql產生outline data的sql.
[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp
[ZHLHRSPMDB2:oracle]:/tmp>
[ZHLHRSPMDB2:oracle]:/tmp>
[ZHLHRSPMDB2:oracle]:/tmp>
[ZHLHRSPMDB2:oracle]:/tmp>
[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@dlhr> @$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql cpk9jsg2qt52r 3384190782
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3384190782 .046
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "cpk9jsg2qt52r"
PLAN_HASH_VALUE: "3384190782"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
on TARGET system in order to create a custom SQL Profile
with plan 3384190782 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql 06c2mucgn6t5g 217508114
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
217508114 .113
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "06c2mucgn6t5g"
PLAN_HASH_VALUE: "217508114"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql
on TARGET system in order to create a custom SQL Profile
with plan 217508114 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
6.替換檔案coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql中的SYS.SQLPROF_ATTR部分,把它更改為
coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql中產生的SYS.SQLPROF_ATTR部分，其中：
coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql的SYS.SQLPROF_ATTR
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
----coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql的SYS.SQLPROF_ATTR
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "TEST"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
生成的檔案在當前目錄：
7.執行替換過SYS.SQLPROF_ATTR的SQL,coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
SQL> @/tmp/coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
SQL>@coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql 11.4.4.4 2016/05/26 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID cpk9jsg2qt52r based on plan hash
SQL>REM value 3384190782.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cpk9jsg2qt52r_3384190782');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select * from scott.test where object_id=10]');
15 DBMS_LOB.CLOSE(sql_txt);
16 h := SYS.SQLPROF_ATTR(
17 q'[BEGIN_OUTLINE_DATA]',
18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
20 q'[DB_VERSION('11.2.0.4')]',
21 q'[ALL_ROWS]',
22 q'[OUTLINE_LEAF(@"SEL$1")]',
23 q'[FULL(@"SEL$1" "TEST"@"SEL$1")]',
24 q'[END_OUTLINE_DATA]');
25 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
26 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
28 sql_text => sql_txt,
29 profile => h,
30 name => 'coe_cpk9jsg2qt52r_3384190782',
31 description => 'coe cpk9jsg2qt52r 3384190782 '||:signature||' '||:signaturef||'',
32 category => 'DEFAULT',
33 validate => TRUE,
34 replace => TRUE,
35 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
36 DBMS_LOB.FREETEMPORARY(sql_txt);
37 END;
38 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
10910590721604799112
SIGNATUREF
---------------------
15966118871002195466
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_cpk9jsg2qt52r_3384190782 completed
8.檢視產生的sql profile,此時原語句在不加hint的情況下也走全表掃了
select * from dba_sql_profiles;
SYS@dlhr> col sql_text for a50
SYS@dlhr> col hints for a50
SYS@dlhr> SELECT b.name,to_char(d.sql_text) sql_text, extractvalue(value(h),'.') as hints
2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,
3 SYS.SQLOBJ$ B,
4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
5 '/outline_data/hint'))) h
6 where a.signature = b.signature
7 and a.category = b.category
8 and a.obj_type = b.obj_type
9 and a.plan_id = b.plan_id
10 and a.signature=d.signature
11 and D.name = 'coe_cpk9jsg2qt52r_3384190782';
NAME SQL_TEXT HINTS
------------------------------ -------------------------------------------------- --------------------------------------------------
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 BEGIN_OUTLINE_DATA
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 DB_VERSION('11.2.0.4')
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 ALL_ROWS
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 OUTLINE_LEAF(@"SEL$1")
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1")
coe_cpk9jsg2qt52r_3384190782 select * from scott.test where object_id=10 END_OUTLINE_DATA
8 rows selected.
SYS@dlhr>
9.驗證SQL Profile是否生效
SYS@dlhr> set autot traceonly explain stat
SYS@dlhr> select * from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- SQL profile "coe_cpk9jsg2qt52r_3384190782" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6973 consistent gets
0 physical reads
0 redo size
4159482 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
注意:
1.這個測試只是為了演示透過coe_xfr_sql_profile.sql實現手動加hint的方法,實際上面的語句問題的處理最佳的方法應該是重新收集scott.test的統計資訊才對.
2.當一條sql既有sql profile又有stored outline時,最佳化器優先選擇stored outline.
3.force_match引數,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.透過sql profile手動加hint的方法很簡單,而為sql新增最合理的hint才是關鍵.
5.測試完後,可以透過 exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' );刪除這個sql profile.
6.執行coe_xfr_sql_profile.sql指令碼的時候使用者需要對當前目錄有生成檔案的許可權，最好當前目錄是/tmp
2.2.2.2 SQL Profile使用示例--使用STA來生成SQL Profile
利用STA對語句進行最佳化後，STA會對語句進行分析，採用最優的最佳化策略，並給出最佳化後的查詢計劃。你可以按照STA給出的建議重寫語句。但是，有些情況下，你可能無法重寫語句（比如在生產環境中，你的語句又在一個包中）。這個時候就可以利用sql profile，將最佳化策略儲存在profile中，Oracle在構建這條語句的查詢計劃時，就不會使用已有相關統計資料，而使用profile的策略，生成新的查詢計劃。
一、 第一步：給使用者賦許可權
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@dlhr>
SYS@dlhr>
SYS@dlhr>
SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> conn lhr/lhr
Connected.
LHR@dlhr>
LHR@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects;
Table created.
LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);
PL/SQL procedure successfully completed.
LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@dlhr> set autot off
LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ;
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
7jt1btjkcczb8
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100
7suktf0w95cry
EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L
HR_20160525_01 where object_id = 100
二、 第二步：建立、執行最佳化任務
LHR@dlhr> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'LHR',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'sql_profile_test',
12 description => 'Task to tune a query on a specified table');
13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
14 END;
15 /
PL/SQL procedure successfully completed.
或者也可以使用sqlid來生成最佳化任務，如下：
LHR@dlhr> DECLARE
2 a_tuning_task VARCHAR2(30);
3 BEGIN
4 a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '7jt1btjkcczb8',
5 task_name => 'sql_profile_test_SQLID');
6 dbms_sqltune.execute_tuning_task(a_tuning_task);
7 END;
8 /
PL/SQL procedure successfully completed.
三、 第三步：檢視最佳化建議LHR@dlhr> set autot off
LHR@dlhr> set long 10000
LHR@dlhr> set longchunksize 1000
LHR@dlhr> set linesize 100
LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_profile_test
Tuning Task Owner : LHR
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/25/2016 16:58:31
Completed at : 05/25/2016 16:58:32
-------------------------------------------------------------------------------
Schema Name: LHR
SQL ID : 9kzm8scz6t92z
SQL Text : select /*+no_index(TB_LHR_20160525_01
TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
-------------------------------------------------------------------------------
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: 99.83%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',
task_owner => 'LHR', 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): .006278 .00004 99.36 %
CPU Time (s): .003397 .000021 99.38 %
User I/O Time (s): 0 0
Buffer Gets: 1249 2 99.83 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
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: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
2- Using SQL Profile
--------------------
Plan hash value: 661515879
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=100)
-------------------------------------------------------------------------------
這裡可以看到，在最佳化建議中給出了新的查詢計劃。現在，我們決定接受這個建議，並且不重寫語句。
四、 第四步：接受profile
LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);
PL/SQL procedure successfully completed.
LHR@dlhr> set autot off
LHR@dlhr> SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints
2 FROM dba_sql_profiles d,
3 dba_advisor_tasks e,
4 SYS.SQLOBJ$DATA A,
5 SYS.SQLOBJ$ B,
6 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
7 '/outline_data/hint'))) h
8 where a.signature = b.signature
9 and a.category = b.category
10 and a.obj_type = b.obj_type
11 and a.plan_id = b.plan_id
12 and a.signature = d.signature
13 and d.task_id=e.task_id
14 and d.name = 'SYS_SQLPROF_0154e728ad3f0000'
15 ;
TASK_NAME NAME
------------------------------ ------------------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
HINTS
----------------------------------------------------------------------------------------------------
sql_profile_test SYS_SQLPROF_0154e728ad3f0000
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
OPTIMIZER_FEATURES_ENABLE(default)
sql_profile_test SYS_SQLPROF_0154e728ad3f0000
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
IGNORE_OPTIM_EMBEDDED_HINTS
在這裡用了包DBMS_SQLTUNE的另一個函式：ACCEPT_SQL_PROFILE。其中，引數task_name即我們建立的最佳化建議任務的名稱，name是profile的名字，可以是任意合法名稱。此外這個函式還有其他一些函式，下面是這個函式的原型：
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL;
task_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE)
RETURN VARCHAR2;
Description是profile的描述資訊；task_owner是最佳化建議任務的所有者；replace為TRUE時，如果這個profile已經存在，就代替它；force_match為TURE時，表示與語句強制匹配，即強制使用繫結變數，和系統引數cursor_sharing設定為FORCE時類似，為FALSE時，與cursor_sharing設定為EXACT時類似，即完全匹配。
這裡要特別提到的是category這個引數，你可以透過設定這個引數，制定特定會話使用這個profile。在10g中，每個會話都有一個新引數SQLTUNE_CATEGORY，他的預設值是DEFAULT。而我們在呼叫這個函式時，如果沒有指定這個引數，那它的值也是DEFAULT，而如果我們給這個profile指定了一個其它的CATEGORY值，如FOR_TUNING，那麼只有會話參SQLTUNE_CATEGORY也為FOR_TUNING時，才會使用這個porfile。為什麼說這個引數很有用呢？試想一個這樣的環境：你在一個生產系統上利用STA調優一條語句，STA已經給出了最佳化建議，但是你又不敢貿然實施它給出的建議（畢竟它只是機器嘛，不能完全信任），你就可以建立一個有特殊CATEGORY的profile，然後在你自己的會話中制定SQLTUNE_CATEGORY為這個特殊的CATEGORY，那就既可以看最佳化建議的實際效果又不影響生產環境。
此外可以透過檢視DBA_SQL_PROFILES來檢視已經建立的profile。
五、 第五步：檢視profile的效果LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 661515879
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從NOTE部分可以看到，語句採用了profile中的資料，建立了新的查詢計劃。並且在查詢計劃中還有一些附加資訊，表明這個語句是採用了’SYS_SQLPROF_0154e728ad3f0000’這個profile，而不是根據物件上面的統計資料來生成的查詢計劃。
但上述方法主要是依賴sql tuning advisor,如果它無法生成你想要的執行計劃.你還可以透過手動的方式,透過sql profile把hint加進去.複雜的SQL的hint可以採用指令碼coe_xfr_sql_profile.sql來產生原語句的outline data和加hint語句的outline data,然後替換對應的SYS.SQLPROF_ATTR,最後執行生成的sql就可以了.
使用PLSQL DEVELOPER 11檢視執行計劃，如下圖，新版本的好處：
2.3 SPM(SQL Plan Management)
2.3.1 SPM基礎知識
SQL 語句的SQL 執行計劃發生更改時，可能存在效能風險。
SQL 計劃發生更改的原因有很多，如最佳化程式版本、最佳化程式統計資訊、最佳化程式引數、方案定義、系統設計和SQL 概要檔案建立等。
在以前版本的Oracle DB 中引入了各種計劃控制技術（如儲存的大綱(storedoutline(9i))和SQL 概要檔案等(SQLprofile(10g))），用於解決計劃更改導致的效能迴歸。但是，這些技術都是需要手動干預的被動式程式。
SQL 計劃管理是一種隨Oracle Database 11g 引入的新功能，透過維護所謂的“SQL 計劃基線(SQL plan baseline(11g))”來使系統能夠自動控制SQL 計劃演變。啟用此功能後，只要證明新生成的SQL 計劃與SQL 計劃基線相整合不會導致效能迴歸，就可以進行此項整合。因此，在執行某個SQL 語句時，只能使用對應的SQL 計劃基線中包括的計劃。可以使用SQL 最佳化集自動載入或植入SQL 計劃基線。
SQL 計劃管理功能的主要優點是系統效能穩定，不會出現計劃迴歸。此外，該功能還可以節省DBA 的許多時間，這些時間通常花費在確定和分析SQL 效能迴歸以及尋找可用的解決方案上。Oracle11g中，Oracle提供dbms_spm包來管理SQL Plan，SPM是一個預防機制，它記錄並評估sql的執行計劃，將已知的高效的sql執行計劃建立為SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的效能而不必關注系統的改變。
在SQL Plan BaseLines捕獲階段，Oracle記錄SQL的執行計劃並檢測該執行計劃是否已經改變，如果SQL改變後的執行計劃是安全的，則SQL就使用新的執行計劃，因此，Oracle維護單個SQL執行計劃的歷史資訊，Oracle維護的SQL執行計劃的歷史僅僅針對重複執行的SQL，SQL Plan Baseline可以手工load，也可以設定為自動捕獲。
載入SQL 計劃基線的方式有兩種:
（1） 即時捕獲,自動捕獲(Automatic Plan Capture)：
使用自動計劃捕獲，方法是：將初始化引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 設定為TRUE。預設情況下，該引數設定為FALSE。將該引數設定為TRUE 將開啟自動標識可重複SQL 語句，以及自動為此類語句建立計劃歷史記錄的功能。 如果要啟用自動的SQL Plan Capture，則需要設定OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，該引數預設為False，如果設定為True，則表示自動捕獲SQL Plan，則系統會自動建立並維護SQL Plan History，SQL Plan History包括最佳化器關注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。
（2） 成批載入（Manual Plan Loading）：
使用DBMS_SPM 程式包；該程式包支援手動管理SQL 計劃基線。使用此程式包，可以將SQL 計劃從遊標快取記憶體或現有的SQL 最佳化集(STS) 直接載入到SQL計劃基線中。對於要從STS 載入到SQL 計劃基線的SQL 語句，需要將其SQL計劃儲存在STS中。使用DBMS_SPM 可以將基線計劃的狀態從已接受更改為未接受（以及從未接受更改為已接受），還可以從登臺表匯出基線計劃，然後使用匯出的基線計劃將SQL 計劃基線載入到其它資料庫中。
也可以手動裝載一個存在的SQL Plan作為SQL Plan Baseline，手動裝載的SQL Plan並不校驗它的效能：
--從SQL Tuning Set中裝載:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');
END;
/
--從Cursor Cache中裝載
DECLARE my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');
END;
/
在SQL 計劃基線演化階段，Oracle DB 會按常規方式評估新計劃的效能，並將效能較好的計劃整合到SQL 計劃基線中。
最佳化程式為SQL 語句找到新的計劃時，會將該計劃作為未接受的計劃新增到計劃歷史記錄中。然後，相對於SQL 計劃基線的效能，驗證該計劃的效能。如果經驗證某個未接受的計劃不會導致效能迴歸（手動或自動），則該計劃會被更改為已接受計劃，並整合到SQL 計劃基線中。成功驗證未接受計劃的過程包括：對此計劃的效能和從SQL計劃基線中選擇的一個計劃的效能進行比較，確保其效能更佳。
演化SQL 計劃基線的方式有兩種：
（1）使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 函式。該函式將返回一個報表，顯示是否已將一些現有的歷史記錄計劃移到了計劃基線中。也可以在歷史記錄中指定要測試的特定計劃。
（2）執行SQL 最佳化指導：透過使用SQL 最佳化指導手動或自動最佳化SQL 語句，演化SQL計劃基線。SQL最佳化指導發現已最佳化的計劃，並確認其效能優於從相應的SQL 計劃基線中選擇的計劃的效能時，就會生成一個建議案以接受SQL 概要檔案。接受了該SQL 概要檔案後，會將已最佳化的計劃新增到相應的SQL 計劃基線中。
在SQL Plan Baselines的演變階段，Oracle評估新的Plan的效能並將效能較好的Plan存放SQL Plan Baselines中，可以使用dbms_spm package的過程EVOLVE_SQL_PLAN_BASELINE將新的SQL Plan存入已經存在的SQL Plan Baselines中，新的Plan將會作為已經Accept Plan加入到SQL Plan Baselines中。
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE report clob;
BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
如果將計劃新增到計劃歷史記錄中，則該計劃將與一些重要的屬性關聯：
（1）SIGNATURE、SQL_HANDLE、SQL_TEXT 和PLAN_NAME 是搜尋操作的重要識別符號。
（2）使用ORIGIN 可以確定計劃是自動捕獲的(AUTO-CAPTURE)、手動演化的(MANUALLOAD)、透過SQL 最佳化指導自動演化的(MANUAL-SQLTUNE) 還是透過自動SQL 最佳化自動演化的(AUTO-SQLTUNE)。
（3） ENABLED 和ACCEPTED：ENABLED屬性表示計劃已啟用，可供最佳化程式使用。如果未設定ENABLED，則系統將不考慮此計劃。ACCEPTED 屬性表示使用者在將計劃更改為ACCEPTED 時計劃已經過驗證為有效計劃（系統自動進行的或使用者手動進行的）。如果將某個計劃更改為ACCEPTED，則僅當使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE()更改其狀態時，該計劃才是非ACCEPTED 的。可以透過刪除ENABLED設定暫時禁用ACCEPTED 計劃。計劃必須為ENABLED 和ACCEPTED，最佳化程式才會考慮使用它。
（4） FIXED 表示最佳化程式僅考慮標記為FIXED 的計劃，而不考慮其它計劃。例如，如果有10 個基線計劃，其中的三個計劃被標記為FIXED，則最佳化程式將僅使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL 計劃基線至少包含一個已啟用的已修復計劃，則該SQL 計劃基線就是FIXED 的。如果在修復的SQL 計劃基線中新增了新計劃，則在手動將這些新計劃宣告為FIXED 之前，無法使用這些新計劃。
可以使用DBA_SQL_PLAN_BASELINES檢視檢視每個計劃的屬性。然後，可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE 函式更改其中的某些屬性。也可以使用DBMS_SPM.DROP_SQL_PLAN_BASELINE 函式刪除計劃或整個計劃歷史記錄。
注：DBA_SQL_PLAN_BASELINES 檢視包含了一些附加屬性；使用這些屬性可以確定各個計劃的上次使用時間，以及是否應自動清除某個計劃。
如果使用的是自動計劃捕獲，則第一次將某個SQL 語句標識為可重複時，其最佳成本計劃將被新增到對應的SQL 計劃基線中。然後，該計劃將用於執行相應的語句。
如果某個SQL 語句存在計劃基線，並且初始化參OPTIMIZER_USE_SQL_PLAN_BASELINES 被設定為TRUE（預設值），則最佳化程式將使用比較計劃選擇策略。每次編譯SQL 語句時，最佳化程式都會先使用傳統的基於成本的搜尋方法建立一個最佳成本計劃，然後嘗試在SQL 計劃基線中找到一個匹配的計劃。如果找到了匹配的計劃，則最佳化程式將照常繼續執行。如果未找到匹配的計劃，則最佳化程式會先將新計劃新增到計劃歷史記錄中，然後計算SQL計劃基線中各個已接受的計劃的成本，並選擇成本最低的那個計劃。使用隨各個已接受的計劃儲存的大綱複製這些已接受的計劃。因此，對於SQL 語句來說，擁有一個SQL 計劃基線的好處就是：最佳化程式始終選擇該SQL 計劃基線中的一個已接受的計劃。
透過SQL 計劃管理，最佳化程式可以生成最佳成本計劃，也可以生成基線計劃。此資訊將被轉儲在有關解釋計劃的plan_table 的other_xml 列中。
此外，還可以使用新的dbms_xplain.display_sql_plan_baseline 函式，顯示某個計劃基線中給定sql_handle 的一個或多個執行計劃。如果還指定了plan_name，則將顯示相應的執行計劃。
注：為了保留向後相容性，如果使用者會話的某個SQL 語句的儲存大綱對是活動的，則將使用此儲存大綱編譯該語句。此外，即使為會話啟用了自動計劃捕獲，也不將最佳化程式使用儲存大綱生成的計劃儲存在SMB 中。
雖然儲存大綱沒有任何顯式遷移過程，但可使用DBMS_SPM 程式包中的LOAD_PLAN_FROM_CURSOR_CACHE 過程或LOAD_PLAN_FROM_SQLSET 過程將其遷移到SQL 計劃基線。遷移完成時，應禁用或刪除原始的儲存大綱。
在SQL Plan選擇階段，SQL每一次編繹，最佳化器使用基於成本的方式，建立一下best-cost的執行計劃，然後去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，則會使用這個執行計劃，如果沒有找到匹配的SQL Plan,最佳化器就會去SQL Plan History中去搜尋成本最低的SQL Plan，如果最佳化器在SQL Plan History中找不到任務匹配的SQL Plan，則該SQL Plan被作為一個Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被驗證不會引起一下效能問題才會被使用。
SPM相關的資料字典：
SELECT * FROM dba_sql_plan_baselines;
SELECT * FROM dba_sqlset_plans;
SELECT * FROM dba_advisor_sqlplans;
2.3.2 刪除Plans 和 Baselines
DROP_SQL_PLAN_BASELINE函式可以從baselines中drop 某個執行的執行計劃，如果不執行plan name，那麼會drop 所有的plan。即drop了baseline。
|
Parameter |
Description |
|
sql_handle |
SQL statement handle. It identifies plans associated with a SQL statement that are to be dropped. If NULL then plan_name must be specified. |
|
plan_name |
Plan name. It identifies a specific plan. Default NULL means to drop all plans associated with the SQL statement identified by sql_handle. |
--刪除某個SQL的baseline
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SQL_7b76323ad90440b9',
plan_name => NULL);
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
--刪除所有baseline
declare
v_plan_num PLS_INTEGER;
begin
for cur in (SELECT * FROM dba_sql_plan_baselines) loop
begin
v_plan_num := dbms_spm.drop_sql_plan_baseline(sql_handle => cur.sql_handle);
exception
when others then
null;
end;
end loop;
end;
/
2.3.3 SPM使用演示
--取消自動捕獲，也可以不取消自動捕捉：
show parameter baselines
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@dlhr> conn lhr/lhr
Connected.
LHR@dlhr>
LHR@dlhr>
LHR@dlhr>
LHR@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
System altered.
--建立表並插入資料：
CREATE TABLE tb_spm_test_lhr (
id NUMBER,
description VARCHAR2(50)
);
DECLARE
TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
l_tab t_tab := t_TAB();
BEGIN
FOR i IN 1 .. 10000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE);
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> CREATE TABLE tb_spm_test_lhr (
2 id NUMBER,
3 description VARCHAR2(50)
4 );
Table created.
LHR@dlhr>
LHR@dlhr> DECLARE
2 TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
3 l_tab t_tab := t_TAB();
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 l_tab.extend;
7 l_tab(l_tab.last).id := i;
8 l_tab(l_tab.last).description := 'Description for ' || i;
9 END LOOP;
10
11 FORALL i IN l_tab.first .. l_tab.last
12 INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
13
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
94 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----獲取剛才查詢的SQL_ID:
set autot off
col SQL_TEXT format a100
select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
and a.SQL_TEXT not like '%v$sql%'
AND sql_text NOT LIKE '%EXPLAIN%';
LHR@dlhr> set autot off
LHR@dlhr> col SQL_TEXT format a100
LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
2 WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
3 and a.SQL_TEXT not like '%v$sql%'
4 AND sql_text NOT LIKE '%EXPLAIN%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
garkwg3yy2ram SELECT description FROM tb_spm_test_lhr WHERE id = 100
----使用SQL_ID 從cursor cache中手工捕獲執行計劃：
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '&sql_id');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
-- --使用DBA_SQL_PLAN_BASELINES檢視檢視SPM 資訊：
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
--重新整理Share Pool，使下次SQL 執行時必須進行硬解析：
ALTER SYSTEM FLUSH SHARED_POOL;
LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
5 sql_id => '&sql_id');
6 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
7 END;
8 /
Enter value for sql_id: garkwg3yy2ram
old 5: sql_id => '&sql_id');
new 5: sql_id => 'garkwg3yy2ram');
Plans Loaded: 1
PL/SQL procedure successfully completed.
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
LHR@dlhr> set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr>
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
555 recursive calls
16 db block gets
667 consistent gets
0 physical reads
3056 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
32 sorts (memory)
0 sorts (disk)
1 rows processed
---建立索引，收集統計資訊，並查詢相同的SQL:
CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
Index created.
LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
PL/SQL procedure successfully completed.
LHR@dlhr>
LHR@dlhr>
LHR@dlhr>
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
640 recursive calls
39 db block gets
493 consistent gets
2 physical reads
12268 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
--這裡我們建立了索引，但是這裡還是走的全表掃描，這裡使用索引明顯才是最優的方案。
--檢視SPM 檢視：
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES NO
--透過baselines查詢的結果，可以看到我們的SQL 產生了2條執行計劃。但是我們認為最優的執行計劃並沒有被標記為ACCEPT，所以沒有使用。
下邊我們演化執行計劃： 演化就是將cost低的執行計劃標記為accept
LHR@dlhr> SET LONG 10000
LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual
new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_4f19d3cf57be7303
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_4y6fmtxbvwws38b725570
------------------------------------
Plan was verified: Time used .018 seconds.
Plan passed performance criterion: 15 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): .308 .025 12.32
CPU Time(ms): .164 .015 10.93
Buffer Gets: 45 3 15
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
--再次檢視DBA_SQL_PLAN_BASELINES檢視：
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES YES
--再次執行SQL:
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2587945646
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
14 db block gets
18 consistent gets
0 physical reads
3048 redo size
553 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--這次正確的使用了索引。 因為只有標記為ENABLE和 ACCEPT的plan才可以被使用。
下面示例將我們的第一個走全表掃描的執行計劃標記為fixed。 標記為fixed的執行計劃會被優先使用。FIXED 表示最佳化程式僅考慮標記為FIXED 的計劃，而不考慮其它計劃。例如，如果有10 個基線計劃，其中的三個計劃被標記為FIXED，則最佳化程式將僅使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL 計劃基線至少包含一個已啟用的已修復計劃，則該SQL 計劃基線就是FIXED 的。如果在修復的SQL 計劃基線中新增了新計劃，則在手動將這些新計劃宣告為FIXED 之前，無法使用這些新計劃。
set autot off
select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => '&sql_handle',
6 plan_name => '&plan_name',
7 attribute_name => 'fixed',
8 attribute_value => 'YES');
9
10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11 END;
12 /
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SQL_4f19d3cf57be7303',
Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2
old 6: plan_name => '&plan_name',
new 6: plan_name => 'SQL_PLAN_4y6fmtxbvwws3184920d2',
Plans Altered: 1
PL/SQL procedure successfully completed.
--驗證：
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
LHR@dlhr> set autot off
LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))
new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic'))
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_4f19d3cf57be7303
SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2196561629
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR |
---------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2587945646
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR |
| 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX |
--------------------------------------------------------
34 rows selected.
LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO
--再次檢視我們之前的SQL:
set autot trace
SELECT description FROM tb_spm_test_lhr WHERE id = 100;
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
8 db block gets
46 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--這裡已經走了全表掃描，根據前邊的示例，我們知道這裡走索引會更優，但因為我們將走全表掃描的執行計劃設定為fixed，所以優先使用這個執行計劃。
2.4 總結
1、coe_xfr_sql_profile.sql指令碼需要從MOS下載，小麥苗已經下載放在了雲盤，大家可以去下載，地址你懂的
2、outline是9i的內容，SQL Profile是10g的新特性，SPM是11g的新特性
---------------------------------------------------------------------------------------------------------------------
About Me
.............................................................................................................................................
● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2107604/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址：
.............................................................................................................................................
● QQ群號：230161599（滿）、618766405
● 微信群：可加我微信，我拉大家進群，非誠勿擾
● 聯絡我請加QQ好友（646634621），註明新增緣由
● 於 2016-05-18 10:00~ 2016-05-26 19:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店：
● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群1 小麥苗的DBA寶典QQ群2 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2146131/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SPM和STA進行固定執行計劃
- oracle 固定執行計劃Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- Oracle手動固定SQL執行計劃OracleSQL
- Oracle緊急固定執行計劃之手段Oracle
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle使用outline固定執行計劃事例Oracle
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（四）
- 控制執行計劃之-SPM BASELINE（三）
- 控制執行計劃之-SPM BASELINE（二）
- 控制執行計劃之-SPM BASELINE（一）
- baseline固定SQL執行計劃SQL
- 用sql profile來固定執行計劃SQL
- 使用OUTLINE固定執行計劃
- oracle如何檢視執行計劃Oracle
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- SQL BASELINE修改固定執行計劃SQL
- 用outline修改固定執行計劃
- 【SPM】Oracle計劃管理器SPM介紹及用例Oracle
- ORACLE執行計劃Oracle
- Oracle執行計劃Explain Plan 如何使用OracleAI
- 透過SPM手動新增執行計劃到baseLine
- 使用coe_xfr_sql_profile固定執行計劃SQL
- Oracle sql執行計劃OracleSQL
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- 如何看懂執行計劃！
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 知識篇 | ORACLE 如何執行計劃繫結Oracle
- Oracle如何手動重新整理執行計劃Oracle
- Oracle10g如何獲取執行計劃Oracle
- Oracle如何檢視真實執行計劃（一）Oracle