latch: cache buffers chains latch contention – a better way for finding the hot block

Here’s a treat for Oracle performance professionals and geeks who are looking for more systematic ways for cache buffers chains (CBC) latch contention troubleshooting. Cache buffers chains latches are taken when a process wants to walk through a cache buffer hash chain, looking if the block with required DBA (data block address) is in buffer cache. If the block happens to be in cache, then in most cases it has to be pinned first before use and unpinned after use, to make sure no-one else can perform an incompatible operation on that block at the same time. The modification of pin structures (pinning/unpinning) is also protected by CBC latches.

CBC latch contention can happen for multiple reasons, but one reason is that there is some really hot block in a SMP system with high number of CPUs (or CMT system with high number of threads like Sun T-series servers). Sometimes there happen to be multiple moderately hot blocks “under” the same CBC latch, which can result in latch contention again.

Traditionally DBAs used to look up the child latch address from V$SESSION_WAIT, sql_trace output or ASH and then look up all buffers protected by that latch from X$BH, using HLADDR column (HLADDR stands for Hash Latch Address). I also have a script for that, bhla.sql (Buffer Headers by Latch Address), which reports me all blocks currently in buffer cache, “under” that particular latch and the corresponding data block addresses and object names:

SQL> @bhla 27E5A780  <-- latch address reported by wait interface (Parameter1 in latch: cache buffers chains wait event)

FLG_LRUFLG                 OBJ OBJECT_TYPE         object                                          TCH DBA     
------------------- ---------- ------------------- ---------------------------------------- ---------- ---------
80000:8                      2 TABLE               SYS.SUBCOLTYPE$                                   0 1 7873  
80000:8                      2 TABLE               SYS.ATTRCOL$                                      0 1 7873  
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:4                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
2202000:8                 3710 TABLE               SYS.WRI$_ADV_REC_ACTIONS                          1 3 972   
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
0:0                      50472 INDEX               SYSMAN.MGMT_JOB_EXEC_IDX04                        1 3 27699 
2202000:8                94227 TABLE PARTITION     SYS.WRH$_ROWCACHE_SUMMARY                         2 3 36142 
80000:8                  54880 INDEX               PERFSTAT.STATS$UNDOSTAT_PK                       14 4 118331
0:8                         37 INDEX               SYS.I_OBJ2                                       24 1 55591 
2000:8                    3680 TABLE               SYS.WRI$_ADV_TASKS                               26 3 739   
0:8                         75 TABLE               SYS.IDL_UB2$                                     35 1 11745 
0:8                         73 TABLE               SYS.IDL_UB1$                                     38 1 12211

From above we see that this child latch is protecting many blocks in buffer cache. So which one is the troublemaker? This is where the touchcount (TCH column) has been traditionally used, the higher the TCH the hotter the buffer, right?

Actually this may not always be true. For example if there is a somewhat hot block which has been steadily in buffer cache for weeks, it may have very high touch count. Now if there is an extremely hot block used by every morning’s batch job (and causing the trouble) but not used later on in the day, this block will get aged out later in the day. Once a block is aged out, its metadata, including TCH is gone! So when the block is loaded back in next morning (causing trouble again), its touch count starts from zero again.

So, by just looking into touchcount numbers you can’t always reliably detect who’s the current troublemaker. One option here would be to query the touchcounts, then wait for a minute, query the touchcounts again and see who’s touchcount increased the most during the minute. This would give a bit better picture about who is causing trouble now.

But still, it would not be always reliable for another reason – touchcounts are incremented only after 3 seconds have passed since last increment! This factor has been coded in to avoid situation such a short but crazy nested loop join hammering a single buffer hundreds of thousands of times in few seconds and then finishing. The buffer wouldn’t be hot anymore but the touchcount would be hundreds of thousands due a single SQL execution. So, unless 3 seconds (of SGA internal time) has passed since last TCH update, the touchcounts would not be increased during buffer access.

This time is controlled by SGA variable kcbatt_ by the way:

SQL> oradebug dumpvar sga kcbatt_
ub4 kcbatt_ [3C440F4, 3C440F8) = 00000003

This 3-second delay leaves us in the following situation, let say there are 2 blocks protected by a CBC child latch:

One block has been accessed once every 3 seconds for 24 hours in a row. A block accessed once per 3 seconds is definitely not a hot block, but its touchcount would be around 28800 (86400 seconds per 24 hours / 3 = 28800).

And there is another block which is accessed crazily for 2 seconds in a row and this happens every 10 seconds. 2 seconds of consecutive access would increase the touchcount by 1. If such access pattern has been going on every 10 seconds over last 24 hours, then the touch count for that buffer would be 86400 / 10 = 8640.

In the first case we can have a very cold block with TCH = 28800 and in second case a very hot block with TCH = 8640 only and this can mislead DBAs to fixing the wrong problem.

Luckily this problem can be solved with LatchProfX :-)

If you don’t know what LatchProfX is, you may want to read these articles first – LatchProf gives you a better way for latch contention troubleshooting compared to old approaches:

And now I introduce a new feature in LatchProfX.

In addition to being able to report the SID of the latch holder and the kernel function why this latch was taken by the session, the newest LatchProfX can also report the object what was protected by the latch under investigation. For cache buffers chains the object is data block address of the block being accessed.

Here’s an example, let say I have CBC latch contention problems and from monitoring tools or sql_trace I have identified the hot latch, I use my sw.sql script here, to report what a session is waiting for:

SQL> @sw 138
 SID STATE   EVENT                        SEC_IN_WAIT                 P1
------- ------- ---------------------------- ----------- ------------------
    138 WAITING latch: cache buffers chains            0 address=0x27E5A780

Sw reports that this session is waiting on cache buffer chains child latch with address 0x27E5a780.

Now we can run latchprofx and report sid, name, hold mode and protected object for that child latch only (instead of latch name we specified its address):

SQL> @latchprofx sid,name,hmode,object % 27E5A780 100000

 SID NAME                   HMODE         OBJECT   Held  Gets  Held %  Held ms
---- ---------------------- ------------ ------- ------ ----- ------- --------
 138 cache buffers chains   exclusive     40EB02   3928  3797    3.93   41.637
 151 cache buffers chains   exclusive     40EB02   3711  3660    3.71   39.337
 138 cache buffers chains   shared        40EB02    623   623     .62    6.604
 151 cache buffers chains   shared        40EB02    544   544     .54    5.766

As you see, LatchProfX has reported a data block address in the object column. This is the datablock we are trying to access in buffer cache under protection of the given child latch.

Lets see what this DBA translates to:

SQL> @dba 40EB02

 RFILE#     BLOCK#
---------- ----------
         1      60162

Press enter to find the segment using V$BH (this may take CPU time), CTRL+C to cancel:

STATE      BLOCK_CLASS        OBJECT_TYPE         object                                          TCH  MODE_HELD
---------- ------------------ ------------------- ---------------------------------------- ---------- ----------
xcur       data block         INDEX               SYS.SYS_IOT_TOP_94276                           331          1

Press enter to query what segment resides there using DBA_EXTENTS (this can be IO intensive), CTRL+C to cancel:

OWNER                          SEGMENT_NAME         PARTITION_NAME  TABLESPACE_NAME
------------------------------ -------------------- --------------- -----------------
SYS                            SYS_IOT_TOP_94276                    SYSTEM

So, the troublemaker is an IOT index segment block belonging to table with object_id 94276. I can find the table name from dba_objects by object_id, or by using my script:

SQL> @oid 94276

owner                     object_name                    object_type        CREATED
------------------------- ------------------------------ ------------------ -----------------
SYS                       KILL_CPU                       TABLE              20090825 23:19:49

Also, as we know the segment name and relative file number (RFILE#) and block number (BLOCK#)  from above output we can dump the datablock with an ALTER SYSTEM DUMP DATAFILE command.

As that command requires absolute file id (not relative) we have to do a little conversion first (in cases where there are multiple files with same relative file number in database):

SQL> select file_id from dba_extents where relative_fno = 1 and segment_name = 'SYS_IOT_TOP_94276';

 FILE_ID
----------
 1

Of course in my little test case all relative file numbers match with absolute numbers (but if you have lots of datafiles, have directly migrated from Oracle 7 or use transportable tablespaces, you may hit this issue).

Finally I can run ALTER SYSTEM DUMP DATAFILE 1 BLOCK 60162 to find out more about the contents of that block. Another option would be to construct a rowid from the data_object_id, rfile and block and query the table using that but as that’s complicated for IOTs, I’ll leave this to a future post.

You can download latest version of LatchProfX here:

This entry was posted in Cool stuff, Oracle and tagged , , , . Bookmark the permalink.

31 Responses to latch: cache buffers chains latch contention – a better way for finding the hot block

  1. Bernard Polarski says:

    In the script bhla.sql I got lru_flags of type 0,2,4,6,8. With a bit of search I managed to find than 0 means not set, 2 is cold LRU, 8 is hot MRU but could not find any explaination on values 4 and 6. In Ixora I find a mention of ‘AUX’ for 4 which seems to refer to ‘auxiliary list’ without any explanation on what are those auxiliary list. Do you have any explanation on those lru_flag code?

  2. Kyle Hailey says:

    Excellent analysis. Love the
    DUAL CONNECT BY LEVEL
    in latchprofx
    and the object id in
    select ksulawhy FROM x$ksuprlat
    that’s a great find.

    Cool stuff.
    Thanks Tanel

  3. Md. Zafar Ahsan says:

    Hi,
    Can anyone let me know how to create X$ proxy views based on x$tables as it gives ORA-01731:

    Regards
    zafar

  4. Tanel Poder says:

    For example this:

    SQL> create view my$ksuprlat as select * from sys.x$ksuprlat;

    View created.

    SQL> grant select on my$ksuprlat to dba;

    Grant succeeded.

    SQL> create public synonym my$ksuprlat for sys.x$ksuprlat;

    Synonym created.

    SQL>
    SQL> select count(*) from x$ksuprlat;

    COUNT(*)
    ———-
    0

  5. vardhan says:

    srry i posted it some unrelated article of you again submitting here
    Hey tanel

    i was just looking at x$bh table and i got the following output .

    ADDR HLADDR INDX BLSIZ NXT_HASH PRV_HASH DBARFIL DBABLK FLAG CLASS STATE TCH
    —————- —————- ———- ———- —————- —————- ———- ———- ———- ———- ———- ———-
    0000002A96F1FAA8 000000006DB3EE28 11 8192 000000006DB3F2C8 000000006D55D6B0 6 15865 0 1 1 1
    0000002A96F1FAA8 000000006DB3EE28 1 8192 000000006DB3F168 000000006D5C2E10 16 5613 524288 1 1 2
    0000002A96F1FAA8 000000006DB3EEF0 14 8192 000000006DB3F3B8 000000006D605E90 1 35170 524288 1 1 126
    0000002A96F1FAA8 000000006DB3EEF0 18 8192 000000006DB3F428 000000006D6048A0 1 35403 524288 1 1 2
    0000002A96F1FBF0 000000006DB3EE28 5 8192 000000006DB3F218 000000006DB3F218 1 16886 524288 1 1 1
    0000002A96F1FBF0 000000006DB3EE28 6 8192 000000006DB3F258 000000006DB3F258 1 30133 0 1 1 1
    0000002A96F1FBF0 000000006DB3EE28 7 8192 000000006DB3F278 000000006DB3F278 6 37555 0 1 1 1

    1. i noticed that same buffer address (addr) is handled by two latch address (hladdr)(2nd and 3rd row in the above) ,which in turns means same buffer exists in two different hash buckets how is this possible ? or am i wrong in my assumption .

    2.if i look at 1st and 2nd row same buffer address (addr) and same hladdr holding two different block’s , how can same buffer hold two different blocks at same time or is the table show older records of some time back .

    can you help me in getting a clear view of this thing

  6. Tanel Poder says:

    @vardhan
    Hi Vardhan,

    You shouldn’t look into the ADDR column of some X$ tables. Some X$ tables (fixed-array based stuff like X$KSUSE and X$KSUPR) do show the actual address of the object in ADDR column. However some X$ tables do more complex logic for retrieving the info you asked for and put it into your processes private memory and then return it as the X$ struct. Thus the ADDR column you see, is just some temporary address in your PGA, not the actual address of the buffer or buffer header.

    You can confirm this by comparing that ADDR address to pmap output of a process and you’ll see it falls into a /dev/zero mapping (private memory) as opposed to the SGA shared memory segment.

    So, you should ignore the ADDR completely in X$BH and use the X$BH.BA column to uniquely identify a buffer.

    Let me know how it works out for you!

  7. rich says:

    hi tanel,
    I have got some problem while using both your scripts sw.sql and latchprof.sql.
    my db is very hot and there are sessions which stays for very short amount of time but waits for CBC (which create CPU load on host),
    with both the scripts its very hard to capture object in question.

    I generally do this.
    get the hot p1 address.

    select p1,event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time 0
    and hladdr=’00000002CE9055F8′
    and o.obj#=bh.obj
    order by tch;

    but end up getting many objects some tables and some indexes..

    do you solution for this kind scenario’s I am more interested in index hot contention.

    I have one more question..
    many times I have seen after gathering stats on table,we started seeing CBC, and we end up putting old stats back.

    Rich.

  8. rich says:

    my above reply has been trimmed by blog site.
    sending in two parts ..

    hi tanel,
    I have got some problem while using both your scripts sw.sql and latchprof.sql.
    my db is very hot and there are sessions which stays for very short amount of time but waits for CBC (which create CPU load on host),
    with both the scripts its very hard to capture object in question.

    I generally do this.
    get the hot p1 address.

    select p1,event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time < sysdate – 1/24 and event like '%latch: cache buffers chai%' group by event,p1 order by count(*);

    but usually get many addresses which is under contention.

    eg:-
    12055730080 latch: cache buffers chai 1919
    12054543880 latch: cache buffers chai 3485
    12055500280 latch: cache buffers chai 19770

  9. rich says:

    then put hot P1 in below script
    select lpad(replace(to_char(12055500280,’XXXXXXXXX’),’ ‘,’0′),16,0) laddr from dual;

    get the laddr and add in below sql.

    select o.name, bh.dbarfil, bh.dbablk, bh.tch
    from x$bh bh, sys.obj$ o
    where tch > 0
    and hladdr=’00000002CE9055F8′
    and o.obj#=bh.obj
    order by tch;

    but end up getting many objects some tables and some indexes..
    do you solution for this kind scenario’s I am more interested in index hot contention.

    I have one more question..
    many times I have seen after gathering stats on table,we started seeing CBC, and we end up putting old stats back.

    Rich.

  10. Tanel Poder says:

    Hi Rich,

    Sounds like some plans switch to nested loops (where the driving rowsource returns lots of rows) and end up hammering some probe table’s index blocks a lot… Of course there may be other reasons, such as just increased CPU consumption which burns all CPU and the CPU starvation causes latch contention to show up as a symptom…

    Anyway, if you want real time monitoring you’d need to use V$ACTIVE_SESSION_HISTORY or V$SESSION instead of DBA_HIST_ACTIVE_SESS_HISTORY as the latter view isn’t populated real time, but flushed when ASH buffer starts to get full.

    Your query probably returns many rows also because one CBC latch may protect many buffers in the cache… As I said in my blog entry, using the touch count isn’t a completely reliable way for finding the hot block anyway.

    It is possible to write a PL/SQL script which collects the V$LATCHHOLDER / X$KSUPRLAT data into a table for further analysis. And X$KSUPRLAT data includes the hot block info (data block address). So whenever a problem happens, you can just look into the collected data from that time and see exactly which DBA and SQL_ID was responsible. I have written such things for my customers in past and they do work.

  11. rich says:

    wow,
    this is real quick reply .. :- )
    thanks for pointing out about pl/sql block ( I will try to get but don’t I will be successful or not).
    I have asked one more question what if the object is in question is index ? (normal index and local index).
    how to deal with it ?
    please shed some light on this.

    many thanks in advance ..

    rich ..

  12. Tanel Poder says:

    Regarding indexes, I think I should write an article about this… there’s no short explanation or a silver bullet that would always work… basically it’s all about either visiting the block less or spreading the data across more blocks (spread the contention)…

  13. rich says:

    I will wait for your reply on index thing, I know you will come with nice explanation as ever :)
    I was thinking the same on index try to rebuild with more pctfree so rows can spread across the diff. blocks.

    thanks Tanel ..

    Rich ..

  14. Tanel Poder says:

    The rebuild would only help you temporarily IF the contention is on index leaf blocks. If the contention is on the root block for example, then the rebuild won’t help as you’ll still have only a single root block after the rebuild too.

    Also, the PCTFREE applies only during the index rebuild/creation, when the index lives on (leaf blocks get fuller) then the PCTFREE isn’t honored there. The table level PCTFREE works differently from index level PCTFREE…

    So, as a starting point, you should find out against which block the contention is…

  15. Tanel Poder says:

    The best way to reduce contention on any “object” is to visit it less often.

    As CBC latches are used by logical IOs then here are some of the many things to look at:

    1) reduce nested loops in execution plans which revisit the same blocks (way too) many times
    2) reduce manual loops or application code which executes some SQL too frequently (and accesses the same data again). A variation of this is caching some data in the application so that the database buffers wouldn’t be visited so much.
    3) optimize the execution plan, access path (early filtering in an index) so that an access path wouldn’t need to do many logical IOs
    4) etc…

    And when these above things can’t be done easily enough, then the workaround is to spread the contention across more buffers, but this depends on which buffers the contention is happening against…

  16. rich says:

    thanks for poiting it out ..
    that’s why ppl call you genius in oracle … I will keep mentioned thing in mind.

    rich ..

  17. fabio says:

    tanel,
    first of all, thanks for all that info you disclosed to us :)

    i have an situation hitting cache buffer chains for an “update” – which happens quite frequently. the access is already done by rowid. we are able to keep up with around 2 million updates in 15 minutes. a long raw column is updated as well, no indexed columns are updated, typical size of the row is 2.5 kbytes.

    the tablespace for this table is ASSM. i have tried your technique and the hot block i get is the segment header for this object. usually we see this segment header thing with inserts only.

    there is no cpu starvation.

    would you point me to some other doc or post for further investigation? my ideas so far are to increase the pctfree to sparse the blocks and increase further the sga to obtain more chains.

    thanks,
    fabio

  18. Tanel Poder says:

    @fabio

    Thanks, few questions:

    1) Which exact db version?
    2) Which hardware platform? (is it a Sun T5xxx coolthreads server perhaps?)
    3) How many concurrent updates/sessions doing these updates?

    Are you updating column values larger, so that the row grows, doesn’t fit into the block and has to be inserted into a new block?

    Please run snapper on one of the sessions and post the output here (or send by email so I can blog about it later :) to see wee what else is happening during these updates…

  19. fabio says:

    @Tanel Poder
    I’ll get the info and will post the details.
    1) 10.2.0.4
    2) OS is solaris 10, the server model is sun fire e25k, wc -l shows 56 cpus
    3) There are between 100 and 300 sessions trying to do the update, depending on the load we try to achieve.

    This question you asked is what I am trying to understand. Neither the vendor or the application folks were able to tell how the column grows. I will do some research using logminer.

    They should have more tests scheduled soon, will send the snapper updates.

    Thanks a lot for getting back

    fabio

  20. joel garry says:

    Getting a 404 for bhla.sql link.

  21. aliyar says:

    very useful informations tanel .. thanks a lot ..

    but getting ” page could not be opened” for all the script links ..

  22. The links to your scripts are broken – I see only a 404 error page. :(

  23. Tanel Poder says:

    @David Fitzjarrell

    Check my blog front page (or the link above) you can download the scripts from there

  24. charles says:

    Hi,
    sorry to bug you, why couldn’t I download your scripts? each time, when I clicked on your links, I got not found.
    Since your scripts are fantastic, I really wish I could take advantage of them. Thanks and wish you have a great day!

  25. Hi, I’m trying to download the script bhla , but the link is broken.
    http://www.tanelpoder.com/files/scripts/bhla.sql
    can you say me were can i find it.
    tnxs

  26. Carlos Alvarez says:

    tanel,
    first of all, thanks for all that info you disclosed to us .

    We still work on ORACLE 10.2.0.3 and have CBC frecuentely. We’d like know if with upgrade at 11.2.0.3 we can give solution at our problem. I don’t find information in metalink about bugs fixed on version 11.2.0.3.

    Thank you so much.

    • Tanel Poder says:

      You’d need to troubleshoot why do you have CBC latch contention, e.g. does some execution plan the same (index) blocks too often etc. And this can be done by measuring which blocks hit the most contention and why (latchprofx-like approach etc).

  27. Xinhuan Zheng says:

    Hi Tanel,

    Can you please provide a test case to produce “Latch: Cache Buffer Chains” wait event?

    Thanks,
    - xinhuan

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>