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 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@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.
Regards
Shiva
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 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***
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
