Oracle中怎么固定執(zhí)行計劃

發(fā)布時間：2021-07-24 16:45:24 來源：億速云閱讀：217 作者：Leah 欄目：關(guān)系型數(shù)據(jù)庫

這篇文章給大家介紹Oracle中怎么固定執(zhí)行計劃，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

1.1 BLOG文檔結(jié)構(gòu)圖

1.2 前言部分

1.2.1 導(dǎo)讀和注意事項

各位技術(shù)愛好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識，~O(∩_∩)O~：

① 固定執(zhí)行計劃的常用方法：outline、SQL Profile、SPM（重點）

② coe_xfr_sql_profile.sql腳本的使用

Tips：

① 若文章代碼格式有錯亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

② 本篇BLOG中命令的輸出部分需要特別關(guān)注的地方我都用灰色背景和粉紅色字體來表示，比如下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關(guān)注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。

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 相關(guān)參考文章鏈接

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技術(shù)暫時鎖定SQL的執(zhí)行計劃：http://blog.itpub.net/26736162/viewspace-2102180/

1.2.3 本文簡介

本文介紹了oracle在固定執(zhí)行計劃的過程中常使用的3種方法，outline,SQL Profile和SPM,其中SQL Profile和SPM是重點需要掌握的內(nèi)容。

---------------------------------------------------------------------------------------------------------------------

第二章固定執(zhí)行計劃的三種方法介紹

2.1 outline

2.1.1 outline基礎(chǔ)知識

在實際項目中，通常在開發(fā)環(huán)境下一些SQL 執(zhí)行沒有任何問題，而到了生產(chǎn)環(huán)境或生產(chǎn)環(huán)境的數(shù)據(jù)量發(fā)生較大的變量時，其SQL 的執(zhí)行效率會異常的慢。此時如果更改SQL ，則可能需要重新修改源程序以及重新編譯程序。如果覺得修改源程序的成本比較大，則可以使用OUTLINE在不改變原應(yīng)用程序的情況下更改特定SQL 的執(zhí)行計劃。

OUTLINE的原理是將調(diào)好的SQL 的執(zhí)行計劃(一系列的HINT)存貯起來，然后該執(zhí)行計劃所對應(yīng)的SQL 用目前系統(tǒng)那個效率低下的SQL 來替代之。從而使得系統(tǒng)每次執(zhí)行該SQL 時，都會使用已存貯的執(zhí)行計劃來執(zhí)行。因此可以在不改變已有系統(tǒng)SQL 的情況下達到改變其執(zhí)行計劃的目的。

OUTLINE方式也是通過存貯HINT的方式來達到執(zhí)行計劃的穩(wěn)定與改變。

當發(fā)現(xiàn)低效SQL之后，可以使用hint優(yōu)化他，對于SQL代碼可以修改的情況，直接修改SQL代碼加上hint即可，但是對于SQL代碼不可修改的情況，Oracle提供了outLine功能來為SQL修改hint，以致執(zhí)行計劃變更！

?OutLine機制：

Outline保存了SQL的hint在outline的表中。當執(zhí)行SQL時，Oracle會使用outline中的hint來為SQL生成執(zhí)行計劃。

?使用 OutLine的步驟：

（1）生成新SQL和老SQL的2個Outline

（2）交換兩個SQL的提示信息

（3） ON LOGON觸發(fā)器設(shè)定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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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基礎(chǔ)知識

在oracle 11g的后續(xù)版本中，use_stored_outlines這個參數(shù)已經(jīng)不存在了。意味著我們不能像以前的版本中使用create outline的方式來為一個sql創(chuàng)建hint，然后使用store outline來固定執(zhí)行計劃這種方式了.

SQL Profile就是為某一SQL語句提供除了系統(tǒng)統(tǒng)計信息、對象（表和索引等）統(tǒng)計信息之外的其他信息，比如運行環(huán)境、額外的更準確的統(tǒng)計信息，以幫助優(yōu)化器為SQL語句選擇更適合的執(zhí)行計劃。SQL Profiles可以說是Outlines的進化。Outlines能夠?qū)崿F(xiàn)的功能SQL Profiles也完全能夠?qū)崿F(xiàn)，而SQL Profiles具有Outlines不具備的優(yōu)化，最重要的有二點：

①　 SQL Profiles更容易生成、更改和控制。

②　 SQL Profiles在對SQL語句的支持上做得更好，也就是適用范圍更廣。

使用SQL Profiles兩個目的：

（一）鎖定或者說是穩(wěn)定執(zhí)行計劃。

（二）在不能修改應(yīng)用中的SQL的情況下使SQL語句按指定的執(zhí)行計劃運行。

10g之前有outlines,10g之后sql profile作為新特性之一出現(xiàn)。如果針對非綁定變量的sql,outlines則力不從心。sql profile最大的優(yōu)點是在不修改sql語句和會話執(zhí)行環(huán)境的情況下去優(yōu)化sql的執(zhí)行效率,適合無法在應(yīng)用程序中修改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等系統(tǒng)權(quán)限。

2.2.2 SQL Profile使用演示

有2種生成SQL Profile的方法，手動和采用STA來生成。

2.2.2.1 SQL Profile使用示例--手工創(chuàng)建SQL Profile

創(chuàng)建測試表，根據(jù)DBA_OBJECTS創(chuàng)建，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默認執(zhí)行計劃,走了索引，通過指定outline可以獲取到系統(tǒng)為我們生成的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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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已經(jīng)為我們生成了，我們選取必要的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.

查看是否生效，已經(jīng)生效了：

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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="">數(shù)據(jù)

最麻煩的sqlprof_attr('FULL(t1@SEL$1)')是這里的格式如何寫.在mos上的文章note 215187.1中的sqlt.zip的目錄utl中提供了腳本coe_xfr_sql_profile.sql可以生成這些信息.

1.建立測試表和數(shù)據(jù)

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.執(zhí)行查詢語句
--執(zhí)行原有的查詢語句,查看執(zhí)行計劃發(fā)現(xiàn)走索引,實際上這時表中大部分行的object_id都已經(jīng)被更新為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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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下都可以。

Oracle中怎么固定執(zhí)行計劃

5.對上面的兩個sql產(chǎn)生outline data的sql.

[ZHLHRSPMDB2:oracle]:/oracle>cd /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

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中產(chǎn)生的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]');

生成的文件在當前目錄：

Oracle中怎么固定執(zhí)行計劃

7.執(zhí)行替換過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 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.查看產(chǎn)生的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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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實現(xiàn)手動加hint的方法,實際上面的語句問題的處理最佳的方法應(yīng)該是重新收集scott.test的統(tǒng)計信息才對.
2.當一條sql既有sql profile又有stored outline時,優(yōu)化器優(yōu)先選擇stored outline.
3.force_match參數(shù),TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.通過sql profile手動加hint的方法很簡單,而為sql添加最合理的hint才是關(guān)鍵.
5.測試完后,可以通過 exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' );刪除這個sql profile.

6.執(zhí)行coe_xfr_sql_profile.sql腳本的時候用戶需要對當前目錄有生成文件的權(quán)限，最好當前目錄是/tmp

2.2.2.2 SQL Profile使用示例--使用STA來生成SQL Profile

利用STA對語句進行優(yōu)化后，STA會對語句進行分析，采用最優(yōu)的優(yōu)化策略，并給出優(yōu)化后的查詢計劃。你可以按照STA給出的建議重寫語句。但是，有些情況下，你可能無法重寫語句（比如在生產(chǎn)環(huán)境中，你的語句又在一個包中）。這個時候就可以利用sql profile，將優(yōu)化策略存儲在profile中，Oracle在構(gòu)建這條語句的查詢計劃時，就不會使用已有相關(guān)統(tǒng)計數(shù)據(jù)，而使用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

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> 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(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 3612989399

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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來生成優(yōu)化任務(wù)，如下：

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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 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)

-------------------------------------------------------------------------------

這里可以看到，在優(yōu)化建議中給出了新的查詢計劃?，F(xiàn)在，我們決定接受這個建議，并且不重寫語句。

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(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 3612989399

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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的另一個函數(shù)：ACCEPT_SQL_PROFILE。其中，參數(shù)task_name即我們創(chuàng)建的優(yōu)化建議任務(wù)的名稱，name是profile的名字，可以是任意合法名稱。此外這個函數(shù)還有其他一些函數(shù)，下面是這個函數(shù)的原型：

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是優(yōu)化建議任務(wù)的所有者；replace為TRUE時，如果這個profile已經(jīng)存在，就代替它；force_match為TURE時，表示與語句強制匹配，即強制使用綁定變量，和系統(tǒng)參數(shù)cursor_sharing設(shè)置為FORCE時類似，為FALSE時，與cursor_sharing設(shè)置為EXACT時類似，即完全匹配。

這里要特別提到的是category這個參數(shù)，你可以通過設(shè)置這個參數(shù)，制定特定會話使用這個profile。在10g中，每個會話都有一個新參數(shù)SQLTUNE_CATEGORY，他的默認值是DEFAULT。而我們在調(diào)用這個函數(shù)時，如果沒有指定這個參數(shù)，那它的值也是DEFAULT，而如果我們給這個profile指定了一個其它的CATEGORY值，如FOR_TUNING，那么只有會話參SQLTUNE_CATEGORY也為FOR_TUNING時，才會使用這個porfile。為什么說這個參數(shù)很有用呢？試想一個這樣的環(huán)境：你在一個生產(chǎn)系統(tǒng)上利用STA調(diào)優(yōu)一條語句，STA已經(jīng)給出了優(yōu)化建議，但是你又不敢貿(mào)然實施它給出的建議（畢竟它只是機器嘛，不能完全信任），你就可以創(chuàng)建一個有特殊CATEGORY的profile，然后在你自己的會話中制定SQLTUNE_CATEGORY為這個特殊的CATEGORY，那就既可以看優(yōu)化建議的實際效果又不影響生產(chǎn)環(huán)境。

此外可以通過視圖DBA_SQL_PROFILES來查看已經(jīng)創(chuàng)建的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(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 661515879

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 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中的數(shù)據(jù)，創(chuàng)建了新的查詢計劃。并且在查詢計劃中還有一些附加信息，表明這個語句是采用了’SYS_SQLPROF_0154e728ad3f0000’這個profile，而不是根據(jù)對象上面的統(tǒng)計數(shù)據(jù)來生成的查詢計劃。

但上述方法主要是依賴sql tuning advisor,如果它無法生成你想要的執(zhí)行計劃.你還可以通過手動的方式,通過sql profile把hint加進去.復(fù)雜的SQL的hint可以采用腳本coe_xfr_sql_profile.sql來產(chǎn)生原語句的outline data和加hint語句的outline data,然后替換對應(yīng)的SYS.SQLPROF_ATTR,最后執(zhí)行生成的sql就可以了.

使用PLSQL DEVELOPER 11查看執(zhí)行計劃，如下圖，新版本的好處：

Oracle中怎么固定執(zhí)行計劃

2.3 SPM(SQL Plan Management)

2.3.1 SPM基礎(chǔ)知識

SQL 語句的SQL 執(zhí)行計劃發(fā)生更改時，可能存在性能風(fēng)險。

SQL 計劃發(fā)生更改的原因有很多，如優(yōu)化程序版本、優(yōu)化程序統(tǒng)計信息、優(yōu)化程序參數(shù)、方案定義、系統(tǒng)設(shè)計和SQL 概要文件創(chuàng)建等。

在以前版本的Oracle DB 中引入了各種計劃控制技術(shù)（如存儲的大綱(storedoutline(9i))和SQL 概要文件等(SQLprofile(10g))），用于解決計劃更改導(dǎo)致的性能回歸。但是，這些技術(shù)都是需要手動干預(yù)的被動式進程。

SQL 計劃管理是一種隨Oracle Database 11g 引入的新功能，通過維護所謂的“SQL 計劃基線(SQL plan baseline(11g))”來使系統(tǒng)能夠自動控制SQL 計劃演變。啟用此功能后，只要證明新生成的SQL 計劃與SQL 計劃基線相集成不會導(dǎo)致性能回歸，就可以進行此項集成。因此，在執(zhí)行某個SQL 語句時，只能使用對應(yīng)的SQL 計劃基線中包括的計劃。可以使用SQL 優(yōu)化集自動加載或植入SQL 計劃基線。

SQL 計劃管理功能的主要優(yōu)點是系統(tǒng)性能穩(wěn)定，不會出現(xiàn)計劃回歸。此外，該功能還可以節(jié)省DBA 的許多時間，這些時間通常花費在確定和分析SQL 性能回歸以及尋找可用的解決方案上。Oracle11g中，Oracle提供dbms_spm包來管理SQL Plan，SPM是一個預(yù)防機制，它記錄并評估sql的執(zhí)行計劃，將已知的高效的sql執(zhí)行計劃建立為SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的性能而不必關(guān)注系統(tǒng)的改變。

在SQL Plan BaseLines捕獲階段，Oracle記錄SQL的執(zhí)行計劃并檢測該執(zhí)行計劃是否已經(jīng)改變，如果SQL改變后的執(zhí)行計劃是安全的，則SQL就使用新的執(zhí)行計劃，因此，Oracle維護單個SQL執(zhí)行計劃的歷史信息，Oracle維護的SQL執(zhí)行計劃的歷史僅僅針對重復(fù)執(zhí)行的SQL，SQL Plan Baseline可以手工load，也可以設(shè)置為自動捕獲。

加載SQL 計劃基線的方式有兩種:

（1）即時捕獲,自動捕獲(Automatic Plan Capture)：

使用自動計劃捕獲，方法是：將初始化參數(shù)OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 設(shè)置為TRUE。默認情況下，該參數(shù)設(shè)置為FALSE。將該參數(shù)設(shè)置為TRUE 將打開自動標識可重復(fù)SQL 語句，以及自動為此類語句創(chuàng)建計劃歷史記錄的功能。如果要激活自動的SQL Plan Capture，則需要設(shè)置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，該參數(shù)默認為False，如果設(shè)置為True，則表示自動捕獲SQL Plan，則系統(tǒng)會自動創(chuàng)建并維護SQL Plan History，SQL Plan History包括優(yōu)化器關(guān)注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。

（2）成批加載（Manual Plan Loading）：

使用DBMS_SPM 程序包；該程序包支持手動管理SQL 計劃基線。使用此程序包，可以將SQL 計劃從游標高速緩存或現(xiàn)有的SQL 優(yōu)化集(STS) 直接加載到SQL計劃基線中。對于要從STS 加載到SQL 計劃基線的SQL 語句，需要將其SQL計劃存儲在STS中。使用DBMS_SPM 可以將基線計劃的狀態(tài)從已接受更改為未接受（以及從未接受更改為已接受），還可以從登臺表導(dǎo)出基線計劃，然后使用導(dǎo)出的基線計劃將SQL 計劃基線加載到其它數(shù)據(jù)庫中。

也可以手動裝載一個存在的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 會按常規(guī)方式評估新計劃的性能，并將性能較好的計劃集成到SQL 計劃基線中。

優(yōu)化程序為SQL 語句找到新的計劃時，會將該計劃作為未接受的計劃添加到計劃歷史記錄中。然后，相對于SQL 計劃基線的性能，驗證該計劃的性能。如果經(jīng)驗證某個未接受的計劃不會導(dǎo)致性能回歸（手動或自動），則該計劃會被更改為已接受計劃，并集成到SQL 計劃基線中。成功驗證未接受計劃的過程包括：對此計劃的性能和從SQL計劃基線中選擇的一個計劃的性能進行比較，確保其性能更佳。

演化SQL 計劃基線的方式有兩種：

（1）使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 函數(shù)。該函數(shù)將返回一個報表，顯示是否已將一些現(xiàn)有的歷史記錄計劃移到了計劃基線中。也可以在歷史記錄中指定要測試的特定計劃。

（2）運行SQL 優(yōu)化指導(dǎo)：通過使用SQL 優(yōu)化指導(dǎo)手動或自動優(yōu)化SQL 語句，演化SQL計劃基線。SQL優(yōu)化指導(dǎo)發(fā)現(xiàn)已優(yōu)化的計劃，并確認其性能優(yōu)于從相應(yīng)的SQL 計劃基線中選擇的計劃的性能時，就會生成一個建議案以接受SQL 概要文件。接受了該SQL 概要文件后，會將已優(yōu)化的計劃添加到相應(yīng)的SQL 計劃基線中。

在SQL Plan Baselines的演變階段，Oracle評估新的Plan的性能并將性能較好的Plan存放SQL Plan Baselines中，可以使用dbms_spm package的過程EVOLVE_SQL_PLAN_BASELINE將新的SQL Plan存入已經(jīng)存在的SQL Plan Baselines中，新的Plan將會作為已經(jīng)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;

如果將計劃添加到計劃歷史記錄中，則該計劃將與一些重要的屬性關(guān)聯(lián)：

（1）SIGNATURE、SQL_HANDLE、SQL_TEXT 和PLAN_NAME 是搜索操作的重要標識符。

（2）使用ORIGIN 可以確定計劃是自動捕獲的(AUTO-CAPTURE)、手動演化的(MANUALLOAD)、通過SQL 優(yōu)化指導(dǎo)自動演化的(MANUAL-SQLTUNE) 還是通過自動SQL 優(yōu)化自動演化的(AUTO-SQLTUNE)。

（3） ENABLED 和ACCEPTED：ENABLED屬性表示計劃已啟用，可供優(yōu)化程序使用。如果未設(shè)置ENABLED，則系統(tǒng)將不考慮此計劃。ACCEPTED 屬性表示用戶在將計劃更改為ACCEPTED 時計劃已經(jīng)過驗證為有效計劃（系統(tǒng)自動進行的或用戶手動進行的）。如果將某個計劃更改為ACCEPTED，則僅當使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE()更改其狀態(tài)時，該計劃才是非ACCEPTED 的?？梢酝ㄟ^刪除ENABLED設(shè)置暫時禁用ACCEPTED 計劃。計劃必須為ENABLED 和ACCEPTED，優(yōu)化程序才會考慮使用它。

（4） FIXED 表示優(yōu)化程序僅考慮標記為FIXED 的計劃，而不考慮其它計劃。例如，如果有10 個基線計劃，其中的三個計劃被標記為FIXED，則優(yōu)化程序?qū)H使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL 計劃基線至少包含一個已啟用的已修復(fù)計劃，則該SQL 計劃基線就是FIXED 的。如果在修復(fù)的SQL 計劃基線中添加了新計劃，則在手動將這些新計劃聲明為FIXED 之前，無法使用這些新計劃。

可以使用DBA_SQL_PLAN_BASELINES視圖查看每個計劃的屬性。然后，可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE 函數(shù)更改其中的某些屬性。也可以使用DBMS_SPM.DROP_SQL_PLAN_BASELINE 函數(shù)刪除計劃或整個計劃歷史記錄。

注：DBA_SQL_PLAN_BASELINES 視圖包含了一些附加屬性；使用這些屬性可以確定各個計劃的上次使用時間，以及是否應(yīng)自動清除某個計劃。

如果使用的是自動計劃捕獲，則第一次將某個SQL 語句標識為可重復(fù)時，其最佳成本計劃將被添加到對應(yīng)的SQL 計劃基線中。然后，該計劃將用于執(zhí)行相應(yīng)的語句。

如果某個SQL 語句存在計劃基線，并且初始化參OPTIMIZER_USE_SQL_PLAN_BASELINES 被設(shè)置為TRUE（默認值），則優(yōu)化程序?qū)⑹褂帽容^計劃選擇策略。每次編譯SQL 語句時，優(yōu)化程序都會先使用傳統(tǒng)的基于成本的搜索方法建立一個最佳成本計劃，然后嘗試在SQL 計劃基線中找到一個匹配的計劃。如果找到了匹配的計劃，則優(yōu)化程序?qū)⒄粘＠^續(xù)運行。如果未找到匹配的計劃，則優(yōu)化程序會先將新計劃添加到計劃歷史記錄中，然后計算SQL計劃基線中各個已接受的計劃的成本，并選擇成本最低的那個計劃。使用隨各個已接受的計劃存儲的大綱復(fù)制這些已接受的計劃。因此，對于SQL 語句來說，擁有一個SQL 計劃基線的好處就是：優(yōu)化程序始終選擇該SQL 計劃基線中的一個已接受的計劃。

通過SQL 計劃管理，優(yōu)化程序可以生成最佳成本計劃，也可以生成基線計劃。此信息將被轉(zhuǎn)儲在有關(guān)解釋計劃的plan_table 的other_xml 列中。

此外，還可以使用新的dbms_xplain.display_sql_plan_baseline 函數(shù)，顯示某個計劃基線中給定sql_handle 的一個或多個執(zhí)行計劃。如果還指定了plan_name，則將顯示相應(yīng)的執(zhí)行計劃。

注：為了保留向后兼容性，如果用戶會話的某個SQL 語句的存儲大綱對是活動的，則將使用此存儲大綱編譯該語句。此外，即使為會話啟用了自動計劃捕獲，也不將優(yōu)化程序使用存儲大綱生成的計劃存儲在SMB 中。

雖然存儲大綱沒有任何顯式遷移過程，但可使用DBMS_SPM 程序包中的LOAD_PLAN_FROM_CURSOR_CACHE 過程或LOAD_PLAN_FROM_SQLSET 過程將其遷移到SQL 計劃基線。遷移完成時，應(yīng)禁用或刪除原始的存儲大綱。

在SQL Plan選擇階段，SQL每一次編繹，優(yōu)化器使用基于成本的方式，建立一下best-cost的執(zhí)行計劃，然后去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，則會使用這個執(zhí)行計劃，如果沒有找到匹配的SQL Plan,優(yōu)化器就會去SQL Plan History中去搜索成本最低的SQL Plan，如果優(yōu)化器在SQL Plan History中找不到任務(wù)匹配的SQL Plan，則該SQL Plan被作為一個Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被驗證不會引起一下性能問題才會被使用。

SPM相關(guān)的數(shù)據(jù)字典：

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函數(shù)可以從baselines中drop 某個執(zhí)行的執(zhí)行計劃，如果不執(zhí)行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

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> 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.

--創(chuàng)建表并插入數(shù)據(jù)：

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;

11 FORALL i IN l_tab.first .. l_tab.last

12 INSERT INTO tb_spm_test_lhr VALUES l_tab(i);

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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中手工捕獲執(zhí)行計劃：

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 執(zhí)行時必須進行硬解析：

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

---創(chuàng)建索引，收集統(tǒng)計信息，并查詢相同的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> set autot trace

LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

----------------------------------------------------------

Plan hash value: 2196561629

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

--這里我們創(chuàng)建了索引，但是這里還是走的全表掃描，這里使用索引明顯才是最優(yōu)的方案。

--查看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查詢的結(jié)果，可以看到我們的SQL 產(chǎn)生了2條執(zhí)行計劃。但是我們認為最優(yōu)的執(zhí)行計劃并沒有被標記為ACCEPT，所以沒有使用。

下邊我們演化執(zhí)行計劃：演化就是將cost低的執(zhí)行計劃標記為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

--再次執(zhí)行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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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才可以被使用。

下面示例將我們的第一個走全表掃描的執(zhí)行計劃標記為fixed。標記為fixed的執(zhí)行計劃會被優(yōu)先使用。FIXED 表示優(yōu)化程序僅考慮標記為FIXED 的計劃，而不考慮其它計劃。例如，如果有10 個基線計劃，其中的三個計劃被標記為FIXED，則優(yōu)化程序?qū)H使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL 計劃基線至少包含一個已啟用的已修復(fù)計劃，則該SQL 計劃基線就是FIXED 的。如果在修復(fù)的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');

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

關(guān)于Oracle中怎么固定執(zhí)行計劃就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

向AI問一下細節(jié)

Oracle中怎么固定執(zhí)行計劃

1.1 BLOG文檔結(jié)構(gòu)圖

1.2 前言部分

1.2.1 導(dǎo)讀和注意事項

1.2.2 相關(guān)參考文章鏈接

1.2.3 本文簡介

---------------------------------------------------------------------------------------------------------------------

第二章 固定執(zhí)行計劃的三種方法介紹

2.1 outline

2.1.1 outline基礎(chǔ)知識

2.1.2 ouline使用演示

2.2 SQL Profile

2.2.1 SQL Profile基礎(chǔ)知識

2.2.2 SQL Profile使用演示

2.2.2.1 SQL Profile使用示例--手工創(chuàng)建SQL Profile

2.2.2.2 SQL Profile使用示例--使用STA來生成SQL Profile

2.3 SPM(SQL Plan Management)

2.3.1 SPM基礎(chǔ)知識

2.3.2 刪除Plans 和 Baselines

2.3.3 SPM使用演示

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標簽

第二章固定執(zhí)行計劃的三種方法介紹