Re: ORA-4031 - shared memory fragmentation

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/04/22
Message-ID: <335C7275.136C@mf.sigov.mail.si>#1/1

Graham Newton wrote:
>
> We've been experiencing several ORA-04031 errors recently when perfoming the
> most innocuous SQL statements on an Oracle 7.2.3 server (IBM RS/6000 AIX
> 3.2.5).
>
> Nothing we've done so far has solved the problem. Basically all we've tried
> is increasing the shared_pool_size a little.
>
> Has anyone else been experiencing these problems? Does anyone have any ideas
> on how to overcome them?
>
> Graham Newton

If you use packages extensively, you should "pin" your most used packages into memory immediately after database startup. That way, packages in shared pool will use contigous memory and shared pool fragmentation will thus be reduced.

You should:

- shutdown your database
- start it up again
- immediately load and pin the mostly used package

(You load the package into shared pool by referencing it. You could execute some dummy procedure from it or execute "ALTER PACKAGE package_name COMPILE". You pin the package by isuing "EXECUTE DBMS_SHARED_POOL.KEEP('package_name')")
- repeat last step for all your heavy used packages.

Regards,

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================

Received on Tue Apr 22 1997 - 00:00:00 CDT