Re: Oracle 9i Latching Issue
Date: Sun, 30 Mar 2008 10:20:53 -0700 (PDT)
Message-ID: <d6e4f317-312a-4812-93f1-4552aaea58c2@s19g2000prg.googlegroups.com>
On Mar 29, 4:09 pm, Johne_uk <edg..._at_tiscali.co.uk> wrote:
> I'd just like to thank everyone who contributed.
>
> One last thing - is there anything I could to to the SYS index below
> to reduce latch waits.
>
> "There was also high waits on the SYS.I_OBJ2 index on the sys.I_OBJ2
> table. "
>
> SEGMENT_NAME TCH
> SYS.I_OBJ2 2028
One thing that the Metalink article failed to mention is that you need
to properly scope the query into V$LATCH_CHILDREN before making the
auumption that you have a specific hot block. To do this, you would
capture the current contents of V$LATCH_CHILDREN into another table,
start a Statspack cature, let the system run normally where you
typically experience slow performance with Discoverer, and after 15
minutes, stop Statspack and compare the ending values of V
$LATCH_CHILDREN with those values that were captured in the separate
table. Sort the delta values descending by sleeps and then misses.
If you find that a particular cache buffers chains latch floats to the
top of the sorted list, with CHILD#=5883 and LEVEL#=1, you can use a
query like this to determine the database objects and row ID (if the
block belongs to a table) of the first table row in the block (note
that this must be run by the SYS user):
SELECT
DO.OWNER||'.'||DO.OBJECT_NAME OBJECT_NAME,
DO.OBJECT_TYPE,
DECODE(DO.OBJECT_TYPE,'TABLE', DBMS_ROWID.ROWID_CREATE(1, BH.OBJ, BH.FILE#, BH.DBABLK, 1),NULL) ROWID_FIRST_ROW, BH.OBJ DATA_OBJECT_ID, BH.FILE#, BH.DBABLK, BH.CLASS,
BH.STATE,
BH.TCH
FROM
V$LATCH_CHILDREN LC,
X$BH BH,
DBA_OBJECTS DO
WHERE
LC.NAME='cache buffers chains'
AND LC.CHILD#=5883 AND LC.LEVEL#=1 AND LC.ADDR=BH.HLADDR AND BH.OBJ=DO.DATA_OBJECT_ID AND BH.TCH>100
ORDER BY
BH.TCH DESC; It could very well be that while the touch count for SYS.I_OBJ2 is high, it might be that there were few sleeps and/or misses on the cache buffers chains latch that protects that block during the slow performance period.
I see that you attempted to create a custom DUAL table - you may want to undo that if you upgrade to 10g, as assess to the DUAL table in 10g takes 1/2 the logical IOs of a normal table (this is not the case in 9i).
You may want to check CPU utilization before and after the Statspack run, as high CPU utilization can lead to problems with latches and increased time for log file sync to complete. This may be caused by essessive logical IO, essessive in-memory sorts, excessive parsing (hard or soft), excessive recursive calls (triggers firing or space management), bad execution plans, etc. To determine if this is a potential problem, compare the before and after values found in V $SYSSTAT. Look at the delta value for "CPU used by this session" - divide the delta value by 100, divide again by the number of CPUs in the server, and divide once again by the number of seconds elapsed between the first capture of V$SYSSTAT and the end capture - this is the percentage of available CPU seconds that were used. If you receive a value of 0.90, then that indicates that an _average_ of 90% of available CPU capacity in the time period was used. It could be that 100% of available CPU capacity was used for 10 minutes, and slightly less than 90% for the remaining 5 minutes, so it is important to keep the duration of the Statspack run short so that the averages are not too misleading.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Mar 30 2008 - 12:20:53 CDT