如何清除某條sql在庫緩存中的執(zhí)行計(jì)劃

《如何清除某條sql在庫緩存中的執(zhí)行計(jì)劃》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫。

1、如何清除某條SQL在庫緩存中的執(zhí)行計(jì)劃如何清除某條SQL在庫緩存中的執(zhí)行計(jì)劃個(gè)人分類：OracleDBA日常管理過程中會遇到需要把特定的sql語句的執(zhí)行計(jì)劃從sharedpool中清除或者需要重新生產(chǎn)新的執(zhí)行計(jì)劃的要求。通常的altersystemflushshared_pool;或者重新執(zhí)行信息統(tǒng)計(jì)又會造成較大的影響，所以可以使用oracle提供的dbms_shared_pool.purge來清除某個(gè)特定的sql的執(zhí)行計(jì)劃。下面是關(guān)于dbms_shared_pool.purge的官方介紹：ThesyntaxfortheDBMS_SHARED_POOL.PURGEpackageis:proc

2、edurepurge(namevarchar2,flagcharDEFAULT'P',heapsnumberDEFAULT1);Explanation:Purgethenamedobjectorparticularheap(s)oftheobject.Inputarguments:name:Thenameoftheobjecttopurge.Therearetwokindsofobjects:PL/SQLobjects,triggers,sequences,typesandJavaobjectswhicharespecifiedbyname,指定'address','hash_value'的

3、SQL游標(biāo)。SQLcursorobjectswhicharespecifiedbyatwopartnumber.Thevalueforthisidentifieristheconcatenationofthe'address'and'hash_value'columnsfromthev$sqlareaview.標(biāo)記位是可選的。如果忽略了該參數(shù)，則該包默認(rèn)是包，過程，函數(shù)的名稱并且忽視名稱。否則，該參數(shù)就指定一個(gè)對象類型。標(biāo)記位是大小寫敏感的。flag:Thisisanoptionalparameter.Iftheparameterisnotspecified,thepackageassume

4、sthatthefirstparameteristhenameofapackage/procedure/functionandwillresolvethename.Otherwise,theparameterisacharacterstringindicatingwhatkindofobjecttopurgethenameidentifies.Thestringiscaseinsensitive.Thepossiblevaluesandthekindsofobjectstheyindicatearegiveninthefollowingtable:ValueKindofObjecttokee

5、p---------------------------Ppackage/procedure/functionQsequenceRtriggerTtypeJSjavasourceJCjavaclassJRjavaresourceJDjavashareddataCcursor--游標(biāo)heaps:heapstopurge.e.gifheap0andheap6aretobepurged.1<<0

6、1<<6=>hex0x41=>decimal65.sospecifyheaps=>65.Defaultis1i.eheap0whichmeansthewholeobjectwillbepurged.實(shí)驗(yàn)環(huán)

7、境版本：11.2.0.1.0dbms_shared_pool.purge只能通過dba才能使用oracle@rac1:rac1/home/oracle>sqlplusyang/yangSQL*Plus:Release11.2.0.1.0ProductiononFriJul815:32:432011yang@rac1>createtableyangtab(idint);Tablecreated.yang@rac1>select*fromyangtab;norowsselectedyang@rac1>selectaddress,hash_value,executions,parse_calls2

8、fromv$sqlwheresql_textlike'select*fromyangtab%';ADDRESSHASH_VALUEEXECUTIONSPARSE_CALLS-----------------------------------------------00000001736EFBB8333795103611yang@rac1>execdbms_shared_pool.purge('0000000