Reasons for flushing shared pool

[Ben Ryan]

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.

[Dave A]

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" <ben...@my-deja.com> wrote in message
news:8snrgj$ovc$1...@nnrp1.deja.com...

[Kevin Brand]

Using MTS? Any cursor-holding parameters set in init.ora?

Just before the flush, look at x$ksmlru. The data in this table is dynamic
in that selected rows are cleared. That is, once you select the rows from
this table, the next select will only contain the new entries ( if any ).

This table will show you the trun over in the shared pool. Column defs:

ksmlrhon: pl block or cursor ( SQL statement being put in the shared pool )
klmlrsiz: size of the SQL statement being put in the shared pool
klmlrnum: number of objects displaced to make room for the new
statement/object

If you see rows in this table all the time ( just before your flush ) and
the sizes of the new entries are large ( 5k to 10k ), you may want to take a
longer look at the SQL statement to find out whether it is sharable ( bind
variables, etc ). To do this, get the hash value from the above table, join
it to v$sqlarea hash_value column. Aside from non-sharable SQL, lots of
turnover in here could also indicate that the shared_pool is too small.

Also, just before the flush, check for latch contention against the
shared_pool latch, as if the shared pool free list becoms fragmented,
sessions can obtain and hold this latch for longer periods of time searching
for a free chunk. Flushing the shared pool truncates this list and can
relieve the wait time associated with it.

-Kevin