ORA-04031错误&SHARED POOL调整

ORA-04031错误&SHARED POOL调整

1.ORACLE Bug导致的ORA-04031: 

BUG 

Description 

Workaround 

Fixed 

<1397603> 1397603>

 ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles. 

 _db_handles_cached = 0 

 8172, 901 

 

<1640583> 1640583>

 ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access  

 Not available 

 8171, 901 

 Bug:1318267   

   Not Public 

 INSERT AS SELECT statements may not be shared when they should be if  TIMED_STATISTICS. It can lead to ORA-4031 

 _SQLEXEC_PROGRESSION_COST=0  

 8171,  8200 

 Bug:1193003 

    Not Public 

 Cursors may not be shared in 8.1 when they should be 

 Not available 

 8162, 8170, 901 

<2104071> 2104071>

 ORA-4031/excessive "miscellaneous" 

shared pool usage possible. 

(many PINS) 

 None-> This is known to affect the XML parser. 

 8174, 9013, 9201 

<263791.1> 263791.1>

 Several number of BUGs related 

to ORA-4031 erros were fixed 

in the 9.2.0.5 patchset 

 9205 

2.编译java代码时出现ORA-4031错误 

出现ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")增大JAVA_POOL_SIZE到一个合适的值即可（一般100m足够）。 

3.LARGE_POOL_SIZE过小导致ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame")，增大LARGE_POOL_SIZE即可。 

4.ORA-04031错误的高级分析 

SESSION级：

SQL> alter session set events '4031 trace name errorstack level 3'; 

SQL> alter session set events '4031 trace name HEAPDUMP level 3'; 

INSTANCE级： 

初始化参数中设置 

event = "4031 trace name errorstack level 3" 

event = "4031 trace name HEAPDUMP level 3" 

5.调整SHARED_POOL_SIZE以避免ORA-04031错误。 

1)使用DBMS_SHARED_POOL.KEEP把经常运行的package keep到共享池中。 

2)调整参数SHARED_POOL_RESERVED_SIZE大致为SHARED_POOL_SIZE的10％；但如果SHARED_POOL_SIZE很大的话可以适当调小该参数；如果     SHARED_POOL_RESERVED_MIN_ALLOC低于default值的话，适当调大该参数，因为SHARED_POOL_RESERVED_MIN_ALLOC较低会导致更多共享语句从SHARED_POOL_RESERVED_SIZE所指定的区域中分配内存。 

3)一些用于定位问题的脚本 

    A.定位应该使用绑定变量的sql语句 

     SELECT substr(sql_text,1,40) "SQL", 

     count(*) , 

     sum(executions) "TotExecs" 

     FROM v$sqlarea 

     WHERE executions < 5 

     GROUP BY substr(sql_text,1,40) 

     HAVING count(*) > 30 

     ORDER BY 2 

     / 

     

     40表示sql语句的前40个字符是一样的，5表示执行次数小于5次，30表示在shared_pool_size中出现不下30次。 

     

    B.以sys用户执行以下语句 

     SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0 

     / 

     

     X$KSMLRU 表显示那段内存的分配导致大多数的chunks从共享内存中aged out。 

     

    C.获得library cache hit 

     SELECT SUM(PINS) "EXECUTIONS", 

                 SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" 

                 FROM V$LIBRARYCACHE 

     / 

     

     D.显示在free list中可用的chunks 

     select '0 (<140)' BUCKET, KSMCHCLS, 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, 10*trunc(KSMCHSIZ/10) 

     UNION ALL 

     select '1 (140-267)' BUCKET, KSMCHCLS, 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, 20*trunc(KSMCHSIZ/20) 

     UNION ALL 

     select '2 (268-523)' BUCKET, KSMCHCLS, 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, 50*trunc(KSMCHSIZ/50) 

     UNION ALL 

     select '3-5 (524-4107)' BUCKET, KSMCHCLS, 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, 500*trunc(KSMCHSIZ/500) 

     UNION ALL 

     select '6+ (4108+)' BUCKET, KSMCHCLS, 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, 1000*trunc(KSMCHSIZ/1000) 

     / 

       

     E.检查高版本计数的sql语句 

     SELECT address, hash_value, 

                         version_count , 

                         users_opening , 

                         users_executing, 

                         substr(sql_text,1,40) "SQL" 

                   FROM v$sqlarea 

                  WHERE version_count > 10 

     / 

     

     

     F.检查使用了多量的shared memory的sql语句 

     SELECT substr(sql_text,1,40) "Stmt", count(*), 

                         sum(sharable_mem)    "Mem", 

                         sum(users_opening)   "Open", 

                         sum(executions)      "Exec" 

                   FROM v$sql 

                  GROUP BY substr(sql_text,1,40) 

                 HAVING sum(sharable_mem) > memsize 

     / 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11990065/viewspace-752909/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11990065/viewspace-752909/