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

You should read the following from Metalink.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=62143.1

After having problems with our shared pool simliar to yours, we set the following in the init.ora and don't get the problems we were getting:

shared_pool_size = 314572800
shared_pool_reserved_size = 31457280
shared_pool_reserved_min_alloc = 4200

The shared pool was already set pretty high, but I think the size is relevant in setting the other parm shared_pool_reserved_size, so I included it above.

-- 

"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").