Hotsos 2006: Day 2

Okay. Day 2 of the 2006 Hotsos Symposium is in the books. It proved to be yet another really good day of learning and sharing of technology. One of my favorite aspects about the symposium format is the ability to get so many varied opinions on such a wide variety of technical topics by simply strolling the floor. You might have a fleeting conversation with someone you have never met and learn something new. That is cool!

Anyway, as for the proceedings I started off in Bob Sneed's "I/O Micro-benchmarking with Oracle in Mind" presentation. He made a few really good points pertaining to common micro-benchmarking errors:

1. We often run irrelevant tests.
2. Failure to pay attention to initial pre-test state influences can sabotage your benchmark (Beware of the warm cache).
3. Failure to repeat tests to ensure repeatability of results.
4. Don't leap to conclusions.

He also mentioned a couple of open source products that can be used for micro-benchmarking: filebench and iozone.

One last comment - which requires further investigation. He said that anyone owning the book Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning should rip out page 134. I will wait until I get back to the office before I read my colleague Michael Eubanks' copy and rip said page out :-)

Then it was on to Doug Burns' "Tuning and Tracing Parallel Execution" presentation. First, off this guy can flat out present (and with terrific humor). I thought the presentation was very good. Some of the notable points were the following.

Why do so few sites implement the Parallel Query option? Well, I didn't know that so few sites were using it, but I will certainly take his word. He mentioned a few reasons:

1. Lack of understanding of the nuisances of PQ.
2. Deterred by horrible early experiences (i.e. cpu starvation, unpredictable results).
3. Community's resistance to change.
4. The very fact that it is not useful in all environments.

He advocated, with a slight nudge from Tom Kyte, the use of the parallel_adaptive_multi_user parameter to adapt the PQ slave use to the current workload on the system. In this manner, we can theoretically take full advantage of Parallel Query while reducing the risk of CPU starvation. If you know me, you know that I love a diagram that can say in one slide what might take 20 bullet items to "spell out". Doug had one particular slide that showed the interaction between parallel query slave processes and the parallel query coordinator processes during a sort operation. It revealed how parallel query slaves can assume the role of both a consumer and a producer at different points within the execution of a single statement. That added a new wrinkle to my gray matter.

He also described some of the challenges of tracing PQ processes in pre-10g Oracle installations, i.e. one trace file per slave and coordinator. However, in 10g this is remedied via the use of trcsess as it generates a consolidated trace file for the query coordinator and the slave processes. I also thought his review of the parallel query wait events and related dynamic performance views topped off a very good presentation. Great job Doug! I look forward to your presentation on Day 3.

I thought Tanel Poder did a really good job in presenting his "End-to-end Performance Diagnosis in Oracle" presentation with relevance and clarity. To summarize his presentation, the highest probability in diagnosing performance issues is usually a correlative of the instrumentation at each layer. The less instrumentation that exists at each "layer" of the application the more time it will take to complete the diagnostic tasks at hand. The number of cycles of "why" -> "where" -> "why" -> "where" -> .... -> "root cause" will typically be greater as the instrumentation at each layer diminishes. The converse is likewise true. Of course, the cost of instrumentation to performance must be weighed against any apparent overhead said instrumentation incurs.

I rounded off the day with back-to-back presentations from Jonathan Lewis and Mogens Norgaard. Jonathan spoke on the "V$ and X$" performance views. With erudition that ostensibly only Jonathan can provide, the V$ and X$ views were exposed for what they truly are, views which can be immensely useful and recreated in a much for database-friendly manner. Unfortunately, this talk only lasted an hour. However, I am privileged to be able to attend the training day with Jonathan on Thursday. So, I am sure any gaps not fully appreciated in this one-hour presentation will be realized in this training session. Jonathan's understanding of Oracle internals is truly amazing. It is obvious he has invested much energy to decode some of the more mysterious aspects of Oracle, such as the v$ and x$ views; in an attempt to deliver to the average DBA quick access paths to otherwise potentially prohibiting queries against these prized structures. Just when you think you know a thing or two about Oracle internals, a Jonathan Lewis presentation quickly reminds you of how little you really know. As Socrates said, "True knowledge exists in knowing that you know nothing." When Jonathan Lewis or Tom Kyte present I feel my seat getting larger and larger beneath me.

Mogens delivered a fantastically witty and delightful presentation to round off the day. I set down my pen and closed my notebook to simply enjoy the offering. I have found Mogens to be one of, if not, the best person to deliver a high level technical discussion seasoned with relevant and primarily non-gratuitous humor. If the majority of the world understood databases he could easily give a stand-up routine each and every night at any venue in the world. He is that good.