Re: shared memory problem (ORA-04031)

Walter Dorninger wrote:
>
> Hi,
>
> I'm currently running a Oracle 8.1.5 on Solaris 2.6 ( E450 2GB ram) and if i
> run some certain statements I get the following error (not very large
> statements):
>
> ORA-04031: unable to allocate 131072 bytes of shared memory ("shared pool",
> "unknown object", "cursor work he", "QERHJ Hash Table")
>
> I have set the initora parameters listed below to the following values:
> ...
> shared_pool_size = 20000000
> sort_area_size = 3000000
> sort_area_retained_size = 500000
> ...
>
> Another issue is that the sort operations are extremly slow ... (perhaps
> this has to do something with this...)
>
> Is it possible that the error is related to the settings in my /etc/system
> file ? - The /etc/system file is listed below:
> set shmsys:shminfo_shmmax=4294967295
> set shmsys:shminfo_shmmin=1
> set shmsys:shminfo_shmmni=100
> set shmsys:shminfo_shmseg=10
> set semsys:seminfo_semmni=100
> set semsys:seminfo_semmsl=100
> set semsys:seminfo_semmns=200
> set semsys:seminfo_semopm=100
> set semsys:seminfo_semvmx=32767
>
> Thanks a lot
> Walter

Time to crank up your shared pool, but possibly more importantly is to look at which cursor, plsql and sequence objects are being used frequently and pin them into the pool.

If you're running 24x7, an occasional flush of the SGA may also help

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse