ORA-4031错误原理及诊断脚本汇总
来源：互联网 发布：《linux就该这么学》 编辑：程序博客网 时间：2024/06/05 17:00
4031_diag_script.zip
1. SGA中的内存池包含不同大小的内存块。当数据库启动时，就有一个大的内存块分配并被hush buckets 里的空闲列表追踪。随着时间推移，随着内存的分配和释放，内存块被按照大小在不同的hush buckets间移动。当SGA里任何一个内存池里出现不能满足内部分配请求的情况时，ORA-04031就出现了。
shared pool共享池的管理方式不同于其它的内存池。。共享池存放与数据字典和library cache有关的信息。但是，这些内存区域根据空闲列表和最近使用算法(LRU)管理。当在共享池的所有搜索结束后，从LRU列表清除所有的可能清除的对象， 多次扫描空闲列表后，仍没有找到内存块，ORA-04031就出现了。这意味着ORA-04031很难预测。
2. 对共享池的监测，可以看它是否包含许多类似的SQL,只有文字不同。 这种情况会占用更多的共享池内存并引共享池碎片，过多的共享池碎片(fragment)会导致虽然共享池中仍有大量的free memory，但都是尺寸较小的内存块(chunk)，当Oracle进程申请一些较大的连续内存空间(memory chunk)时，虽然共享池中的free memory大小远大于申请的连续空间大小，仍会引发ORA-4031错误。使用绑定变量可以使SQL 共享。使用本文所附的脚本可以查出内存中是否有许多类似SQL。
即使使用了绑定变量后，仍然可能存在高version count（子指针）的情况。为了使子指针共享，CURSOR_SHARING参数可能需要调整。metalink 文档Note 296377.1 和 261020.1可以提供详细信息。若造成4031的原因是由于未绑定变量或者游标无法共享导致的过度硬解析(Hard Parse)，则应当调整应用绑定变量或者调整初始化参数。
3. 9i中开始引入shared pool subpool子池技术。设计多个共享池子池的目的是分散单个shared pool LRU Latch的并行压力。 ，每一个子池都包含自有的LRU LIST和保留区域等其他内存结构。 subpools子池的数量会在Oracle实例启动(startup nomount)时根据SGA_MAX_SIZE(或11g中的memory_max_target)以及服务器上的CPU数量而自动决定。子池数量最多为7个，在Card系统中为4个。
每一个subpool都是一个”迷你型”的共享池，其包括自有的Freelist、内存结构记录和LRU LIST。subpool子池技术是对shared pool并发扩张能力的增强，且每一个子池现在都使用独立的shared pool child latch来保护。这意味着不再像9i以前的版本那样因为只有一个shared pool latch而剧烈争用了。
但是在实际使用中发现版本9i中仍存在一些BUG，可能导致在子池之间的内存使用分布不平衡均匀，这可能导致虽然部分子池使用率不高，但是个别子池内存过度分配，从而导致ORA-4031错误。一般把这种现象称作”subpool imbalance”。
This issue could occur if in the “Memory Utilization of Subpool” sections in your trace file, there is skewed distribution of allocations across subpools, i.e. allocation size for one area in a subpool is much larger than the same area in another subpool.
Starting in 9i, the SGA can be divided into subpools. Multiple Subpools are designed to relieve pressure on a single LRU latch in the Shared Pool. Each subpool will include its own LRU list, Reserved Area, etc. The number of subpools is computed by an algorithm using the SGA_MAX_SIZE (or memory_max_target in 11g), and the number of CPU’s on the server. The maximum number of subpools is 7.
The shared pool and its associated reserved area, as well as the large pool, participate in subpooling. Each subpool is a “mini” shared pool, having its own set of Free Lists, memory structure entries, and LRU list. This was a scalability change made to the Shared Pool/Large Pool to increase the throughput of these pools in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared/Large Pool for a single latch as in earlier versions.
If the distribution of memory usage is not balanced somewhat equally among the subpools, we can get an ORA-4031 as one subpool is over allocated while others remain under allocated. This condition is known as a subpool imbalance.
By analyzing the uploaded file, we have found the following symptoms that may have caused the issue:
** In your trace file, there is evidence of subpool imbalance:
sql area in subpool 0 has 64536 bytes, while in subpool 4 it has 271924816 bytes, which is 4213 times larger.
Recommended Solution
1) Decrease the number of subpools in use, or increase SHARED_POOL_SIZE, depending on the current number of subpools.(To find the current number of subpools, see REFERENCE below)
If the current number of subpools is…
2…………….increase the parameter SHARED_POOL_SIZE by 15%
Note: If you decrease the subpools to one, you limit the Shared Pool to only one Least Recently Used (LRU) latch. One very large subpool maintains a long LRU list and memory operations in the Shared Pool can impact performance on the database. The level of impact is dependent on many factors. Testing of this change in your environment is the only reliable method to determine the cost of making this change in your database.
3…………….reduce the number of subpools to 2
4…………….reduce the number of subpools to 2
5…………….reduce the number of subpools to 3
6…………….reduce the number of subpools to 3
7…………….reduce the number of subpools to 3
Note: Decreasing the number of subpools (>1) may help alleviate the imbalance problem without affecting performance as heavily. Again testing of the change in your environment is the only reliable method to determine the performance impacts in your database.
You can change the number of subpools by:
SQL> alter system set “_kghdsidx_count”=(desired value of number of subpools) scope=spfile;
Note: _kghdsidx_count in the above mentioned SQL is a hidden parameter. However, changing this particular hidden parameter will not have side-effects within the database and can be modified as a workaround. This is not intended to be a long term fix.
2) Restart the database for the change to take effect.
3) If ORA-04031 errors persist, please review the following notes for known subpool imbalance issues.
NOTE:811974.1 – ORA-4031 In Partition Maintenance Job From Subpool Imbalance (Doc ID 811974.1)
NOTE:835176.1 – Ora-4031 Errors using Full Outer Joins
NOTE:6271590.8 – Bug 6271590 – SGA subheap imbalance with lots of free memory in a few subheaps
NOTE:4184298.8 – Bug 4184298 – Subpool imbalance for “session parameters” can lead to ORA-4031
o Instructions for capturing a heapdump from the next occurrence of an ORA-04031 error are shown below.
alter system set events ’4031 trace name HEAPDUMP level 2′;
o Turn this event off using
alter system set events ’4031 trace name HEAPDUMP off’;
4) If the latest patchset recommended by the above notes have been applied and ORA-04031 errors persist, please submit a new Service Request to have the issue investigated by Oracle Support.
NOTE: If you captured any heapdump please upload to the Service Request to expedite the resolution time on the Service Request.
REFERENCE: To check the current number of subpools, use the following query:
set pages 1000 lines 120 col name for a60col value for a30 spool diagnosis1.lstSELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; SELECT substr(sql_text,1,90) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5GROUP BY substr(sql_text,1,90) HAVING count(*) > 30 ORDER BY 2;select * from v$sgastat where pool like 'shared%' order by bytes;select sa.sql_text,sa.version_count ,ss.* from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.ADDRESS andsa.version_count > 50 order by sa.version_count ;select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx andnam.ksppinm like '%shared%' order by 1;col free_space for 999,999,999,999 head "TOTAL FREE"col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"col free_count for 999,999,999,999 head "COUNT"col reqeust_misses for 999,999,999,999 head "REQUEST|MISSES"col reqeust_failures for 999,999,999,999 head "REQUEST|FAILURES"col max_free_size for 999,999,999,999 head "LARGEST CHUNK"select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZE from v$shared_pool_reservedcol Parameter format a25col "Session Value" format a15col "Instance Value" format a15select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indx and a.ksppinm in('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');set pagesize 100select * from v$sga_dynamic_components;col requests for 999,999,999col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "col last_miss_size for 999,999,999 head "LAST MISS|SIZE "col pct for 999 head "HIT|% "col request_failures for 999,999,999,999 head "FAILURES"select requests,decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_sizefrom v$shared_pool_reserved;select p.inst_id, p.free_space, p.avg_free_size, p.free_count, p.max_free_size, p.used_space, p.avg_used_size, p.used_count, p.max_used_size, s.requests, s.request_misses, s.last_miss_size, s.max_miss_size, s.request_failures, s.last_failure_size, s.aborted_request_threshold, s.aborted_requests, s.last_aborted_size from (select avg(x$ksmspr.inst_id) inst_id, sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free_space, avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg_free_size, sum(decode(ksmchcls,'R-free',1,0)) free_count, max(decode(ksmchcls,'R-free',ksmchsiz,0)) max_free_size, sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used_space, avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg_used_size, sum(decode(ksmchcls,'R-free',0,1)) used_count, max(decode(ksmchcls,'R-free',0,ksmchsiz)) max_used_size from x$ksmspr where ksmchcom not like '%reserved sto%') p, (select sum(kghlurcn) requests, sum(kghlurmi) request_misses, max(kghlurmz) last_miss_size, max(kghlurmx) max_miss_size, sum(kghlunfu) request_failures, max(kghlunfs) last_failure_size, max(kghlumxa) aborted_request_threshold, sum(kghlumer) aborted_requests, max(kghlumes) last_aborted_size from x$kghlu) s;SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;set pagesize 80set verify offset heading offset feedback offset termout offcol sp_size format 999,999,999 justify rightcol x_sp_used format 999,999,999 justify rightcol sp_avail format 999,999,999 justify rightcol sp_sz_pins format 999,999,999 justify rightcol sp_no_pins format 999,999 justify rightcol sp_no_obj format 999,999 justify rightcol sp_sz_obj format 999,999 justify rightcol sp_no_stmts format 999,999 justify rightcol sp_sz_kept_chks format 999,999,999 justify rightcol sp_no_kept_chks format 999,999 justify rightcol val2 new_val x_sp_size noprintselect value val2from v$parameterwhere name='shared_pool_size'/col val2 new_val x_sp_used noprintcol val3 new_val x_sp_no_stmts noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3from v$sqlarea/col val2 new_val x_sp_no_obj noprintcol val3 new_val x_sp_sz_obj noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')/col val2 new_val x_sp_avail noprintselect sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'/col val2 new_val x_sp_no_kept_chks noprintcol val3 new_val x_sp_sz_kept_chks noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere kept='YES'/col val2 new_val x_sp_no_pins noprintselect count(*) val2from v$session a, v$sqltext bwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value/col val2 new_val x_sp_sz_pins noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2from v$session a, v$sqltext b, v$sqlarea cwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value and b.address||b.hash_value = c.address||c.hash_value/set termout onset heading offttitle - center 'Shared Pool''s Library Cache Information' skip 2select 'Size : ' ||&x_sp_size sp_size, 'Number of shared cursors : ' ||&x_sp_no_stmts sp_no_stmts, 'Used by shared cursors : ' ||&x_sp_used, 'Number of programmatic constructs : ' ||&x_sp_no_obj sp_no_obj, 'Used by programmatic constructs : ' ||&x_sp_sz_obj sp_sz_obj, 'Available : ' ||&x_sp_avail sp_avail, 'Kept object chunks : ' ||&x_sp_no_kept_chks sp_no_kept_chks, 'Kept object chunks size : ' ||&x_sp_sz_kept_chks sp_sz_kept_chks, 'Pinned statements : ' ||&x_sp_no_pins sp_no_pins, 'Pinned statements size : ' ||&x_sp_sz_pins sp_sz_pinsfrom dual/ttitle offset heading onset feedback onset pages 1000 lines 120 col name for a60col value for a30select * from v$sgastat where pool like 'shared%' and name='free memory';select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", count(*) "Count" , max(KSMCHSIZ) "Biggest", trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ<140 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) UNION ALL select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 140 and 267 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) UNION ALLselect '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 268 and 523 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) UNION ALL select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 524 and 4107 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) UNION ALL select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ >= 4108 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded FROM (SELECT ksmlrcom alloc_type, ksmlrsiz alloc_size, ksmlrnum num_objs_flushed, ksmlrhon object_loaded, RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking FROM x$ksmlru WHERE inst_id = USERENV('INSTANCE') AND ksmlrsiz > 0) WHERE order_ranking 400) WHERE order_ranking 0 AND o.type LIKE 'JAVA%') WHERE order_ranking 0 AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')) WHERE order_ranking 0 AND o.type = 'CURSOR')WHERE order_ranking set pages 1000 lines 120 col name for a60col value for a30 spool diagnosis1.lstSELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; SELECT substr(sql_text,1,90) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5GROUP BY substr(sql_text,1,90) HAVING count(*) > 30 ORDER BY 2;select * from v$sgastat where pool like 'shared%' order by bytes;select sa.sql_text,sa.version_count ,ss.* from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.ADDRESS andsa.version_count > 50 order by sa.version_count ;select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx andnam.ksppinm like '%shared%' order by 1;col free_space for 999,999,999,999 head "TOTAL FREE"col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"col free_count for 999,999,999,999 head "COUNT"col reqeust_misses for 999,999,999,999 head "REQUEST|MISSES"col reqeust_failures for 999,999,999,999 head "REQUEST|FAILURES"col max_free_size for 999,999,999,999 head "LARGEST CHUNK"select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZE from v$shared_pool_reservedcol Parameter format a25col "Session Value" format a15col "Instance Value" format a15select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indx and a.ksppinm in('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');set pagesize 100select * from v$sga_dynamic_components;col requests for 999,999,999col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "col last_miss_size for 999,999,999 head "LAST MISS|SIZE "col pct for 999 head "HIT|% "col request_failures for 999,999,999,999 head "FAILURES"select requests,decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_sizefrom v$shared_pool_reserved;select p.inst_id, p.free_space, p.avg_free_size, p.free_count, p.max_free_size, p.used_space, p.avg_used_size, p.used_count, p.max_used_size, s.requests, s.request_misses, s.last_miss_size, s.max_miss_size, s.request_failures, s.last_failure_size, s.aborted_request_threshold, s.aborted_requests, s.last_aborted_size from (select avg(x$ksmspr.inst_id) inst_id, sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free_space, avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg_free_size, sum(decode(ksmchcls,'R-free',1,0)) free_count, max(decode(ksmchcls,'R-free',ksmchsiz,0)) max_free_size, sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used_space, avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg_used_size, sum(decode(ksmchcls,'R-free',0,1)) used_count, max(decode(ksmchcls,'R-free',0,ksmchsiz)) max_used_size from x$ksmspr where ksmchcom not like '%reserved sto%') p, (select sum(kghlurcn) requests, sum(kghlurmi) request_misses, max(kghlurmz) last_miss_size, max(kghlurmx) max_miss_size, sum(kghlunfu) request_failures, max(kghlunfs) last_failure_size, max(kghlumxa) aborted_request_threshold, sum(kghlumer) aborted_requests, max(kghlumes) last_aborted_size from x$kghlu) s;SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;set pagesize 80set verify offset heading offset feedback offset termout offcol sp_size format 999,999,999 justify rightcol x_sp_used format 999,999,999 justify rightcol sp_avail format 999,999,999 justify rightcol sp_sz_pins format 999,999,999 justify rightcol sp_no_pins format 999,999 justify rightcol sp_no_obj format 999,999 justify rightcol sp_sz_obj format 999,999 justify rightcol sp_no_stmts format 999,999 justify rightcol sp_sz_kept_chks format 999,999,999 justify rightcol sp_no_kept_chks format 999,999 justify rightcol val2 new_val x_sp_size noprintselect value val2from v$parameterwhere name='shared_pool_size'/col val2 new_val x_sp_used noprintcol val3 new_val x_sp_no_stmts noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3from v$sqlarea/col val2 new_val x_sp_no_obj noprintcol val3 new_val x_sp_sz_obj noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')/col val2 new_val x_sp_avail noprintselect sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'/col val2 new_val x_sp_no_kept_chks noprintcol val3 new_val x_sp_sz_kept_chks noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere kept='YES'/col val2 new_val x_sp_no_pins noprintselect count(*) val2from v$session a, v$sqltext bwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value/col val2 new_val x_sp_sz_pins noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2from v$session a, v$sqltext b, v$sqlarea cwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value and b.address||b.hash_value = c.address||c.hash_value/set termout onset heading offttitle - center 'Shared Pool''s Library Cache Information' skip 2select 'Size : ' ||&x_sp_size sp_size, 'Number of shared cursors : ' ||&x_sp_no_stmts sp_no_stmts, 'Used by shared cursors : ' ||&x_sp_used, 'Number of programmatic constructs : ' ||&x_sp_no_obj sp_no_obj, 'Used by programmatic constructs : ' ||&x_sp_sz_obj sp_sz_obj, 'Available : ' ||&x_sp_avail sp_avail, 'Kept object chunks : ' ||&x_sp_no_kept_chks sp_no_kept_chks, 'Kept object chunks size : ' ||&x_sp_sz_kept_chks sp_sz_kept_chks, 'Pinned statements : ' ||&x_sp_no_pins sp_no_pins, 'Pinned statements size : ' ||&x_sp_sz_pins sp_sz_pinsfrom dual/ttitle offset heading onset feedback onset pages 1000 lines 120 col name for a60col value for a30select * from v$sgastat where pool like 'shared%' and name='free memory';select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", count(*) "Count" , max(KSMCHSIZ) "Biggest", trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ<140 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) UNION ALL select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 140 and 267 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) UNION ALLselect '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 268 and 523 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) UNION ALL select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 524 and 4107 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) UNION ALL select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ >= 4108 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded FROM (SELECT ksmlrcom alloc_type, ksmlrsiz alloc_size, ksmlrnum num_objs_flushed, ksmlrhon object_loaded, RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking FROM x$ksmlru WHERE inst_id = USERENV('INSTANCE') AND ksmlrsiz > 0) WHERE order_ranking 400) WHERE order_ranking 0 AND o.type LIKE 'JAVA%') WHERE order_ranking 0 AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')) WHERE order_ranking 0 AND o.type = 'CURSOR')WHERE order_ranking
set pages 1000 lines 120 col name for a60col value for a30 spool diagnosis1.lstSELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; SELECT substr(sql_text,1,90) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5GROUP BY substr(sql_text,1,90) HAVING count(*) > 30 ORDER BY 2;select * from v$sgastat where pool like 'shared%' order by bytes;select sa.sql_text,sa.version_count ,ss.* from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.ADDRESS andsa.version_count > 50 order by sa.version_count ;select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx andnam.ksppinm like '%shared%' order by 1;col free_space for 999,999,999,999 head "TOTAL FREE"col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"col free_count for 999,999,999,999 head "COUNT"col reqeust_misses for 999,999,999,999 head "REQUEST|MISSES"col reqeust_failures for 999,999,999,999 head "REQUEST|FAILURES"col max_free_size for 999,999,999,999 head "LARGEST CHUNK"select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZE from v$shared_pool_reservedcol Parameter format a25col "Session Value" format a15col "Instance Value" format a15select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indx and a.ksppinm in('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');set pagesize 100select * from v$sga_dynamic_components;col requests for 999,999,999col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "col last_miss_size for 999,999,999 head "LAST MISS|SIZE "col pct for 999 head "HIT|% "col request_failures for 999,999,999,999 head "FAILURES"select requests,decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_sizefrom v$shared_pool_reserved;select p.inst_id, p.free_space, p.avg_free_size, p.free_count, p.max_free_size, p.used_space, p.avg_used_size, p.used_count, p.max_used_size, s.requests, s.request_misses, s.last_miss_size, s.max_miss_size, s.request_failures, s.last_failure_size, s.aborted_request_threshold, s.aborted_requests, s.last_aborted_size from (select avg(x$ksmspr.inst_id) inst_id, sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free_space, avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg_free_size, sum(decode(ksmchcls,'R-free',1,0)) free_count, max(decode(ksmchcls,'R-free',ksmchsiz,0)) max_free_size, sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used_space, avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg_used_size, sum(decode(ksmchcls,'R-free',0,1)) used_count, max(decode(ksmchcls,'R-free',0,ksmchsiz)) max_used_size from x$ksmspr where ksmchcom not like '%reserved sto%') p, (select sum(kghlurcn) requests, sum(kghlurmi) request_misses, max(kghlurmz) last_miss_size, max(kghlurmx) max_miss_size, sum(kghlunfu) request_failures, max(kghlunfs) last_failure_size, max(kghlumxa) aborted_request_threshold, sum(kghlumer) aborted_requests, max(kghlumes) last_aborted_size from x$kghlu) s;SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;set pagesize 80set verify offset heading offset feedback offset termout offcol sp_size format 999,999,999 justify rightcol x_sp_used format 999,999,999 justify rightcol sp_avail format 999,999,999 justify rightcol sp_sz_pins format 999,999,999 justify rightcol sp_no_pins format 999,999 justify rightcol sp_no_obj format 999,999 justify rightcol sp_sz_obj format 999,999 justify rightcol sp_no_stmts format 999,999 justify rightcol sp_sz_kept_chks format 999,999,999 justify rightcol sp_no_kept_chks format 999,999 justify rightcol val2 new_val x_sp_size noprintselect value val2from v$parameterwhere name='shared_pool_size'/col val2 new_val x_sp_used noprintcol val3 new_val x_sp_no_stmts noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3from v$sqlarea/col val2 new_val x_sp_no_obj noprintcol val3 new_val x_sp_sz_obj noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')/col val2 new_val x_sp_avail noprintselect sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'/col val2 new_val x_sp_no_kept_chks noprintcol val3 new_val x_sp_sz_kept_chks noprintselect decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3from v$db_object_cachewhere kept='YES'/col val2 new_val x_sp_no_pins noprintselect count(*) val2from v$session a, v$sqltext bwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value/col val2 new_val x_sp_sz_pins noprintselect sum(sharable_mem+persistent_mem+runtime_mem) val2from v$session a, v$sqltext b, v$sqlarea cwhere a.sql_address||a.sql_hash_value = b.address||b.hash_value and b.address||b.hash_value = c.address||c.hash_value/set termout onset heading offttitle - center 'Shared Pool''s Library Cache Information' skip 2select 'Size : ' ||&x_sp_size sp_size, 'Number of shared cursors : ' ||&x_sp_no_stmts sp_no_stmts, 'Used by shared cursors : ' ||&x_sp_used, 'Number of programmatic constructs : ' ||&x_sp_no_obj sp_no_obj, 'Used by programmatic constructs : ' ||&x_sp_sz_obj sp_sz_obj, 'Available : ' ||&x_sp_avail sp_avail, 'Kept object chunks : ' ||&x_sp_no_kept_chks sp_no_kept_chks, 'Kept object chunks size : ' ||&x_sp_sz_kept_chks sp_sz_kept_chks, 'Pinned statements : ' ||&x_sp_no_pins sp_no_pins, 'Pinned statements size : ' ||&x_sp_sz_pins sp_sz_pinsfrom dual/ttitle offset heading onset feedback onset pages 1000 lines 120 col name for a60col value for a30select * from v$sgastat where pool like 'shared%' and name='free memory';select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", count(*) "Count" , max(KSMCHSIZ) "Biggest", trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ<140 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) UNION ALL select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 140 and 267 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) UNION ALLselect '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 268 and 523 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) UNION ALL select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 524 and 4107 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) UNION ALL select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ >= 4108 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"FROM X$KSMSP GROUP BY KSMCHCLS;SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded FROM (SELECT ksmlrcom alloc_type, ksmlrsiz alloc_size, ksmlrnum num_objs_flushed, ksmlrhon object_loaded, RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking FROM x$ksmlru WHERE inst_id = USERENV('INSTANCE') AND ksmlrsiz > 0) WHERE order_ranking 400) WHERE order_ranking 0 AND o.type LIKE 'JAVA%') WHERE order_ranking 0 AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')) WHERE order_ranking 0 AND o.type = 'CURSOR')WHERE order_ranking
NBBugFixedDescription 1681610311.2.0.4, 12.1.0.1.1, 12.1.0.2, 12.2.0.0XIN aborts with ORA-4031 due to memory leak in OCIXStreamInCommit 1671075311.2.0.4, 12.1.0.2, 12.2.0.0Excess shared pool memory use / ORA-4031 from DROP EDITION 1657178511.2.0.4, 12.1.0.2, 12.2.0.0High “KTC latch subh” SGA memory use due to “lobs commit callback” 1395145612.1.0.2, 12.2.0.0ASMCMD does not use bind variables in its SQL – affects shared pool 1600915811.2.0.3.BP19, 11.2.0.4, 12.1.0.1ORA-4031 / excess shared pool memory using Securefiles 1593175611.2.0.3.BP21, 11.2.0.4, 12.1.0.1ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds) 867373711.2.0.4, 12.1.0.1Diagnostic enhancement to summarize sub-pool allocations in ORA-4031 trace files 1405329811.1.0.6“kksss-heap” memory leak from PMON cleanup (ORA-4031) when audit enabled 1588100411.2.0.4, 12.1.0.1Excessive Memory usage with Extended Cursor Sharing 1429282511.2.0.3.BP10, 11.2.0.4, 12.1.0.1ORA-4031 in ASM as default memory parameters values for 11.2 ASM instances low 1402905011.2.0.2.BP18, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1MMAN failing to donate granules despite unused PGA (possible ORA-4031) 1400309011.2.0.3.8, 11.2.0.3.BP20, 11.2.0.4, 12.1.0.1ORA-4031 with high memory utilization in the ‘init_heap_kfsg’ subheap / “ASM map headers” under ‘init_heap_kfsg’ 1381473911.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1Excessive KQR X PO” allocations in a RAC environment (can cause ORA-4031) 1353697112.1.0.1ORA-4031 with large partition tables – superseded 1345657311.2.0.4, 12.1.0.1Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing 1343093811.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1ORA-4031 with high “KTC latch subh” memory usage 1325538811.2.0.4, 12.1.0.1Slow parsing of large number of subqueries eligible for coalescing. 1325024411.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.1Shared pool leak of “KGLHD” memory when using multiple subpools 1307265411.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1Unnecessary ORA-4031 for “large pool”,”PX msg pool” from PQ slaves 1238796911.2.0.3, 12.1.0.1get_geometry() does not use bind variables internally leading to shared pool fragmentation 1178279011.2.0.3, 12.1.0.1Excess CPU & memory use / ORA-4030 / ORA-4031 parsing deeply nested queries 1165181011.2.0.2.3, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1ORA-4031 or Excess shared pool use can be seen by FileOpenBlock objects 1106319111.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.1ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory 1063409611.2.0.3, 12.1.0.1excessive memory usage with grouping sets and temp table transformation 1035963111.2.0.3, 12.1.0.1High elapsed time and shared pool usage during parse with virtual columns 1032595311.2.0.3, 12.1.0.1ORA-600[733] / ORA-4031 allocating memory for hash join – superceded 1030890611.2.0.3, 12.1.0.1ORA-4031 using structured binary XMLIndex 1008933311.2.0.2.6, 11.2.0.2.BP15, 11.2.0.3, 12.1.0.1“init_heap_kfsg” memory leaks in SGA of db instance using ASM 1008227711.2.0.1.BP12, 11.2.0.2.3, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1Excessive allocation in PCUR or KGLH0 heap of “kkscsAddChildNo” (ORA-4031) 1004293711.2.0.3, 12.1.0.1High memory group in ges_cache_ress and ORA-4031 errors in RAC 957867011.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.1ORA-4031 from frequent Partition Maintenance Operation 940302611.2.0.2, 12.1.0.1Startup or PQ can report ORA-4031 errors requesting large “PX msg pool” chunks of memory 932013010.2.0.5.3, 11.2.0.2, 12.1.0.1ORA-4031 for “temporary tabl” using temporary table transformations (STAR / WITH) 901598311.2.0.1.1, 11.2.0.2, 12.1.0.1ORA-4031 for “temporary tabl” on query with star transformation 893445711.2.0.2, 12.1.0.1ORA-4031 during EXPLAIN PLAN or ORA-4030 during SELECT 883559111.2.0.2, 12.1.0.1Excess memory use parsing invalid SQL with COUNT() in subquery select list 881336611.1.0.7.3, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1ORA-4031 due to over large granule size 853754411.2.0.2, 12.1.0.1Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins 750945111.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1SGA memory leak possible for “KSN WaitID” memory when using shared servers 1354839011.2.1.8.8Error 4031 in kgttusec – plsql memory > 1g unreliable 902600811.2.0.1.BP02, 11.2.0.2, 12.1.0.1“GES_RESOURCES” may increase over time with high enqueue activity 905890011.2.0.1.BP10, 11.2.0.2OERI [kcbi_get_bhs_4] / “Shared IO Pool” takes too much SGA memory 859947711.1.0.7.3, 11.2.0.1Large “kcbi io desc” shared pool allocations can lead to ORA-4031 852817110.2.0.5.6, 11.1.0.7.10, 11.2.0.1High shared pool use due to frequent ADD/EXCHANGE partition operations 837188410.2.0.5, 11.2.0.1Dumps/Errors/Excessive Parse Time and Memory allocation with Star transformation 824473410.2.0.5, 11.1.0.7.8, 11.2.0.1NUMA Pool misconfigured at startup (ORA-4031) 822142511.1.0.7.2, 11.2.0.1ORA-4031 with kgs-heap overusing one subpool 746246310.2.0.5, 11.2.0.1R-tree Index based spatial query on large table fails with ORA-4031 / ORA-7445 [kghualloc] 744380811.2.0.1Excessive Shared Memory utilization during Query Parsing 734044810.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1Excess shared pool memory use / ORA-4031 from REGEXP_LIKE – superceded 725018211.2.0.1Excess shared pool memory for AQ (“kwqiccns: notification st” chunks) 720792110.2.0.5, 11.2.0.1State object leak / ORA-4031 from stale guess DBAs on secondary IOT index 703162210.2.0.5, 11.2.0.1Spin on kkojnp / ORA-600 [15160] with Star transformation 686808010.2.0.5, 11.1.0.7, 11.2.0.1ORA-4031 with NUMA 685806210.2.0.4.1, 10.2.0.5, 11.1.0.7.5, 11.2.0.1Shared pool memory leak when services created / deleted 680050710.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1Shared pool memory leak (“ksws service *” memory) using EXPDP often 673056710.2.0.5, 11.2.0.1ORA-4031 / OERI [17137] with NUMA optimization enabled 662401111.1.0.7, 11.2.0.1ORA-4030 / ORA-4031 on startup with > 4Gb SGA_TARGET 653086110.2.0.5, 11.2.0.1ORA-4301 when using stored Java 633455210.2.0.4, 11.1.0.7, 11.2.0.1Hang / ORA-4031 / OERI:kfrcsoDelete_3 on rollback of ASM file resize 627159010.2.0.4.2, 10.2.0.5, 11.1.0.7, 11.2.0.1SGA subheap imbalance with lots of free memory in a few subheaps 608616211.2.0.1Slow parsing / Excessive Memory Usage with Star Transformation and Query Rewrite 612641511.1.0.7OERI [kjccgmb:1] during shutdown instance 601118210.2.0.4, 11.1.0.7Parsing of large query takes long time / memory leak / ORA-4030 /4031 538698610.2.0.4, 11.1.0.7Leak / 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 10363436 Memory leak / ORA-4031 using TDE column encryption with PKI keys 1280869611.1.0.6Shared pool memory leak of “hng: All sessi” memory 730691510.2.0.4.4, 10.2.0.5, 11.1.0.6EXCHANGE PARTITION leaks “KGL handles” shared pool memory 604305210.2.0.4, 11.1.0.6Leak in perm allocations with “library cache” comments (ORA-4031) 600161710.2.0.4, 11.1.0.6LCK may accumulate CPU time / ORA-4031 possible 595070810.2.0.4, 11.1.0.6‘gcs resources’ and ‘gcs shadows’ are imbalanced across shared pool subpools 561804910.2.0.4, 11.1.0.6“mvobj part des” leaked memory after partition DDL (ORA-4031) 557323810.2.0.4, 11.1.0.6Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool 554851010.2.0.4, 11.1.0.6_FIX_CONTROL parameter leaks memory in the shared pool 554838910.2.0.4, 11.1.0.6Library cache allocation for ‘column mapping’ not using uniform sized extents 550850510.2.0.4, 11.1.0.6ORA-4031 while shared heap still has unused reserved extents 547917210.2.0.4, 11.1.0.6ORA-4031 with multiple partially-allocated permanent chunks 537797310.2.0.4, 11.1.0.6Register schema fails with ORA-4031 524026411.1.0.6ORA-4030 / ORA-4031 / Excessive Memory Usage with view merging and large IN LISTs. 499495610.2.0.3, 11.1.0.6Number of configured shared pool subpools not correct 446705810.2.0.4, 11.1.0.6IO requests can flush the pool / signal a hidden ORA-4031 error 35198079.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6ORA-4031 querying V$SEGSTAT 329172310.2.0.3, 11.1.0.6DBWR may crash instance if shared pool memory low 6347725 “obj stat mem” leak when global temporary tables used 41951759.2.0.7, 10.1.0.5, 10.2.0.1Create MVIEW with large WHERE clause may dump / ORA-4031 418429810.1.0.5, 10.2.0.1Subpool imbalance for “session parameters” can lead to ORA-4031 39483849.2.0.7, 10.1.0.4, 10.2.0.1ORA-4031 possible using LGWR ASYNC for archiving 351342710.1.0.3, 10.2.0.1ORA-4031 can cause many SGA heapdumps to occur causing shared pool latch contention 341939610.2.0.1Restore spfile with dummy instance from ASM fails with RMAN-20001 / ORA-4031 34052379.2.0.6, 10.1.0.3, 10.2.0.1ORA-4031 / excessing SGA consumption due to large IN LIST and NOT IN list 335649210.2.0.1DBMS_LOB.LOADFROMFILE leaks library cache locks / shared pool “perm” memory 32994079.2.0.6, 10.1.0.3, 10.2.0.1Invalidating cursors frequently can fragment the SGA and leak “kglau” memory 304672510.2.0.1ORA-4031 due to shared_pool fragmented with high ges resources & enqueues 29539319.2.0.4, 10.2.0.1ORA-4031 parsing query with numerous groupings*46581889.2.0.8Long waits for “ksu process alloc latch yield” 42319219.2.0.8, 10.1.0.2Truncate partition tables leaks SGA memory 32324019.2.0.5, 10.1.0.2ORA-4031 with Streams apply site+31507059.2.0.5, 10.1.0.2Fix to help reduce the chances of an ORA-4031 with high OPEN_CURSORS 31506809.2.0.5, 10.1.0.2Excessive shared pool permanent memory use / ORA-4031 possible with SESSION_CACHED_CURSORS 308381810.1.0.2Reloaded cursors can leak shared pool memory 30776519.2.0.5, 10.1.0.2LOB manipulation can leak “buffer” state objects causing hangs/shared pool problems 284613810.1.0.2Dump (under opiosq0) from SVRMGRL sessions when shared pool is full 28177289.2.0.4, 10.1.0.2Shared pool memory leak / ORA-4031 when collecting segment statistics 28047249.2.0.4, 10.1.0.2Out of memory in JAVA POOL shows an ORA-4031 “shared pool” message 27988519.2.0.4, 10.1.0.2ORA-4031 possible from SMON during SHUTDOWN or STARTUP 27930389.2.0.4, 10.1.0.2Session may die with ORA-4031 if shared pool exhausted when storing object level statistics 27869689.2.0.4, 10.1.0.2Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors 26070299.2.0.3, 10.1.0.2High memory use optimizing SQL with functional indexes and many expressions 25867629.2.0.3, 10.1.0.2Direct LOB reads can result in ORA-4031 / excessive shared pool usage 25633019.2.0.5, 10.1.0.2ORA-4031 possible in RAC environment under load 25611159.2.0.5, 10.1.0.2SGA memory growth using outbound TCP/IP database links from shared servers 25231519.2.0.3, 10.1.0.2ORA-7445[updexe] with AUDIT_TRAIL=DB with stressed shared pool 25015919.2.0.3, 10.1.0.2ORA-928 / ORA-4031 on “ALTER VIEW <sys_view> COMPILE” if REMOTE objects exist with same name 24869549.2.0.3, 10.1.0.2ORA-4031 / Dump from concurrent DGURIServlet access to SQLX XMLType view 23518549.0.1.4, 9.2.0.2, 10.1.0.2Dispatchers may hang if ORA-4031 occurs 17823819.2.0.5, 10.1.0.2Excessive SGA use / ORA-4031 possible with large number of JOBS 28053359.2.0.4Excessive shared pool memory use for parse of certain statments using functional indexes 24420429.2.0.5ORA-4031 possible using STREAMS on multi-cpu machine 24725419.2.0.2PMON ORA-7445 [kghxfr] after shared server gets ORA-4031 22447899.0.1.4, 9.2.0.1ORA-4031 / excessive shared pool usage from query with full outer join / union 22446429.0.1.4, 9.2.0.1ALTER VIEW operations may encounter ORA-4031 errors 22085708.1.7.4, 9.0.1.4, 9.2.0.1ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view 21852949.2.0.1Shared server may dump (koklhfr) after ORA-4031 in “LARGE POOL” 21673269.0.1.3, 9.2.0.1ORA-4031 possible during patch set upgrade scripts 21436379.0.1.3, 9.2.0.1ORA-4031 possible starting the cache advisory 21291788.1.7.4, 9.0.1.3, 9.2.0.1Using NULL or an empty string (”) in an INLIST can cause ORA-4031 / waste shared pool memory 21040718.1.7.4, 9.0.1.3, 9.2.0.1ORA-4031 / excessive “miscellaneous” shared pool use possible (many PINS) 18659178.1.7.3, 9.0.1.2, 9.2.0.1ORA-4031 / high CPU from OR expansion of negated predicates with RBO 13335268.1.7.3, 9.0.1.2, 9.2.0.1ORA-4031 / Poor performance using persistent Object data types 16429649.0.1.0ORA-4031 / sga leak from IOT as inner table in NESTED LOOP 16405838.1.7.1, 9.0.1.0ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access+13976038.1.7.2, 9.0.1.0ORA-4031 / SGA memory leak of PERMANENT memory for buffer handles 13966758.1.7.2, 9.0.1.0PLSQL may hang/spin/ORA-4031 with SQL WHERE (a,b) in ( (c1,d1),(c2,d2)… ) 13407188.1.7.2, 9.0.1.0OERI:KCBSTC_PARENT_AB possible in CKPT following an ORA-4031 error 13485018.1.6.3, 8.1.7.0MV refresh unnecessarily invalidates shared cursors 13287098.0.6.2, 8.1.6.3, 8.1.7.0OERI:15428 can be raised when ORA-4031 is hit internally 12560118.0.6.2, 8.1.6.2, 8.1.7.0MTS servers may OERI:1113 after an ORA-4031 (OERI:730 on 8i) 10926218.1.6.2, 8.1.7.0ORA-4031 when DROPPING a PARTITION 8987988.1.5.1, 8.1.6.0ORA-4031 on startup from large DB_BLOCK_BUFFERS (or GC_RELEASABLE_LOCKS) 4313037.3.3.1, 7.3.4.0Startup gives ORA-4031 with GC_DB_LOCKS=157K
comm diag script:1. Upload the output for:> opatch lsinventory -detail2. Please upload following trace and incidents files associated with occurrences of 4031 errors.3. Upload the initialization parameter file for this database.4. How many times the error has been reported? Is it one-time or many ?5. Does the error happen around the same time?6. What has changed? Any parameter changed?7. Has the application changed or the workload increased?8. Kindly describe your environment.Is it RAC or standalone environment? Is ASM used ?9. what actions have been done during the occurrence of errors?10. Are you able to reproduce this issue at will?11. Provide the output for :connect user/passwordselect sql_id, sql_text, version_count from v$sqlstatswhere version_count > 5order by version_count desc;12. Provide the output for :connect user/passwordselect * from v$sga_dynamic_components;select * from V$MEMORY_DYNAMIC_COMPONENTS;select * from V$SHARED_POOL_RESERVED;select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv valwhere nam.indx = val.indx and nam.ksppinm like '%shared%' order by 1;select a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indxand a.ksppinm like '%kghdsidx%';select PQ_SLAVE_MISMATCH , PX_MISMATCH from V$SQL_SHARED_CURSOR;set linesize 90set pagesize 60column component format a25column Final format 99,999,999,999column STARTED format A25SELECT COMPONENT ,OPER_TYPE,FINAL_SIZE Final,to_char(start_time,'dd-mon hh24:mi:ss') STARTED FROM V$SGA_RESIZE_OPS;SELECT COMPONENT,OPER_TYPE,COUNT(1) FROM V$SGA_RESIZE_OPS GROUP BY COMPONENT,OPER_TYPE;SELECT A.BEGIN_INTERVAL_TIME,A.END_INTERVAL_TIME, B.BYTESFROM WRM$_SNAPSHOT A, DBA_HIST_SGASTAT BWHERE A.SNAP_ID = B.SNAP_IDAND B.POOL = 'SHARED POOL' AND B.NAME = 'KGH: NO ACCESS' ORDER BY 1;show parameter session_cached_cursors;
===================================================================================================================================
1) by checking the share pool usage
Shared Pool Usage
===========================================================
R-free - Reserved List
R-freea - Reserved List
free - Free Memory
freeabl - Memory for user / system processing
perm - Memory allocated to the system
recr - Memory for user / system processing
what exactly perm is referring to ? is there any indication for showing the healthiness of the share pool by referring to this statistics ?
we found that , every time there was a ora-4031 occur, the value of perm with a high value.
2) doc 146599.1, is it useful to to monitor X$KSMSP View , is it Similar to Heapdump Information ?
3) doc 146599.1, is it Useful to track allocations in the shared pool that cause other objects in the shared pool to be aged?
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
4)Is REQUEST_MISSES valid when init para SHARED_POOL_RESERVED_MIN_ALLOC not set?
1.) PERM allocations refer to PERMANENT allocations. Permanent allocations are never freed. They live till lifetime of instance. That is why those allocations keep on growing. For 10.2.0.4 are you using Automatic Shared Memory Management ?
2.) It is similar to heapdump information but If you are using Automatic shared_memory management it may not give exact information.
3.) It is useful in case you are seeing lots of invalidations/reloads. Query v$librarycache to get that information.
4.) By default, _shared_pool_reserved_min_alloc is set to 4400 bytes. So, request misses are valid in that case as well.
Please note that frequently querying x$ views is not recommended. Queries on these views are lock intensive and may impact performance.
a.) Permanent allocations cannot be flushed by using 'alter system flush shared_pool' command. Permanent allocations get released only after instance restart. There is no specified ratio/percentage of permanent chunks in the shared_pool space.
b.) The first point to start is
1. Monitor v$sgastat. Check for any component that is growing abnormally. For example, if 'KGH: NO ACCESS' component has grown abnormally, you can check in metalink for known bugs. In case the 'SQL area' is growing abnormally, it may be a cause of concern as application may not be bind variables. Then taking heapdump may reveal if that is cause of fragmentation.
2. Querying v$librarycache is of help to understand the HIT ratio, reloads and invalidations that have happened since instance startup.
3. In severe cases or for diagnostics, heapdumps may be taken along with errorstack.
'sql area' is a component that can be seen as a part of output from v$sgastat.
Suppose we have shared_pool sized to 500M and as the time passes. As the time progresses, in case you see this component growing to 200-250M, it is likely that application is not using bind variables. To check if application is using bind variables or not, you can gather AWR reports and check sections 'sql ordered by version count' This section would give SQLs that didn't get shared due to some reasons. After that SR can be raised for more understanding.
In case ORA-04031 error gets reported, you can refer the following document and upload the information to the SR for analysis.
Article-ID: Note 430473.1
Title: ORA-4031 Common Analysis/Diagnostic Scripts
1.) Library cache overview gives the following:
*** High level breakdown of memory ***
sharable : 39.93M
persistent : 32.11M
runtime : 30.27M
SQL Memory Usage (total) : 102.31M (80%)
Shareable memory refers to memory that can be shared. Example: if an sql is using bind variables, it is likely that the parse tree for that statement would be shared with next session executing the same sql.
Persistent memory is something similar to Permanent memory that would remain till lifetime.
Execution/runtime memory refers to the memory that is required during execution of a cursor/sql
There should be less number of SQLs that are executed just once. Because, if sql's are not shared, each SQL would be having its own execution plan and would get executed independently... in other words, multiple unshareable statements just one execution.
On the other hand, if SQLs are shared, there would be one statement with multiple executions.
2.) There should be less misses in the library cache. The more the hit percentage, more is the probability that a shareable sql is found in library cache. In case, we are not able to find the misses in library cache would increase and we would require reload of parse tree in the library cache.
3.) Resize operations are done internally by oracle when using ASMM using feedback mechanisms from advisories. In case there are lots of resize operations and there are lots of shrinks and growth of components happenning in the SGA, you may need to set the values of those components to some value and as such those components would not shrink below the specified value.
4.) Versions HWM: Gives The highest number of children that are present in the cache under a parent for an SQL statement, from the time when instance was last started.
I believe that you are trying to set these warnings and alert using EM.
Anyways, there is no rule of considering the permanent memory growth as Threshold and critical.
You can set 60% as threshold and 75% as critical.
Please note that 200M is very low for a production database to work. With 200M of shared_pool size, It is highly likely to receive ORA-04031 in this case.
Minimum size of shared_pool for 10g database is recommended as 500M based on empirical analysis. In case the permanent chunks are increasing abnormally, we would consider taking a heapdump incase the problem is occurring again and again.
When Automatic Shared Memory is in use, we have a concept of shared pool durations. In memory there is different lifetime for every memory data structure. Some data structures (memory chunks) remain till lifetime of instance (instance duration or permanent chunks) and some live just till the time session is there (session duration) and so forth. With ASMM enabled, permanent chunks don't scan LRU list to get memory. They directly contact granule manager for more memory. In case if granule manager is not able to release that memory, ORA-04031 gets reported.
In such cases, we would consider disabling ASMM (setting sga_target to zero and manually setting values of db_cache_size, shared_pool_size).
In case you have disabled ASMM and still seeing abnormal growth of permanent chunks, we may have to take heapdump for analysis and check in case it is a memory leak/bug. However, to alleviate the problem, DB restart is the only option because, permanent chunks would not get released using 'alter system flush shared_pool' command.
Regarding RAC and shared_pool_size:
RAC specific memory is allocated at the time of SGA creation, mostly in the shared pool. However, the memory does not count in the SHARED_POOL_SIZE as defined in the init.ora parameter file. Therefore, when migrating Oracle from single instance to RAC, you do not need to adjust the SHARED_POOL_SIZE parameter to accommodate the additional memory that RAC uses; Oracle does that automatically. Note that the memory for the KCL global cache lock elements will be allocated in the buffer cache, and not in the shared pool.
The note: 455179.1 clearly talks about how to calculate number of subpools allocated at startup. What is your question ?
Shared_pool reserved is for large requests greater than or equal to 4400 bytes. So, any request that wants shared pool space of 4400 bytes or more would search normal shared_pool space. In case it is not able to find the same sized chunk, only then it would go and scan the reserved pool for free chunks. So, only in case of large requests, shared_pool_reserved would come into picture.
In the view v$shared_pool_reserved, we generally look at "REQUEST_FAILURES" to check 'Number of times that no memory was found to satisfy a request.' In case this keeps on increasing, it may be a signal of impending ORA-04031 error.
It is the same question that you asked earlier as well.
Yes, the number of misses and number of objects executed once has to be monitored.
How much is the threshold value for that is something that we cannot easily suggest. Ideally, misses should be zero. That means, whatever we are trying to find is already present in the library cache. As suggested earlier in the output of 'LibCacheOverview.sql' , Ideal percentages for 1 time executions is 20% or lower.
The REQUEST_MISSES you are referring to the view from v$shared_pool_reserved. That is within the reserved area. You may be seeing lots of misses in the normal shared_pool area. Query v$librarycache to get the number of misses in library cache. And apart from Misses there are lots of other reasons to receive ORA-04031 error.
There are lots of queries that may have different version counts. Suppose:
SQL> select * from emp, may have 4 different children.
Another SQL may be having version count of 12. So, if 12 is the highest version count for the instance, Version HWM would report the highest version count for a sql in instance.
Largest memory object refers to the max. size of object that was loaded in shared pool. In your case it is 408,084 bytes.
REQUEST_MISSES is related to RESERVED POOL. Reserved pool is meant for large allocations like PL/SQL compilations or trigger compilation. Only requests that are larger than 4400 bytes can go and check for free space in reserved pool.
Please note that allocations below 4400 bytes would not go to reserved area. You may get ORA-04031 error while requesting for 64 bytes.
So, in this case REQUEST_MISSES parameter would not mean anything. It may be zero or any positive number. This is because a 64 bytes request would never go and check RESERVED POOL.
===================================================================================================================================
got BUG 9578670 - ORA-4031 WHILE DROP PARTITIONS so we need a patch for the bug
Bug 9578670: ORA-4031 WHILE DROP PARTITIONS, SOLUTION FROM DOC ID: 419551.1 DIDN'T HELP
Product Version 11.1.0.7
Fixed in Product Version 12.1
Fix By 11.2.0.3
No backport available for 11.2.0.2
REDISCOVERY INFORMATION:
If you are running frequent partition maintenance operations, have multiple
subpools in the shared pool, and encounter ORA-4031 out of memory errors due
to many "mvobj part des" allocations, you could be hitting this issue.
WORKAROUND:
Set "_kghdsidx_count"=1.
Alert file
----------
Wed Feb 16 04:26:22 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw00_13679.trc (incident=36418):
ORA-04031: unable to allocate 4128 bytes of shared memory ("shared pool","UPDATE_CREATE","PRTMV^3a844b88","kkpomSort hashed kpn's")
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36418/jasper_dw00_13679_i36418.trc
Wed Feb 16 04:26:44 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_ora_31713.trc (incident=36578):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^edb30de5","kglHeapInitialize:temp")
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36578/jasper_ora_31713_i36578.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw0e_6924.trc (incident=36466):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^56d22d85","kglHeapInitialize:temp")
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36466/jasper_dw0e_6924_i36466.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_cjq0_9684.trc (incident=36362):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^bd2af6d6","kglHeapInitialize:temp")
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36362/jasper_cjq0_9684_i36362.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw01_3033.trc (incident=36666):
ORA-04031: unable to allocate 3952 bytes of shared memory ("shared pool","SELECT ","pacdHds_kkpaco","kggec.c.kggfa")
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36666/jasper_dw01_3033_i36666.trc
Wed Feb 16 04:26:47 2011
Dumping diagnostic data in directory=[cdmp_20110216042647], requested by (instance=1, osid=31713), summary=[incident=36578].
...
Trace files
-----------
Not provided
Incident trace files
--------------------
jasper_dw01_3033_i36666.trc
*** ACTION NAME:(SYS_IMPORT_TABLE_03) 2011-02-16 04:26:47.535
Dump continued from file: /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw01_3033.trc
ORA-04031: unable to allocate 3952 bytes of shared memory ("shared pool","SELECT ","pacdHds_kkpaco","kggec.c.kggfa")
========= Dump for incident 36666 (ORA 4031) ========
*** 2011-02-16 04:26:47.535
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=4ujz53z0x60rn) -----
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("GDR"."AVG_ARH" NOT XMLTYPE) PARTITION ("D110120_22") ("TIMESTAMP","TEID","DST_IP","DST_PORT","SRC_IP","SRC_PORT","BYTES") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL)
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
0x3614e5dd0 58 package body SYS.KUPD$DATA_INT
0x361a817d0 3443 package body SYS.KUPD$DATA
0x3647ec9f0 14258 package body SYS.KUPW$WORKER
0x3647ec9f0 4535 package body SYS.KUPW$WORKER
0x3647ec9f0 9096 package body SYS.KUPW$WORKER
0x3647ec9f0 1688 package body SYS.KUPW$WORKER
---> HEAP DUMP heap name="SQLA^c1d302f4" desc=0x75dfc6b0
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
freeable 30 47528 1584.27
recreate 1 4056 4056.00
perm 1 80 80.00
free 1 1040 1040.00
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
TCHK^c1d302f4 12 48672 4056.00 92.35
perm 1 80 80.00 0.15
Free(heap.awk) 1 1040 1040.00 1.97
...
Total = 52704 bytes 51.47k 0.05MB
Descriptor 36de851e8 was the largest with size 48672
This is 92.35 % of the total heap
ds=0x36de851e8 has size 44616 (% of heap=84.65)
Memory used by descriptors with more than 10240 bytes:
Total = 44616 (% of heap=84.65)
Total PERMANENT memory actually used is 80
Largest CONTIGUOUS free memory (NORMAL) was 1040 ( 1.02k)
Largest descriptor count was 12
---> HEAP DUMP heap name="KGLH0^c1d302f4" desc=0x381acb728
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
perm 2 2648 1324.00
free 1 776 776.00
freeable 4 608 152.00
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
perm 2 2648 1324.00 65.67
Free(heap.awk) 1 776 776.00 19.25
kgltbtab 4 608 152.00 15.08
Total = 4032 bytes 3.94k 0.00MB
Total PERMANENT memory actually used is 2464
Largest CONTIGUOUS free memory (NORMAL) was 776 ( 0.76k)
Free List Bucket Summary :
Bucket 0 [size=0 ] Count= 2 Av.Size= 388 Max= 776
Opatch
------
There are no Interim patches installed in this Oracle Home.
RDA
----
db_cache_size 0
shared_pool_size 0
streams_pool_size 234881024
memory_target 13555990528
Minimum and Maximum Component Final Size
Component Lowest Low (MiB) Highest High (MiB)
DEFAULT buffer cache 1375731712 1312 2717908992 2592
java pool 67108864 64 67108864 64
large pool 33554432 32 67108864 64
shared pool 2382364672 2272 2919235584 2784
streams pool 234881024 224 301989888 288
Trace files
-----------
Trace file jasper_dw01_3033.trc not provided.
jasper_dw01_3033_i36666.trc
---> HEAP DUMP heap name="SQLA^c1d302f4" desc=0x75dfc6b0
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
TCHK^c1d302f4 12 48672 4056.00 92.35
Free(heap.awk) 1 1040 1040.00 1.97
Total = 52704 bytes 51.47k 0.05MB
Descriptor 36de851e8 was the largest with size 48672
This is 92.35 % of the total heap
ds=0x36de851e8 has size 44616 (% of heap=84.65)
Memory used by descriptors with more than 10240 bytes:
Total = 44616 (% of heap=84.65)
Total PERMANENT memory actually used is 80
Largest CONTIGUOUS free memory (NORMAL) was 1040 ( 1.02k)
Largest descriptor count was 12
This does not include a complete heapdump.
alter system set events ‘4031 trace name heapdump level 536870914’;
reproduce the problem and provide the next generated trace files so that we can check the complete memory allocation when the problem is raised?
===================================================================================================================================
Primary Issue:
Unexpectedly High Subpool Allocation in Shared Pool
The issue could occur if in the "Memory Utilization of Subpool" sections of your the trace file, largest allocation size over all subpools in total is NOT one of the following expected names:
"free memory",
"sql area",
"SQLA",
"library cache",
"KGH: NO ACCESS",
"ges resource",
"gcs resource",
"ksfd.*I/O",
"*db_block_buffers*",
"*pcur*",
"*ccur*",
Another condition that this issue could occur is that if "free memory" is the largest, check whether the second largest allocation name is NOT in the above list,
Another condition that this issue could occur is that the top allocation is one of the following, and its allocation size is above 50% of the total subpool size:
"sql area", "SQLA", "library cache", "*pcur*", "*ccur*".
Certain areas of the Shared Pool tend to grow aggressively and stabilize. Areas associated with the Library Cache and cursor objects for example. In your case, Further investigation is required to diagnose the large memory allocations in your Shared Pool that do not match up with expected areas of growth, and it would provide better information for the cause of ORA-04031.
By analyzing the uploaded file, we have found the following symptoms that may have caused the issue:
** In your trace file, Top 5 Allocation in all subpools:
AllocationName........Size
KGLH0........58063984 bytes
private strands........33911808 bytes
event statistics per s........29735936 bytes
ksunfy : SSO free list........27718400 bytes
dbktb: trace buffer........24576000 bytes
Recommended Solution
1. Please create a new Service Request to further investigate the issue. Provide the following diagnostics with the new SR:
a) Review NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
b) Look in the section of the article titled 'Software Requirements/Prerequisites'. You can download the zip file clicking on the 'Click here' link. Run the scripts below and provide the output for analysis
TrendSGAstats.sql -- spools output to trends.out (run on 10g and 11g)
ReservedAnalysis.sql -- spools output to reserved.out (runs on 8i and higher)
2) Upload the diagnostics output from these scripts as well as an AWR report for the hour time frame prior to the ORA-4031 errors with your new Service Request.
- ORA-4031错误原理及诊断脚本汇总
- 一次ORA-01041错误诊断
- ORA-08103错误原理及解决方案
- ORACLE 常见错误及解决汇总 ORA-20000 ORA-03113 ORA-14452 ORA-08103
- ORA错误小汇总
- 诊断并解决ORA-04031 错误
- .诊断并解决ORA-04031 错误
- 诊断并解决ORA-04031 错误
- 84、诊断并解决ORA-04031 错误
- 诊断和解决错误ORA-04031
- 诊断并解决ORA-04031 错误
- 诊断并解决ORA-04031 错误
- 【ORA-04031】诊断并解决ORA-04031错误
- ora-600汇总Ora-00600 错误的代码含义及常用查询
- powershell_读取ORA错误脚本
- inbound connection timed out (ORA-3136)错误诊断
- Data Guard RMAN-04006 和 ORA-01017 错误诊断
- 诊断并解决 ORA-4030 错误 (Doc ID 1548826.1)
- Maven插件之sql-maven-plugin
- Java串口通信——Notepad与Eclipse之迷
- 图的表示--邻接矩阵
- 移动WebApp开发-phoneGap+iOS入门
- URAL 1098 Questions 约瑟夫环
- ORA-4031错误原理及诊断脚本汇总
- phoneGap工程的创建
- 戏说通信业发展历史
- 网络编程学习_基本socket函数
- 方向和美国信号覆盖面方向和美国信号覆盖面方向和美国信号覆盖面
- windows下C++的socket编程入门--文件传输
- Java几种内部类总结
- 通过设置 session_cached_cursors参数
- Linux常用命令（一）
