ORA-4031错误原理及诊断脚本汇总

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 a60
col value for a30
 spool diagnosis1.lst

SELECT 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 < 5
GROUP 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 and
sa.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 and
nam.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_reserved

col Parameter format a25
col "Session Value" format a15
col "Instance Value" format a15

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where 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 100
select * from v$sga_dynamic_components;

col requests for 999,999,999
col 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_size
from 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 80
set verify off
set heading off
set feedback off
set termout off

col sp_size     format          999,999,999 justify right
col x_sp_used   format          999,999,999 justify right
col sp_avail    format          999,999,999 justify right
col sp_sz_pins format           999,999,999 justify right
col sp_no_pins format           999,999 justify right
col sp_no_obj format            999,999 justify right
col sp_sz_obj format            999,999 justify right
col sp_no_stmts format          999,999 justify right
col sp_sz_kept_chks format      999,999,999 justify right
col sp_no_kept_chks format      999,999 justify right

col val2 new_val x_sp_size noprint
select value val2
from   v$parameter
where  name='shared_pool_size'
/
col val2 new_val x_sp_used noprint
col val3 new_val x_sp_no_stmts noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3
from   v$sqlarea
/
col val2 new_val x_sp_no_obj noprint
col val3 new_val x_sp_sz_obj noprint
select decode(count(*),'',0,count(*)) val2,
       decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from v$db_object_cache
where type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
/
col val2 new_val x_sp_avail noprint
select sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'
/
col val2 new_val x_sp_no_kept_chks noprint
col val3 new_val x_sp_sz_kept_chks noprint
select decode(count(*),'',0,count(*)) val2,
       decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from   v$db_object_cache
where  kept='YES'
/
col val2 new_val x_sp_no_pins noprint
select count(*) val2
from v$session a, v$sqltext b
where a.sql_address||a.sql_hash_value = b.address||b.hash_value
/
col val2 new_val x_sp_sz_pins noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2
from   v$session a,
       v$sqltext b,
       v$sqlarea c
where  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 on
set heading off

ttitle -
  center  'Shared Pool''s Library Cache Information'  skip 2
select  '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_pins
from    dual
/

ttitle off
set heading on
set feedback on

set pages 1000 lines 120
 col name for a60
col value for a30

select * 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 ALL
select '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

 

 

 

NB Bug Fixed Description
16816103 11.2.0.4, 12.1.0.1.1, 12.1.0.2, 12.2.0.0 XIN aborts with ORA-4031 due to memory leak in OCIXStreamInCommit
16710753 11.2.0.4, 12.1.0.2, 12.2.0.0 Excess shared pool memory use / ORA-4031 from DROP EDITION
16571785 11.2.0.4, 12.1.0.2, 12.2.0.0 High “KTC latch subh” SGA memory use due to “lobs commit callback”
13951456 12.1.0.2, 12.2.0.0 ASMCMD does not use bind variables in its SQL – affects shared pool
16009158 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1 ORA-4031 / excess shared pool memory using Securefiles
15931756 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1 ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds)
8673737 11.2.0.4, 12.1.0.1 Diagnostic enhancement to summarize sub-pool allocations in ORA-4031 trace files
14053298 11.1.0.6 “kksss-heap” memory leak from PMON cleanup (ORA-4031) when audit enabled
15881004 11.2.0.4, 12.1.0.1 Excessive Memory usage with Extended Cursor Sharing
14292825 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 ORA-4031 in ASM as default memory parameters values for 11.2 ASM instances low
14029050 11.2.0.2.BP18, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 MMAN failing to donate granules despite unused PGA (possible ORA-4031)
14003090 11.2.0.3.8, 11.2.0.3.BP20, 11.2.0.4, 12.1.0.1 ORA-4031 with high memory utilization in the ‘init_heap_kfsg’ subheap / “ASM map headers” under ‘init_heap_kfsg’
13814739 11.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1 Excessive KQR X PO” allocations in a RAC environment (can cause ORA-4031)
13536971 12.1.0.1 ORA-4031 with large partition tables – superseded
13456573 11.2.0.4, 12.1.0.1 Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing
13430938 11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 ORA-4031 with high “KTC latch subh” memory usage
13255388 11.2.0.4, 12.1.0.1 Slow parsing of large number of subqueries eligible for coalescing.
13250244 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1 Shared pool leak of “KGLHD” memory when using multiple subpools
13072654 11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1 Unnecessary ORA-4031 for “large pool”,”PX msg pool” from PQ slaves
12387969 11.2.0.3, 12.1.0.1 get_geometry() does not use bind variables internally leading to shared pool fragmentation
11782790 11.2.0.3, 12.1.0.1 Excess CPU & memory use / ORA-4030 / ORA-4031 parsing deeply nested queries
11651810 11.2.0.2.3, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1 ORA-4031 or Excess shared pool use can be seen by FileOpenBlock objects
11063191 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.2, 11.2.0.3.BP04, 11.2.0.4, 12.1.0.1 ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10634096 11.2.0.3, 12.1.0.1 excessive memory usage with grouping sets and temp table transformation
10359631 11.2.0.3, 12.1.0.1 High elapsed time and shared pool usage during parse with virtual columns
10325953 11.2.0.3, 12.1.0.1 ORA-600[733] / ORA-4031 allocating memory for hash join – superceded
10308906 11.2.0.3, 12.1.0.1 ORA-4031 using structured binary XMLIndex
10089333 11.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
10082277 11.2.0.1.BP12, 11.2.0.2.3, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1 Excessive allocation in PCUR or KGLH0 heap of “kkscsAddChildNo” (ORA-4031)
10042937 11.2.0.3, 12.1.0.1 High memory group in ges_cache_ress and ORA-4031 errors in RAC
9578670 11.2.0.1.BP11, 11.2.0.2.5, 11.2.0.2.BP09, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1 ORA-4031 from frequent Partition Maintenance Operation
9403026 11.2.0.2, 12.1.0.1 Startup or PQ can report ORA-4031 errors requesting large “PX msg pool” chunks of memory
9320130 10.2.0.5.3, 11.2.0.2, 12.1.0.1 ORA-4031 for “temporary tabl” using temporary table transformations (STAR / WITH)
9015983 11.2.0.1.1, 11.2.0.2, 12.1.0.1 ORA-4031 for “temporary tabl” on query with star transformation
8934457 11.2.0.2, 12.1.0.1 ORA-4031 during EXPLAIN PLAN or ORA-4030 during SELECT
8835591 11.2.0.2, 12.1.0.1 Excess memory use parsing invalid SQL with COUNT() in subquery select list
8813366 11.1.0.7.3, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1 ORA-4031 due to over large granule size
8537544 11.2.0.2, 12.1.0.1 Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins
7509451 11.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1 SGA memory leak possible for “KSN WaitID” memory when using shared servers
13548390 11.2.1.8.8 Error 4031 in kgttusec – plsql memory > 1g unreliable
9026008 11.2.0.1.BP02, 11.2.0.2, 12.1.0.1 “GES_RESOURCES” may increase over time with high enqueue activity
9058900 11.2.0.1.BP10, 11.2.0.2 OERI [kcbi_get_bhs_4] / “Shared IO Pool” takes too much SGA memory
8599477 11.1.0.7.3, 11.2.0.1 Large “kcbi io desc” shared pool allocations can lead to ORA-4031
8528171 10.2.0.5.6, 11.1.0.7.10, 11.2.0.1 High shared pool use due to frequent ADD/EXCHANGE partition operations
8371884 10.2.0.5, 11.2.0.1 Dumps/Errors/Excessive Parse Time and Memory allocation with Star transformation
8244734 10.2.0.5, 11.1.0.7.8, 11.2.0.1 NUMA Pool misconfigured at startup (ORA-4031)
8221425 11.1.0.7.2, 11.2.0.1 ORA-4031 with kgs-heap overusing one subpool
7462463 10.2.0.5, 11.2.0.1 R-tree Index based spatial query on large table fails with ORA-4031 / ORA-7445 [kghualloc]
7443808 11.2.0.1 Excessive Shared Memory utilization during Query Parsing
7340448 10.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1 Excess shared pool memory use / ORA-4031 from REGEXP_LIKE – superceded
7250182 11.2.0.1 Excess shared pool memory for AQ (“kwqiccns: notification st” chunks)
7207921 10.2.0.5, 11.2.0.1 State object leak / ORA-4031 from stale guess DBAs on secondary IOT index
7031622 10.2.0.5, 11.2.0.1 Spin on kkojnp / ORA-600 [15160] with Star transformation
6868080 10.2.0.5, 11.1.0.7, 11.2.0.1 ORA-4031 with NUMA
6858062 10.2.0.4.1, 10.2.0.5, 11.1.0.7.5, 11.2.0.1 Shared pool memory leak when services created / deleted
6800507 10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1 Shared pool memory leak (“ksws service *” memory) using EXPDP often
6730567 10.2.0.5, 11.2.0.1 ORA-4031 / OERI [17137] with NUMA optimization enabled
6624011 11.1.0.7, 11.2.0.1 ORA-4030 / ORA-4031 on startup with > 4Gb SGA_TARGET
6530861 10.2.0.5, 11.2.0.1 ORA-4301 when using stored Java
6334552 10.2.0.4, 11.1.0.7, 11.2.0.1 Hang / ORA-4031 / OERI:kfrcsoDelete_3 on rollback of ASM file resize
6271590 10.2.0.4.2, 10.2.0.5, 11.1.0.7, 11.2.0.1 SGA subheap imbalance with lots of free memory in a few subheaps
6086162 11.2.0.1 Slow parsing / Excessive Memory Usage with Star Transformation and Query Rewrite
6126415 11.1.0.7 OERI [kjccgmb:1] during shutdown instance
6011182 10.2.0.4, 11.1.0.7 Parsing of large query takes long time / memory leak / ORA-4030 /4031
5386986 10.2.0.4, 11.1.0.7 Leak / ORA-4031 leak when DROP UNUSED COLUMN issued on large partitioned table
13888380 ORA-4031 in DB instance when using ASM due to high use of “init_heap_kfsg” memory
10363436 Memory leak / ORA-4031 using TDE column encryption with PKI keys
12808696 11.1.0.6 Shared pool memory leak of “hng: All sessi” memory
7306915 10.2.0.4.4, 10.2.0.5, 11.1.0.6 EXCHANGE PARTITION leaks “KGL handles” shared pool memory
6043052 10.2.0.4, 11.1.0.6 Leak in perm allocations with “library cache” comments (ORA-4031)
6001617 10.2.0.4, 11.1.0.6 LCK may accumulate CPU time / ORA-4031 possible
5950708 10.2.0.4, 11.1.0.6 ‘gcs resources’ and ‘gcs shadows’ are imbalanced across shared pool subpools
5618049 10.2.0.4, 11.1.0.6 “mvobj part des” leaked memory after partition DDL (ORA-4031)
5573238 10.2.0.4, 11.1.0.6 Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool
5548510 10.2.0.4, 11.1.0.6 _FIX_CONTROL parameter leaks memory in the shared pool
5548389 10.2.0.4, 11.1.0.6 Library cache allocation for ‘column mapping’ not using uniform sized extents
5508505 10.2.0.4, 11.1.0.6 ORA-4031 while shared heap still has unused reserved extents
5479172 10.2.0.4, 11.1.0.6 ORA-4031 with multiple partially-allocated permanent chunks
5377973 10.2.0.4, 11.1.0.6 Register schema fails with ORA-4031
5240264 11.1.0.6 ORA-4030 / ORA-4031 / Excessive Memory Usage with view merging and large IN LISTs.
4994956 10.2.0.3, 11.1.0.6 Number of configured shared pool subpools not correct
4467058 10.2.0.4, 11.1.0.6 IO requests can flush the pool / signal a hidden ORA-4031 error
3519807 9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 ORA-4031 querying V$SEGSTAT
3291723 10.2.0.3, 11.1.0.6 DBWR may crash instance if shared pool memory low
6347725 “obj stat mem” leak when global temporary tables used
4195175 9.2.0.7, 10.1.0.5, 10.2.0.1 Create MVIEW with large WHERE clause may dump / ORA-4031
4184298 10.1.0.5, 10.2.0.1 Subpool imbalance for “session parameters” can lead to ORA-4031
3948384 9.2.0.7, 10.1.0.4, 10.2.0.1 ORA-4031 possible using LGWR ASYNC for archiving
3513427 10.1.0.3, 10.2.0.1 ORA-4031 can cause many SGA heapdumps to occur causing shared pool latch contention
3419396 10.2.0.1 Restore spfile with dummy instance from ASM fails with RMAN-20001 / ORA-4031
3405237 9.2.0.6, 10.1.0.3, 10.2.0.1 ORA-4031 / excessing SGA consumption due to large IN LIST and NOT IN list
3356492 10.2.0.1 DBMS_LOB.LOADFROMFILE leaks library cache locks / shared pool “perm” memory
3299407 9.2.0.6, 10.1.0.3, 10.2.0.1 Invalidating cursors frequently can fragment the SGA and leak “kglau” memory
3046725 10.2.0.1 ORA-4031 due to shared_pool fragmented with high ges resources & enqueues
2953931 9.2.0.4, 10.2.0.1 ORA-4031 parsing query with numerous groupings
* 4658188 9.2.0.8 Long waits for “ksu process alloc latch yield”
4231921 9.2.0.8, 10.1.0.2 Truncate partition tables leaks SGA memory
3232401 9.2.0.5, 10.1.0.2 ORA-4031 with Streams apply site
+ 3150705 9.2.0.5, 10.1.0.2 Fix to help reduce the chances of an ORA-4031 with high OPEN_CURSORS
3150680 9.2.0.5, 10.1.0.2 Excessive shared pool permanent memory use / ORA-4031 possible with SESSION_CACHED_CURSORS
3083818 10.1.0.2 Reloaded cursors can leak shared pool memory
3077651 9.2.0.5, 10.1.0.2 LOB manipulation can leak “buffer” state objects causing hangs/shared pool problems
2846138 10.1.0.2 Dump (under opiosq0) from SVRMGRL sessions when shared pool is full
2817728 9.2.0.4, 10.1.0.2 Shared pool memory leak / ORA-4031 when collecting segment statistics
2804724 9.2.0.4, 10.1.0.2 Out of memory in JAVA POOL shows an ORA-4031 “shared pool” message
2798851 9.2.0.4, 10.1.0.2 ORA-4031 possible from SMON during SHUTDOWN or STARTUP
2793038 9.2.0.4, 10.1.0.2 Session may die with ORA-4031 if shared pool exhausted when storing object level statistics
2786968 9.2.0.4, 10.1.0.2 Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors
2607029 9.2.0.3, 10.1.0.2 High memory use optimizing SQL with functional indexes and many expressions
2586762 9.2.0.3, 10.1.0.2 Direct LOB reads can result in ORA-4031 / excessive shared pool usage
2563301 9.2.0.5, 10.1.0.2 ORA-4031 possible in RAC environment under load
2561115 9.2.0.5, 10.1.0.2 SGA memory growth using outbound TCP/IP database links from shared servers
2523151 9.2.0.3, 10.1.0.2 ORA-7445[updexe] with AUDIT_TRAIL=DB with stressed shared pool
2501591 9.2.0.3, 10.1.0.2 ORA-928 / ORA-4031 on “ALTER VIEW <sys_view> COMPILE” if REMOTE objects exist with same name
2486954 9.2.0.3, 10.1.0.2 ORA-4031 / Dump from concurrent DGURIServlet access to SQLX XMLType view
2351854 9.0.1.4, 9.2.0.2, 10.1.0.2 Dispatchers may hang if ORA-4031 occurs
1782381 9.2.0.5, 10.1.0.2 Excessive SGA use / ORA-4031 possible with large number of JOBS
2805335 9.2.0.4 Excessive shared pool memory use for parse of certain statments using functional indexes
2442042 9.2.0.5 ORA-4031 possible using STREAMS on multi-cpu machine
2472541 9.2.0.2 PMON ORA-7445 [kghxfr] after shared server gets ORA-4031
2244789 9.0.1.4, 9.2.0.1 ORA-4031 / excessive shared pool usage from query with full outer join / union
2244642 9.0.1.4, 9.2.0.1 ALTER VIEW operations may encounter ORA-4031 errors
2208570 8.1.7.4, 9.0.1.4, 9.2.0.1 ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view
2185294 9.2.0.1 Shared server may dump (koklhfr) after ORA-4031 in “LARGE POOL”
2167326 9.0.1.3, 9.2.0.1 ORA-4031 possible during patch set upgrade scripts
2143637 9.0.1.3, 9.2.0.1 ORA-4031 possible starting the cache advisory
2129178 8.1.7.4, 9.0.1.3, 9.2.0.1 Using NULL or an empty string (”) in an INLIST can cause ORA-4031 / waste shared pool memory
2104071 8.1.7.4, 9.0.1.3, 9.2.0.1 ORA-4031 / excessive “miscellaneous” shared pool use possible (many PINS)
1865917 8.1.7.3, 9.0.1.2, 9.2.0.1 ORA-4031 / high CPU from OR expansion of negated predicates with RBO
1333526 8.1.7.3, 9.0.1.2, 9.2.0.1 ORA-4031 / Poor performance using persistent Object data types
1642964 9.0.1.0 ORA-4031 / sga leak from IOT as inner table in NESTED LOOP
1640583 8.1.7.1, 9.0.1.0 ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access
+ 1397603 8.1.7.2, 9.0.1.0 ORA-4031 / SGA memory leak of PERMANENT memory for buffer handles
1396675 8.1.7.2, 9.0.1.0 PLSQL may hang/spin/ORA-4031 with SQL WHERE (a,b) in ( (c1,d1),(c2,d2)… )
1340718 8.1.7.2, 9.0.1.0 OERI:KCBSTC_PARENT_AB possible in CKPT following an ORA-4031 error
1348501 8.1.6.3, 8.1.7.0 MV refresh unnecessarily invalidates shared cursors
1328709 8.0.6.2, 8.1.6.3, 8.1.7.0 OERI:15428 can be raised when ORA-4031 is hit internally
1256011 8.0.6.2, 8.1.6.2, 8.1.7.0 MTS servers may OERI:1113 after an ORA-4031 (OERI:730 on 8i)
1092621 8.1.6.2, 8.1.7.0 ORA-4031 when DROPPING a PARTITION
898798 8.1.5.1, 8.1.6.0 ORA-4031 on startup from large DB_BLOCK_BUFFERS (or GC_RELEASABLE_LOCKS)
431303 7.3.3.1, 7.3.4.0 Startup gives ORA-4031 with GC_DB_LOCKS=157K

 

 

 


create or replace procedure DBMS_SUPPORT_DBMONITORP wrapped 
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
164 17d
Pljh8tADZP5MAl3Z72C8RxNWZo8wgzKur9xGfC+BR+pHN0ZMjP1aeqE8NoMxY7MJJW+MSawa
YO8eSA4ftiIZ+waz613XNP6L7fLVQDQ853qc6H8yXD55/eHljispnka9MMl/+8yjU5pJUqtd
MI4BpqY5T8iTS+U46eh4iz1Hd7ro9zkHzdjMS5coo6Ewy72RQsl8kIEGphNDkkudbYZitOEf
D881K7o2qGzANTbjRa8NGTcgmiNb8hfYMF/o+ne+LdmGi1SZP7fbBd4YKzww3tpbhnJ9Vb0O
pDj17R56cWr4uCLXB6QIFRGhFSa97aiMpEOZnKReCZ4T+bZNze39+YqYKLEQTP9Pmj8NavFe
viEOWIdz4TnCOP4=

/
PROMPT Create DBMS_SUPPORT_DBMONITOR TRIGGER
create or replace trigger DBMS_SUPPORT_DBMONITOR
after startup on database 
declare
begin 
   DBMS_SUPPORT_DBMONITORP;
end;
/

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. admin says:
    ANSWER IN CHINESE
    ======
    1.SGA 里的内池包含不同大小的内存块。当数据库启动时,就有一个大的内存块分配并被hush buckets 里的空闲列表追踪。
    随着时间推移,随着内存的分配和释放。内存块被按照大小在不同的hush buckets间移动。当SGA里任何一个内存池里出现
    不能满足内部分配请求的情况时,ORA-04031就出现了。
    
    共享池的管理方式不同于其它的内存池。共享池存放与数据字典和library cache有关的信息。但是,这些
    内存区域根据空闲列表和最近使用算法(LRU)管理的。当在共享池的所有搜索结束后,从LRU列表清除所有
    的可能清除的对象, 多次扫描空闲列表后,仍没有找到内存块,ORA-04031就出现了。这意味着ORA-04031很难预测。
    ORA-04031有很多种可能原因,错误产生时间的跟踪信息提供的是内存情况的"受害会话", 不是原因。
    metalink 文档Note 396940.1 可以提供详细信息。
    
    
    2. 对共享池的监测,可以看它是否包含许多类似的SQL,只有文字不同。 这种情况会占用更多的共享池内存
    并引起碎片。这两种情况都会引起ORA-4031错误。使用绑定变量可以使SQL 共享。使用以下的命令可以查出
    内存中是否有许多类似SQL。
    
    即使使用了绑定变量后,仍然可能存在高version count(子指针)的情况。为了使子指针共享,CURSOR_SHARING
    参数需要调整。
    metalink 文档Note 296377.1 和 261020.1可以提供详细信息。
    
    3. 造成4031的原因不是bug或产品缺陷。 是需要根据要求来调整一些参数来控制共享池的使用。
    
  2. admin says:
    comm diag script:
    
    1. Upload the output for:
    > opatch lsinventory -detail
    
    2. 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/password
    select sql_id, sql_text, version_count from v$sqlstats
    where version_count > 5
    order by version_count desc;
    
    12. Provide the output for :
    connect user/password
    select * 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 val
    where 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 c
    where a.indx = b.indx and a.indx = c.indx
    and a.ksppinm like '%kghdsidx%';
    
    select PQ_SLAVE_MISMATCH , PX_MISMATCH from V$SQL_SHARED_CURSOR;
    
    set linesize 90
    set pagesize 60
    column component format a25
    column Final format 99,999,999,999
    column STARTED format A25
    
    SELECT 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.BYTES
    FROM WRM$_SNAPSHOT A, DBA_HIST_SGASTAT B
    WHERE A.SNAP_ID = B.SNAP_ID
    AND B.POOL = 'SHARED POOL' AND B.NAME = 'KGH: NO ACCESS' ORDER BY 1;
    
    show parameter session_cached_cursors;
    
    
  3. 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.

  4. 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?

  5. maclean says:

    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.

  6. maclean says:

    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 <= 100 ORDER BY order_ranking/

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569