oracle中ora-04301故障处理

作者：袖梨 2022-06-29

上一次数据库报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 情况。

相关文章

精彩推荐