Hi,
My problem: Facing Latch free contention on cache buffer chains
Table size=247G
the query
SELECT /*+ index(b) */
SUM (MAX (b.t_value))
FROM big_tab b
WHERE b.c_number > :b1
AND b.p_num = :b2
AND b.p_code = 'P'
AND NOT EXISTS (
SELECT 1
FROM big_tab c
WHERE c.c_number <= :b1
AND c.p_num = :b2
AND c.p_code = 'P'
AND c.a_num = b.a_num
AND c.b_num = b.b_num
AND c.orig_date = b.orig_date
AND c.orig_trk_grp = b.orig_trk_grp
AND c.orig_trk_mem = b.orig_trk_mem
AND c.fin_trk_grp = b.fin_trk_grp)
GROUP BY b.a_num,
b_num,
b.orig_date,
b.orig_trk_grp,
b.orig_trk_mem,
b.fin_trk_grp
The explain plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21173200 Card=1 Byte
s=98)

1 0 SORT (AGGREGATE) (Cost=21173200 Card=1 Bytes=98)
2 1 SORT (GROUP BY) (Cost=21173200 Card=1 Bytes=98)
3 2 MERGE JOIN (ANTI) (Cost=21173200 Card=7662 Bytes=75087
6)

4 3 SORT (JOIN) (Cost=21173004 Card=7662 Bytes=383100)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TAB' (Cost
=21172968 Card=7662 Bytes=383100)

6 5 BITMAP CONVERSION (TO ROWIDS)
7 6 BITMAP INDEX (RANGE SCAN) OF 'BIG_TAB_IDX'
8 3 SORT (UNIQUE) (Cost=197 Card=1 Bytes=48)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TAB' (Cost
=191 Card=1 Bytes=48)

10 9 BITMAP CONVERSION (TO ROWIDS)
11 10 BITMAP INDEX (RANGE SCAN) OF 'BIG_TAB_IDX'
hot blocks
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#

BIG_TAB 313 47929 2030 319
BIG_TAB 313 56121 2030 319
BIG_TAB 327 20849 2030 319
Any ideas??