避免ORA-04031错误与SHARED POOL调整
来源:互联网 发布:php oa办公系统源代码 编辑:程序博客网 时间:2024/05/17 09:33
避免ORA-04031错误与SHARED POOL调整
http://space.itpub.net/6906/viewspace-21627
o
http://wenku.baidu.com/view/108f210bf12d2af90242e672.html
SHARABLE_MEM:占用的共享内存大小 (单位: byte)
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
- 避免ORA-04031错误与SHARED POOL调整
- 共享池的调整与优化(Shared pool Tuning)
- 共享池的调整与优化(Shared pool Tuning)
- 共享池的调整与优化(Shared pool Tuning)
- 共享池的调整与优化(Shared pool Tuning)
- Oracle 共享池的调整与优化(Shared pool Tuning)
- Shared pool深入分析及性能调整
- Shared pool深入分析及性能调整
- Shared pool深入分析及性能调整
- Shared pool深入分析及性能调整
- ORA-00371 not enough shared pool memory
- shared pool的4031错误解决办法
- Shared pool
- ORA-00371: not enough shared pool memory解决方法
- Buffer Cache与Shared Pool原理
- Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools (文档 ID 146599.1)
- ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN :EXEC_STR := SYS.DBMS...","PL/SQL MPCODE","BAMIM
- shared pool与log buffer学习(五)
- PHP5中PDO的简单使用
- TeX-源文档组织结构
- 知识的深度和广度
- ARM __iomem __force
- Java Applet
- 避免ORA-04031错误与SHARED POOL调整
- 拷贝文件
- 读书笔记(2)
- 态度决定高度
- 强连通分量
- C语言中遇到的问题及解决方法
- 10700 - Camel trading
- 黑马程序员-C#基础知识
- Eclipse快捷键大全