ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 避免ORA-04031错误与SHARED POOL调整

避免ORA-04031错误与SHARED POOL调整

原创 Linux操作系统 作者:husthxd 时间:2004-11-10 00:00:00 0 删除 编辑

ORA-04031错误&SHARED POOL调整


1.ORACLE Bug导致的ORA-04031:

BUG

Description

Workaround

Fixed

<Bug:1397603>

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

_db_handles_cached = 0

8172, 901

<Bug: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

<Bug:2104071>

ORA-4031/excessive "miscellaneous"
shared pool usage possible.
(many PINS)

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

8174, 9013, 9201

<Note: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
     / 
    

参考文档:


【metalink 】Diagnosing and Resolving Error ORA-04031

【metalink 】Understanding and Tuning the Shared Pool

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

上一篇: 推荐好书:
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1399
  • 访问量
    3843691