Yesterday was a very busy day at UKOUG 2012: many technical sessions about performances and also a time travel back in the evening. I really like sessions about performances (Oracle Optimizer) and Oracle behinds, but I will not focus on the technical side because it will take too much time (maybe in future blogs!). I will just summarize a little the main interesting topics.

I had the opportunity to attend several sessions during this day keeping the theme of the execution of SQL statements: how works the optimizer, how can we influence the decisions of the CBO (Cost-Based Optimizer), how can we check the execution, etc...

SQL Monitor

It's a feature embedded in Enterprise Manager but you can also run it from SQL Plus. The generated reports focuses on one SQL execution but give us all details on where the database spent his time to give the results.
You can see each part of an execution plan and its corresponding CPU or I/O consumption. It also displays all plan statistics you can find with dbms_xplan to quickly see of the plan seems to be the wrong one.

And I can add that the HTML version is pretty cool, some components are dynamic and self-contained in the page itself.

Optimizer hints

Maria Colgan explained us that hints are some additional information we can give to the optimizer to help plan calculation. That's why hints seem to be ignored sometimes but in fact it's because the information was not enough or not useful in that particular case.

If you want to use hints, the event trace 10053 will be you friend to know if the hints are been recognized and used by Oracle.

Controlling the execution plans

Oracle provides us several ways of controlling the execution plans without modifying the code:

• Outlines
• SQL Profiles
• SQL Patches
• SQL Baselines

They are a lot of cases where we need to fix an issue quickly without having the time to perform a deep analysis. Therefore these methods should be considered as exception and most of time as a solution to give time to resolve the root cause.

Back in the future

This year event theme was 1950's Las Vegas.

Very nice theme, many people at the tables but I enjoyed playing with the provided chips. 
It was also the moment where the speakers are available to discuss not only about the technics.

See you for the last conference day.