Wednesday 22 September 2010

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).

No comments:

Post a Comment