If the developers are using a lot of dynamic sql they may not be using bind
variables and that is going to suck.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Holger Marzen" <holger_at_marzen.de> wrote in message
news:bbckv1$mou$2_at_bluebell.marzen.de...
> * 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").
Received on Sun Jun 01 2003 - 15:25:37 CDT
