shared pool library cache latch 競爭優化辦法

renjixinchina發表於2014-08-05

優化

shared pool library cache latch 競爭嚴重的症狀如下:

shared pool latch 或者 latch:shared pool 之類的Latch爭用

高CPU解析時間

V$LIBRARYCACHE 中的高reloads

多版本的cursors

大量的parse call

經常發生ORA-04031 錯誤

調優辦法如下:

1. 減少硬解析

a) 調整沒有繫結變數的sql

檢視沒有繫結變數使用頻率比較高的sql 並調整程式繫結變數

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC

b) 調整CURSOR_SHARING引數

可以設定FORCE 和SIMILAR來實現遊標共享,在10g 如果設定為force 引數值的不同需要不同的執行計劃(資料傾斜),可能會造成效能下降,11g雖然有acs但是也存在不少的bug,設定為similar可能會造成大量不能共享的子游標（由於acs的引入11gsimilar將被廢棄），所以設定此引數一定要慎重

2. 減少軟解析

c) 調整SESSION_CACHED_CURSORS

當軟解析負載大,而硬解析很小(library cache latch等待很高,shared pool latch很小 ) 考慮增長該引數

也可以通過以下引數查詢是否需要增加改引數

select 'session_cached_cursors' parameter,

lpad(value, 5) value,

decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage

from (select max(s.value) used

from v$statname n, v$sesstat s

where n.name = 'session cursor cache count'

and s.statistic# = n.statistic#),

(select value from v$parameter where name = 'session_cached_cursors')

union all

select 'open_cursors',

lpad(value, 5),

to_char(100 * used / value, '990') || '%'

from (select max(sum(s.value)) used

from v$statname n, v$sesstat s

where n.name in ('opened cursors current')

and s.statistic# = n.statistic#

group by s.sid),

(select value from v$parameter where name = 'open_cursors')

d) 將執行次數較多的cursor pin在shared pool

CREATE OR REPLACE PROCEDURE pincurs AS
addr_plus_hash varchar2(100);
cursor c1 is select rawtohex(address) addr,hash_value from v$sqlarea where executions > 10;

BEGIN
for C in C1 loop
addr_plus_hash := c.addr||','||c.hash_value;
DBMS_SHARED_POOL.KEEP (addr_plus_hash,'C');
end loop;
END pincurs;
/

3. 適當設定shared_pool大小

e) 調整SHARED_POOL_SIZE

控制shared pool自己的大小，它能對效能造成影響。如果太小，則共享的資訊會被從共享池中交換出去，過一陣子有需要被重新裝載，

如果發生頻繁發生ora-0431或shared_pool 頻繁grow 則可以考慮增大SHARED_POOL_SIZE,，

如果literal SQL使用較多而且shared pool又很大，長時間使用後內部記憶體freelist上會產生大量小的記憶體碎片，使得shared pool latch被持有的時間變長，進而導致效能問題。這種情況可以考慮調小shared_pool_size大小。

f) SHARED_POOL_RESERVED_SIZE

SHARED_POOL_RESERVED_SIZE 建議值: SHARED_POOL_SIZE

10%

最大不要超過SHARED_POOL_SIZE的50%

可以通過檢視來監控 SHARED_POOL_RESERVED使用情況,如果request_failures>0並且持續增加,可以考慮增大

g) SHARED_POOL_RESERVED_MIN_ALLOC

建議保值預設值

4. Keep或定時重新整理shared_pool

h) DBMS_SHARED_POOL.KEEP

i) Flushing the SHARED POOL

在使用大量literal SQL的系統中，shared pool隨時間推移會產生大量碎片進而導致併發能力的下降。Flushing shared pool能夠使得很多小塊碎片合併，所以經常能夠在一段時間內恢復系統的效能。清空之後可能也會產生短暫的效能下降，因為這個操作同時也會把沒造成shared pool碎片的共享SQL也清除了。清空shared pool的命令是：

ALTER SYSTEM FLUSH SHARED_POOL;

注意：如果顯式的使用以上命令，即使是用 DBMS_SHARED_POOL.KEEP 而被保留的那些物件可能也會被釋放掉，包括它們佔用的記憶體。如果是隱式的 flush （由於 shared pool上的記憶體壓力）這個時候“kept"的物件不會被釋放。

注意:如果sequence使用了cache選項，沖刷shared pool有可能會使sequence在其範圍內產生不連續的記錄。使用DBMS_SHARED_POOL.KEEP('sequence_name','Q')來保持sequence會防止這種不連續的情況發生。

也可以選擇重新整理某個物件

j) DBMS_SHARED_POOL.PURGE

5. 升級CPU

增加每個CPU的處理能力可以減少latch 被持有的時間從而有助於在Oracle 的各個release上減少shared pool競爭。換一個更快的CPU一般來說會比增加一個慢的CPU效果要好。

6. BUG

已知的影響shared_pool的bug

NB	Bug	Fixed	Description
	17891943		CASE expressions result in high child cursor counts due to BIND_MISMATCH
	17752995	11.2.0.4.3, 11.2.0.4.BP06, 12.1.0.2, 12.2.0.0	ORA-4031 from explain of SQL with many subqueries and OR expansion
	17273405	12.1.0.2, 12.2.0.0	Cursors not shared / increasing VERSION_COUNT when expected when using stored outlines
	17232014	11.2.0.4.BP09, 12.1.0.2, 12.2.0.0	RAC startup with large buffer cache reserves too little space for GES causing shared pool issues
	17158214	11.2.0.4.BP09, 12.1.0.1.4, 12.1.0.2, 12.2.0.0	Excess LPX memory use / ORA-4031 processing XML (affects DataPump)
	17037130	12.1.0.2, 12.2.0.0	Excess shared pool "PRTMV" memory use / ORA-4031 with partitioned tables
	16710753	11.2.0.3.BP22, 11.2.0.4, 12.1.0.1.4, 12.1.0.2, 12.2.0.0	Excess shared pool memory use / ORA-4031 from DROP EDITION
	16571785	11.2.0.3.BP22, 11.2.0.4, 12.1.0.2, 12.2.0.0	High "KTC latch subh" SGA memory use due to "lobs commit callback"
E	15898589	12.1.0.2, 12.2.0.0	Enhancement to restrict the size of SGA base library cache heaps
	14750501	11.2.0.4, 12.2.0.0	User defined operators cause literal recursive SQL use in IXCOSTFN1 / IXCOSTFN2
	13951456	11.2.0.4.3, 11.2.0.4.BP06, 12.1.0.2, 12.2.0.0	ASMCMD does not use bind variables in its SQL - affects shared pool
E	13771513	12.1.0.2, 12.2.0.0	Additional diagnostics for heapdumps and ORA-4031 errors
	16009158	11.2.0.3.BP19, 11.2.0.4, 12.1.0.1	ORA-4031 / excess shared pool memory using Securefiles
	15931756	11.2.0.3.11, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1	ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds)
	14711917	11.2.0.4, 12.1.0.1	High version count in RAC due to PX_MISMATCH
	14276566	11.2.0.4, 12.1.0.1	SQL with CASE expression not shared with CURSOR_SHARING=FORCE
	14176247	11.2.0.4, 12.1.0.1	Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE)
	13914613	11.2.0.3.6, 11.2.0.3.BP12, 11.2.0.4, 12.1.0.1	Excessive time holding shared pool latch in kghfrunp with auto memory management
	13632653	11.2.0.4, 12.1.0.1	XML related child cursors not shared due to ANYDATA_TRANSFORMATION
	13364735	11.2.0.4, 12.1.0.1	ORA-4025 when cursor is obsoleted which uses literal replacement
	12313857	11.2.0.2.9, 11.2.0.2.BP19, 11.2.0.3, 12.1.0.1	Child cursors not shared when using Editioning views
	9107440	11.2.0.2	DVSYS.DBMS_MACADM.GET_SESSION_INFO uses literal SQL underneath
	14053298	11.1.0.6	"kksss-heap" memory leak from PMON cleanup (ORA-4031) when audit enabled
E	2450264	10.1.0.2, 9.2.0.3, 9015PSE	Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE
	18693124	12.2.0.0	High version_count for SQL with remote PL/SQL operand
	18665660	12.1.0.2, 12.2.0.0	High child cursor counts due to OPTIMIZER_MISMATCH with Optimizer_features_enable=9.2.0
	16175381	11.2.0.2.10, 11.2.0.2.BP20, 11.2.0.3.6, 11.2.0.3.BP16, 11.2.0.4, 12.2.0.0	Process spin in kkscsSearchChildList() -> kkshGetNextChild() with fix for bug 14613900 present
	15858022	11.2.0.4, 12.2.0.0	Frequent invalidation of tuning objects with VPD
	14542720	11.2.0.4, 12.2.0.0	ORA-4025 with CURSOR_SHARING / Dynamic Sampling
	15881004	11.2.0.3.BP24, 11.2.0.4, 12.1.0.1	Excessive Memory usage with Extended Cursor Sharing
	14292825	11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	ORA-4031 in ASM as default memory parameters values for 11.2 ASM instances low
	14040433	10.2.0.5.8, 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.5, 11.2.0.3.BP08, 11.2.0.4, 12.1.0.1	Cursors not shared due to DIFF_CALL_DURN even though they should share
	14029050	11.2.0.2.BP18, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	MMAN failing to donate granules despite unused PGA (possible ORA-4031)
	14021941	11.2.0.4, 12.1.0.1	JDBC program shows high cursor version count and high bind mismatch count
	14003090	11.2.0.3.8, 11.2.0.3.BP20, 11.2.0.4, 12.1.0.1	ORA-4031 with high memory utilization in the 'init_heap_kfsg' subheap / "ASM map headers" under 'init_heap_kfsg'
	13814739	11.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1	Excessive KQR X PO" allocations in a RAC environment (can cause ORA-4031)
	13511004	11.2.0.4, 12.1.0.1	Memory leak / ORA-4030 / high version_count for cursors using SQLT_TIMESTAMP_TZ binds
	13456573	11.2.0.3.BP23, 11.2.0.4, 12.1.0.1	Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing
	13430938	11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	ORA-4031 with high "KTC latch subh" memory usage
	13250244	11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	Shared pool leak of "KGLHD" memory when using multiple subpools
	13054713	11.2.0.3.BP05, 11.2.0.4, 12.1.0.1	High version_count / ORA-942 on statements where DDLs happen over the objects.
	12976376	11.2.0.2.6, 11.2.0.2.BP16, 11.2.0.3.3, 11.2.0.3.BP05, 12.1.0.1	High VERSION_COUNT for SQL with binds, including recursive dictionary SQL - superseded
	12621588	11.2.0.3.8, 11.2.0.3.BP11, 11.2.0.4, 12.1.0.1	ORA-7445 [koksOpnHasObjWTempLob2] from query containing lots of ANSI joins
	12596686	11.2.0.4, 12.1.0.1	JDBC thin app sends scale value of 0 or 9 for Timestamp binds causing many child cursors
	12596444	11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.BP08, 11.2.0.4, 12.1.0.1	Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
	12392122	11.2.0.3, 12.1.0.1	dbms_shared_pool.keep does not fully prevent cursor from being aged out
	12387969	11.2.0.3, 12.1.0.1	get_geometry() does not use bind variables internally leading to shared pool fragmentation
	12387079	12.1.0.1	High Version Count with PURGED_CURSOR reason - superceded
	12348331	11.2.0.3, 12.1.0.1	ORA-7445 [kqlr_get_diana_ts] when using _kgl_debug
	12345980	11.2.0.3, 12.1.0.1	high parse time with cursor_sharing=force when session_cached_cursors set
	12334286	11.2.0.2.11, 11.2.0.2.BP21, 11.2.0.3, 12.1.0.1	High version counts with CURSOR_SHARING=FORCE (BIND_MISMATCH and INCOMP_LTRL_MISMATCH)
	12320556	11.2.0.4, 12.1.0.1	High version count for child cursors referencing a remote object due to AUTH_CHECK_MISMATCH
	11782790	11.2.0.3, 12.1.0.1	Excess CPU & memory use / ORA-4030 / ORA-4031 parsing deeply nested queries
	11663661	11.2.0.2.BP10, 11.2.0.3, 12.1.0.1	ORA-7445 [kglsca] with KEPT cursors
	11657468	11.2.0.3, 12.1.0.1	Excessive mutex waits with adaptive cursor sharing
	11651810	11.2.0.2.3, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1	ORA-4031 or Excess shared pool use can be seen by FileOpenBlock objects
	11063191	11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.2, 11.2.0.3.BP04, 11.2.0.4, 12.1.0.1	ORA-4031 with hint /+ CURSOR_SHARING_EXACT / - excessive "KKSSP^nn" memory
	10636231	11.2.0.1.BP12, 11.2.0.2.3, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.1	High version count for INSERT .. RETURNING statements with reason INST_DRTLD_MISMATCH
	10351178	11.2.0.4, 12.1.0.1	High version counts caused by auto tuned PGA memory
	10308906	11.2.0.3, 12.1.0.1	ORA-4031 using structured binary XMLIndex
	10297948	11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	High Version Count with PX_MISMATCH on Serial Queries in RAC
	10274265	11.2.0.4, 12.1.0.1	Event 10503 does not work at session level
E	10187168	11.1.0.7.7, 11.2.0.1.BP12, 11.2.0.2.2, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.1	Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold
	10182051	11.2.0.3, 12.1.0.1	Extended cursor sharing generates many shareable child cursors
	10151017	11.2.0.1.BP12, 11.2.0.2.1, 11.2.0.2.BP03, 11.2.0.3, 12.1.0.1	Many MERGE child cursors due to INST_DRTLD_MISMATCH inside global transaction
	10145210	11.2.0.3, 12.1.0.1	High VERSION_COUNT in V$SQLAREA for Text / domain index queries with peeked binds
	10111765	11.2.0.3, 12.1.0.1	High version count on queries with NOAPPEND hint due to INST_DRTLD_MISMATCH
	10086843	11.2.0.3, 12.1.0.1	Recursive SQL cursors not reused - PMON crashes instance with ORA-600 [kglLockOwnersListDelete]
	10082277	11.2.0.1.BP12, 11.2.0.2.3, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1	Excessive allocation in PCUR or KGLH0 heap of "kkscsAddChildNo" (ORA-4031)
	10042937	11.2.0.3, 12.1.0.1	High memory group in ges_cache_ress and ORA-4031 errors in RAC
	9951918	11.2.0.4, 12.1.0.1	RAC "ges_res_cache" may grow excessively causing poor performance and memory starvation
	9944129	11.2.0.1.BP12, 11.2.0.2, 12.1.0.1	SQL not shared due to INST_DRTLD_MISMATCH with global transaction
	9905110	11.2.0.3, 12.1.0.1	PRO*COBOL bind variables not handled correctly (ORA-6502 and/or high version count)
	9847634	11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1	High VERSION_COUNT due to AUTH_CHECK_MISMATCH with TABLE() function
	9732503	11.2.0.2, 12.1.0.1	latch free waits for SQL Memory Manager latch / extra child cursors
	9689310	10.2.0.5.7, 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2, 12.1.0.1	Excessive child cursors / high VERSION_COUNT / ORA-600 [17059] due to bind mismatch
	9680430	11.2.0.3, 12.1.0.1	High version count with CURSOR_SHARING = FORCE due to CBO transformation
	9675816	11.2.0.2, 12.1.0.1	Self deadlock with 'library cache lock' waits / OERI:17059
	9578670	11.2.0.1.BP11, 11.2.0.2.5, 11.2.0.2.BP09, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1	ORA-4031 from frequent Partition Maintenance Operation
	9499385	11.2.0.2, 12.1.0.1	Multiple child cursors using PLSQL UROWID binds (bind_mismatch='Y')
	9380377	11.2.0.2, 12.1.0.1	INSERT / MERGE child cursor leak due to INST_DRTLD_MISMATCH - superceded
	9375300	11.2.0.2.5, 11.2.0.2.BP07, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1	Cursors not shared due to reason 'PQ_SLAVE_MISMATCH' or 'PX_MISMATCH' on RAC - superceded
	9320130	10.2.0.5.3, 11.2.0.2, 12.1.0.1	ORA-4031 for "temporary tabl" using temporary table transformations (STAR / WITH)
	9287616	11.2.0.3, 12.1.0.1	Accessing [G]V$SQL or [G]V$SQLTEXT_WITH_NEWLINES may be slow / takes a long time / latch contention
	9058865	11.2.0.1.3, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1	OERI[17059] / high version_count after schema dropped
	9015983	11.2.0.1.1, 11.2.0.2, 12.1.0.1	ORA-4031 for "temporary tabl" on query with star transformation
	8981059	11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1	High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking
	8946311	11.1.0.7.10, 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1	Increase max children before reporting ORA-600 [17059]
	8865718	10.2.0.5.3, 11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1	Recursive cursors for MV refresh not shared
	8861624	11.2.0.3, 12.1.0.1	Need to tighten NLS cursor sharing criteria on child reload
	8813366	11.1.0.7.3, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1	ORA-4031 due to over large granule size
	8537544	11.2.0.2, 12.1.0.1	Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins
	8211733	10.2.0.5.3, 11.1.0.7.8, 11.2.0.2, 12.1.0.1	Shared pool latch contention when shared pool is shrinking
	7352775	11.2.0.2, 12.1.0.1	Many child cursors when PARALLEL_INSTANCE_GROUP set wrong
	5207465	12.1.0.1	High version count for remote SQL when CURSOR_SHARING set (FORCE or SIMILAR)
	14770516	12.1.0.0	SGA memory leak in KGLH0 under shared pool
+	12830339	11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3	Long shared pool latch waits / instance crash in 11.2 with ORA-240 / ORA-15064
	11930680	11.2.0.3	High VERSION_COUNT due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS with secure view merging
	9951162	11.2.0.3	Excessive "KTSL subheap" memory use in shared pool
	9140262	11.2.0.2	ORA-600 [ksliwat5] followed by cpu spike/"library cache: mutex X" Waits
	9058900	11.2.0.1.BP10, 11.2.0.2	OERI [kcbi_get_bhs_4] / "Shared IO Pool" takes too much SGA memory
	7606338	11.2.0.1	High version count with CDC
	13022135		ORA-600 [kglKeepHandle] possible in 11.1.0 if cursor obsolete feature is enabled
	8599477	11.1.0.7.3, 11.2.0.1	Large "kcbi io desc" shared pool allocations can lead to ORA-4031
	8528171	10.2.0.5.6, 11.1.0.7.10, 11.2.0.1	High shared pool use due to frequent ADD/EXCHANGE partition operations
	8453245	11.2.0.1	Many child cursors with CURSOR_SHARING = FORCE
	8244734	10.2.0.5, 11.1.0.7.8, 11.2.0.1	NUMA Pool misconfigured at startup (ORA-4031)
	8221425	11.1.0.7.2, 11.2.0.1	ORA-4031 with kgs-heap overusing one subpool
	7648406	10.2.0.5, 11.1.0.7.4, 11.2.0.1	Child cursors not shared for "table_..." cursors (that show as "SQL Text Not Available") when NLS_LENGTH_SEMANTICS = CHAR
	7626014	11.1.0.7.5, 11.2.0.1	OERI[kksfbc-new-child-thresh-exceeded] can occur / unnecessary child cursors
	7408621	11.1.0.7.3, 11.2.0.1	An unbound child cursor may not be shared
	7340448	10.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1	Excess shared pool memory use / ORA-4031 from REGEXP_LIKE - superseded
	7250182	11.2.0.1	Excess shared pool memory for AQ ("kwqiccns: notification st" chunks)
	7212120	11.1.0.7, 11.2.0.1	Session cursor cache not used properly when CURSOR_SHARING=force/similar
	7207921	10.2.0.5, 11.2.0.1	State object leak / ORA-4031 from stale guess DBAs on secondary IOT index
	7122093	10.2.0.5, 11.2.0.1	'latch: library cache' contention caused by queries on V$ views.
	7025450	10.2.0.5, 11.2.0.1	DBMS_SHARED_POOL.KEEP does not keep future child cursors
	6981690	10.2.0.4.4, 10.2.0.5, 11.1.0.7.1, 11.2.0.1	Cursor not shared when running PX query on mounted RAC system
	6879763	11.2.0.1	Excessive gets for "shared pool simulator" latch
	6868080	10.2.0.5, 11.1.0.7, 11.2.0.1	ORA-4031 with NUMA
	6858062	10.2.0.4.1, 10.2.0.5, 11.1.0.7.5, 11.2.0.1	Shared pool memory leak when services created / deleted
	6800507	10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1	Shared pool memory leak ("ksws service *" memory) using EXPDP often
	6644714	11.1.0.7, 11.2.0.1	High number of child cursors with adaptive cursor sharing
	6530861	10.2.0.5, 11.2.0.1	ORA-4301 when using stored Java
	6271590	10.2.0.4.2, 10.2.0.5, 11.1.0.7, 11.2.0.1	SGA subheap imbalance with lots of free memory in a few subheaps
	6530010	11.1.0.7	High version count for cursor with binds with fix for bug 5872943
	6333663	10.2.0.4, 11.1.0.7	Shared pool latch contention due to fragmentation of large pool
	6011182	10.2.0.4, 11.1.0.7	Parsing of large query takes long time / memory leak / ORA-4030 /4031
	5555683	10.2.0.4, 11.1.0.7	Intermittent ORA-1801 when shared pool under pressure
	5386986	10.2.0.4, 11.1.0.7	Leak / ORA-4031 leak when DROP UNUSED COLUMN issued on large partitioned table
	13888380		ORA-4031 in DB instance when using ASM due to high use of "init_heap_kfsg" memory
	9737897		V$SGASTAT shows "obj stat memo" increases continuously
	8922013		OERI [17059] / excess child cursors for SQL referencing REMOTE objects
	10157392	12.1.0.1	High version counts for SQL with binds (BIND_MISMATCH)
	12808696	11.1.0.6	Shared pool memory leak of "hng: All sessi" memory
	7306915	10.2.0.4.4, 10.2.0.5, 11.1.0.6	EXCHANGE PARTITION leaks "KGL handles" shared pool memory
	6043052	10.2.0.4, 11.1.0.6	Leak in perm allocations with "library cache" comments (ORA-4031)
	5950708	10.2.0.4, 11.1.0.6	'gcs resources' and 'gcs shadows' are imbalanced across shared pool subpools
	5704636	10.2.0.4, 11.1.0.6	Event 10503 does not work in 10.2.0.1 - 10.2.0.3 inclusive
D	5618049	10.2.0.4, 11.1.0.6	"mvobj part des" leaked memory after partition DDL (ORA-4031)
	5573238	10.2.0.4, 11.1.0.6	Shared pool memory use / ORA-4031 due to "obj stat memo" in one subpool
	5548510	10.2.0.4, 11.1.0.6	_FIX_CONTROL parameter leaks memory in the shared pool
	5548389	10.2.0.4, 11.1.0.6	Library cache allocation for 'column mapping' not using uniform sized extents
	5514982	10.2.0.5, 11.1.0.6	Unnecessary soft parses with connection pooling
	5508505	10.2.0.4, 11.1.0.6	ORA-4031 while shared heap still has unused reserved extents
	5505337	10.2.0.3, 11.1.0.6	Excessive CPU time spent freeing cursor stats when shared pool under load
	5479172	10.2.0.4, 11.1.0.6	ORA-4031 with multiple partially-allocated permanent chunks
	5465597	10.2.0.4, 11.1.0.6	SQL apply does not use bind variables while processing sequence updates
	5345437	10.2.0.3, 11.1.0.6	Intermittent wrong data / NLS errors (eg ORA-1801)
C	5082178	10.2.0.4, 11.1.0.6	Bind peeking may occur when it should not
	4994956	10.2.0.3, 11.1.0.6	Number of configured shared pool subpools not correct
	4701527	9.2.0.8, 10.2.0.4, 11.1.0.6	Cursors not shared when executing procedures over a dblink
	4581334	9.2.0.8, 10.2.0.4, 11.1.0.6	Cursors accessing remote tables may be repeatedly rebuilt and not used
	4467058	10.2.0.4, 11.1.0.6	IO requests can flush the pool / signal a hidden ORA-4031 error
	4458226	9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6	High version count with cursor_sharing=force
	4367986	10.2.0.4, 11.1.0.6	Bind peeked parallel cursors do not share
	4359367	10.1.0.5, 10.2.0.2, 11.1.0.6	High version_count with cursor sharing
	3519807	9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6	ORA-4031 querying V$SEGSTAT
	5841488	10.2.0.4	Extra child cursors for INSERT or MERGE SQL with fix for bug 4701527
P*	5705795	10.2.0.4, 11.1.0.7	Many child cursors possible for SQL using BINDS
	6347725		"obj stat mem" leak when global temporary tables used
	4249345	10.1.0.5, 10.2.0.1	Shared cursor sizes larger than earlier releases
	4238592	9.2.0.8, 10.1.0.5, 10.2.0.1	High pseudo-cursor version count
	4204326	9.2.0.7, 10.1.0.5, 10.2.0.1	Cursors with TABLE() expressions no shared when USE_STORED_OUTLINES set
E	4204014	10.1.0.5, 10.2.0.1	Enhancement to help reduce library cache latch contention
	4201684	10.1.0.5, 10.2.0.1	Excess shared pool usage when CURSOR_SPACE_FOR_TIME set
	4201664	10.1.0.5, 10.2.0.1	EXECUTE IMMEDIATE of anonymous PLSQL may be reparsed unnecessarily
	4184298	10.1.0.5, 10.2.0.1	Subpool imbalance for "session parameters" can lead to ORA-4031
	4102493	9.2.0.7, 10.1.0.5, 10.2.0.1	Dump (kxsDump) possible when ORA-4031 attempting to be signalled
	4007764	10.1.0.5, 10.2.0.1	Auditing can cause excess shared pool memory use
	3941893	10.1.0.4, 10.2.0.1	Latch contention in cursor cache when flushing AWR SQL statistics
	3910149	9.2.0.7, 10.1.0.5, 10.2.0.1	Very small SGA memory leak in RAC environments
	3768052	9.2.0.7, 10.1.0.4, 10.2.0.1	PLSQL Commit / Rollback wastes cursors and is slower than 8i
	3513427	10.1.0.3, 10.2.0.1	ORA-4031 can cause many SGA heapdumps to occur causing shared pool latch contention
	3476255	10.2.0.1	PLSQL name resolution problem for different SCHEMA
	3406977	9.2.0.6, 10.1.0.3, 10.2.0.1	High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
	3405237	9.2.0.6, 10.1.0.3, 10.2.0.1	ORA-4031 / excessing SGA consumption due to large IN LIST and NOT IN list
	3356492	10.2.0.1	DBMS_LOB.LOADFROMFILE leaks library cache locks / shared pool "perm" memory
	3299407	9.2.0.6, 10.1.0.3, 10.2.0.1	Invalidating cursors frequently can fragment the SGA and leak "kglau" memory
	3046725	10.2.0.1	ORA-4031 due to shared_pool fragmented with high ges resources & enqueues
	2953931	9.2.0.4, 10.2.0.1	ORA-4031 parsing query with numerous groupings
	2893444	9.2.0.6, 10.1.0.3, 10.2.0.1	Internal SQL in the extensible optimizer does not always use bind variables
	2730704	10.1.0.5, 10.2.0.1	OUT binding in "call" slower than OUT binding in "begin ... end"
	4231921	9.2.0.8, 10.1.0.2	Truncate partition tables leaks SGA memory
	3589588	9.2.0.6, 10.1.0.2	OERI[15201] if shared pool under load
E	3316003	9.2.0.5, 10.1.0.2	Internal change to help avoid ORA-4031 for multi-subpool SGAs
	3310746	9.2.0.5, 10.1.0.2	Additional trace and diagnostics for ORA-4031 errors
	3232401	9.2.0.5, 10.1.0.2	ORA-4031 with Streams apply site
+C	3150705	9.2.0.5, 10.1.0.2	Fix to help reduce the chances of an ORA-4031 with high OPEN_CURSORS
	3150680	9.2.0.5, 10.1.0.2	Excessive shared pool permanent memory use / ORA-4031 possible with SESSION_CACHED_CURSORS
	3083818	10.1.0.2	Reloaded cursors can leak shared pool memory
	3077651	9.2.0.5, 10.1.0.2	LOB manipulation can leak "buffer" state objects causing hangs/shared pool problems
	3070246	9.2.0.5, 10.1.0.2	Database hang / latch contention possible
	3063763	9.2.0.5, 10.1.0.2	Cursors may not be shared when using long binds
	3003273	9.2.0.5, 10.1.0.2	Under heavy load some shared pool memory may not be freed due to latch contention
	2986643	9.2.0.5, 10.1.0.2	Child cursors are not cached when executing SQL with a DB LINK when sql_trace=true
	2936434	9.2.0.5, 10.1.0.2	DML cursors may not be shared after ALTER SESSION DISABLE\|FORCE PARALLEL QUERY
	2909346	9.2.0.6, 10.1.0.2	Domain indexes can produce recursive literal SQL (affects shared pool)
	2817728	9.2.0.4, 10.1.0.2	Shared pool memory leak / ORA-4031 when collecting segment statistics
	2798851	9.2.0.4, 10.1.0.2	ORA-4031 possible from SMON during SHUTDOWN or STARTUP
	2786968	9.2.0.4, 10.1.0.2	Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors
	2760217	9.2.0.4, 10.1.0.2	Cursors not shared when CREATE_STORED_OUTLINES set to a category
+	2748963	9.2.0.4, 10.1.0.2	USE_STORED_OUTLINE may not share SQL / PLSQL anonymous blocks
	2607029	9.2.0.3, 10.1.0.2	High memory use optimizing SQL with functional indexes and many expressions
	2586762	9.2.0.3, 10.1.0.2	Direct LOB reads can result in ORA-4031 / excessive shared pool usage
	2563301	9.2.0.5, 10.1.0.2	ORA-4031 possible in RAC environment under load
	2561115	9.2.0.5, 10.1.0.2	SGA memory growth using outbound TCP/IP database links from shared servers
	2300743	9.2.0.8, 10.1.0.2	OERI[504] on "shared pool" latch possible
	2283941	9.0.1.4, 9.2.0.2, 10.1.0.2	Changes to reduce contention on "library cache latch"
C	2276769	9.2.0.5, 10.1.0.2	Different static PLSQL cursors may not be shared
	2273604	9.0.1.4, 9.2.0.2, 10.1.0.2	CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
	2264893	9.0.1.4, 9.2.0.2, 10.1.0.2	Recursive SQL under triggers can contribute to latch contention
	1782381	9.2.0.5, 10.1.0.2	Excessive SGA use / ORA-4031 possible with large number of JOBS
*	4157713	9.2.0.7	OERI[KGHPIH:DS] can occur when shared pool under load
	3869563	9.2.0.7	Cursors not shared for certain SQL with SQL_TRACE enabled and fix for bug 2228280
	3658039	9.2.0.6	ORA-6540 / shared pool memory use from EMON during PLSQL notification
	3260252	9.2.0.5	Allow Streams to use > 10% shared pool before spilling
	3090397	9.2.0.5	ORA-4031 / excessive shared pool using LGWR ASYNC or SYNC=PARALLEL
	2805335	9.2.0.4	Excessive shared pool memory use for parse of certain statments using functional indexes
	2708134	9.2.0.3	FIRST_ROWS(K) HINT causes cursor not to be shared
	2272866	9.0.1.4, 9.2.0.2	Client side PLSQL can cause excessing "library cache lock" and latch gets
	2442042	9.2.0.5	ORA-4031 possible using STREAMS on multi-cpu machine
	2385153	9.2.0.2, 10.1.0.2	Shared pool LEAK on global enqueue timeouts in RAC
	2580648	9.2.0.2	Anonymous PLSQL not shared with SQL_TRACE / TIMED_STATISTICS
	2245798	9.0.1.4, 9.2.0.1	X$KSMLRU does not work for multiple shared pool heaps
	2244789	9.0.1.4, 9.2.0.1	ORA-4031 / excessive shared pool usage from query with full outer join / union
	2244642	9.0.1.4, 9.2.0.1	ALTER VIEW operations may encounter ORA-4031 errors
	2213853	9.0.1.4, 9.2.0.1	SHARED_POOL_RESERVED_SIZE parameter is ignored
	2208570	8.1.7.4, 9.0.1.4, 9.2.0.1	ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view
	2167326	9.0.1.3, 9.2.0.1	ORA-4031 possible during patch set upgrade scripts
	2129178	8.1.7.4, 9.0.1.3, 9.2.0.1	Using NULL or an empty string ('') in an INLIST can cause ORA-4031 / waste shared pool memory
	2104071	8.1.7.4, 9.0.1.3, 9.2.0.1	ORA-4031 / excessive "miscellaneous" shared pool use possible (many PINS)
	2041699	8.1.7.3, 9.0.1.3, 9.2.0.1	Child library cache latch contention using PLSQL functions in large selects
	1865917	8.1.7.3, 9.0.1.2, 9.2.0.1	ORA-4031 / high CPU from OR expansion of negated predicates with RBO
	1819214	8.1.7.3, 9.0.1.3, 9.2.0.1	Reduce latch hold time for "row cache objects" latch
	1815042	8.1.7.4, 9.0.1.1, 9.2.0.1	Unnecessary hold of library cache latch in RAC or OPS environments
	1742027	9.0.1.2, 9.2.0.1	Identical SQL/PLSQL using different Array sizes are not shared
	1333526	8.1.7.3, 9.0.1.2, 9.2.0.1	ORA-4031 / Poor performance using persistent Object data types
	2159152	8.1.7.4, 9.0.1.0	Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
	1642964	9.0.1.0	ORA-4031 / sga leak from IOT as inner table in NESTED LOOP
	1640583	8.1.7.1, 9.0.1.0	ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access
	1623256	8.1.7.2, 9.0.1.0	Identical SQL referencing SCHEMA.SEQUENCE.NEXTVAL not shared by different users
	1607828	8.1.7.2, 9.0.1.0	OCI client make leak cursors (ORA-1000)
	1589185	8.1.7.2, 9.0.1.0	Performance fix to reduce sleeps on "library cache latch"
	1484634	8.0.6.3, 8.1.7.1, 9.0.1.0	Large row cache can cause long shared pool latch waits (OPS only)
	1467575	8.1.7.1, 9.0.1.0	ALTER SYSTEM FLUSH SHARED POOL can cause PDML to hang
+	1397603	8.1.7.2, 9.0.1.0	ORA-4031 / SGA memory leak of PERMANENT memory for buffer handles
	1396675	8.1.7.2, 9.0.1.0	PLSQL may hang/spin/ORA-4031 with SQL WHERE (a,b) in ( (c1,d1),(c2,d2)... )
	1366837	8.0.6.3, 8.1.7.1, 9.0.1.0	Cursors referencing a fully qualified FUNCTION are not shared
	1318267	8.1.7.1, 9.0.1.0	INSERT AS SELECT may not share SQL when it should
	2791662		Instance slow down/hang from DROP of object with many dependents
	1357233	8.1.6.3, 8.1.7.0	ALTER SESSION FORCE PARALLEL PQ/DML/DDL does not share recursive SQL
	1348501	8.1.6.3, 8.1.7.0	MV refresh unnecessarily invalidates shared cursors
E	1258708	8.1.7.0	ENH: Reduce need to get PARENT library cache latch
	1210242	8.1.6.2, 8.1.7.0	Cursors not shared if both TIMED_STATISTICS and SQL_TRACE are enabled
	1193003	8.1.6.2, 8.1.7.0	Cursors may not be shared in 8.1 when they should be
+	1157495	8.1.7.0	Cursor invalidations can waste shared memory (heap 0)
	1115424	8.0.6.2, 8.1.6.2, 8.1.7.0	Cursor authorization and dependency lists too long - can impact shared pool / OERI:17059
	1092621	8.1.6.2, 8.1.7.0	ORA-4031 when DROPPING a PARTITION
	1065010	8.0.6.2, 8.1.6.2, 8.1.7.0	Access to DC_HISTOGRAM_DEFS from Remote Queries can impact shared pool performance.
	969413	8.0.6.2, 8.1.6.3, 8.1.7.0	OERI:16606 may be reported under very heavy shared pool load
+E	986149	8.0.6.0, 8.1.6.0	ENH: More freelists for shared pool memory chunks (reduced latch contention)
	918002	8.1.5.1, 8.1.6.0	Cursors are not shared if SQL_TRACE or TIMED_STATISTICS is TRUE
	888551	8.1.5.1, 8.1.6.0	TIMED_STATISTICS can affect cursor sharing / Dump from EXPLAIN or enable/disable SQL_TRACE
	858015	8.1.6.0	Shared pool memory for views higher if QUERY_REWRITE_ENABLED set
	633498	7.3.4.3, 8.0.4.3, 8.0.5.0, 8.0.6.0, 8.1.5.0	Selecting from some V$ views can make statements unsharable
	724620	7.3.4.4, 8.0.4.3, 8.0.5.2, 8.0.6.0	Select from VIEW now uses less shared memory (less latch gets)
	596953	8.0.4.4, 8.0.5.2, 8.0.6.0, 8.1.5.0	Excessive shared pool fragmentation due to 2K context area chunk size.
	1131711	8.0.6.2, 8.1.5.0	SQL from PLSQL using NUMERIC binds may not be shared when it should
	625806	7.3.4.3, 8.0.4.2, 8.0.5.1, 8.0.6.0, 8.1.5.0	Cursor not shared for a VIEW using FUNCTION / with DBMS_SQL
	520708	7.3.3.6, 7.3.4.2, 8.0.4.0	Better handling of small memory chunks in the SGA

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-1246003/，如需轉載，請註明出處，否則將追究法律責任。

優化Shared Pool Latch與Library Cache Latch競爭
2013-11-09
優化
Shared Pool優化和Library Cache Latch衝突優化
2014-08-26
優化
故障排除：Shared Pool優化和Library Cache Latch衝突優化
2016-12-04
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列6
2013-09-07
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列5
2013-09-07
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列4
2013-09-07
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列3
2013-09-07
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列2
2013-09-07
優化
【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列1
2013-09-07
優化
共享池之六:shared pool latch/ library cache latch /lock pin 簡介
2014-03-16
等待模擬-library cache shared pool 硬解析
2013-06-27
【雲和恩墨大講堂】故障分析 | library cache latch 競爭案例分享
2018-02-02
Shared pool的library cache lock/pin及硬解析
2016-11-07
深入理解shared pool共享池之library cache的library cache lock系列四
2015-12-08
深入理解shared pool共享池之library cache的library cache pin系列三
2015-11-24
shared pool之三：library cache結構/library cache object的結構-dump LibraryHandle
2014-01-04
Object
Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL
2017-10-26
Object
深入理解shared pool共享池之library cache系列一
2015-11-24
深入理解shared pool共享池之library cache系列二
2015-11-24
[20210520]11g shared pool latch與library cache mutex的簡單探究.txt
2021-05-20
Mutex
[20210521]11g shared pool latch與library cache mutex的簡單探究4.txt
2021-05-21
Mutex
latch:shared pool的一點理解
2015-03-01
latch:library cache lock等待事件
2018-06-27
事件
oracle優化--shared_pool (3)
2012-05-02
Oracle優化
oracle優化--shared_pool (2)
2012-04-28
Oracle優化
oracle優化--shared_pool (1)
2012-04-28
Oracle優化
轉_診斷latch:shared pool等待事件
2013-07-02
事件
[20210520]11g shared pool latch與library cache mutex的簡單探究3.txt
2021-05-20
Mutex
latch：cache buffers chains的優化思路
2015-06-09
AI優化
【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法
2023-04-10
ASMOracle
library cache pin/lock的解決辦法
2017-05-23
Oracle效能最佳化之診斷latch競爭（轉）
2009-05-06
Oracle
oracle10g_oracle11g_library cache_shared pool管理方面的小區別
2013-04-24
Oracle
oracle library cache相關的等待事件及latch
2013-04-23
Oracle事件
sql version count引發cursor:pin s wait x及library cache latch library cache lock
2015-12-16
SQLAI
深入理解shared pool共享池空間及library cache分配之ora-4031 系列一
2017-03-28
一個關於latch: library cache事件的處理
2010-11-19
事件
共享池的調整與優化(Shared pool Tuning)
2011-10-11
優化