Re: Reasons for flushing shared pool

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Fri, 20 Oct 2000 09:31:19 -0500
Message-ID: <8spkgs$rbk$1@news.gte.com>

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

"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.
Received on Fri Oct 20 2000 - 09:31:19 CDT