Re: severe lib cache contention !!

From: Christian Trassens <ctrassens_at_yahoo.com>
Date: Sun, 29 Jul 2001 11:22:50 -0700
Message-ID: <F001.003587A7.20010729110521@fatcity.com>

I don't remember the answers you received. However I suggest you to reduce the shared_pool_size to a minimum size possible. Set the
shared_pool_reserved_size if you have PL code. Put the STANDARD package in keep with the
dbms_shared_pool.keep. Set also _kgl_bucket_count to a value like 4 or 3 depending on the size of shared pool. And also put in the init.ora the
_sqlexec_progression_count=0. Read the notes of metalink about it. And set a cron with a sql script that verify the amount of free memory from v$sgastat and the # of library cache latches. If it isn't appeared to be any free memory or library cache latch contention is high, trigger the script to issue an alter system flush shared_pool. Set the cron in a period of minutes. Another recomendation try with public synonyms if you have a lot of objets. Better would be if the application could use alter session set current_schema. However the most worthiest of all suggestions, would be to look at the sql running. They shouldn't take longer to accomplish their results or to finish. I mean the longer it takes the transaction the most probable for your to see contention in this latch and also in the cache buffer chains latch.

Regards.

Rahul <rahul_at_ratelindo.co.id> wrote:
> list,
> i just ported a system to 8.1.5, the application
> DOES NOT make use
> of bind variables, each and every query (fired from
> 8 clients every 2-3
> seconds)
> parses and executes, this keeps the shared pool
> patch also very busy...
>
> i cannot tune the app, is there something i can do
> to reduce the lib cache
> and shared pool
> latch contention ??
>
> v$session_wait show all sessions waiting on "latch
> free"
>
> the sleeps in lib cache latch are not properly
> spread out also...
>
> SQL> select * from v$latch_children where latch#=99
> order by 8
>
> CHILD# GETS MISSES SLEEPS
> --------------------------------------
> 1 209093 149 117
> 5 283185 602 221
> 6 304797 616 264
> 7 583061 3354 1417
> 4 2697205 191099 33858
> 2 11636488 8683396 980522
> 3 60982875 47012872 8777649
>
> Interestingly, the same app was NOT showing any
> contention on these latches
> in 7.3.2 !!
>
> how can i solve this problem ?
>
> TIA
>
> Rahul
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Rahul
> INET: rahul_at_ratelindo.co.id
>
> 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).

Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: ctrassens_at_yahoo.com

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

Received on Sun Jul 29 2001 - 13:22:50 CDT