RE: distribution of the sleeps on the library cache latches

Hi Ed,

Of course, I'd suggest that the application be enhanced to use bind variables appropriately! ;-)

In the interim, I would introduce a script such as 'keeper.sql' from the Ixora web site to keep all the reusable
material in the library cache so as to reduce the impact of the flushes. Once that is working as desired, I would
increase the flush frequency to an interval of say 1 hour or 30 minutes. The size of the library cache and thus shared
pool utilization will still grow over time, but more slowly. I would then reduce the shared pool size to approximately
the size that it grew to after 1 day of normal application usage. To then mitigate the risk of ORA-4031 errors I would
ensure that 'shared_pool_reserved_size' is allowed to default, but set '_shared_pool_reserved_min_alloc' to its minimum value (which is 4000 or 5000, version dependent). An instance restart once a week would be good too if you can manage
that.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/              -  For DBAs
@   http://www.secularislam.org/call.htm  -  For Muslims
@   http://www.christianity.net.au/       -  For all

-----Original Message-----
Sent: Friday, 19 October 2001 19:09
To: Steve Adams; ORACLE-L_at_fatcity.com

Hi Steve,

yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case?

Thanks in advance,
Ed

> Hi Ed,
>
> I would agree with the _kgl_latch_count change, but the _kgl_bucket_count
change seems unwarranted and extreme. Rather I
> suspect that the size of your library cache hash table rather reflects an
oversized shared pool, probably with some use
> of literal SQL.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/ - For DBAs
> @ http://www.secularislam.org/call.htm - For Muslims
> @ http://www.christianity.net.au/ - For all
>
>
> -----Original Message-----
> From: Edward Shevtsov [mailto:ed_at_mb.ru]
> Sent: Friday, 19 October 2001 18:02
> To: ORACLE-L_at_fatcity.com
> Cc: Steve Adams
> Subject: Re: distribution of the sleeps on the library cache latches
>
>
> Hi Steve,
>
> thanks for your reply. I'm thinking about twice increasing number of
> library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on
> them.
> Also I would like to set _kgl_bucket_count = 8 according to output of your
> script. Do you think it's a good idea in my case.
>
> NAME IMPACT SLEEP_RATE HOLDING LEVEL#
> ------------------------- ---------- ---------- ---------- ----------
> library cache 60333579.3 0.32% 172945238 5
> shared pool 19313269.2 1.40% 8265405 7
> cache buffers chains 1950080.11 0.00% 629411 1
> row cache objects 738401.912 0.04% 3369329 4
> session allocation 70758.0784 0.01% 144008 5
> cache buffer handles 56104.2222 0.01% 71913 3
> redo allocation 33494.1227 0.02% 215582 6
> cache buffers lru chain 12784.3859 0.00% 198869 3
> checkpoint queue latch10980.4325 0.00% 52259 7
> latch wait list 9976.33016 0.04% 24412 9
> redo writing 4846.5256 0.01% 75484 5
>
> Regards,
> Ed
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).