手工建立SQL profile

我們已經學習瞭如何透過SQL Tuning Advisor來使用SQL Profile，接下來我們來學習一些更高階的知識，如何手工建立一個SQL Profile。本章一開頭我們提到，SQL Profile其實也是透過一些儲存的hint來發揮作用的，那麼這些hint是儲存在什麼地方的？ORACLE 10G版本，可以透過檢視sys.sqlprof$、sys.sqlprof$attr來獲得SQL Profile使用的hint，但是11G後這兩個資料字典基表不再有效，需要透過檢視sys.sqlobj$data、sys.sqlobj$來檢視SQL Profile使用的hint。

我們繼續接著上面一節，看看透過SQL Tuning Advisor建立的SQL Profile使用到的hint。（11G版本）

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 = 'SYS_SQLPROF_01479094feeb0003'

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

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

OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "TEST"@"SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE(default)

這些hint都不是我們日常所用的hint，大部分是以OPT_ESTIMATE打頭的，例如OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)代表的是把表test經過謂詞過濾後返回的基數修正為原始評估的基數乘以0.004，也就是縮小了250倍：基數從25000縮小為100。按照OPT_ESTIMATE提示縮小後的基數非常的準確，由於OPT_ESTIMATE告訴了最佳化器非常準確的基數資訊，因此最佳化器再次評估執行計劃的時候選擇了索引掃描。

就如我們看到的SQL Profile並沒有明確的告訴最佳化器使用索引掃描，只是告訴它應該如何糾正最佳化器的原始評估，以得到更好的基數資訊。但是隨著時間的推移，這些提示資訊可能會變得過時，最終變得不再有效，因此使用了SQL Profile的SQL也可能會遭遇執行計劃發生變化，沒起到鎖定執行計劃的作用。本章後面會介紹如何讓SQL Profile起到鎖定執行計劃的作用。

n Note：SQL Profile裡可能會包含哪些hint？這裡對SQL Profile裡一些常出現的hint做出解釋。

1) OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)

返回10倍於預估的表的基數

2) OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)

返回十分之一的預估的索引的基數

3) OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2)

當test1,test2做join時，返回4.2倍與預估的基數

4) TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107)

為表提供統計資訊：如行數、塊數

5) COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)

為表上的列提供統計資訊：如空值、最大值、最小值等

6) INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107)

為索引提供統計資訊：如索引塊數、索引條目數

7) ALL_ROWS

設定最佳化器的模式為ALL_ROWS

8) IGNORE_OPTIM_EMBEDDED_hintS

忽略嵌入在SQL裡的hint

雖然ORACLE官方只提供了透過SQL Tuning Advisor來建立SQL Profile，但是一些ORACLE的愛好者慢慢的發現了SQL Tuning Advisor底層的運作機制，發現SQL Tuning Advisor其實是透過呼叫dbms_sqltune包的import_sql_profile來建立的SQL Profile。透過import_sql_profile過程，可以為任何的SQL建立想要的SQL Profile。我們來看看import_sql_profile如何使用。

PROCEDURE IMPORT_SQL_PROFILE

Argument Name Type In/Out Default?

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

SQL_TEXT CLOB IN

PROFILE SQLPROF_ATTR IN

NAME VARCHAR2 IN DEFAULT

DESCRIPTION VARCHAR2 IN DEFAULT

CATEGORY VARCHAR2 IN DEFAULT

VALIDATE BOOLEAN IN DEFAULT

REPLACE BOOLEAN IN DEFAULT

FORCE_MATCH BOOLEAN IN DEFAULT

使用IMPORT_SQL_PROFILE來建立SQL Profile需要提供一些引數，SQL_TEXT指SQL語句的文字，我們可以從v$sqlarea的sql_fulltext中獲得SQL語句的完整文字資訊，PROFILE指的是需要為這個SQL文字繫結的hint集合，name為SQL Profile的名稱，DESCRIPTION為對SQL Profile的描述資訊，CATEGORY為SQL Profile所屬的類資訊，預設為default，VALIDATE代表建立的SQL Profile是否有效，預設為true, REPLACE代表是否取代之前存在的SQL Profile，FORCE_MATCH代表採用何種文字標準化方式產生簽名，預設為false。關於FORCE_MATCH的意義，在本章文字標準化與signature一節有詳細解釋。我們來手工建立一個SQL Profile看看：

SQL>exec dbms_sqltune.drop_sql_profile('profile_c37q7z5qjnwwf_dwrose');

PL/SQL procedure successfully completed.

SQL>declare

2 l_profile_name varchar2(30);

3 cl_sql_text clob;

4 begin

5 select sql_fulltext

6 into cl_sql_text

7 from v$sqlarea

8 where sql_id = 'c37q7z5qjnwwf';

9

10 select 'profile_' || 'c37q7z5qjnwwf' || '_dwrose'

11 into l_profile_name

12 from dual;

13 dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,

14 profile =>

15 sqlprof_attr('INDEX_RS_ASC(TEST T_IND)'),

16 category => '',

17 name => l_profile_name,

18 force_match => FALSE);

19 end;

20 /

PL/SQL procedure successfully completed.

我們先透過dbms_sqltune包的drop_sql_profile過程刪除了透過SQL Tuning Advisor建立的SQL Profile，然後透過import_sql_profile手工建立了一個SQL Profile，而且我們使用了我們常見的hint INDEX_RS_ASC(TEST T_IND)，而不是SQL Profile預設的以OPT_ESTIMATE打頭的hint，上面的程式碼已經成功的建立了一個SQL Profile，我們看看使用常規的hint會不會起作用。

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

COUNT(NAME)

-----------

100

1 row selected.

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

PLAN_TABLE_OUTPUT

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

SQL_ID c37q7z5qjnwwf, child number 1

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

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

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

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 512K| 51 (2)| 00:00:01 |

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

Note

-----

- SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

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

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

INDEX_RS_ASC(TEST T_IND)

雖然執行計劃的輸出Note部分顯示已經使用到了SQL Profile，但是執行計劃並沒有如我們預期一樣被改變，依然是全表掃描，檢視儲存hint的基表也顯示索引掃描的hint已經被繫結到了這個SQL上，那麼問題出哪了？

這是由於SQL Profile對於hint是非常挑剔的，SQL Profile裡接受的hint需要提供Query Block Name(初始化引數類的hint不需要提供Query Block Name），否則最佳化器會忽略掉這些hint，我們重新設定SQL Profile的Hints，在Hints中加上Query Block Name看看。（Query Block Name相關知識參考本章Query Block Name一節）

SQL>declare

2 l_profile_name varchar2(30);

3 cl_sql_text clob;

4 begin

5 select sql_fulltext

6 into cl_sql_text

7 from v$sqlarea

8 where sql_id = 'c37q7z5qjnwwf';

9

10 select 'profile_' || 'c37q7z5qjnwwf' || '_dwrose'

11 into l_profile_name

12 from dual;

13 dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,

14 profile =>

15 sqlprof_attr('INDEX_RS_ASC(@SEL$1 TEST@SEL$1 T_IND))'),

16 category => '',

17 name => l_profile_name,

18 force_match => FALSE);

19 end;

20 /

PL/SQL procedure successfully completed.

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

COUNT(NAME)

-----------

100

1 row selected.

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

PLAN_TABLE_OUTPUT

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

SQL_ID c37q7z5qjnwwf, child number 1

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

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

這一次hint起作用了，執行計劃輸出的Note部分可以知道建立的SQL Profile已經起作用了，執行計劃已經走了索引掃描，看來SQL Profile可以接受常規的hint ,只不過這些hint要包含Query Block Name，如果SQL Profile發現指定的hint無效，會簡單的忽略掉這些hint，不會報任何的錯誤，也不會做任何的校驗。既然常規的hint可以對SQL Profile起作用，那麼我們也可以用SQL Profile來鎖定執行計劃了。從上面的執行計劃輸出也可以看到由於我們使用了常規的hint，因此執行計劃的基數資訊並沒有得到糾正，僅僅是透過index_rs_asc這種暴力的hint把執行計劃強制修正為索引掃描了。

SQL Profile（第三篇）

手工建立SQL profile

相關文章