性能測(cè)試調(diào)優(yōu)方案知識(shí)匯總

1、一數(shù)據(jù)庫(kù)實(shí)例優(yōu)化、1 oracle_Shared Pool 優(yōu)化和 Library Cache Latch沖突優(yōu)化簡(jiǎn)介本文檔旨在介紹從Oracle 7到Oracle 11g shared pool調(diào)優(yōu)的關(guān)鍵問題。特別對(duì)于存在下列問 題的 系統(tǒng)非常重要： library cache latch/es 或者 latch:library cache 之類的 Latch 爭(zhēng)用 shared pool latch 或者 latch:shared pool 之類的 Latch 爭(zhēng)用高 CPU解析時(shí)間 V$LIBRARYCACHE 中的高 reloads多版本的cursors大量的 parse call經(jīng)常

2、發(fā)生ORA-04031錯(cuò)誤Troubleshooting Steps什么是 shared poo ？Oracle在SGA的一個(gè)特定區(qū)域中保留SQL語(yǔ)句packages,對(duì)象信息以及其它一些內(nèi)容，這就是大 家熟悉的shared pool。這個(gè)共享內(nèi)存區(qū)域是由一個(gè)復(fù)雜的cache和heap manager構(gòu)成 的。它需要 解決三個(gè)基本問題：每次分配的內(nèi)存大小是不一致的，從幾個(gè)字節(jié)到上千個(gè)字節(jié)；因?yàn)閟hared pool的目的是為了最大化共享信息，所以不是每次一個(gè)用戶用完之后就可以釋放這段內(nèi)存（在傳統(tǒng)的 heap man ager方式會(huì)遇到這個(gè)問題）。內(nèi)存中的信息可能對(duì)于其他session來說是有用

3、的Oracle并不能事先知道這些內(nèi)容是否會(huì)被再次用到；Shared pool中的內(nèi)容不能被寫入到硬盤區(qū)域中，這一點(diǎn)和傳統(tǒng)cache是不一樣的。只有H可重建的信息可以被覆蓋，因?yàn)樗麄兛梢栽谙麓涡枰獣r(shí)重建?；谶@些背景，我們就可以理解shared pool的管理是一件非常復(fù)雜的事情。下面的章節(jié)列出了一些影響shared pool性能和它相關(guān)的latch的關(guān)鍵問題，包括：專用術(shù)語(yǔ)Literal SQL一個(gè)Literal SQL語(yǔ)句是指在predicate中使用具體值，而不是使用綁定變量，即不同的執(zhí)行語(yǔ)句使用 的具體值可能是不一樣的。例1:應(yīng)用程序使用了：SELECT * FROM emp WHERE

4、 en ame=CLARK：而不是：SELECT * FROM emp WHERE en ame=:bi nd1;例2:以下語(yǔ)句不用綁定變量但是也不會(huì)被認(rèn)為是literal SQL，因?yàn)檫@個(gè)語(yǔ)句可以被多次執(zhí)行共享。SELECT sysdate FROM dual;例3:如果整個(gè)應(yīng)用都是用相同的值20來檢查version的話，那么這個(gè)語(yǔ)句可以被認(rèn)為是可以共享的。SELECT version FROM app_version WHERE versio n2.0;Hard Parse （硬解析）如果一個(gè)新的SQL被發(fā)起，但是又不在shared pool里面的話，它將被完整的解析一次。例 如： Ora

5、cle必須在shared pool中分配內(nèi)存，檢查句法和語(yǔ)義等等這被稱為hard parse,它在CPU使用和latch獲取上的都是非常消耗資源的。Soft Parse （軟解析）如果一個(gè)session發(fā)起一個(gè)已經(jīng)在shared pool中的SQL語(yǔ)句并且它可以使用一個(gè)當(dāng)前存在的 版本， 那么這個(gè)過程被稱為一個(gè)soft parsed對(duì)于應(yīng)用來說，它只需請(qǐng)求解析這個(gè)語(yǔ)句。完全相同的語(yǔ)句？如果兩個(gè)SQL語(yǔ)句的含義相同但是沒有使用相同的字符，那么Oracle認(rèn)為它們是不同的語(yǔ)句。比如SCOTT在一個(gè)Session中提交的這兩個(gè)語(yǔ)句：SELECT ENAME from EMP;SELECT en am

6、e from emp;盡管它們實(shí)際上是相同的，但是因?yàn)榇髮懽帜福?E和小寫字母5e的區(qū)別，他們不會(huì)被認(rèn)為是完全相同的語(yǔ)句。Sharable SQL如果是兩個(gè)不同的session發(fā)起了完全相同的SQL語(yǔ)句，這也不意味著這個(gè)語(yǔ)句是可以共享 的。比 如說：用戶SCOTT下有一個(gè)表EMP，發(fā)起了下面的語(yǔ)句：SELECT ENAME from EMP;用戶FRED有一個(gè)自己的表也叫EMP并且發(fā)起相同的語(yǔ)句：SELECT ENAME from EMP;盡管語(yǔ)句完全一樣但是由于需要訪問的EMP表是不同的對(duì)象，所以需要對(duì)這條語(yǔ)句產(chǎn)生不同的版本。 有很多條件來判斷兩個(gè)完全一致的SQL文本是不是真的是完全相同（以

7、至于他們可以被共享），包括：語(yǔ)句中引用的所有的對(duì)象名必須都被解析成實(shí)際相同的對(duì)象發(fā)起語(yǔ)句的session中的optimizer相關(guān)的參數(shù)應(yīng)該一致*綁定變量的類型和長(zhǎng)度應(yīng)該是”相似的”（這里不做詳細(xì)討論，但是類型和長(zhǎng)度的不同確實(shí)會(huì)導(dǎo)致語(yǔ)句被分為不同的版本）發(fā)起語(yǔ)句的NLS （National Language Support）設(shè)置必須相同語(yǔ)句的版本正如之前在Sharable SQL沖描述的，如果兩個(gè)語(yǔ)句字面上完全相同但是又不能被共享，則會(huì)對(duì)相同的語(yǔ)句產(chǎn)生不同的version，即版本。如果Oracle要匹配一個(gè)包含多個(gè)版本的語(yǔ)句，它將不得 不檢查每一個(gè)版本來看它們是不是和當(dāng)前被解析的語(yǔ)句完全相同。

8、所以最好用以下方法來避免高版本數(shù)（high version count）:*客戶端使用的綁定變量最大長(zhǎng)度需標(biāo)準(zhǔn)化如果有大量的schema會(huì)包含相同名字的對(duì)象，那么避免使用一個(gè)相同的SQL語(yǔ)句。比女口：SELECT xx FROM MYTABLE ;并且每個(gè)用戶都有一個(gè)自己的MYTABLE的情 況 在 Oracle 8.1 可以將 _SQLEXEC_PROGRESSION_COST 設(shè)置成0Library Cache 和 Shared Pool latchesshared pool latch是用來保護(hù)從shared pool中分配和釋放內(nèi)存的關(guān)鍵性操作。Library cache latche

9、 （以及 Oracle 7.1 中的 library cache pin latch）是用來保護(hù) library cache 中的 操作。所有的這些Latch都是潛在的資源爭(zhēng)用的對(duì)象，latch gets發(fā)生的次數(shù)直接受到shared pool中活動(dòng)（activity）個(gè)數(shù)的影響，特別是parse操作。任何減少latch gets或者shared pool中活 動(dòng) （activity）個(gè)數(shù)的嘗試都有助于提高性能和可擴(kuò)展性。Literal SQL 和 Shared SQL 的比較這一個(gè)小章節(jié)中描述了 literal SQL和sharable SQL各自的優(yōu)點(diǎn)：Literal SQL 在有完整的統(tǒng)

10、計(jì)信息并且 SQL語(yǔ)句在predicate （限定條件）中使用具體值時(shí)，基于成本的 優(yōu)化器（CBO）能工作的最好。比較下面的語(yǔ)句：SELECT dist in ct cust_ref FROM orders WHERE total_cost 10000.0; 和SELECT dist in ct cust_ref FROM orders WHERE total_cost :bi ndA;對(duì)于第一個(gè)語(yǔ)句，CBO可以使用已經(jīng)收集的histogram來判斷是否使用全表掃描比使用TOTAL_COST列上索引掃描快（假設(shè)有索引的話）。第二個(gè)語(yǔ)句CBO并不知道綁定變量:bindA對(duì) 應(yīng)行數(shù)的比例，因?yàn)樵摻?/p>

11、定變量沒有一個(gè)具體的值以確定執(zhí)行計(jì)劃。例：:bindA可以是 0.0 或者 99999999999999999.9。Orders表上兩個(gè)不同的執(zhí)行路徑的響應(yīng)時(shí)間可能會(huì)不同，所以當(dāng)你需要CBO為你選出最好的執(zhí)行計(jì)劃的時(shí)候，選用使用literal語(yǔ)句會(huì)更好。在一個(gè)典型的Decision Support Systems （決策 支持系統(tǒng)）中，重復(fù)執(zhí)行標(biāo)準(zhǔn)語(yǔ)句的時(shí)候非常少，所以共享一個(gè)語(yǔ)句的幾率很小，而且花在Parse上的CPU時(shí)間只占每個(gè)語(yǔ)句執(zhí)行時(shí)間的非常小一部分，所以更重要的是給optimizer盡可 能詳細(xì)的信息，而不是縮短解析時(shí)間。Sharable SQL如果應(yīng)用使用了 literal （無共

12、享）SQL，則會(huì)嚴(yán)重限制可擴(kuò)展性和生產(chǎn)能力。在對(duì)CPU的需求、library cache和shared pool latch的獲取和釋放次數(shù)方面，新SQL語(yǔ)句的parse成本很 高。 比如：僅僅parse 一個(gè)簡(jiǎn)單的語(yǔ)句就可能需要獲取和釋放library cache latch 20或者30次。除非它是一個(gè)臨時(shí)的或者不常用的SQL，并且需要讓CBO得到盡可能多的信息來生成一個(gè)好的執(zhí)行計(jì)劃，否則最好讓所有的SQL是共享的。減輕Shared Pool負(fù)載Parse 一次并執(zhí)行多次 在OLTP類型的應(yīng)用中，最好的方法是只讓一個(gè)語(yǔ)句被解析一次，然后保持這個(gè)cursor的打開狀態(tài)，在需要的時(shí)候重復(fù)執(zhí)行它

13、。這樣做的結(jié)果是每個(gè)語(yǔ)句只被Parse 了一次（不管是soft parse 還是hard parse）。顯然，總會(huì)有些語(yǔ)句很少被執(zhí)行，所以作為一個(gè)打開的cursor維護(hù)它們是一種浪費(fèi)。請(qǐng)注意一個(gè)session最多只能使用參數(shù):open_cursors定義的cursor數(shù)，保持cursor打開會(huì)增 力口總 體open cursors的數(shù)量。OCI中開發(fā)者能直接控制cursor，在預(yù)編譯器中，HOLD_CURSOR參數(shù)控制cursor是否被保持打 開。消除 Literal SQL如果你有一個(gè)現(xiàn)有的應(yīng)用程序，你可能沒法消除所有的literal SQL，但是你還是得設(shè)法消除其中一部分會(huì)產(chǎn)生問題的語(yǔ)句。

14、從V$SQLAREA視圖可能找到適合轉(zhuǎn)為使用綁定變量的語(yǔ)句。下面的查詢列出SGA中有大量相似語(yǔ)句的SQL :SELECT substr(sql_text,1,40) SQL,cou nt(*),sum(executi ons) TotExecsFROM v$sqlareaWHERE executio ns 30ORDER BY 2J在10g以上的版本可以用下面的語(yǔ)句 ：SET pages 10000SET linesize 250column FORCE_MATCHING_SIGNATURE format 99999999999999999999999WITH c AS(SELECT FORC

15、E_MATCHING_SIGNATURE,COUNT(*) entFROM v$sqlareaWHERE FORCE_MATCHING_SIGNATURE!=0GROUP BY FORCE_MATCHING_SIGNATUREHAVING COUNT(*) 20)sq AS(SELECT sql_text ,FORCE_MATCHING_SIGNATURE,row_number() over (partitio n BY FORCE_MATCHING_SIGNATURE ORDER BY sqldDESC) pFROM v$sqlarea sWHERE FORCE_MATCHING_SIGNA

16、TURE IN(SELECT FORCE_MATCHING_SIGNATUREFROM c)SELECT sq.sql_text ,sq.FORCE_MATCHING_SIGNATURE,t un shared coun t FROM c, sqWHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATUREAND sq.p =1ORDER BY t DESC對(duì)SQL語(yǔ)句，去掉重復(fù)的空格（不包括字符常量），將大小寫轉(zhuǎn)換成相同，比如均為大寫（不包括字符常量）后，如果 SQL相同，那么SQL語(yǔ)句的exact_matching_signatur

17、e就是相同的。對(duì)SQL語(yǔ)句，去掉重復(fù)的空格（不包括字符常量），將大小寫轉(zhuǎn)換成相同，比如均為大寫（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL語(yǔ)句的force_matching_signature就是相同的。但是例外的情況是：如果 SQL中有綁定變量， force_matching_signature 就會(huì)與 exact_matching_signature 樣的生成標(biāo)準(zhǔn)?？梢钥吹?，現(xiàn)在 exact_matching_signature 與 force_matching_signature 完全一樣了。從force_matching_signature的特性，我們可以想到

18、一個(gè)用途，用于查找沒有使用綁定變量 的SQL 語(yǔ)句，類似于使用plan_hash_value來查找。注意：如果系統(tǒng)中有l(wèi)ibrary cache latch爭(zhēng)用的問題，上面的語(yǔ)句會(huì)導(dǎo)致爭(zhēng)用加劇。值40,5和30只是示例，這個(gè)查詢查找前40個(gè)字符相同的，只被執(zhí)行過很少次數(shù)，而又全少在shared pool里出現(xiàn)30次的語(yǔ)句。通常來說，literal語(yǔ)句以下面的形式開始，并且每個(gè)語(yǔ)句的前 面部分字符是相同的：SELECT col1,col2,col3 FROM table WHERE .注意：在轉(zhuǎn)化literal SQL使用綁定變量時(shí)有一定程度的限制。請(qǐng)放心我們已經(jīng)反復(fù)證明轉(zhuǎn)化那些經(jīng)常執(zhí)行的語(yǔ)句會(huì)

19、消除 shared pool的問題并且能顯著提高可擴(kuò)展性。請(qǐng)查看你的應(yīng)用中使用的工具的文檔來決定如何在語(yǔ)句中使用綁定變量。避免 Invalidations有些命令會(huì)將cursor的狀態(tài)變成成INVALIDATE。這些命令直接修改cursor相關(guān)對(duì)象的上 下文環(huán) 境。它包括TRUNCATE,表或索弓I上的ANALYZE或DBMS_STATS.GATHER_XXX關(guān)聯(lián)對(duì)象的權(quán) 限變更。相對(duì)應(yīng)的cursor會(huì)留在SQLAREA中，但是下次被引用時(shí)會(huì)被完全reload并重新parse,所以會(huì)對(duì)數(shù)據(jù)庫(kù)的整體性能造成影響。下面的查詢可以幫我們找到 In validation較多的cursor:SELECT

20、 SUBSTR （sql_text, 1,40） SQL, in validati ons FROM v$sqlareaORDER BY in validatio ns DESC;更多的詳細(xì)信息，請(qǐng)參考Note:115656.1和Note:123214.1CURSOR_SHARING 參數(shù)（816 以上）（在 Oracle8.1.6 引入）.這個(gè)參數(shù)需要小心使用。如果它被設(shè)為FORCE ,那么Oracle會(huì)盡可能用系統(tǒng)產(chǎn)生的綁定變量來替換 原來SQL中的literals部分。對(duì)于很多僅僅是literal不一樣的相似的語(yǔ)句，這會(huì)讓 它們共享cursor。 這個(gè)參數(shù)可以在系統(tǒng)級(jí)別或者session

21、級(jí)別動(dòng)態(tài)設(shè)置：ALTER SESSION SET cursor_shari ng = FORCE;或者ALTER SYSTEM SET cursor_shari ng = FORCE; 或者在init.ora中設(shè)置注意：因?yàn)镕ORCE會(huì)導(dǎo)致系統(tǒng)產(chǎn)生的綁定變量替換literal,優(yōu)化器（CBO）可能會(huì)選擇一個(gè)不同的執(zhí)行計(jì)劃，因?yàn)槟軌虍a(chǎn)生最好執(zhí)行計(jì)劃的litera I值已經(jīng)不存在了。在Oracle9i （以上），可以設(shè)置CURSOR_SHARING=SIMILAR。如果這些語(yǔ)句只是literal部分不同，并且這些literal不會(huì)對(duì)SQL的含義有影響，或者可能會(huì)導(dǎo)致使用不同的執(zhí)行計(jì)劃，那么 SIM

22、ILAR會(huì)共享這些語(yǔ)句。此增強(qiáng)功能適用于當(dāng)FORCE會(huì)產(chǎn)生一個(gè)不同并且不是想要的執(zhí)行計(jì)劃時(shí)，從而提高了參數(shù)CURSOR_SHARING的可用性。設(shè)置CURSOR_SHARING=SIMILAR，Oracle會(huì)決定哪些literals可以被”安全”的替換成綁定變 量，這樣 做的結(jié)果是有些SQL在可能產(chǎn)生更好執(zhí)行計(jì)劃的時(shí)候也不會(huì)被共享。關(guān)于這個(gè)參數(shù)的更多詳細(xì)信息，請(qǐng)參考 Note:94036.1。注意:Similar在Oracle 12中不推薦使用。（譯者注：根據(jù)Note:1169017.1，Oracle12將會(huì)移除cursor_sharing = SIMILAR的設(shè)置，而且在11g中就已經(jīng)不推薦

23、使用了，因?yàn)橛蠥daptive Cursor Shari ng 的新特性）請(qǐng)參考：Docume nt:1169017.1 ANNOUNCEMENT: Deprecati ng the cursor_shari ng = SIMILAR sett ingSESSION_CACHED_CURSORS 參數(shù)是一個(gè)可以在instanee級(jí)別或者session級(jí)別設(shè)置的數(shù)值參數(shù)：ALTER SESSION SET session_cached_cursors = NNN; 數(shù)值NNN決定在一個(gè)session中可以被cached的 cursor的個(gè)數(shù)。當(dāng)一個(gè)語(yǔ)句被parse的時(shí)候，Oracle會(huì)首先檢查s

24、ession的私有緩存中指向的語(yǔ)句，如果有可被共享的語(yǔ)句版本的話，它就可以被使用。這為經(jīng)常被parse的語(yǔ)句提供了一個(gè)捷徑，可以比soft或者h(yuǎn)ard parse使用更少的CPU和非常少的Latch get。為了被緩沖在session緩存中，同樣的語(yǔ)句必須在相同的cursor中被parse 3次，之后一個(gè)指向 shared cursor的指針會(huì)被添加到你的session緩存中。如果session緩存cursor已達(dá)上限，則最近 最少使用的那一個(gè)會(huì)被替換掉。如果你還沒有設(shè)置這個(gè)參數(shù)，建議先設(shè)置為50作為初始值。之后查看bstat/estat報(bào)告的統(tǒng)計(jì) 信息章 節(jié)的session cursor c

25、ache hits的值，從這個(gè)值可以判斷cursor緩存是否有作用。如果有必要的話，可以增加或者減少cursor緩存的值。SESSION_CACHED_CURSORS對(duì)于forms經(jīng)常 被打開和關(guān)閉的Oracle Forms應(yīng)用非常有用。CURSOR_SPACE_FOR_TIME 參數(shù)控制同一個(gè)語(yǔ)句不同執(zhí)行之間一個(gè)cursor是否部分被保持（pin）住。如果設(shè)置其他參數(shù)都沒效果的話，就值得嘗試這個(gè)參數(shù)。這個(gè)參數(shù)在有不經(jīng)常被使用的共享語(yǔ)句，或者有非常多的cursor被pinning / unpinning的時(shí)候是有幫助的。（查看視圖：v$latch_misses -如果大多 數(shù) latch 等待

26、是因?yàn)?cursor 的 pinning 和 unpinning 導(dǎo)致的kglpnc: child和kglupc: child）.你必須保證shared pool對(duì)于工作負(fù)載來說是足夠大的，否則性能會(huì)受到嚴(yán)重影響而且最終會(huì)產(chǎn)生ORA-4O31 錯(cuò)誤。如果你把這個(gè)參數(shù)設(shè)為TRUE,請(qǐng)留意：如果SHARED_POOL對(duì)于工作負(fù)載來說太小的話更容易產(chǎn)生ORA-4031錯(cuò)誤。如果你的應(yīng)用有 cursor泄漏，那么泄漏的cursor會(huì)浪費(fèi)大量?jī)?nèi)存并在一段時(shí)間的運(yùn)行之后 對(duì)性能產(chǎn)生負(fù)面影響。目前已知的設(shè)置為true可能會(huì)導(dǎo)致的問題：Bug:770924 (Fixed 8061 and 8160) ORA-

27、600 17302 may occurBug:897615 (Fixed 8061 and 8160) Garbage Explain Plan over DBLINKBug:1279398 (Fixed 8162 and 8170) ORA-600 17182 from ALTER SESSION SET NLS.CLOSE_CACHED_OPEN_CURSORS 參數(shù)這個(gè)參數(shù)已經(jīng)在Oracle8i被廢棄。控制當(dāng)一個(gè)事務(wù)提交時(shí)是否PL/SQL cursor被關(guān)閉。默認(rèn)值是FALSE，該設(shè)置在不同commits之后 保持PL/SQL cursor打開以減少hard parse的次數(shù)。如果設(shè)成T

28、RUE的話可能會(huì)增加SQL在不用的 時(shí)候被從shared pool中清除出去的可能性。SHARED_POOL_RESERVED_SIZE 參數(shù)已經(jīng)有相當(dāng)多的文檔解釋過參數(shù)。這個(gè)參數(shù)在Oracle 7.1.5被引進(jìn)，它把shared pool的一部分預(yù)留出來用于較大內(nèi)存的分配。這個(gè)預(yù)留區(qū)域是從shared pool自身劃分出來的。從實(shí)踐角度來說我們應(yīng)該把 SHARED_POOL_RESERVED_SIZE設(shè)成 SHARED_POOL_SIZE 的 10%，除非 shared pool 非常大或 者 SHARED_POOL_RESERVED_MIN_ALLOC被設(shè)得小于默認(rèn)值：*如果shared

29、pool非常大的話，設(shè)成10%會(huì)浪費(fèi)很多內(nèi)存因?yàn)榭赡茉O(shè)成幾MB就夠用了。如果 SHARED_POOL_RESERVED_MIN_ALLOC被設(shè)的較小，則很多的空間請(qǐng)求都會(huì)符合從 保留空間中分配的條件，那么10%也許就不夠了。查看視圖的FREE_SPACE列可以很容易監(jiān)控保留區(qū)域的使用情況。SHARED_POOL_RESERVED_MIN_ALLOC 參數(shù)在Oracle8i這個(gè)參數(shù)是隱藏的.盡管有些情況下SHARED_POOL_RESERVED_MIN_ALLOC設(shè)成4100或者4200可能對(duì)緩 解較大 壓力下的shared pool的沖突有幫助，但是在大多數(shù)情況下應(yīng)保持默認(rèn)值。SHARED_P

30、OOL_SIZE 參數(shù)控制shared pool自己的大小，它能對(duì)性能造成影響。如果太小，則共享的信息會(huì)被從共享池中交換 出去，過一陣子有需要被重新裝載(重建)。如果literal SQL使用較多而且shared pool又很大，長(zhǎng)時(shí) 間使用后內(nèi)部?jī)?nèi)存freelist上會(huì)產(chǎn)生大量小的內(nèi)存碎片，使得 shared poollatch被持有的時(shí)間變長(zhǎng)，進(jìn)而導(dǎo)致性能問題。在這種情況下，較小的shared pool也許比較大的shared pool好。因?yàn)锽ug:986149的改進(jìn)，這個(gè)問題在8.0.6和8.1.6以上版本被大大減 少了。.注意：一定要避免由于shared pool設(shè)置過大進(jìn)而導(dǎo)致的s

31、wap的發(fā)生的情況，因?yàn)楫?dāng)swap發(fā)生的 時(shí)候性能會(huì)急劇下降。參考Note:1012046.6來根據(jù)工作量計(jì)算SHARED_POOL_SIZE需要的大小。_SQLEXEC_PROGRESSION_COST parameter 參數(shù)（81.5 以上）這是一個(gè)Oracle 8.1.5引入的隱含參數(shù)。這里提到它是因?yàn)槟J(rèn)設(shè)置可能導(dǎo)致SQL共享方面的一些問題。設(shè)置成0會(huì)避免在shared pool中產(chǎn)生語(yǔ)句高版本的問題。例：在init.ora文件中增加這個(gè)參數(shù)# _SQLEXEC_PROGRESSION_COST 并且設(shè)成 0 來避免 SQL 共享問題 #參考 Note:62143.1 獲取更多信息_

32、sqlexec_progressi on _cost=0注意設(shè)成0的一個(gè)副作用會(huì)導(dǎo)致V$SESSION_LONGOPS視圖中不記錄長(zhǎng)時(shí)間運(yùn)行的查詢。參考Note:68955.1獲取關(guān)于這個(gè)參數(shù)的更多信息。預(yù)編譯器的 HOLD_CURSOR 和 RELEASE_CURSOR 選項(xiàng)當(dāng)使用Oracle預(yù)編譯器預(yù)編譯程序的時(shí)候，shared pool的行為可以通過參數(shù)RELEASE_CURSOR和HOLD_CURSOR來控制。這些參數(shù)可以決定當(dāng)cursor執(zhí)行完畢之 后 library cache 禾口 session cache 中 cursor 的狀態(tài)。關(guān)于這個(gè)參數(shù)的更多信息，請(qǐng)參考Note:73

33、922.1將 cursor 固定（pinning） 在 shared pool 中另外一種減少library cache latch使用的方法是將cursor固定在shared pool中，詳見以下文檔：Note:130699.1 How to Reduce LI BRARY CACHE LATCH C onten ti on Using a Procedure to KEEP Cursors Executed 10 timesDBMS_SHARED_POOL .KEEP這個(gè)存儲(chǔ)過程（RDBMS/ADMIN目錄下的DBMSPOOL.SQL腳本中有定義）可以用來將對(duì) 象 KEEP 到 share

34、d pool 中，DBMS_SHARED_POOL.KEEP 可以KEEP packages, procedures, functions, triggers （7.3+）和 sequences （ + ），在 Note:61760.1 中有完整的描述。通常情況下，建議將那些需要經(jīng)常使用的package 一直keep在shared pool中。KEEP操作在數(shù)據(jù)庫(kù)啟動(dòng)后需要盡快實(shí)施，因?yàn)樵趕hutdown之后Oracle不會(huì)自動(dòng)重新keep這些對(duì)象。注意:在Oracle 7.2之前DBMS_SHARED_POOL.KEEP實(shí)際上不會(huì)把需要KEEP的對(duì)象完整 的放到 shared pool中。所

35、以建議在每一個(gè)要被KEEP的package中放一個(gè)空的存儲(chǔ)過程，在執(zhí)行完DBMS_SHARED_POOL.KEEP之后再調(diào)用一下這個(gè)空存儲(chǔ)過程來保證對(duì)象被完全裝 載。這在 7.2之后已經(jīng)修復(fù)了。Flushing （清空）SHARED POOL在使用大量literal SQL的系統(tǒng)中，shared pool隨時(shí)間推移會(huì)產(chǎn)生大量碎片進(jìn)而導(dǎo)致并發(fā)能力的下 降。Flushing shared pool能夠使得很多小塊碎片合并，所以經(jīng)常能夠在一段時(shí)間內(nèi)恢復(fù)系統(tǒng)的性 能。清空之后可能也會(huì)產(chǎn)生短暫的性能下降，因?yàn)檫@個(gè)操作同時(shí)也會(huì)把沒造成shared pool碎片的共享SQL也清除了。清空shared poo

36、l的命令是：ALTER SYSTEM FLUSH SHARED_POOL;注意：如果顯式的使用以上命令，即使是用DBMS_SHARED_POOL.KEEP而被保留的那些對(duì)象可能也會(huì)被釋放掉，包括它們占用的內(nèi)存。如果是隱式的flush（由于shared pool上的內(nèi)存壓力）這個(gè)時(shí)候一 kept的對(duì)象不會(huì)被釋放。注意：如果sequenee使用了 cache選項(xiàng)，沖刷shared pool有可能會(huì)使sequenee在其范圍內(nèi)產(chǎn) 生不連續(xù)的記錄。使用 DBMS_SHARED_POOL.KEEP （sequence_name,Q）來保持 sequenee 會(huì) 防止這種不連續(xù)的情況發(fā)生。DBMS_SHA

37、RED_POOL .PURGE也可以不刷新整個(gè)shared pool，而只清空其中的單個(gè)對(duì)象。下面的文檔說明了 10g和11g中女 M 可清空 library cache heap。Docume nt:751876.1 DBMS_SHARED_POOL.PURGE Is Not Worki ng On 1020.4使用 V$ 視圖（V$SQL 和 V$SQLAREA）注意有一些V$視圖需要獲取相關(guān)的latch來返回查詢的數(shù)據(jù)。用來展示library cache和SQL area的 視圖就是值得注意的。所以我們建議有選擇性的運(yùn)行那些需要訪問這種類型視圖的語(yǔ)句。特別需要指 出的是，查詢V$SQLA

38、REA會(huì)在library cache latch上產(chǎn)生大量的負(fù)載，所以一般可以使用對(duì)latch訪問比較少的v$sql做替代一一這是因?yàn)閂$SQLAREA的輸出是基 于shared pool中所有語(yǔ)句的GROUP BY操作，而V$SQL沒有用GROUP BY操作。MTS, Shared Server 和 XA由于多線程服務(wù)器（MTS）的User Global Area （UGA）是存放在shared pool中的，所以會(huì)增加 shared pool的負(fù)載。在Oracle7上的XA session也會(huì)產(chǎn)生同樣的問題，因?yàn)樗麄兊腢GA也是在 shared pool 里面（在 Oracle8/8i 開

39、始 XA session 不再把 UGA 放到 shared pool 中）。在 Oracle8中Large Pool可以被用來減少M(fèi)TS對(duì)shared pool活動(dòng)的影響但是，Large Pool中的內(nèi)存分配仍然會(huì)使用shared pool latch。對(duì)Large Pool的描述請(qǐng)參考Note:62140.1.使用dedicate connections （專有連接）替代MTS可以使UGA在進(jìn)程私有內(nèi)存中分配而不是shared pool。私有內(nèi)存分配不會(huì)使用shared pool latch，所以在有些情況下從MTS切換到專 有連接可以幫 助減少競(jìng)爭(zhēng)。在Oracle9i中，MTS被改名為S

40、hared Server。但是對(duì)于shared pool產(chǎn)生影響的行為從根本 上說 還是一樣的。使用SQL查看Shared Pool問題這一章節(jié)展示了一些可以用來幫助找到shared pool中的潛在問題的SQL語(yǔ)句。這些語(yǔ)句的輸出最好spool到一個(gè)文件中。注意:這些語(yǔ)句可能會(huì)使latch競(jìng)爭(zhēng)加劇，我們?cè)谏厦娴摹笔褂肰$視圖(V$SQL和V$SQLAREA) above.查找 literal SQLSELECT substr(sql_text,1,4O) SQL,cou nt(*),sum(executi ons) TotExecsFROM v$sqlareaWHERE executio n

41、s 30ORDER BY 2這個(gè)語(yǔ)句有助于找到那些經(jīng)常被使用的literal SQL -請(qǐng)查看上面的”消除Literal SQL檢索 Library Cache hit ratioSELECT SUM(PINS) EXECUTIONS,SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE;如果misses/executions高于1%的話，則需要嘗試減少library cache miss的發(fā)生。檢查hash chain的長(zhǎng)度：SELECT hash_value, coun t(*)FROM v$sqlareaGROUP B

42、Y hash_valueHAVING coun t(*) 5-這個(gè)語(yǔ)句正常應(yīng)該返回 0行。如果有任何HASH_VALUES存在高的count(兩位數(shù)的) 的話，你需要查看是否是 bug的影響或者是literal SQL使用了不正常的形式。建議進(jìn) 一步列出所有有相同HASH VALUE的語(yǔ)句。例如：SELECT sql_text FROM v$sqlarea WHERE hash_value=;如果這些語(yǔ)句看起來一樣，則查詢V$SQLTEXT去找完整的語(yǔ)句。有可能不同的SQL文本會(huì)映 射到相同的hash值，比如：在7.3中，如果一個(gè)值在語(yǔ)句中出現(xiàn)2次而且中間正好間隔32 個(gè)字節(jié)的話，這兩個(gè)語(yǔ)句會(huì)

43、映射出相同的hash值。檢查高版本SELECT address, hash_value, version_count , users_ope ning , users_executi ng, substr(sql_text,1,40) SQLFROM v$sqlareaWHERE version_co unt 10在上面的Sharable SQL”章節(jié)中，我們已經(jīng)描述了，一個(gè)語(yǔ)句的不同”版本”是當(dāng)語(yǔ)句的字符完全一致但是需要訪問的對(duì)象或者綁定變量不一致等等造成的。在Oracle8i的不同版本中因?yàn)檫M(jìn)度監(jiān)控的問題也會(huì)產(chǎn)生高版本。在這篇文檔的前面描述過了，我們可以ffi_SQLEXEC_PROGRE

44、SSION_COST設(shè)成0來禁止進(jìn)度監(jiān)控產(chǎn)生高版本。找到占用shared pool內(nèi)存多的語(yǔ)句：SELECT substr(sql_text,1,40) Stmt, coun t(*), sum(sharable_mem) Mem, sum(users_ope ning) Ope n, sum(executi ons) ExecFROM v$sqlGROUP BY substr(sql_text,1,40)HAVING sum(sharable_mem) & MEMSIZEJ這里MEMSIZE取值為shared pool大小的10%，單位是byte。這個(gè)語(yǔ)句可以查出占用shared pool很

45、大內(nèi)存的那些SQL，這些SQL可以是相似的literal語(yǔ)句或者是一個(gè)語(yǔ)句 的不同版本。導(dǎo)致shared pool內(nèi)存aged out的內(nèi)存分配SELECT *FROM x$ksmlruWHERE ksmlrnum0J 注意：因?yàn)檫@個(gè)查詢?cè)诜祷夭怀^10行記錄后就會(huì)消除X$KSMLRU的內(nèi)容，所以請(qǐng)用SPOOL保存輸出的內(nèi)容。X$KSMLRU表顯示從上一次查詢?cè)摫黹_始，哪些內(nèi)存分配操作導(dǎo) 致了最多的內(nèi)存塊被清除出shared pool。有些時(shí)候，這會(huì)有助于找到那些持續(xù)的請(qǐng)求分配空間的session或者語(yǔ)句。如果一個(gè)系統(tǒng)表現(xiàn)很好而且共享SQL使用得也不錯(cuò)，但是偶爾會(huì)變慢，這個(gè)語(yǔ)句可以幫助找到原因

46、。關(guān)于X$KSMLRU的更多信息請(qǐng)查看Note:43600.1。在不同Oracle Releases中的都會(huì)遇到的問題在不同的release中有一些通用的會(huì)影響shared pool性能的問題：*增加每個(gè)CPU的處理能力可以減少latch被持有的時(shí)間從而有助于在Oracle的各個(gè)release上減少shared pool競(jìng)爭(zhēng)。換一個(gè)更快的CPU 一般來說會(huì)比增加一個(gè)慢的CPU效果要好。*如果你設(shè)置了一個(gè)EVENT，不管基于什么原因，請(qǐng)讓Oracle support檢查這個(gè)event是否會(huì)對(duì)shared pool的性能造成影響。*確保Oracle實(shí)例有足夠的內(nèi)存，避免SGA內(nèi)存被操作系統(tǒng)swap

47、交換出去的風(fēng)險(xiǎn)。例如：在AIX上操作系統(tǒng)的不正確設(shè)置可能會(huì)導(dǎo)致shared pool問題-參考 Note:316533.1 .Bug修復(fù)和增強(qiáng)功能這里列出了主要的影響shared pool的bug和功能增強(qiáng)。Fixe列是4位數(shù)字的服務(wù)器版本號(hào)，表示 在哪個(gè)版本上這個(gè)問題被修復(fù)或者功能被增強(qiáng)一一例如：8062表示在8.062上修復(fù)，9000表示這個(gè)問題在Oracle9i上修復(fù)。BugVersions Fixed DescriptionIdentical SQL referencingBug.16232568-9090008063Bug.1366837-9081719000SCHEMA.SEQUE

48、NCE.NEXTVAL not shared by different usersCursors referencing a fully qualified FUNCTION are not sharedBug 1484634-90806381719000Large row cache can cause long shared pool latch waits (OPS only)Bug.1318267815-909000INSERT AS SELECT may not share SQL when it shouldBug J149820-81780628162817。ENHANCEMEN

49、T: Reduced latch gets purging from shared poolBug 1150143-817806281628170ENHANCEMENT: Delay purge when bind mismatchNHANCEMENT: Reduce need to get PARENT libraryBug.1258708-81781708163Bug.13485018-81781708163Bug.1357233815-81781708162Bug.1193003 b 15-81781708162Bug.1210242815-8178170ache latchVIEW r

50、efresh unnecessarily invalidates shared cursorsALTER SESSION FORCE PARALLEL PQ/DML/DDL doesnot share recursive SQLCursors may not be shared in 8.1 when they should beCursors not shared if both TIMED STATISTICS andSQL TRACE are enabled8060Bug.9861497-8168160ENHANCEMENT: More freelists for shared pool

51、 memory chunks (reduced latch contention)Shared pool memory for views higher ifBug.858015815-8168160QUERY REWRITE ENABLED setBug. 918002815-81681518160Cursors are not shared if SQL_TRACE orTIMED_STATISTICS is TRUEBug.888551815-81681518160TIMED_STATISTICS can affect cursor sharing / Dumpfrom EXPLAIN

52、ornable/disabl 3QL_TRACEBug,10650108-817806281628170Access to DC_HISTOGRAM_DEFS from Remote Queries can impact shared pool performan ce.BugJ 115424書17806281628170Cursor authorization and dependency lists too long - can impact shared poolBug.1131711803-8062 8062SQL from PLSQL using NUMERIC binds may

53、not be8150shared when it shouldBug.139760381782ORA-4031 / SGA leak of PERMANENT memory for buffer handlesBug.16405838168171ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access8200歷史記錄這里的記錄是關(guān)于Oracle7.3之前的版本的，只是為了完整性的原因被包含在本文檔中：在7.3中，PLSQL被增強(qiáng)為可使用頁(yè)執(zhí)行編碼以減少shared pool大量?jī)?nèi)存分配的數(shù)量,并且減少使

54、用KEEP的需求。 Oracle 7.1.6到7.2.3有很多已知問題。請(qǐng)查看Note:32871.1從 Oracle 7.1 至 U 7.2，library cache 的 latch 機(jī)制發(fā)生了改變。* 一些歷史bug:BugVersionsFixedDescriptionBug.59695380-818044 8052 80608150Excessive shared pool fragmentation due to 2K context area chunk size.Bug.724620700-8147344 8043 80528060Select from VIEW now us

55、es less shared memory (less latch gets)Bug.6334987-8157343 8043 80508150Selecting from some V$ views can make statements unsharableBug.6258067X-8067343 8042 80518060 8150Cursor not shared for a VIEW using FUNCTION / with DBMS_SQLBug:5207087X-8047336 7342 8040Better handling of small memory chunks in

56、 theSGAReferencesNOTE:43600.1 - VIEW: X$KSMLRU - LRU flushes from the shared pool - (7.3 - 8.1)NOTE:61760.1 - Us ing the Oracle DBMS_SHARED_POOL PackageNOTE:94036.1 - Ini t.ora Parameter CURSOR_SHARING Refere nee NoteBUG:1065010 - PERFORMANCE PROBLEM WITH RECURSIVE LINKSBUG:1115424 - CURSOR AUTHORIZ

57、ATION AND DEPENDENCY LISTS GROWCAUSING LATCH CONTENTENTIONHidde nNOTE:68955.1 - In it.ora Parameter H_SQLEXEC_PROGRESSION_COSTRefere nee NoteNOTE:73922.1 - Tuning Precompiler Applicatio nsNOTE:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Worki ng On BUG:1366837 - CURSOR NOT SHARED FOR TABLES INVOKING A

58、FUNCTIONBUG:1484634 - ONE INSTANCE OF OPS HANGSBUG:1623256 - IDENTICAL SQL REFERENCING SCHEMA.SEQUENCE.NEXTVALNOT SHARED BY DIFFERENT USERSBUG:1640583 - ORA-4031 AND CACHE BUFFER CHAIN CONTENTION AFTERUPGRADE TO 8163NOTE:62140.1 - Fun dame ntals of the Large PoolNOTE:62143.1 - Troubleshooti ng: Tuni

59、ng the Shared Pool and Tuning Library Cache Latch Conten tio nBUG:625806 - CURSOR NOT SHARED FOR VIEWS INVOKING A FUNCTIONBUG:633498 - STATEMENTS IN SHARED POOL DONT GET REUSED AFTERSELECTING FROM V$OPEN_CURSORBUG:897615 - EXPLAIN PLAN OVER DBLINK PUTS GARBAGE IN THE PLANTABLENOTE:115656.1 - WAIT SC

60、ENARIOS REGARDING LIBRARY CACHE PIN ANDLIBRARY CACHE LOAD LOCKNOTE:1169017.1 - ANNOUNCEMENT:Deprecating the cursor_sharing = SIMILARsetti ngNOTE:123214.1 - Truncate - Causes In validatio ns in the LIBRARY CACHENOTE:1012046.6 -How to Calculate Your Shared Pool SizeNOTE:32871.1 - ALERT: Library Cache