Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: HIGH latch free Wait on moving from RBO to CBO?

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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 3 Mar 2006 21:08:44 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF3020380D44@BLRKECMSG11.ad.infosys.com>


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_at_gmail.com] Sent: Fri 3/3/2006 10:35 AM
To: VIVEK_SHARMA; vivek_sharma_1_at_yahoo.com 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 9.2.0.5 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.

regards
anand

On 03/03/06, shiva subramaniam <shiva.subramaniam_at_gmail.com> 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.                              

        Regards                   

        Shiva                            

        On 3/3/06, VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com > 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 9.2.0.5 (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 Pct 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 9.2.0.5 (64-Bit), Solaris 9, Number of CPUs on DB Server = 48 Thanks Folks Vivek P.S. include my yahoo id (in CC) while replying please if possible **************** 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***
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 03 2006 - 09:38:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US