Re: HIGH latch free Wait on moving from RBO to CBO?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Mar 2006 10:27:48 -0000
Funnily enough, the first (technical) slide in my
"Migrating to CBO" presentation at Hotsos shows
how you can increase the CPU and latch activity
even if every single execution plan stays the same.
You really need a snapshot from a comparable period
running under RBO before drawing any conclusions.
(a) It is possible that you have a few changes in execution
path that result in much more logical I/O - hence much
more CPU usage. In this case, you might have virtually
no change in the amount of latch activity on the library
cache - but have much more of a problem getting latches
or coming back onto a CPU after sleeping for a latch
because of CPU starvation.
(b) On the other hand, you may simply be doing much more
work whilst parsing, which would mean an increase in
latch acquisition on the library cache, and latch competition.
Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of
4400 Help?
Probably not. But since you have 5,000 users, can you
check that your shared_pool_reserved is at least 5,000 * 25KB
plus a few dozen MB. One possible pressure point on latching
comes from Oracle attempting to allocate a memory chunk for
the session parameters. If you have lots of sessions logging on
and off all the time, you need to get these chunks into the
reserved pool.
Your comment about cursor_sharing = force/similar could
be indicative of other latch activity going sky-high and causing
extreme blocking. (I would have bet on that if you had only
complained about 'similar', but 'force' makes me a little cautious -
it is possible, of course, that you get the same symptom for two
different reasons).
Qs Will Tuning of individual SQLs reduce this wait ?
Quite possibly - if your underlying problem is scenario
(a) above. In 9.2.0.5 I would have a quick check for
execution plans that were showing btree-bitmap conversions,
and subquery-unnesting. These are operations that have
caused people particular grief in the past on upgrades.
Otherwise look for SQL that appears to be much more CPU
intensive than it was before the upgrade. (Again, if you have
the snapshots across a similar period - at level 5 for the "top
sql" reports).
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Subject: HIGH latch free Wait on moving from RBO to CBO?
Date: Thu, 2 Mar 2006 23:27:22 +0530
From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
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 ,
--
//www.freelists.org/webpage/oracle-l
Other related posts:
