HIGH latch free Wait on moving from RBO to CBO?

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Thu, 2 Mar 2006 23:27:22 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF3020380D42@BLRKECMSG11.ad.infosys.com>

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

Received on Thu Mar 02 2006 - 11:57:22 CST