Re: ORA-04031 - Which packages should be kept in memory?

From: Tim Arnold <timkarnold_at_comcast.net>
Date: Fri, 27 Apr 2007 09:30:23 -0400
Message-ID: <ytidneiOH9uxZqzbnZ2dnUVZ_gOdnZ2d@comcast.com>

"Alexander Skwar" <alexander_at_skwar.name> wrote in message news:1259740.papkDWZVjN_at_kn.gn.rtr.message-center.info...
> Hello!
>
> All of a sudden, I'm getting ORA-04031 error messages on of my Ora9i
> databases. On various sites on the internet, like
http://www.techonthenet.com/oracle/errors/ora04031.php,
> I find the following:
>
> Use the DBMS_SHARED_POOL package to pin large packages.
>
> How do I know or decide, which packages I should KEEP in memory
> with DBMS_SHARED_POOL.KEEP()? I know that it says to keep "large
> packages" - but I suppose I should only keep those "packages"
> which are needed. How do I know, which packages are needed? What
> is "large"?
>
> Thanks,
>
> Alexander Skwar

Have you gathered statistics recently?
Something like -- exec dbms_stats.gather_schema_statistics('schema name', CASCADE=>TRUE);

We had the same problem and the above solved it. However, the vendor told us he forgot to provide index creation scripts for our site.
Execution went from 1.5 Hr to 8 min. Received on Fri Apr 27 2007 - 08:30:23 CDT