cache buffer chains slows down the database

Hi

I have recently come into a problem in one of customer databases. The database is 8.1.7.4 running on Suse Enterprise Linux 7. It has been in production for 2.5 years with no performance problem at all until these few days, whenever a specific report is run then this session starts causing latch free waits to all sessions which tries to access a table which the report uses. Sounds hot block however doesnt seems like it. I managed to query x$bh and joined with dba_extents with latch address of the contending latch children, the highest TCH I see is 40. From v$latch_miss I see this increases constantly

SELECT parent_name, "WHERE", SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT
FROM v$latch_misses
WHERE parent_name='cache buffers chains' ORDER BY 3 ...........
cache buffers chains kcbso1: set no access 44891 45 99

I dont have a clue what is kcbso1 and cant find any information related to this specific latch children.

The hash chains isnt long neither, the latch only manages 68 blocks and with highest TCH 40.

The performance is horrible, the funny thing it only hangs those users who access the same table as the report. From v$latchholder identified the SID and the SID in v$session_wait suffers latch free as well, same latch address as all users but with very low tries (p3) whereas other suffer high number of tries. Sometimes the holding session suffer up to 300 seconds seconds_in_wait seen from v$session_wait.

What can this be? How can a report hang 80% users? :-0

Note: The latch address is not constant, one day is one the other day another latch children Received on Wed Jul 14 2004 - 14:50:04 CDT