RE: cache buffer chains contention

Have a look of the hot blocks. The most probable reason for cache buffer chains are SQLs with bad performance. Don't discard contention on higher level latches as redo allocation.

So to look for those hot blocks, use one of the S.Adams scripts: hot_hash_blocks.sql ( www.ixora.com.au) and these one too:

select  hladdr "LATCH ADDRESS",
        dbafil "FILE#",
        dbablk "BLOCK#",
        state,
        gets,
        lc.sleeps
        from x$bh bh,
        v$latch_children lc
        where lc.addr=bh.hladdr and
        state!=0
        and sleeps>10000
        order by sleeps;

Then search for the objects in the dba_extents and then run after the SQLs with top_ten stmts similar to the app of materialdreams or the ones from S.Adams site.

Again from S.Adams site you can read some answers of these problems.

Regards.

> -----Mensaje original-----
> De: elain he [SMTP:elainhe_at_hotmail.com]
> Enviado el: miércoles 28 de febrero de 2001 0:31
> Para: Multiple recipients of list ORACLE-L
> Asunto: cache buffer chains contention
>
> Does anyone have any advice on reducing cache buffer chains latch
> contention?
>
> From v$latch_children, I found the children with the highest sleeps and
> narrowed that to two tables. A method of reducing the contention is
> probably
> to reduce the number of records per block but that would also mean that
> now
> there will be more blocks to read. Is there any other ways of reducing the
>
> contention? I've tried increasing/reducing _db_block_hash_buckets but that
>
> did not help.
>
> db_block_buffers=40960
> _db_block_hash_buckets=db_block_buffers/4
> db_block_size=8K
> tables' pctfree=30, pctused=40
>
> thanks.
>
> elain
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: elain he
> INET: elainhe_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Trassens, Christian
  INET: CTrassens_at_uni2.es

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).