Re: ORA-00604, ORA-04031

On Dec 3, 11:53 am, Steve Robin <ocma..._at_gmail.com> wrote:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
> pool","select file#, block#, ts# fr...","sga heap(1,0)","kglsim object
> batch")
>
> I got these error in oracle 10g, I was not able to investigate the
> reason, because I was not able to connect with database. I needed to
> kill process and then restart the database.
>
> I got following line in mmon trc file.
>
> SGA POLICY: Cache below reserve and cant get memory from any other
> component.
>
> sga_target = 380M
> sga_max_size = 380M
>
> Oracle : 10.2.0.2
> OS : SunOS 5.9
>
> Please let me know if I cann't find the cause for it, as well
> solution.

Hi Steve,

Try querying dba_hist_sgastat (if licensed) for changes over time. Something like...

select time,instance_number,

       max(decode(name,'free memory',shared_pool_bytes,null)) free_memory,

       max(decode(name,'library cache',shared_pool_bytes,null)) library_cache,

       max(decode(name,'sql area',shared_pool_bytes,null)) sql_area   from (select to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') time,

               dhs.instance_number,
               name,
               bytes - LAG(bytes, 1, NULL) OVER (ORDER BY
dhss.instance_number,name,dhss.snap_id) AS shared_pool_bytes
          from dba_hist_sgastat dhss,
               dba_hist_snapshot dhs
          where name in( 'free memory', 'library cache', 'sql area')
            and pool = 'shared pool'
            and dhss.snap_id = dhs.snap_id
            and dhss.instance_number = dhs.instance_number
          order by dhs.snap_id,name)
          group by time,
           instance_number

You could then correlate that with historical SQL statement executions to see where the root cause lies.

HTH, Steve Received on Tue Dec 04 2007 - 11:45:06 CST