Wednesday, 22 September 2010

Stanley at OOW 2010

Yesterday, on Tuesday September 21st, I met with Debra Lilley, Robyn Sands, Mogens Norgard and Dan Norris at the Chevys. When we exited I took some funny pictures of Dan, Debra and Mogens and, of course, Stanley-Oracle ACE Director. And I'm pretty sure everybody knows Stanley. If not, go to his home page and learn more about him.

My Oracle Open World 2010 Presentation - "Query Transformations"

This year I have a presentation titled "Query Transformations" in Moscone South, Room 300 on Thursday, September 23 at 12:00. In last years I had done a similar presentation several times at different conferences. However, this presentation contains new transformations which came in Oracle 11gR2. The list of query transformations I will talk about is the following:

–JPPD - join predicate push-down
–FPD - filter push-down
–PM - predicate move-around
–CVM - complex view merging
–SPJ - select-project-join
–SJC - set join conversion
–SU - subquery unnesting
–OBYE - order by elimination
–CNT - count(col) to count(*) transformation
–JE - Join Elimination
–JF - join factorization

Especially the last one added in 11gR2 is quite interesting and has the background in the patent registration application which could be found here. Another very interesting source of information is the paper presented by several Oracle employees at VLDB'09 which is available here. During the whole presentation I am showing excerpts from CBO trace file in order that the audience can learn how CBO reports about performed transformation. Some useful pieces about transformations are also available in Optimizer Development Team blog.

According to number of enrolled attendees it looks like the room will be almost full. Yesterday I had a discussion with Mohamed Zait who is in the Optimizer development team and he was surprised that the topic about query transformations is so interesting for OOW attendees. My personal opinion shared also by many others is that the content of too many presentations at OOW contains just a "helicopter" view of what is available but doesn't show or explain anything what is going on "behind the scene". However, with great pleasure I must admit that I was able to find some very good presentation during last 3 days what just confirms the fact that the speakers feel the need to go beyond the "usual" level of expertize and I am very thankful for that. One of such presentation was prepared by Maria Colgan titled "Explaining the Explain Plan: Interpreting Execution Plans for SQL Statements" where I haven't heard some new stuff but liked very much the way she presented the topic and the contents of the presentation. One of the very important messages she gave was that people should not change database parameters unless they really know what they are doing. She especially mentioned famous OPTIMIZER_INDEX_COST_ADJ which should be left at it's default value of 100. I was missing at least some words about system statistics. The other one I found also very interesting was "Oracle Database Performance Secrets Finally Revealed" presented by Greg Rahn and Michael Hallas.

My presentation is available for downloading at my home site. You have to login in (or register first if you have no login yet).

Friday, 17 September 2010

Michigan OakTable Symposium - Day 2

Today I had my presentation in the first slot in the morning and I was pleasantly surprised as the room was full. Obviously the "execution plan stability" is one of the biggest concerns of every DBA. After the presentation Jonathan Lewis told me that he made 4 pages of notes while attending the presentation and now he has a lot of new stuff to investigate.
Next session I attended was Cristian Antognini's presentation about obtaining the execution plans which was part of two slot presentation about execution plans. As usually, Cristian had a very good presentation.

The next session was Jeremiah Wilton's battle between SQL server and Oracle, both running in Amazon cloud environment. Again, a very nice presentation.

Before the lunch we made a group picture of all Oakies gathered at the event. Unfortunately some of them are missing on the picture.

Thursday, 16 September 2010

Michigan Oak Table Symposium 2010 (MOTS) in Ann Arbor, MI, USA

This morning we started with probably the biggest gathering of OakTable members ever. We gathered in Ann Arbor in Michigan for a two day event just before the Oracle Open World in San Francisco. The seminar is running in 3 parallel sessions.
Cary Millsap, one of the founders of OakTable, told a short story how OakTable was founded and then he introduced Mogens Norgard - "Moans The Magnificent" who was answering to the secret questions even before he knew for them. Believe, we had a lot of fun with the answers and questions as well. On the picture you can see Mogens answering the questions and wearing the magic hat.
As Doug Burns couldn't make to this event I offered to have another presentation which I had already in the past and was proposed by Carol - "Optimizing Access Paths".

Tomorrow I'll be talking about "SQL Plan Baselines, Adaptive Cursor Sharing , Automatic Cardinality Feedback SQL Tuning in Oracle 11gR2".