溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“如何手工創建SQL Profile”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 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”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
