建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
本篇內(nèi)容介紹了“如何手工創(chuàng)建SQL Profile”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
創(chuàng)新互聯(lián)建站是專(zhuān)業(yè)的化德網(wǎng)站建設(shè)公司，化德接單;提供網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì),網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專(zhuān)業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行化德網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專(zhuān)業(yè)做搜索引擎喜愛(ài)的網(wǎng)站,專(zhuān)業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!
手工創(chuàng)建SQL profile
ORACLE 10G版本，可以通過(guò)查看sys.sqlprof$、sys.sqlprof$attr來(lái)獲得SQL Profile使用的hint，但是11G后這兩個(gè)數(shù)據(jù)字典基表不再有效，需要通過(guò)查看sys.sqlobj$data、sys.sqlobj$來(lái)查看SQL Profile使用的hint。
我們繼續(xù)接著上面一節(jié)，看看通過(guò)SQL Tuning Advisor創(chuàng)建的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都不是我們?nèi)粘Ｋ玫膆int，大部分是以O(shè)PT_ESTIMATE打頭的，例如OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)代表的是把表test經(jīng)過(guò)謂詞過(guò)濾后返回的基數(shù)修正為原始評(píng)估的基數(shù)乘以0.004，也就是縮小了250倍：基數(shù)從25000縮小為100。按照OPT_ESTIMATE提示縮小后的基數(shù)非常的準(zhǔn)確，由于OPT_ESTIMATE告訴了優(yōu)化器非常準(zhǔn)確的基數(shù)信息，因此優(yōu)化器再次評(píng)估執(zhí)行計(jì)劃的時(shí)候選擇了索引掃描。
就如我們看到的SQL Profile并沒(méi)有明確的告訴優(yōu)化器使用索引掃描，只是告訴它應(yīng)該如何糾正優(yōu)化器的原始評(píng)估，以得到更好的基數(shù)信息。但是隨著時(shí)間的推移，這些提示信息可能會(huì)變得過(guò)時(shí)，最終變得不再有效，因此使用了SQL Profile的SQL也可能會(huì)遭遇執(zhí)行計(jì)劃發(fā)生變化，沒(méi)起到鎖定執(zhí)行計(jì)劃的作用。本章后面會(huì)介紹如何讓SQL Profile起到鎖定執(zhí)行計(jì)劃的作用。
n Note：SQL Profile里可能會(huì)包含哪些hint？這里對(duì)SQL Profile里一些常出現(xiàn)的hint做出解釋。 1) OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10) 返回10倍于預(yù)估的表的基數(shù) 2) OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1) 返回十分之一的預(yù)估的索引的基數(shù) 3) OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2) 當(dāng)test1,test2做join時(shí)，返回4.2倍與預(yù)估的基數(shù) 4) TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107) 為表提供統(tǒng)計(jì)信息：如行數(shù)、塊數(shù) 5) COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207) 為表上的列提供統(tǒng)計(jì)信息：如空值、最大值、最小值等 6) INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107) 為索引提供統(tǒng)計(jì)信息：如索引塊數(shù)、索引條目數(shù) 7) ALL_ROWS 設(shè)置優(yōu)化器的模式為ALL_ROWS 8) IGNORE_OPTIM_EMBEDDED_hintS 忽略嵌入在SQL里的hint |
雖然ORACLE官方只提供了通過(guò)SQL Tuning Advisor來(lái)創(chuàng)建SQL Profile，但是一些ORACLE的愛(ài)好者慢慢的發(fā)現(xiàn)了SQL Tuning Advisor底層的運(yùn)作機(jī)制，發(fā)現(xiàn)SQL Tuning Advisor其實(shí)是通過(guò)調(diào)用dbms_sqltune包的import_sql_profile來(lái)創(chuàng)建的SQL Profile。通過(guò)import_sql_profile過(guò)程，可以為任何的SQL創(chuàng)建想要的SQL Profile。我們來(lái)看看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來(lái)創(chuàng)建SQL Profile需要提供一些參數(shù)，SQL_TEXT指SQL語(yǔ)句的文本，我們可以從v$sqlarea的sql_fulltext中獲得SQL語(yǔ)句的完整文本信息，PROFILE指的是需要為這個(gè)SQL文本綁定的hint集合，name為SQL Profile的名稱(chēng)，DESCRIPTION為對(duì)SQL Profile的描述信息，CATEGORY為SQL Profile所屬的類(lèi)信息，默認(rèn)為default，VALIDATE代表創(chuàng)建的SQL Profile是否有效，默認(rèn)為true, REPLACE代表是否取代之前存在的SQL Profile，F(xiàn)ORCE_MATCH代表采用何種文本標(biāo)準(zhǔn)化方式產(chǎn)生簽名，默認(rèn)為false。關(guān)于FORCE_MATCH的意義，在本章文本標(biāo)準(zhǔn)化與signature一節(jié)有詳細(xì)解釋。我們來(lái)手工創(chuàng)建一個(gè)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.
|
我們先通過(guò)dbms_sqltune包的drop_sql_profile過(guò)程刪除了通過(guò)SQL Tuning Advisor創(chuàng)建的SQL Profile，然后通過(guò)import_sql_profile手工創(chuàng)建了一個(gè)SQL Profile，而且我們使用了我們常見(jiàn)的hint INDEX_RS_ASC(TEST T_IND)，而不是SQL Profile默認(rèn)的以O(shè)PT_ESTIMATE打頭的hint，上面的代碼已經(jīng)成功的創(chuàng)建了一個(gè)SQL Profile，我們看看使用常規(guī)的hint會(huì)不會(huì)起作用。
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)
|
雖然執(zhí)行計(jì)劃的輸出Note部分顯示已經(jīng)使用到了SQL Profile，但是執(zhí)行計(jì)劃并沒(méi)有如我們預(yù)期一樣被改變，依然是全表掃描，查看存儲(chǔ)hint的基表也顯示索引掃描的hint已經(jīng)被綁定到了這個(gè)SQL上，那么問(wèn)題出哪了？
這是由于SQL Profile對(duì)于hint是非常挑剔的，SQL Profile里接受的hint需要提供Query Block Name(初始化參數(shù)類(lèi)的hint不需要提供Query Block Name），否則優(yōu)化器會(huì)忽略掉這些hint，我們重新設(shè)置SQL Profile的Hints，在Hints中加上Query Block Name看看。（Query Block Name相關(guān)知識(shí)參考本章Query Block Name一節(jié)）
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起作用了，執(zhí)行計(jì)劃輸出的Note部分可以知道創(chuàng)建的SQL Profile已經(jīng)起作用了， 執(zhí)行計(jì)劃已經(jīng)走了索引掃描，看來(lái)SQL Profile可以接受常規(guī)的hint ,只不過(guò)這些hint要包含Query Block Name，如果SQL Profile發(fā)現(xiàn)指定的hint無(wú)效，會(huì)簡(jiǎn)單的忽略掉這些hint，不會(huì)報(bào)任何的錯(cuò)誤，也不會(huì)做任何的校驗(yàn)。既然常規(guī)的hint可以對(duì)SQL Profile起作用，那么我們也可以用SQL Profile來(lái)鎖定執(zhí)行計(jì)劃了。從上面的執(zhí)行計(jì)劃輸出也可以看到由于我們使用了常規(guī)的hint，因此執(zhí)行計(jì)劃的基數(shù)信息并沒(méi)有得到糾正，僅僅是通過(guò)index_rs_asc這種暴力的hint把執(zhí)行計(jì)劃強(qiáng)制修正為索引掃描了。
“如何手工創(chuàng)建SQL Profile”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
本文名稱(chēng)：如何手工創(chuàng)建SQLProfile
分享鏈接：http://fisionsoft.com.cn/article/piodis.html
