溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家介紹Oracle中怎么固定執行計劃，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
各位技術愛好者，看完本文后，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 固定執行計劃的常用方法：outline、SQL Profile、SPM（重點）
② coe_xfr_sql_profile.sql腳本的使用
Tips：
① 若文章代碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：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皆可，您的批評指正是我寫作的最大動力。
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/
本文介紹了oracle在固定執行計劃的過程中常使用的3種方法，outline,SQL Profile和SPM,其中SQL Profile和SPM是重點需要掌握的內容。
在實際項目中，通常在開發環境下一些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;
測試過程如下：
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>
在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種生成SQL Profile的方法，手動和采用STA來生成。
創建測試表，根據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>
一、 使用sqlprof_attr<font times"="" roman?="" new="">數據
最麻煩的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
利用STA對語句進行優化后，STA會對語句進行分析，采用最優的優化策略，并給出優化后的查詢計劃。你可以按照STA給出的建議重寫語句。但是，有些情況下，你可能無法重寫語句（比如在生產環境中，你的語句又在一個包中）。這個時候就可以利用sql profile，將優化策略存儲在profile中，Oracle在構建這條語句的查詢計劃時，就不會使用已有相關統計數據，而使用profile的策略，生成新的查詢計劃。
undefined
[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
undefined
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.
undefined
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)
-------------------------------------------------------------------------------
這里可以看到，在優化建議中給出了新的查詢計劃。現在，我們決定接受這個建議，并且不重寫語句。
undefined
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。
undefined
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查看執行計劃，如下圖，新版本的好處：
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;
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;
/
--取消自動捕獲，也可以不取消自動捕捉：
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
關于Oracle中怎么固定執行計劃就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
