cache buffer chains

[simply_dba]

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??

[KenEwald]

Your explain plan looks good, but it makes sense that this query would cause hot blocks and latch contention. It's what Don Burleson would call a "self-eating watermelon".

Please post the following:

1. Version

2. select count(*) from v$latch_children where name = 'cache buffers chains';

or

3. db_block_buffers setting to determine # of latches

If db_block_buffers < 2052,
latches = 2 ^ trunc(log(2, db_block_buffers - 4) - 1)

If db_block_buffers >= 2052 and <= 131,075
latches = 1024

If db_block_buffers > 131,075,
latches = 2 ^ trunc(log(2, db_block_buffers - 4) - 6)

4. Long shot: # cpu's

[OracleDoc]

Originally posted by KenEwald
Your explain plan looks good, but it makes sense that this query would cause hot blocks and latch contention. It's what Don Burleson would call a "self-eating watermelon".

Ken why would you want circumvent the censors and use such foul language at simply_dba for asking an innocent question?

[abhaysk]

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

I find some part of the code unncessary.. BTW, can you tell us what is the PK Cols?

And also the plan looks bad..


Rgds
Abhay.

[simply_dba]

(KenEwald)
1) Version is 9.2.0.5
2) We are not using db_block_buffers(it is set to 0)
No.of latches=1024
3) #cpu=8.


(abhaysk)
Can't help it if it looks bad to you.In fact it looks bad to me too.But it is a purchased code(wrapped).

No Pks. Non-unique bitmap indexes on (1)c_number(big_tab_idx),(2)p_code


(OracleDoc)
No Harm caused.I usually don't read what follows the word 'DON BURLSON'

[KenEwald]

How 'bout Michael Moore's next movie - "The Redneck in Tennis Shoes".

OK, 9.2.0.5 instances default to 1024.

Sounds like you're stuck with what you've got.

I know it's out there, but, how about putting the hot datafiles on solid state (ram) disk?

[simply_dba]

NAH:That's not possible at present.
However the problem seems to conserned with the package itself.It was causing ora-1555 errors=>causing the sqls to cling on the latchers for an infinite period of time.

How 'bout Michael Moore's next movie - "The Redneck in Tennis Shoes".