上一次数据库报ora-04301，调整了一下shared pool size ，好过一段时间，最近又发生了这个错误。所以决心好好整一下应用，主要还是碎片太严重啊。

先看看网友们的一些总结，分析：

笔记[理解与调整共享池]
http://ms.itpub.net/redirect.php?fid=2&tid=1068873&goto=nextnewset

使用shell脚本监控共享池内存碎片
http://www.itpub.net/thread-1072958-1-1.html

Oracle数据库性能优化实务共享池分析
http://wenku.baidu.com/view/412e8d47b307e87101f69614.html

实例故障分析
http://informix.blog.163.com/blog/static/9508027120099200465266/

Oracle 查看 Shared Pool 信息的相关脚本
http://blog.csdn.net/tianlesoftware/article/details/6622318

深入内存组件shared pool--之ORA-04031错误
http://blog.csdn.net/inthirties/article/details/4822268

eygle的shared pool分析
http://www.eygle.com/archives/2004/10/shared_pool-1.html

---------------------------------------------------------------------------------------

select ksmchidx,

(case

when ksmchsiz >= 65560 then

254

when ksmchsiz >= 32792 then

253

when ksmchsiz >= 40 then

1

when ksmchsiz >= 32 then

0

end) bucket#,

count(*) free_chunks,

sum(ksmchsiz) free_space,

trunc(avg(ksmchsiz)) avg_chunk_size

from x$ksmsp

where ksmchcls = 'free'

group by ksmchidx,

(case

when ksmchsiz >= 65560 then

254

when ksmchsiz >= 32792 then

253

when ksmchsiz >= 40 then

1

when ksmchsiz >= 32 then

0

end);

这个是观察shared pool碎片情况的SQL。

select name,value from v$sysstat where name like '%parse%';

查看解析情况，好多的硬解析，和软解析差不了！

SELECT substr(sql_text, 1, 40) "SQL", count(*), sum(executions) "TotExecs",max(sql_id) sql_id

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text, 1, 40)

HAVING count(*) > 30

ORDER BY 2;

select * FROM v$sqlarea where sql_id='g2b789ppwxjx8';

查占用shared pool较多的sql。

ELECT 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;

shared pool 情况。

故障分析
http://space.itpub.net/14075938/viewspace-474727

ITPub博客

ora-04301故障处理

最新文章