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

• 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 - 05:36:17 CDT