Latch, mutex and beyond

December 10, 2012

RuOUG2012

Filed under: Conference,Latch — andreynikolaev @ 8:34 pm

Last Thursday I spoke about “Latch Internals” at RuOUG-2012 conference in Moscow.

Large timeslot allowed me discuss new topics. If you are interesting, you can download the presentation and supplementary demo scripts.
The scripts are in .zip archive, rename it after downloading.

Many thanks to the RuOUG team!

About these ads

5 Comments »

  1. Thank You .

    Comment by goryszewskig — December 11, 2012 @ 8:40 am | Reply

  2. Hi Andrey,
    Not sure whether it’s a right place to ask you something about latch cache buffer chain contention on an index root block.Since I don’t have your email ID,I am placing my question in the comment section.In my production system(oracle 10.2.0.4) users are complaining slowness when they try to retrive few rows from a tiny table(133 rows in 5 blocks) using “Unique Index Scan”.The index has only 1 block(root,leaf,branches all in one).I could see a severe latch:cbc contention on two SQLs that are executed at very higher rate(250/sec) from multiple users.
    NAME HMODE FUNC OBJECT Held Gets Held % Held ms Avg hold ms
    ————- ——————— ———— ——————————— ———- ———- ——- ———– ———–
    f7srmb2gqa245 cache buffers chains exclusive kcbgtcr: kslbegin excl 400011C 105103 101444 10.51 10057.306 .099
    f7srmb2gqa245 cache buffers chains exclusive kcbrls: kslbegin 400011C 69188 66923 6.92 6620.600 .099
    5cmrd9mq8jnr8 cache buffers chains exclusive kcbgtcr: kslbegin excl 400011C 5773 5503 .58 552.418 .100
    5cmrd9mq8jnr8 cache buffers chains exclusive kcbrls: kslbegin 400011C 3438 3260 .34 328.982 .101
    f7srmb2gqa245 cache buffers chains shared kcbgtcr: kslbegin excl 400011C 1227 1227 .12 117.412 .096
    f7srmb2gqa245 cache buffers chains shared kcbrls: kslbegin 400011C 929 929 .09 88.896 .096

    why would a select statement needs an execlusive latch access in this case?

    Thanks
    Tony

    Comment by Tonydba — January 17, 2013 @ 3:51 pm | Reply

    • Hello!
      This is interesting latch contention case.
      Can you additionally describe:
      1. Exact Oracle version and patches..
      2. Problematic “two SQLs” and their execution plans
      3. Run my script
      @latch_stats_10g.sql
      and send me the results

      You can contact me at email Andrey.Nikolaev@rdtex.ru
      Best Regards
      Andrey

      Comment by andreynikolaev — January 18, 2013 @ 8:41 pm | Reply

  3. i can’t download you script ,can you descript you url, thank you

    Comment by ian — September 4, 2013 @ 2:56 am | Reply

    • WordPress does not allow files with .zip extension.
      Therefore, you can “Save as” this file as .zip and unarchive.

      Comment by andreynikolaev — September 28, 2013 @ 6:30 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 67 other followers

%d bloggers like this: