X$BH and Latch Contention - Secrets of the Oracle Database - page 105

By joining X$BH and DBA_OBJECTS , we may find out to which database objects the blocks
protected by the child latch belong (script latch_vs_blocks.sql ).

SQL> SELECT bh.file#, bh.dbablk, bh.class,
decode(bh.state,0,'free',1,'xcur',2,'scur',3,'cr',
4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
9,'memory',10,'mwrite',11,'donated') AS status,
decode(bitand(bh.flag,1), 0, 'N', 'Y') AS dirty, bh.tch,
o.owner, o.object_name, o.object_type
FROM x$bh bh, dba_objects o
WHERE bh.obj=o.data_object_id
AND bh.hladdr='697ACFD8'
ORDER BY tch DESC;
FILE# DBABLK CLASS STATUS DIRTY TCH OWNER OBJECT_NAME OBJECT_TYPE
----- ------ ----- ------ ----- --- ------ ----------------- -----------
4 476 1 xcur N 24 NDEBES SYS_IOT_TOP_53076 INDEX
1 48050 1 xcur N 4 SYS SYS_C00651 INDEX
1 7843 1 xcur N 2 SYS ICOL$ TABLE
1 7843 1 xcur N 2 SYS IND$ TABLE
1 7843 1 xcur N 2 SYS COL$ TABLE
1 7843 1 xcur N 2 SYS CLU$ TABLE
1 7843 1 xcur N 2 SYS C_OBJ# CLUSTER

The index SYS_IOT_TOP_53076 has the highest touch count among all objects protected
by the child latch. 1 This index is the segment underlying an index organized table.

SQL> SELECT table_name, table_type
FROM dba_indexes
WHERE index_name='SYS_IOT_TOP_53076';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
CUSTOMER TABLE

The big question is whether this whole investigation deserves the DBA's time and energy
or whether he is about to become afflicted by CTD. Compulsive tuning disorder (CTD) is a term
coined by Gaja Krishna Vaidyanatha, co-author of Oracle Performance Tuning 101 ([VaDe 2001]).
Of course, CTD is a pun on the designation of the serious psychiatric disorder OCD (obsessive
compulsive disorder). Looking at a Statspack report that quantifies the workload that caused
the latch contention, it turns out that the wait event latch: cache buffers chains is insignificant.
I modified the Statspack report parameter top_n_events to include the top ten timed events in
the report (default is five). In spite of this, the cache buffers chains wait event did not make the cut.

It's also possible to join X$BH with DBA_EXTENTS by using the where-clause WHERE bh.file#=e.file_id AND
bh.dbablk BETWEEN e.block_id AND e.block_id+e.blocks-1 . However, the response time of such a
query is quite long.

1.