Re: Reasons for flushing shared pool

There are many reasons I can think of to flush the shared pool, but given that bind variables are being used, it seems a better solution for all of those reasons is to enlarge the SGA if it is in fact the library cache that is having problems. Perhaps, they have multiple databases on a server and they can't set shared memory max high enough to enlarge the SGA and still start the DB? I know it's a long shot, but its about the only thing I can think of off the top of my head. If so, you could reduce the db block buffers to make the library cache bigger.

I would not necessarily think that the library cache is too small unless the library cache hit ratio reflects that. Sometimes an error occurs when running stuff and for the life of me I can't remember what that error is (sorry), but it's because a large chunk of memory is needed and only small fragments remain. This is caused by shared pool reserved size not being large enough(or sometimes the min alloc being too big). Flushing the shared pool makes room for the stuff that needs to get put in memory. Of course, setting the reserved size larger will do the same thing.

V$SHARED_POOL_RESERVED may shed some light on the situation.

Dave A

--
Dave A


"Ben Ryan" <benryan_at_my-deja.com> wrote in message
news:8snrgj$ovc$1_at_nnrp1.deja.com...


> I have a client who I am told is running an OLTP production

> database with a job scheduled to run ever hour to flush the

> shared pool. (Presumably via executing ALTER SYSTEM FLUSH SHARED_POOL

> by running, say, a cron job.)

>

> The application using the database (8.0.5.2.1 on HP-UX 11.0), to

> the best of my knowledge does NOT use PL/SQL and DOES use bind

> variables (application uses ODBC).

>

> The client is encountering problems with insufficient space in

> the shared pool. Given that the application is using bind

> variables, then it sounds to me that the shared pool (specifically the

> libary cache) is too small.

>

> Can anyone think why flushing the shared pool would be a useful

> in this senario?

>

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.