3

I want to flush shared pool because of error like

ORA-04031: unable to allocate 32 bytes of shared memory 

So I want to flush shared pool using

alter system flush shared_pool;

Connected as sysdba using

hwflow08:flman800 > sqlplus "conn as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 28 05:39:56 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: Connected.

While executing alter system command we faced ORA-01012

SQL> alter system flush buffer_cache;
alter system flush buffer_cache
*
ERROR at line 1:
ORA-01012: not logged on

Can anyone please guide how can I flush the Shared Pool ? (I dont want to shutdown the DB nor want to increase size of shared pool)

Previously Same issue Came :

I left with option to SHUTDOWN DB. So after STARTUP , when i connected through SYSDBA and executed same query alter system flush shared_pool; That time it works perfectlly fine. So question remains as why ORACLE is not allowing flush shared pool when it is out of memory ? What is efficient way to use alter system flush shared_pool;

EDIT : Seems like its a 10g BUG

5
  • 1st you should check altert log file. Then edit your question and add OS platform. And then you also should check installation guide - memory sizing.
    – ibre5041
    Mar 10, 2015 at 12:16
  • whether different OS will behave differently on this ? Mar 10, 2015 at 12:18
  • sure. You have to check SGA sizing against OS limits.
    – ibre5041
    Mar 10, 2015 at 12:20
  • oh no sry. this has nothing to od with OS limits. I was wrong. You probably have an application which parses too many SQL statements and Oracle is unable to allocate more ram for library cache.
    – ibre5041
    Mar 10, 2015 at 12:22
  • Ya..exctaly..After certain days it goes out of memory Mar 10, 2015 at 12:24

1 Answer 1

2

check SGA region sizes - buffer pool, dictionary cache, library cache.

Also check the dynamic view v$sga_resize_ops. There you might find a reason. Also these version of Oracle had a bug, which caused that Oracle could not get rid of the old exec plans if there were too many of them. You should upgrade to terminal patchset 10.2.0.5.

You should also consider setting parameter cursor_sharing, but this might not help in some cases. 1st you should find a culprit, typically this is caused by some ORM framework like Hibernate.

Update: There is also memory leak in early release 12c RAC cluser. So when you execute this:

SQL> select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <11;

NAME                       BYTES/1024/1024/1024
-------------------------- --------------------
ges resource dynamic                 7.42374295
value block free list                4.61375274
free memory                          2.66423168
gcs resources                        1.78276435
ges enqueues                         1.45691999
gcs shadows                          .990424648
db_block_hash_buckets                .343753815
gcs res hash bucket                        .125
gc name table                            .09375
KTSL subheap                         .086000413

So you can see that 7GB of SGAs RAM are occupied by some clusterware buffers.

But most common source of these errors is caused by enormous amount of hard parsing causing library cache growth.

7
  • You mean to say it's kind of bug that Oracle doesnt allow us to flush when out of memory. Mar 10, 2015 at 12:30
  • 1
    this not logged in message look like your session crashes, when you try to flush buffer pool. you should check alert log to prove/disprove it.
    – ibre5041
    Mar 10, 2015 at 12:32
  • you should also flush the whole shared pool, not just a buffer cache.
    – ibre5041
    Mar 10, 2015 at 12:33
  • i also tried for flush shared pool but same issue Mar 10, 2015 at 12:36
  • 1
    Upgrade to 11g. Your problem happens because SQLs fragment shared pool and flush cannot defragment it. 11g is more resilient to such shared pool fragmentation. Mar 10, 2015 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.