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

[jiong]

Hi All,

We have Oracle8.1.7 Release 3 installed on Sun Solaris 64-bit machine with Solaris 8. We constantly have the

'ORA-04031: unable to allocate 4096 bytes of shared memory'

error message. In the init.ora file, we have set 'shared_pool_size = 10485760'. I don't think it is an application problem. Is it because some bugs in Oracle? Should we apply some patchset or is there a workaround? Your help is greatly appreciated.

[marist89]

10M shared pool? I would bump this up to at least 32M, preferably 64M if you have the Physical RAM.

ORA 4031 errors usually result from shared pool fragmentation. There were some bugs in 8.1.7.1 in this area, but you probably just don't have enough for what you are trying to do. I would also look to make sure you are using bind variables as much as possible.

[jiong]

Hi Jeff,

Thank you for your suggestion. We have 2GB RAM. How big do you think we can put for shared_pool_size? Is 64, 128, or even 256 MB fine?

Thanks,

[Shestakov]

From 64M to 128M for most application enough.

[jperusco]

I have had this issue with two 8.1.7.3.0 databases. There is a patchset you can apply but there is also a workaround.

Add the following to your init.ora:

event="4031 trace name errorstack level 3"

_db_handles_cached = 0

works a treat.

John

[davidhle]

Your shared pool is quite small. You can run the following script every 15 min during a day to estimate how much memory you need for the share pool size:
---------
set numwidth 15
column shared_pool_size format 999,999,999
column sum_obj_size format 999,999,999
column sum_sql_size format 999,999,999
column sum_user_size format 999,999,999
column min_shared_pool format 999,999,999
select to_number(value) shared_pool_size,
sum_obj_size,
sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache),
(select sum(sharable_mem) sum_sql_size
from v$sqlarea),
(select sum(250 * users_opening) sum_user_size
from v$sqlarea), v$parameter
where name = 'shared_pool_size';

--------
ORA-04031 also occurs if you use MTS mode and set the large pool size too small. USe the following script to estimate your large pool (avg(value) * max # of sessions)

select a.name, avg(b.value)
from v$statname a, v$sesstat b
where a.statistic#= b.statistic# and a.name like '%uga%'
group by a.name;
--------


david le

[jiong]

Thanks for all you guys!

I have applied the workaround and appreciate the input.

[jiong]

David,

I found your scripts very helpful. Thank you for sharing them with us.