oracle中ora-04301故障處理

上一次資料庫報ora-04301，調整了一下shared pool size ，好過一段時間，最近又發生了這個錯誤。所以決心好好整一下應用，主要還是片段太嚴重啊。

代碼如下

複製代碼

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 情況。