OraFAQ Forum: Server Administration » ORA-04031: unable to allocate 4192 bytes of shared memory

Home » RDBMS Server » Server Administration » ORA-04031: unable to allocate 4192 bytes of shared memory

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-04031: unable to allocate 4192 bytes of shared memory [message #345931]

Fri, 05 September 2008 06:14

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?

Report message to a moderator

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

Michel Cadot
Messages: 68666
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

Report message to a moderator

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

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 ?

Report message to a moderator

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

BlackSwan
Messages: 26766
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)!

Report message to a moderator

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

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 ?

Report message to a moderator

Previous Topic:	using OMF database creation
Next Topic:	database hang + page file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 08 05:59:27 CDT 2024