关于oracle shared pool问题

查看shared pool 使用情况

select s.pool Name,
       Round(to_number(p.value)/1024,2)||'K' "Size",
       Round(s.bytes/1024,2)||'K' "Free",
       Round((s.bytes/p.value),4)*100 "percentfree(%) "
from   v$sgastat s,
       v$parameter p
where s.name='free memory' and
      p.name='shared_pool_size' and
      s.pool='shared pool'
union
select s.pool Name,
       Round(to_number(p.value)/1024,2)||'K' "Size",
       Round(s.bytes/1024,2)||'K' "Free",
       Round((s.bytes/p.value),4)*100 "percentfree(%) "
from   v$sgastat s,
       v$parameter p
where s.name='free memory' and
      p.name='large_pool_size' and
      s.pool='large pool'

 

遇到的问题的分析，解决

前几天业务主机alert log里有报ORA-04031错误，但是shared_pool_size已经足够大了，经过分析，我们想可能的原因是因为程序写的不好，不能够很好的进行重复调用，从而导致经常性SQL编译，致使SHARED POOL内碎片太多，从而导致没有连续的SHARED POOL可以使用，引发ORA-04031；

我们的解决方法是在每天的适当时候执行alter system flush shared_pool;命令以刷新shared_pool，即清除shared_pool内所有内容，下面具体解决过程。

需要说明的是这不是一个很好的方法，但目前我们还没有更好的方法来解决这样的问题。

============alert_ora8.log Error Info================================
Thu Jan  2 14:52:17 2003
Errors in file /oracle/app/oracle/admin/ora8/udump/ora_2492.trc:
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","USER_O
BJECTS","sga heap","library cache")
============alert_ora8.log Error Info================================

phoenix1> oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (/"%s/",/"%s/",/"%s"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".

phoenix1> grep pool initora8.ora|grep -v "#"
shared_pool_size = 2048000000

phoenix1>su - cyx
phoenix1>su -
phoenix1:/backup/cyx#crontab -l|more
30 7,12 * * * su - oracle8 -c /backup/cyx/altersystem.sh >/backup/cyx/altersyste
m.log 2>>/backup/cyx/altersystem.log &

phoenix1:/backup/cyx#cat *sh
sqlplus /nolog <connect internal/sldsys11
alter system flush shared_pool;
exit
EOF

好了，到此结束。
 
 

观察过你的SESSION，看他们都干了些什么？此外，通过select * from v$db_object_cache where sharable_mem > 10000 你可以知道使用内存数据超过10000的SESSION是哪些
看来，share_poor_szie。