| 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?
|
|
|
|
|
|
| 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 ?
|
|
|
|
|
|
| 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 ?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
