上一次数据库报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 情况。
时间： 2024-11-06 07:09:55