cache buffer chains
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: cache buffer chains

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    cache buffer chains

    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??
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    (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'
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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".
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width