Advanced Query Optimization Question

Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.  I’m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.  I was also able to get some good insight into other types of Oracle environments and felt the conference was an overall success.

The conference ended with a closing session on the topic of Oracle Exadata Storage Server presented by Oracle Director of Product Management, Charles Garry.  While the slide deck was quite marketing-heavy, Charles did a good job entertaining everyone and answering questions.

After the session, I had a few discussions with people regarding the Exadata architecture and the method by which Oracle Database is able to distribute nodes of a query execution plan directly to the storage server for local processing.  For people who haven’t worked on query optimizers and executors, it seems that this is an area of technical confusion.  Regardless, Charles and I both rode the shuttle back to the airport, which gave me a chance to talk with him about Oracle from both a company and technology perspective.

Also, now that I’m home and have completed my second SEOUC presentation, The Life of a Query, I wanted to follow-up on my experience and ask you a question.

First, the description of The Life of a Query is as follows:

Have you ever wondered what happens when you execute a query?  In this session, we’ll take a walk through the Life of an Oracle Query from beginning to end.  Not only does this help you better understand the various steps in the execution of your query, but it will also give you a good appreciation for the architecture of the Oracle Database server and, in particular, of the query optimizer.  You will gain valuable information that will allow you to solve performance issues and write more efficient queries going forward.  Additionally, I will share some of my insights into the Oracle optimizer as we move through this discussion.  This discussion is also a great refresher for those of you familiar with Oracle concepts and architecture.

In short, this session takes a query and walks through all major components of the Oracle database starting from the client and going through connection, the entire Oracle Kernel stack, and back to the client.  After the session, I received quite a few positive comments from attendees.  Moreover, I found that people are extremely interested in how query optimization works.

While I covered the basics of parsing, query rewrite, and optimization, it seemed that including view/subquery merging, basic algebraic optimization, join permutation, join elimination, and partition elimination wasn’t enough.  Surprisingly, someone even asked to go into more depth on the mathematics behind query optimization; specifically the application of graph theory to plans and the way costing is performed using a graph.  This brings me to my question:

Would anyone be interested in a fairly advanced presentation/article on query optimization?

For comparison purposes, my meaning of fairly advanced is between the level of Jonathan Lewis’ Cost-Based Oracle Fundamentals and not-quite pure math.  If I decide to do it, I’m thinking of presenting it similar to Craig Shallahamer’s based-on-math-yet-practically-applied Forecasting Oracle Performance, describing the math and theory but visually demonstrating how it applies to actual queries.  Thoughts?

Currently At SEOUC

While I had almost missed my flight yesterday, I am now in Charlotte North Carolina attending and presenting at the SouthEastern Oracle Users Conference. My first session, Benchmarking: Tools, Methodologies, and Techniques, was earlier today and I think it went OK. I’ve also attended a couple sessions as well.

Specifically, I was impressed with the keynote from Rich Niemiec (the President of TUSC) this morning. While it was basically a history of Oracle, containing some of the details shared by Larry Ellison himself during the 2007 OOW keynote, Rich was able to add a bit more insight into things due to his past interviews and discussions with old, key Oracle employees such as Bruce Scott. Also of note was a presentation from Karen Morton (Method R Corporation–Think Hotsos), which covered the basics of how statistics affect the Oracle CBO and how to manage them. I found her demonstration, using playing cards, an excellent way to visualize basic query optimization calculations for things such as selectivity; I believe others did as well.

Also attending the conference are the guys from Bizwhazee, with their awesome PL/SQL+Web+Ajax product, TURBOEnterprise. If you want to try TURBOEnterprise, you can download it free for development from their web site. Or, if you just want to check out some of the cool Web 2.0 stuff you can develop in pure PL/SQL, you can play with it online.

Tanel Poder Training in New York

I’m pleased to announce that good friend and fellow Oracle expert, Tanel Poder, will be giving a 2-day special training for NYOUG members on February 3rd and 4th, 2009.  His topic, Advanced Oracle Troubleshooting for DBAs and Performance Engineers, provides Oracle DBAs with an excellent, end-to-end approach for troubleshooting and solving Oracle problems.

Tanel’s extensive experience researching and administering Oracle gives him a unique perspective from which he is able to provide DBAs with a thorough understanding of Oracle as it interacts with the operating system.  Tanel’s approach and examples illustrate how one can easily identify, diagnose, and decipher database issues using utilities and information not available from within Oracle itself.

I wholeheartedly endorse this session and believe that anyone who wants to take their Oracle knowledge to the next level will greatly benefit from it.