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 |
CASE expressions result in high child cursor counts due to BIND_MISMATCH | |||
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 | ||
12.1.0.2, 12.2.0.0 | Cursors not shared / increasing VERSION_COUNT when expected when using stored outlines | ||
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 | ||
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) | ||
12.1.0.2, 12.2.0.0 | Excess shared pool "PRTMV" memory use / ORA-4031 with partitioned tables | ||
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 | ||
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 | 12.1.0.2, 12.2.0.0 | Enhancement to restrict the size of SGA base library cache heaps | |
11.2.0.4, 12.2.0.0 | User defined operators cause literal recursive SQL use in IXCOSTFN1 / IXCOSTFN2 | ||
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 | 12.1.0.2, 12.2.0.0 | Additional diagnostics for heapdumps and ORA-4031 errors | |
11.2.0.3.BP19, 11.2.0.4, 12.1.0.1 | ORA-4031 / excess shared pool memory using Securefiles | ||
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) | ||
11.2.0.4, 12.1.0.1 | High version count in RAC due to PX_MISMATCH | ||
11.2.0.4, 12.1.0.1 | SQL with CASE expression not shared with CURSOR_SHARING=FORCE | ||
11.2.0.4, 12.1.0.1 | Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE) | ||
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 | ||
11.2.0.4, 12.1.0.1 | XML related child cursors not shared due to ANYDATA_TRANSFORMATION | ||
11.2.0.4, 12.1.0.1 | ORA-4025 when cursor is obsoleted which uses literal replacement | ||
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 | ||
11.2.0.2 | DVSYS.DBMS_MACADM.GET_SESSION_INFO uses literal SQL underneath | ||
11.1.0.6 | "kksss-heap" memory leak from PMON cleanup (ORA-4031) when audit enabled | ||
E | 10.1.0.2, 9.2.0.3, 9015PSE | Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE | |
12.2.0.0 | High version_count for SQL with remote PL/SQL operand | ||
12.1.0.2, 12.2.0.0 | High child cursor counts due to OPTIMIZER_MISMATCH with Optimizer_features_enable=9.2.0 | ||
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 | ||
11.2.0.4, 12.2.0.0 | Frequent invalidation of tuning objects with VPD | ||
11.2.0.4, 12.2.0.0 | ORA-4025 with CURSOR_SHARING / Dynamic Sampling | ||
11.2.0.3.BP24, 11.2.0.4, 12.1.0.1 | Excessive Memory usage with Extended Cursor Sharing | ||
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 | ||
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 | ||
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) | ||
11.2.0.4, 12.1.0.1 | JDBC program shows high cursor version count and high bind mismatch count | ||
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' | ||
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) | ||
11.2.0.4, 12.1.0.1 | Memory leak / ORA-4030 / high version_count for cursors using SQLT_TIMESTAMP_TZ binds | ||
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 | ||
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 | ||
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 | ||
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. | ||
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 | ||
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 | ||
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 | ||
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) | ||
11.2.0.3, 12.1.0.1 | dbms_shared_pool.keep does not fully prevent cursor from being aged out | ||
11.2.0.3, 12.1.0.1 | get_geometry() does not use bind variables internally leading to shared pool fragmentation | ||
12.1.0.1 | High Version Count with PURGED_CURSOR reason - superceded | ||
11.2.0.3, 12.1.0.1 | ORA-7445 [kqlr_get_diana_ts] when using _kgl_debug | ||
11.2.0.3, 12.1.0.1 | high parse time with cursor_sharing=force when session_cached_cursors set | ||
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) | ||
11.2.0.4, 12.1.0.1 | High version count for child cursors referencing a remote object due to AUTH_CHECK_MISMATCH | ||
11.2.0.3, 12.1.0.1 | Excess CPU & memory use / ORA-4030 / ORA-4031 parsing deeply nested queries | ||
11.2.0.2.BP10, 11.2.0.3, 12.1.0.1 | ORA-7445 [kglsca] with KEPT cursors | ||
11.2.0.3, 12.1.0.1 | Excessive mutex waits with adaptive cursor sharing | ||
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 | ||
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 | ||
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 | ||
11.2.0.4, 12.1.0.1 | High version counts caused by auto tuned PGA memory | ||
11.2.0.3, 12.1.0.1 | ORA-4031 using structured binary XMLIndex | ||
11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 | High Version Count with PX_MISMATCH on Serial Queries in RAC | ||
11.2.0.4, 12.1.0.1 | Event 10503 does not work at session level | ||
E | 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 | |
11.2.0.3, 12.1.0.1 | Extended cursor sharing generates many shareable child cursors | ||
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 | ||
11.2.0.3, 12.1.0.1 | High VERSION_COUNT in V$SQLAREA for Text / domain index queries with peeked binds | ||
11.2.0.3, 12.1.0.1 | High version count on queries with NOAPPEND hint due to INST_DRTLD_MISMATCH | ||
11.2.0.3, 12.1.0.1 | Recursive SQL cursors not reused - PMON crashes instance with ORA-600 [kglLockOwnersListDelete] | ||
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) | ||
11.2.0.3, 12.1.0.1 | High memory group in ges_cache_ress and ORA-4031 errors in RAC | ||
11.2.0.4, 12.1.0.1 | RAC "ges_res_cache" may grow excessively causing poor performance and memory starvation | ||
11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 | SQL not shared due to INST_DRTLD_MISMATCH with global transaction | ||
11.2.0.3, 12.1.0.1 | PRO*COBOL bind variables not handled correctly (ORA-6502 and/or high version count) | ||
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 | ||
11.2.0.2, 12.1.0.1 | latch free waits for SQL Memory Manager latch / extra child cursors | ||
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 | ||
11.2.0.3, 12.1.0.1 | High version count with CURSOR_SHARING = FORCE due to CBO transformation | ||
11.2.0.2, 12.1.0.1 | Self deadlock with 'library cache lock' waits / OERI:17059 | ||
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 | ||
11.2.0.2, 12.1.0.1 | Multiple child cursors using PLSQL UROWID binds (bind_mismatch='Y') | ||
11.2.0.2, 12.1.0.1 | INSERT / MERGE child cursor leak due to INST_DRTLD_MISMATCH - superceded | ||
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 | ||
10.2.0.5.3, 11.2.0.2, 12.1.0.1 | ORA-4031 for "temporary tabl" using temporary table transformations (STAR / WITH) | ||
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 | ||
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 | ||
11.2.0.1.1, 11.2.0.2, 12.1.0.1 | ORA-4031 for "temporary tabl" on query with star transformation | ||
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 | ||
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] | ||
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 | ||
11.2.0.3, 12.1.0.1 | Need to tighten NLS cursor sharing criteria on child reload | ||
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 | ||
11.2.0.2, 12.1.0.1 | Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins | ||
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 | ||
11.2.0.2, 12.1.0.1 | Many child cursors when PARALLEL_INSTANCE_GROUP set wrong | ||
12.1.0.1 | High version count for remote SQL when CURSOR_SHARING set (FORCE or SIMILAR) | ||
12.1.0.0 | SGA memory leak in KGLH0 under shared pool | ||
+ | 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 | |
11.2.0.3 | High VERSION_COUNT due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS with secure view merging | ||
11.2.0.3 | Excessive "KTSL subheap" memory use in shared pool | ||
11.2.0.2 | ORA-600 [ksliwat5] followed by cpu spike/"library cache: mutex X" Waits | ||
11.2.0.1.BP10, 11.2.0.2 | OERI [kcbi_get_bhs_4] / "Shared IO Pool" takes too much SGA memory | ||
11.2.0.1 | High version count with CDC | ||
ORA-600 [kglKeepHandle] possible in 11.1.0 if cursor obsolete feature is enabled | |||
11.1.0.7.3, 11.2.0.1 | Large "kcbi io desc" shared pool allocations can lead to ORA-4031 | ||
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 | ||
11.2.0.1 | Many child cursors with CURSOR_SHARING = FORCE | ||
10.2.0.5, 11.1.0.7.8, 11.2.0.1 | NUMA Pool misconfigured at startup (ORA-4031) | ||
11.1.0.7.2, 11.2.0.1 | ORA-4031 with kgs-heap overusing one subpool | ||
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 | ||
11.1.0.7.5, 11.2.0.1 | OERI[kksfbc-new-child-thresh-exceeded] can occur / unnecessary child cursors | ||
11.1.0.7.3, 11.2.0.1 | An unbound child cursor may not be shared | ||
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 | ||
11.2.0.1 | Excess shared pool memory for AQ ("kwqiccns: notification st" chunks) | ||
11.1.0.7, 11.2.0.1 | Session cursor cache not used properly when CURSOR_SHARING=force/similar | ||
10.2.0.5, 11.2.0.1 | State object leak / ORA-4031 from stale guess DBAs on secondary IOT index | ||
10.2.0.5, 11.2.0.1 | 'latch: library cache' contention caused by queries on V$ views. | ||
10.2.0.5, 11.2.0.1 | DBMS_SHARED_POOL.KEEP does not keep future child cursors | ||
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 | ||
11.2.0.1 | Excessive gets for "shared pool simulator" latch | ||
10.2.0.5, 11.1.0.7, 11.2.0.1 | ORA-4031 with NUMA | ||
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 | ||
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 | ||
11.1.0.7, 11.2.0.1 | High number of child cursors with adaptive cursor sharing | ||
10.2.0.5, 11.2.0.1 | ORA-4301 when using stored Java | ||
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 | ||
11.1.0.7 | High version count for cursor with binds with fix for bug 5872943 | ||
10.2.0.4, 11.1.0.7 | Shared pool latch contention due to fragmentation of large pool | ||
10.2.0.4, 11.1.0.7 | Parsing of large query takes long time / memory leak / ORA-4030 /4031 | ||
10.2.0.4, 11.1.0.7 | Intermittent ORA-1801 when shared pool under pressure | ||
10.2.0.4, 11.1.0.7 | Leak / ORA-4031 leak when DROP UNUSED COLUMN issued on large partitioned table | ||
ORA-4031 in DB instance when using ASM due to high use of "init_heap_kfsg" memory | |||
V$SGASTAT shows "obj stat memo" increases continuously | |||
OERI [17059] / excess child cursors for SQL referencing REMOTE objects | |||
12.1.0.1 | High version counts for SQL with binds (BIND_MISMATCH) | ||
11.1.0.6 | Shared pool memory leak of "hng: All sessi" memory | ||
10.2.0.4.4, 10.2.0.5, 11.1.0.6 | EXCHANGE PARTITION leaks "KGL handles" shared pool memory | ||
10.2.0.4, 11.1.0.6 | Leak in perm allocations with "library cache" comments (ORA-4031) | ||
10.2.0.4, 11.1.0.6 | 'gcs resources' and 'gcs shadows' are imbalanced across shared pool subpools | ||
10.2.0.4, 11.1.0.6 | Event 10503 does not work in 10.2.0.1 - 10.2.0.3 inclusive | ||
D | 10.2.0.4, 11.1.0.6 | "mvobj part des" leaked memory after partition DDL (ORA-4031) | |
10.2.0.4, 11.1.0.6 | Shared pool memory use / ORA-4031 due to "obj stat memo" in one subpool | ||
10.2.0.4, 11.1.0.6 | _FIX_CONTROL parameter leaks memory in the shared pool | ||
10.2.0.4, 11.1.0.6 | Library cache allocation for 'column mapping' not using uniform sized extents | ||
10.2.0.5, 11.1.0.6 | Unnecessary soft parses with connection pooling | ||
10.2.0.4, 11.1.0.6 | ORA-4031 while shared heap still has unused reserved extents | ||
10.2.0.3, 11.1.0.6 | Excessive CPU time spent freeing cursor stats when shared pool under load | ||
10.2.0.4, 11.1.0.6 | ORA-4031 with multiple partially-allocated permanent chunks | ||
10.2.0.4, 11.1.0.6 | SQL apply does not use bind variables while processing sequence updates | ||
10.2.0.3, 11.1.0.6 | Intermittent wrong data / NLS errors (eg ORA-1801) | ||
C | 10.2.0.4, 11.1.0.6 | Bind peeking may occur when it should not | |
10.2.0.3, 11.1.0.6 | Number of configured shared pool subpools not correct | ||
9.2.0.8, 10.2.0.4, 11.1.0.6 | Cursors not shared when executing procedures over a dblink | ||
9.2.0.8, 10.2.0.4, 11.1.0.6 | Cursors accessing remote tables may be repeatedly rebuilt and not used | ||
10.2.0.4, 11.1.0.6 | IO requests can flush the pool / signal a hidden ORA-4031 error | ||
9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 | High version count with cursor_sharing=force | ||
10.2.0.4, 11.1.0.6 | Bind peeked parallel cursors do not share | ||
10.1.0.5, 10.2.0.2, 11.1.0.6 | High version_count with cursor sharing | ||
9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 | ORA-4031 querying V$SEGSTAT | ||
10.2.0.4 | Extra child cursors for INSERT or MERGE SQL with fix for bug 4701527 | ||
P* | 10.2.0.4, 11.1.0.7 | Many child cursors possible for SQL using BINDS | |
"obj stat mem" leak when global temporary tables used | |||
10.1.0.5, 10.2.0.1 | Shared cursor sizes larger than earlier releases | ||
9.2.0.8, 10.1.0.5, 10.2.0.1 | High pseudo-cursor version count | ||
9.2.0.7, 10.1.0.5, 10.2.0.1 | Cursors with TABLE() expressions no shared when USE_STORED_OUTLINES set | ||
E | 10.1.0.5, 10.2.0.1 | Enhancement to help reduce library cache latch contention | |
10.1.0.5, 10.2.0.1 | Excess shared pool usage when CURSOR_SPACE_FOR_TIME set | ||
10.1.0.5, 10.2.0.1 | EXECUTE IMMEDIATE of anonymous PLSQL may be reparsed unnecessarily | ||
10.1.0.5, 10.2.0.1 | Subpool imbalance for "session parameters" can lead to ORA-4031 | ||
9.2.0.7, 10.1.0.5, 10.2.0.1 | Dump (kxsDump) possible when ORA-4031 attempting to be signalled | ||
10.1.0.5, 10.2.0.1 | Auditing can cause excess shared pool memory use | ||
10.1.0.4, 10.2.0.1 | Latch contention in cursor cache when flushing AWR SQL statistics | ||
9.2.0.7, 10.1.0.5, 10.2.0.1 | Very small SGA memory leak in RAC environments | ||
9.2.0.7, 10.1.0.4, 10.2.0.1 | PLSQL Commit / Rollback wastes cursors and is slower than 8i | ||
10.1.0.3, 10.2.0.1 | ORA-4031 can cause many SGA heapdumps to occur causing shared pool latch contention | ||
10.2.0.1 | PLSQL name resolution problem for different SCHEMA | ||
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 | ||
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 | ||
10.2.0.1 | DBMS_LOB.LOADFROMFILE leaks library cache locks / shared pool "perm" memory | ||
9.2.0.6, 10.1.0.3, 10.2.0.1 | Invalidating cursors frequently can fragment the SGA and leak "kglau" memory | ||
10.2.0.1 | ORA-4031 due to shared_pool fragmented with high ges resources & enqueues | ||
9.2.0.4, 10.2.0.1 | ORA-4031 parsing query with numerous groupings | ||
9.2.0.6, 10.1.0.3, 10.2.0.1 | Internal SQL in the extensible optimizer does not always use bind variables | ||
10.1.0.5, 10.2.0.1 | OUT binding in "call" slower than OUT binding in "begin ... end" | ||
9.2.0.8, 10.1.0.2 | Truncate partition tables leaks SGA memory | ||
9.2.0.6, 10.1.0.2 | OERI[15201] if shared pool under load | ||
E | 9.2.0.5, 10.1.0.2 | Internal change to help avoid ORA-4031 for multi-subpool SGAs | |
9.2.0.5, 10.1.0.2 | Additional trace and diagnostics for ORA-4031 errors | ||
9.2.0.5, 10.1.0.2 | ORA-4031 with Streams apply site | ||
+C | 9.2.0.5, 10.1.0.2 | Fix to help reduce the chances of an ORA-4031 with high OPEN_CURSORS | |
9.2.0.5, 10.1.0.2 | Excessive shared pool permanent memory use / ORA-4031 possible with SESSION_CACHED_CURSORS | ||
10.1.0.2 | Reloaded cursors can leak shared pool memory | ||
9.2.0.5, 10.1.0.2 | LOB manipulation can leak "buffer" state objects causing hangs/shared pool problems | ||
9.2.0.5, 10.1.0.2 | Database hang / latch contention possible | ||
9.2.0.5, 10.1.0.2 | Cursors may not be shared when using long binds | ||
9.2.0.5, 10.1.0.2 | Under heavy load some shared pool memory may not be freed due to latch contention | ||
9.2.0.5, 10.1.0.2 | Child cursors are not cached when executing SQL with a DB LINK when sql_trace=true | ||
9.2.0.5, 10.1.0.2 | DML cursors may not be shared after ALTER SESSION DISABLE|FORCE PARALLEL QUERY | ||
9.2.0.6, 10.1.0.2 | Domain indexes can produce recursive literal SQL (affects shared pool) | ||
9.2.0.4, 10.1.0.2 | Shared pool memory leak / ORA-4031 when collecting segment statistics | ||
9.2.0.4, 10.1.0.2 | ORA-4031 possible from SMON during SHUTDOWN or STARTUP | ||
9.2.0.4, 10.1.0.2 | Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors | ||
9.2.0.4, 10.1.0.2 | Cursors not shared when CREATE_STORED_OUTLINES set to a category | ||
+ | 9.2.0.4, 10.1.0.2 | USE_STORED_OUTLINE may not share SQL / PLSQL anonymous blocks | |
9.2.0.3, 10.1.0.2 | High memory use optimizing SQL with functional indexes and many expressions | ||
9.2.0.3, 10.1.0.2 | Direct LOB reads can result in ORA-4031 / excessive shared pool usage | ||
9.2.0.5, 10.1.0.2 | ORA-4031 possible in RAC environment under load | ||
9.2.0.5, 10.1.0.2 | SGA memory growth using outbound TCP/IP database links from shared servers | ||
9.2.0.8, 10.1.0.2 | OERI[504] on "shared pool" latch possible | ||
9.0.1.4, 9.2.0.2, 10.1.0.2 | Changes to reduce contention on "library cache latch" | ||
C | 9.2.0.5, 10.1.0.2 | Different static PLSQL cursors may not be shared | |
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 | ||
9.0.1.4, 9.2.0.2, 10.1.0.2 | Recursive SQL under triggers can contribute to latch contention | ||
9.2.0.5, 10.1.0.2 | Excessive SGA use / ORA-4031 possible with large number of JOBS | ||
* | 9.2.0.7 | OERI[KGHPIH:DS] can occur when shared pool under load | |
9.2.0.7 | Cursors not shared for certain SQL with SQL_TRACE enabled and fix for bug 2228280 | ||
9.2.0.6 | ORA-6540 / shared pool memory use from EMON during PLSQL notification | ||
9.2.0.5 | Allow Streams to use > 10% shared pool before spilling | ||
9.2.0.5 | ORA-4031 / excessive shared pool using LGWR ASYNC or SYNC=PARALLEL | ||
9.2.0.4 | Excessive shared pool memory use for parse of certain statments using functional indexes | ||
9.2.0.3 | FIRST_ROWS(K) HINT causes cursor not to be shared | ||
9.0.1.4, 9.2.0.2 | Client side PLSQL can cause excessing "library cache lock" and latch gets | ||
9.2.0.5 | ORA-4031 possible using STREAMS on multi-cpu machine | ||
9.2.0.2, 10.1.0.2 | Shared pool LEAK on global enqueue timeouts in RAC | ||
9.2.0.2 | Anonymous PLSQL not shared with SQL_TRACE / TIMED_STATISTICS | ||
9.0.1.4, 9.2.0.1 | X$KSMLRU does not work for multiple shared pool heaps | ||
9.0.1.4, 9.2.0.1 | ORA-4031 / excessive shared pool usage from query with full outer join / union | ||
9.0.1.4, 9.2.0.1 | ALTER VIEW operations may encounter ORA-4031 errors | ||
9.0.1.4, 9.2.0.1 | SHARED_POOL_RESERVED_SIZE parameter is ignored | ||
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 | ||
9.0.1.3, 9.2.0.1 | ORA-4031 possible during patch set upgrade scripts | ||
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 | ||
8.1.7.4, 9.0.1.3, 9.2.0.1 | ORA-4031 / excessive "miscellaneous" shared pool use possible (many PINS) | ||
8.1.7.3, 9.0.1.3, 9.2.0.1 | Child library cache latch contention using PLSQL functions in large selects | ||
8.1.7.3, 9.0.1.2, 9.2.0.1 | ORA-4031 / high CPU from OR expansion of negated predicates with RBO | ||
8.1.7.3, 9.0.1.3, 9.2.0.1 | Reduce latch hold time for "row cache objects" latch | ||
8.1.7.4, 9.0.1.1, 9.2.0.1 | Unnecessary hold of library cache latch in RAC or OPS environments | ||
9.0.1.2, 9.2.0.1 | Identical SQL/PLSQL using different Array sizes are not shared | ||
8.1.7.3, 9.0.1.2, 9.2.0.1 | ORA-4031 / Poor performance using persistent Object data types | ||
8.1.7.4, 9.0.1.0 | Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate | ||
9.0.1.0 | ORA-4031 / sga leak from IOT as inner table in NESTED LOOP | ||
8.1.7.1, 9.0.1.0 | ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access | ||
8.1.7.2, 9.0.1.0 | Identical SQL referencing SCHEMA.SEQUENCE.NEXTVAL not shared by different users | ||
8.1.7.2, 9.0.1.0 | OCI client make leak cursors (ORA-1000) | ||
8.1.7.2, 9.0.1.0 | Performance fix to reduce sleeps on "library cache latch" | ||
8.0.6.3, 8.1.7.1, 9.0.1.0 | Large row cache can cause long shared pool latch waits (OPS only) | ||
8.1.7.1, 9.0.1.0 | ALTER SYSTEM FLUSH SHARED POOL can cause PDML to hang | ||
+ | 8.1.7.2, 9.0.1.0 | ORA-4031 / SGA memory leak of PERMANENT memory for buffer handles | |
8.1.7.2, 9.0.1.0 | PLSQL may hang/spin/ORA-4031 with SQL WHERE (a,b) in ( (c1,d1),(c2,d2)... ) | ||
8.0.6.3, 8.1.7.1, 9.0.1.0 | Cursors referencing a fully qualified FUNCTION are not shared | ||
8.1.7.1, 9.0.1.0 | INSERT AS SELECT may not share SQL when it should | ||
Instance slow down/hang from DROP of object with many dependents | |||
8.1.6.3, 8.1.7.0 | ALTER SESSION FORCE PARALLEL PQ/DML/DDL does not share recursive SQL | ||
8.1.6.3, 8.1.7.0 | MV refresh unnecessarily invalidates shared cursors | ||
E | 8.1.7.0 | ENH: Reduce need to get PARENT library cache latch | |
8.1.6.2, 8.1.7.0 | Cursors not shared if both TIMED_STATISTICS and SQL_TRACE are enabled | ||
8.1.6.2, 8.1.7.0 | Cursors may not be shared in 8.1 when they should be | ||
+ | 8.1.7.0 | Cursor invalidations can waste shared memory (heap 0) | |
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 | ||
8.1.6.2, 8.1.7.0 | ORA-4031 when DROPPING a PARTITION | ||
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. | ||
8.0.6.2, 8.1.6.3, 8.1.7.0 | OERI:16606 may be reported under very heavy shared pool load | ||
+E | 8.0.6.0, 8.1.6.0 | ENH: More freelists for shared pool memory chunks (reduced latch contention) | |
8.1.5.1, 8.1.6.0 | Cursors are not shared if SQL_TRACE or TIMED_STATISTICS is TRUE | ||
8.1.5.1, 8.1.6.0 | TIMED_STATISTICS can affect cursor sharing / Dump from EXPLAIN or enable/disable SQL_TRACE | ||
8.1.6.0 | Shared pool memory for views higher if QUERY_REWRITE_ENABLED set | ||
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 | ||
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) | ||
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. | ||
8.0.6.2, 8.1.5.0 | SQL from PLSQL using NUMERIC binds may not be shared when it should | ||
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 | ||
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15747463/viewspace-1246003/