Re: Oracle 8.1.7: shared pool fragmented - can I clear it on-the-fly?

Holger Marzen wrote:

> * On Sun, 1 Jun 2003 01:29:19 +0200, Antoine BRUNEL wrote:
>
> > How can you say you shared pool is fragmented ??
>
> If the database runs fine again after a restart *and* the developers
> swear that they didn't change the application *and* the shared pool is
> already huge *and* the programmers make heavy use of pl/sql *and* they
> have hundreds of concurrent connections to Oracle *and* I find many
> archived Google postings dealing with "cannot allocate xxx bytes"
> then it might be a good guess.
>
> > Be precise with error codes in your terms.... informatic is a cartesian
> > science, not an art.
>
> Your're right.
>
> > However, memory management issues can cause some areas of shared pool to
> > have a bad use of memory... this was some times ago an issue, notably in the
> > sql area for non binded statements.
> >
> > In some case, doing an "alter system flush shared_pool" may help for
> > application which are not using shared SQL, or obviously is doing strange
> > statements.
>
> I'll try this to survive until the next maintenance can be done.
>
> > This command will flush from SQL area un-pinned statements (not currently
> > being executed), and PL/SQL blocks. This can result in unused chunks of
> > memory to be said free, knowing these chunks are of the same size.
> >
> > If your application is making a strong use of big PL/SQL blocks (stored or
> > not), then ensure these are using bind variables (they can be shared), and
>
> I'll aks the programmers if they can do that.
>
> > maybe try to allocate a bigger dedicated space for large PL/SQL blocks
> > (someting like "shared_pool_reserved_size").

I think you have jumped to a conclusion with far too little supporting information.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)