Home » RDBMS Server » Server Administration » ORA-04031: unable to allocate 4192 bytes of shared memory
ORA-04031: unable to allocate 4192 bytes of shared memory [message #345931] Fri, 05 September 2008 06:14 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am using Oracle 10.2.0.2 on Solaris . I am using dedicated connections and not a shared server.

We were facing memory issues with the database saying that it was not able to allocate enough memory for shared pool

SELECT sum(bytes), pool
FROM v$sgastat
GROUP BY pool
/
SUM(BYTES) POOL
---------- ------------
  50330720
2201703296 shared pool
  67194376 streams pool
  16777216 large pool
  67108864 java pool

SQL> select * from v$sgastat where name = 'free memory';
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                1394430736
large pool   free memory                  16713000
java pool    free memory                  42001600
streams pool free memory                  66491096


I do have enough of shared pool free memory. But I don't know why I am facing this error ?

Can anyone help me with this issue?
Re: ORA-04031: unable to allocate 4192 bytes of shared memory [message #345938 is a reply to message #345931] Fri, 05 September 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you have memory but in so small chunks that it can't be used.

Regards
Michel
Re: ORA-04031: unable to allocate 4192 bytes of shared memory [message #346001 is a reply to message #345931] Fri, 05 September 2008 09:21 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have increased the size of sga_max_size .. kept the same sga_target and I have restarted the DB but now the shared pool free space is getting filled up very fast.

SQL> select sum(SHARABLE_MEM) from v$db_object_cache;

SUM(SHARABLE_MEM)
-----------------
       1675951484

SQL> select * from v$sgastat where name = 'free memory';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 228399192
large pool   free memory                  16713000
java pool    free memory                  55500736
streams pool free memory                  66609120

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3008M
sga_target                           big integer 2304M


Need to know what is causing the issue ?
Re: ORA-04031: unable to allocate 4192 bytes of shared memory [message #346002 is a reply to message #345931] Fri, 05 September 2008 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but now the shared pool free space is getting filled up very fast.
Which would you prefer, that the SGA "free space" remain unused & wasted space or should Oracle put the "free space to actual use & therefore be no longer "free"?

Oracle putting "free space" to work & driving the free space towards zero is a Good Thing (tm)!
Re: ORA-04031: unable to allocate 4192 bytes of shared memory [message #346322 is a reply to message #345931] Mon, 08 September 2008 03:44 Go to previous message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
How how can I monitor the memory used in the shared pool ?
When I check the shared pool size from the v$sgastat and v$db_object_cache I am not able to determine the complete allocation of the shared pool

SQL> SELECT sum(bytes), pool
FROM v$sgastat
GROUP BY pool
/
SUM(BYTES) POOL
---------- ------------
 100655744
2169624352 shared pool
  67114544 streams pool
  16777216 large pool
  67108864 java pool

SQL> select * from v$sgastat where name = 'free memory'
/

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 425876864
large pool   free memory                  16713000
java pool    free memory                  46485184
streams pool free memory                  66591080

SQL> select sum(SHARABLE_MEM) from v$db_object_cache;

SUM(SHARABLE_MEM)
-----------------
       1399886953


So the memory allocated to shared pool is 2.16 GB . The free memory is 425M , so the used memory is around 1.8G but the memory used by the objects is 1.4G . But what about the other 400M ? How is it getting used , is it something related to fragmentation or is this pary of memory being used by any other ?
Previous Topic: using OMF database creation
Next Topic: database hang + page file
Goto Forum:
  


Current Time: Sat Dec 10 08:56:56 CST 2016

Total time taken to generate the page: 0.24623 seconds