In these pre-OOW 2013 days, everyone is excited about new features of Oracle 12c. This post describes particularly interesting one – IO outliers profiling in V$KERNEL_IO_OUTLIER.
According to Oracle Documentation and Glenn Fawcett blog we know that V$KERNEL_IO_OUTLIER uses the DTrace to collect information about the amount of time an IO spends in operating system components. DTrace is the Solaris dynamic tracing tool which allows to observe all OS activity.
V$KERNEL_IO_OUTLIER should provide an operating system-based latency breakdown of IO taking an excessive amount of time to complete. This will be essential diagnostics feature. But, many people complained that V$KERNEL_IO_OUTLIER is always empty. This was enough for me to start investigating it.
I will discuss the view, when and how it works, the underlying DTrace script and how it can be used for pre-12c Oracle.
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!
Several months have passed since my previous “mutex wait” post. I was so busy with work and conference presentations. Thanks to all my listeners at UKOUG2011, Hotsos2012 and Medias2012 conferences and several seminars for inspiring questions and conversations.
I. Unexpected change.
Now it is time to discuss how contemporary Oracle waits for mutexes. My previous posts described evolution of “invisible and aggressive” 10.2-11.1 mutex waits into fully accounted and less aggressive 11gR2 mutexes. Surprisingly Oracle 188.8.131.52.2 (or 184.108.40.206 PSU2) appeared in April 2011 demonstrated almost negligible CPU consumption during mutex waits. (more…)
A week ago I was back home from MEDIAS-2012 conference. It was held in Limassol (Cyprus), near the spectacular ruins of ancient city Amathus. This was unique style general Computer Science conference with speakers including legendary Soviet cosmonaut Alexandr Serebrov and the inventor of Mean Value Analysis Professor Martin Reiser.
In my experience the Reiser’s Law stating that “Software is getting slower more rapidly than hardware becomes faster” has been repeatedly illustrated by numerous performance problems I observe.
RDTeX presentations covered Oracle topics ranged from Data Warehousing by Mikhail Kozyr to Oracle Coherence by Alexei Zolotarev.
This conference gave me unique opportunity to discuss mathematics related to Oracle mutexes. If you are interested, you can download my presentation here.
And, of course, Cyprus is a great place to visit!
Thanks to Professor S.V. Klimenko for kindly inviting me to MEDIAS 2012 conference.
Thanks to RDTEX for financial support.
Thanks to RDTEX Technical Support Centre Director S.P. Misiura for years of encouragement and support of my investigations.
This post does not discuss mutexes or latches. I wrote it under the impression of recent escalation due to database corruption.
This was Oracle 10.2.0.4 database on HP-UX. Database files underwent recurrent corruption. Most commonly blocks were corrupted in system and undo tablespaces. This resulted in corrupted dictionary tables and indexes. Neither db_block_checking/db_block_checksum nor installation of latest PSU and OS patches helped. SCNs showed that these dictionary blocks were not changed for years. It looks likes some program other than DBWR overwrites the blocks. Corruptions caused downtimes to restore and recover datafiles.
Week ago I was back home from the 10th Hotsos Symposium, the best performance dedicated Oracle conference in the world.
This was rare opportunity to see extraordinary presentations and discuss advanced Oracle topics. Inspired by Jonathan Lewis new book Oracle Core: Essential Internals for DBAs and Developers book I was happy to attend his Training Day.
I was lucky to speak at this jubilee Symposium about mutex internals and mutex contention.
Many thanks to the Hotsos team for this great conference.
To compare old and new latch mechanisms, I found useful the following illustration. Since it is hard for us, humans, to visualize milli- and microseconds, imagine “time microscope” that zooms in timed events one million times.
Alternatively, just imagine contemporary Oracle software running on 1950th style hardware.
Such microscope will magnify the microsecond to second. One real “second” will transform to one million seconds. It takes more than 11 days. Light will travel at sonic speed. Lunar rocket will crawl like snail.
I will be speaking about latches at UK Oracle User Group Conference 2011. It will be my first visit to this extraordinary conference and UK. Hopefully see you there!
I would like to describe how Oracle versions 220.127.116.11-18.104.22.168.1 waited for mutexes. This algorithm also appears to be used in post-22.214.171.124.2 PSUs and new 126.96.36.199 patchset as _mutex_wait_scheme=0.
My previous post demonstrated that before version 11.2:
- “Cursor: pin S” was pure wait for CPU. Long “cursor: pin S” waits indicated CPU starvation.
- Mutex contention was almost invisible to Oracle Wait Interface
- Spin time to acquire mutex was accounted as CPU time. It was service time, not waiting time.
Things changed. Mutex waits in Oracle 11.2 significantly differ from previous versions. Contemporary mutex waits are not CPU aggressive anymore, completely visible to Oracle Wait Interface and highly tunable.
Oracle KGX mutexes appeared more than 7 years ago. However, mutex waits are still obscure. Oracle Documentation provided only brief description of mutex wait events without any information about wait durations and timeouts.
Look at the following timeline: