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: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Mar 2006 10:27:48 -0000
Message-ID: <007101c63ead$1e3ac470$0300a8c0@Primary>

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_at_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 ,
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 03 2006 - 04:27:48 CST

Original text of this message

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