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!
Thank You .
Comment by goryszewskig — December 11, 2012 @ 8:40 am
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?
Comment by Tonydba — January 17, 2013 @ 3:51 pm
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
and send me the results
You can contact me at email Andrey.Nikolaev@rdtex.ru
Comment by andreynikolaev — January 18, 2013 @ 8:41 pm
RSS feed for comments on this post. TrackBack URI
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account. ( Log Out / Change )
You are commenting using your Twitter account. ( Log Out / Change )
You are commenting using your Facebook account. ( Log Out / Change )
Connecting to %s
Notify me of follow-up comments via email.
Theme: Rubric. Blog at WordPress.com.
Get every new post delivered to your Inbox.
Join 49 other followers