RE: HIGH latch free Wait on moving from RBO to CBO?

  • From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 3 Mar 2006 21:08:44 +0530

High "Latch free" WAIT Resolved by Tuning the BAD SQLs which were doing FULL 
Table Scans. 
CPU Usage on BOTH APP & DB Servers thus Fell down to 60 % from 100 %.
Thanks Anand, Kirti, Jonathan for the quick & valuable feedback


From: Anand Rao [mailto:panandrao@xxxxxxxxx]
Sent: Fri 3/3/2006 10:35 AM
To: VIVEK_SHARMA; vivek_sharma_1@xxxxxxxxx
Cc: oracle-l
Subject: Re: HIGH latch free Wait on moving from RBO to CBO?

Hi Vivek,

Yes, as Kirti mentioned, there are too many variables to check.

It would be wise to actually *TEST* your application in a phased manner rather 
than just arbitrarily changing CURSOR_SHARING or other parameters. 

if i am not mistaken, the last time i touched the your application on a 5 TB 
database, it suffered heavily with the same problem on on Solaris 9 on 
a 48 CPU machine.

you really need to change your SQL. That's the long term solution. 

from the small statspack extract, it looks like you have problems with library 
cache latch rather than CBC latch. but a more detailed information from 
Statspack, V$LATCH, V$LATCH_CHILDREN will be helpful to you.

Metalink has excellent articles on Shared Pool tuning, assuming that you have 
isolated the problem to pure lib cache issues.

for the short term, increase your session_cached_cursors to 500 or more and 
check the results. leave open_cursors at 200. Remember, your shared pool needs 
a proportional increase too. I guess you are already using 1GB or more. On top 
of that you have MTS :-) 

have you configured large_pool_size ?

Have you tried dividing your shared pool into multiple sub pools? 

need to set _kghdsidx_count=3 or 5

contact Oracle Support before doing this change.

you have to dig deeper and find out what is causing this latch contention.

1) Is it too many (hard) parses?
2) are you sharing SQL? Version count, etc.
3) Aging of objects?
4) Heavy Sequence usage?
5) Too many lookups to the Dictionary Cache

are you using LMTs and ASSM?

I guess you are already using Partitioning for both tables and indexes.

many factors can contribute to the overall symptom. you have to test it in an 
organised fashion. 

see if session_cached_cursors relieves you a bit. use cursor_sharing=similar 
and see if it makes any difference.


On 03/03/06, shiva subramaniam <shiva.subramaniam@xxxxxxxxx> wrote: 

        I am wondering if you are using DISM (Dynamic Intimate shared memory). 
If you are using DISM, please check if there is any swapping.
        On 3/3/06, VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx > wrote: 

                ISSUE - Getting HIGH Latch Free Wait on the following Latches 
after moving from RBO to CBO. ( ALL Objects been analyzed at 100 %). CPU Usage 
on DB Server has gone up by about 30 %. NOTE - Application has also been 
migrated to a Higher release along with the CBO movement. 
                Qs Any init.ora parameters to Tune ?
                Would increasing _shared_pool_reserved_min_alloc to 6140 from 
the Default of 4400 Help?
                Setting cursorsharing = FORCE/SIMILAR caused "%sys" component 
of CPU Usage to shoot to 99 % within minutes of Database startup. Seemed to be 
hitting some Bug in (64 Bit) on Solaris 9. Has anybody experienced this 
& is it solved in a 9i Higher Patch ? Which Patch on 9i is advisable? 
                Qs Will Tuning of individual SQLs reduce this wait ? 
                Any Other Advice please?
                Top 5 Timed Events
       % Total
                Event                                               Waits    
Time (s) Ela Time 
                -------------------------------------------- ------------ 
----------- --------
                latch free                                     17,386,965     
457,377    63.67
                CPU time                                                      
121,612     16.93
                db file sequential read                       142,824,481      
72,252    10.06
                log file sync                                     982,290      
28,909     4.02
                db file scattered read                          8,532,496      
14,464     2.01
                Wait Events for DB: UBIFIN  Instance: UBIFIN  Snaps: 1515 -1517
                Latch Activity for DB:
                                                          Pct    Avg   Wait     
                                             Get          Get   Slps   Time     
  NoWait NoWait
                Latch                       Requests      Miss  /Miss    (s)    
 Requests   Miss
                ------------------------ -------------- ------ ------ ------ 
------------ ------ 
                cache buffers chains      4,141,035,268    0.3    1.2      0  
311,630,019   26.7
                cache buffers lru chain       3,785,689    6.1    0.2   2300  
321,104,446   25.1
                library cache                86,823,459    1.3     0.5  31780   
    56,524   17.4
                Database Size 1 TB , Number of Concurrent Users = 5000 ,
                Application Hybrid Application - Banking Product , Oracle (64-Bit), Solaris 9, Number of CPUs on DB Server = 48 
                Thanks Folks
                P.S. include my yahoo id (in CC) while replying please if 
                **************** CAUTION - Disclaimer *****************
                This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION 
intended solely for the use of the addressee(s). If you are not the intended 
recipient, please notify the sender by e-mail and delete the original message. 
Further, you are not to copy, disclose, or distribute this e-mail or its 
contents to any other person and any such actions are unlawful. This e-mail may 
contain viruses. Infosys has taken every reasonable precaution to minimize this 
risk, but is not liable for any damage you may sustain as a result of any virus 
in this e-mail. You should carry out your own virus checks before opening the 
e-mail or attachment. Infosys reserves the right to monitor and review the 
content of all messages sent to or from this e-mail address. Messages sent to 
or from this e-mail address may be stored on the Infosys e-mail system. 
                ***INFOSYS******** End of Disclaimer ********INFOSYS***


Other related posts: