Query Block Name

可能很多人用手工建立SQL Profile的一個最大的難處是不知道該如何寫出讓SQL Profile接受的hint，因為傳統的hint是不包含Query Block Name的，把Query Block Name增加到hint裡會顯得這個技術有點複雜。其實Query Block Name並不是複雜的技術，我們搞清楚了它的由來、它的技術細節後，就會豁然開朗。

n Note ：初始化引數類的hint並不需要Query Block Name，他們的作用域是全域性的，初始化類的引數有很多：all_rows、dynamic_sampling、gather_plan_statistics、result_cache等等。

我們先看下如下的一個SQL：

select count(*) from test where status in (select status from test where status='Inactive');

如果我們想讓子查詢select status from test where status='Inactive'走索引掃描而不要走全索引掃描，這個hint該如何寫？

select count(*) from test where status in (select /*+ index_rs(test t_ind) */ status from test where status='Inactive');

但是如果我們使用的是SQL Profile等技術，不是直接在SQL語句裡新增hint，這個hint要在全域性起作用，如果僅僅是透過index_rs(test t_ind)來表達你的意圖並不夠，因為這個SQL裡有2處都引用到了test表，index(test t_ind)到底是對SQL的哪個地方的test起作用並不明確，因此才會引出Query Block Name的概念，一個select（update/delete/merge等也都是）就是一個Query Block。系統預設產生的查詢塊是以一個由字母組成的字首加數字組成的，字首是基於語句的型別。查詢塊的數字編號是按照SQL語句解析階段查詢塊出現的位置從左到右來進行的。看下錶：

字首	語句型別
CRI$	CREATE INDEX 語句
DEL$	DELETE 語句
INS$	INSERT 語句
MRG$	MERGE語句
SEL$	SELECT 語句
SET$	集合操作如UNION
UPD$	UPDATE 語句

例如我們上面的語句select count(*) from test where status in (select status from test where status='Inactive')可以拆解為2個查詢塊，select count(*) from test為SEL$1，select status from test where status='Inactive'為SEL$2，但是在SQL語句解析階段，SQL語句會做查詢轉換，這些帶有子查詢的SQL最終會被展開為只包含一個select的單一查詢塊的SQL，轉換後的SQL語句的查詢塊命名會與其他部分的命名不一樣，它是包含字首加一個八個字元的雜湊值。我們可以透過dbms_xplan.display_cursor增加outline引數來獲得SQL PLAN的outline data資料，這些hint裡包含了查詢塊的命名。

SQL>select * from table(dbms_xplan.display_cursor(null,null,'outline'));

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 10)

OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$5DA710D3")

UNNEST(@"SEL$2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

INDEX_FFS(@"SEL$5DA710D3" "TEST"@"SEL$1" ("TEST"."STATUS"))

INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS"))

LEADING(@"SEL$5DA710D3" "TEST"@"SEL$1" "TEST"@"SEL$2")

USE_HASH(@"SEL$5DA710D3" "TEST"@"SEL$2")

END_OUTLINE_DATA

*/

如上輸出的hint，我們在瞭解上面所做的描述後，應該大體能夠看懂，SEL$1查詢塊對應的作用域是select count(*) from test為SEL$1，SEL$2查詢塊對應的作用域是select status from test where status='Inactive'，而SEL$5DA710D3查詢塊是最佳化器對SQL做展開後為新SQL生成的查詢塊，SQL展開後都只會有一層，不包含子查詢、巢狀查詢等，因此展開後的SQL只會包含一個查詢塊。我們來對上面的outline data部分的關鍵hint做出一些解釋。INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS"))：@"SEL$5DA710D3"代表整個查詢塊的名稱，說明這個hint是全域性hint，"TEST"@"SEL$2"代表引用的物件是在SEL$2查詢塊內的test，("TEST"."STATUS")代表了索引所引用的欄位，這裡也可以直接寫索引名。

n Note：透過dbms_xplan.display_cursor增加outline引數來獲得SQL PLAN的outline data資料是一種可以快速獲得查詢塊、獲得提示的方式，用這些hint可以非常容易獲得、構造出我們需要的hint，也是我本人最喜歡使用的一種方式。其實這些hint都是儲存在v$sql_plan的other_xml欄位中，我們也可以透過轉換函式直接從這個欄位中查詢到這些hint。

SQL>select

2 extractvalue(value(d), '/hint') as outline_hints

3 from

4 xmltable('/*/outline_data/hint'

5 passing (

6 select

7 xmltype(other_xml) as xmlval

8 from

9 v$sql_plan

10 where

11 sql_id = '4ujkuvfura9ys'

12 and child_number = 0

13 and other_xml is not null

14 )

15 ) d;

OUTLINE_hintS

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

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 10)

OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$5DA710D3")

UNNEST(@"SEL$2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

INDEX_FFS(@"SEL$5DA710D3" "TEST"@"SEL$1" ("TEST"."STATUS"))

INDEX(@"SEL$5DA710D3" "TEST"@"SEL$2" ("TEST"."STATUS"))

LEADING(@"SEL$5DA710D3" "TEST"@"SEL$1" "TEST"@"SEL$2")

USE_HASH(@"SEL$5DA710D3" "TEST"@"SEL$2")

我們也可以自己給查詢塊來命名，這是依靠qb_name這個hint來實現：

SQL>select /*+ qb_name(wxh) */count(*) from test where status in (select /*+ qb_name(wxb) index_rs(test t_ind)*/status from test where status='Inactive');

COUNT(*)

----------

100

1 row selected.

SQL>select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 10)

OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$AA13F0C1")

UNNEST(@"WXB")

OUTLINE(@"WXH")

OUTLINE(@"WXB")

INDEX_FFS(@"SEL$AA13F0C1" "TEST"@"WXH" ("TEST"."STATUS"))

INDEX(@"SEL$AA13F0C1" "TEST"@"WXB" ("TEST"."STATUS"))

LEADING(@"SEL$AA13F0C1" "TEST"@"WXH" "TEST"@"WXB")

USE_HASH(@"SEL$AA13F0C1" "TEST"@"WXB")

END_OUTLINE_DATA

*/

Outline Data輸出的hint裡，相關的查詢塊的名稱已經變成我們自己命名的查詢塊的名稱了。

最佳實踐

預設透過SQL Tuning Advisor建立的SQL Profile，是透過一些修正因子來達到修正執行計劃的目的，但是隨著資料的變化，時間的推移，這些SQL Profile裡包含的修正因子可能也已經不再準確，因此在某些使用了SQL Profile的SQL上可能會發現剛開始這些SQL Profile工作的很好，但是不久就會產生一些問題。雖然SQL Tuning Advisor建立的SQL Profile有著這些缺點，但是用它來為一個SQL產生比最佳化器更好的執行計劃也是一個非常好的事，我本人非常喜歡用SQL Tuning Advisor來最佳化一個SQL，然後會去檢視SQL Profile到底提供了什麼建議，然後測試這些建議，如果效能確實有提升，我會考慮接受這些SQL Profile，然後透過本文後面介紹飛方式來鎖定這個SQL的執行計劃，讓SQL Profile達到鎖定執行計劃的目的。既然SQL Profile也可以使用常見的hint，例如index、full等，那麼我們就可以想個辦法以常見的hint替換掉修正因子類的hint(OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)）來達到鎖定執行計劃的目的。如何把透過SQL Tuning Advisor方式建立的SQL Profile轉變為可以直接鎖定執行計劃的SQL Profile？我們再倒回到本章的第一節：使用SQL Tuning Advisor建立了一個SQL Profile，我們看看如何來鎖定這個執行計劃。10GR2後，任何SQL解析後，都會在v$sql_plan的other_xml中儲存outline需要的hint資訊。如：

SQL>select other_xml from v$sql_plan where sql_id='c37q7z5qjnwwf' and other_xml is not null and child_number = 0;

OTHER_XML

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

<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![C

DATA["TEST"]]></info><info type="plan_hash">4130896540</info><info type="plan_ha

sh_2">432850053</info><info type="sql_profile"><![CDATA["profile_c37q7z5qjnwwf_d

wrose"]]></info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_hintS]]></hin

t><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[D

B_VERSION('11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampl

ing' 10)]]></hint><hint><![CDATA[OPT_PARAM('_optimizer_skip_scan_enabled' 'false

')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1

")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS"

))]]></hint></outline_data></other_xml>

輸出的資訊不容易看懂，10GR2後也在display_cursor中增加了outline引數來獲取這一塊的資訊：

SQL>select * from table(dbms_xplan.display_cursor('c37q7z5qjnwwf',0,'outline'));

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 10)

OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS"))

END_OUTLINE_DATA

*/

執行計劃的輸出Outline Data部分的資料就是從v$sql_plan的other_xml裡取出來的，我們也可以直接透過一些轉換函式來把v$sql_plan的other_xml中的值變為我們可以閱讀的方式：

SQL>select

2 extractvalue(value(d), '/hint') as outline_hints

3 from

4 xmltable('/*/outline_data/hint'

5 passing (

6 select

7 xmltype(other_xml) as xmlval

8 from

9 v$sql_plan

10 where

11 sql_id = 'c37q7z5qjnwwf'

12 and child_number = 0

13 and other_xml is not null

14 )

15 ) d;

OUTLINE_hintS

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

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('optimizer_dynamic_sampling' 10)

OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS"))

因此我們可以在透過SQL Tuning Advisor方式使用SQL Profile 後，透過把other_xml中的hint取出來，replace引數設定為true，然後透過dbms_sqltune.import_sql_profile包把這些hint植入，替換之前由SQL Tuning Advisor產生的SQL Profile，這樣就達到了鎖定執行計劃的目的。我本人已經採用這種方式最佳化過很多複雜的SQL，屢試不爽！

SQL>declare

2 l_profile_name varchar2(30);

3 cl_sql_text clob;

4 ar_profile_hints sys.sqlprof_attr;

5 begin

6 select -----從other_xml裡取出hint

7 extractvalue(value(d), '/hint') as outline_hints

8 bulk collect

9 into

10 ar_profile_hints

11 from

12 xmltable('/*/outline_data/hint'

13 passing (

14 select

15 xmltype(other_xml) as xmlval

16 from

17 v$sql_plan

18 where

19 sql_id = 'c37q7z5qjnwwf'

20 and child_number = 0

21 and other_xml is not null

22 )

23 ) d;

24

25 select -----取出sql文字

26 sql_fulltext

27 into

28 cl_sql_text

29 from

30 v$sqlarea

31 where

32 sql_id = 'c37q7z5qjnwwf';

33

34

35 select 'profile_'||'c37q7z5qjnwwf'||'_dwrose'

36 into l_profile_name -----構造profile的name

37 from dual;

38

39 dbms_sqltune.import_sql_profile(

40 sql_text => cl_sql_text,

41 profile =>ar_profile_hints,

42 category => '',

43 name => l_profile_name,

44 force_match =>FALSE,

45 replace => true ------取代之前由SQL Tuning Advisor產生的Profile

46 );

47

48 dbms_output.put_line(' ');

49 dbms_output.put_line('Profile '||l_profile_name||' created.');

50 dbms_output.put_line(' ');

51

52 end;

53 /

PL/SQL procedure successfully completed.

上面的程式碼透過包dbms_sqltune的函式import_sql_profile建立了一個SQL Profile，接受的hint為ar_profile_hints物件，這個物件是從other_xml裡解析出來的，replace引數設定為了true，代表替換之前由SQL Tuning Advisor產生的SQL Profile。我們看看新建立的SQL Profile是否起作用了：

SQL>select count(name) from test where status='Inactive';

COUNT(NAME)

-----------

100

SQL>SQL>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID c37q7z5qjnwwf, child number 0

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

select count(name) from test where status='Inactive'

Plan hash value: 4130896540

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

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

| 0 | SELECT STATEMENT | | | | 218 (100)| |

| 1 | SORT AGGREGATE | | 1 | 21 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 25000 | 512K| 218 (1)| 00:00:03 |

|* 3 | INDEX RANGE SCAN | T_IND | 25000 | | 63 (0)| 00:00:01 |

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

Note

-----

- SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

根據執行計劃的Note部分顯示，剛才建立的SQL Profile已經起作用了，而且SQL Profile的名字也是按照我們命名的格式。我們來看看後臺儲存的hint是什麼樣子的，如果符合預期的話，應該跟other_xml中的hint一致。

SQL>SELECT extractValue(value(h),'.') AS hint

2 FROM sys.sqlobj$data od, sys.sqlobj$ so,

3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h

4 WHERE so.name = 'profile_98p6bqwfau56j_dwrose'

5 AND so.signature = od.signature

6 AND so.category = od.category

7 AND so.obj_type = od.obj_type

8 AND so.plan_id = od.plan_id;

hint

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

IGNORE_OPTIM_EMBEDDED_hintS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS"))

不出所料，SQL Profile裡儲存的hint與當時我們查詢v$sql_plan的other_xml裡的hint一致。

這種方式非常棒，即使用了SQL Tuning Advisor的優點，可以為SQL提供非常好的最佳化建議，又使用到了SQL Profile來鎖定執行計劃，不用擔心修正因子過時導致執行計劃改變的問題了。

SQL Profile（第四篇）

Query Block Name

最佳實踐

相關文章