Advances in SQL Tuning Techniques at HOTSOS

Late breaking news - I'll be speaking at the 2010 HOTSOS conference!

http://www.hotsos.com/sym10/sym_speakers_hailey.html

Advances in SQL Tuning Techniques

In the past few years some novel SQL tuning techniques have appeared such Oracle 10g’s SQL profiles, 11g's SQL baselines , extended execution statistics, Tuning by Cardinality Feedback, Visual SQL Tuning, Oracle 11g’s real time SQL monitoring and plan stability. Find out what about these methods and how to best harness them easily and effectively on your system.

Learning objectives:

1) How to identify queries that are potentially suboptimal

2) How to analyze the query

3) What steps to take to come up with an optimal and stable plan

HOTOS is my favorite Oracle conference both for the high caliber of the speakers as well as the participants. Here is the impressive speaker list for 2010:

http://www.hotsos.com/sym10/sym_speakers.html

I'll also be teaching a 2 day Oracle performance and tuning seminar on the following dates and locations:

Melbourne Feb 22-23

Sydney Feb 25-26

Singapore Mar 1-2

Hong Kong Mar 4-5

Oracle Users Group - NoCOUG Fri 13th

Had an enjoyable day at the NoCOUG held at Oracle Corp, my old stomping grounds.

It was fun to see and catch up with Greg Rahn, John Beresniewicz, Graham Wood, Juan Loaiza, Maria Colgan and Tanel Poder. Tanel's presentation that I saw was the highlight for me. With in the first couple of minutes he showed some super timesaving devices for SQLPLUS in windows that I'd wanted for ages. For example, there seems to be no way to change the title of sqlplusw.exe but but by using sqlplus.exe in a cmd window you can with the simple dos "title" command. My first reaction was "I can't use a cmd window because cut and paste is wanky" then Tanel pointed out that classic cut and paste can be enabled via the cmd widow properties. He also showed the sqlplus command "set markup html on" which outputs sql query results in HTML - so what? well by spooling to a file with ".html" extention and using the dos command "start output.html" via sqlplus' host, we then get a browser with the data in an HTML table which is really nice when the data is wide. Same thing works with output to ".xls" - we get HTML data open directly in Excel. Very cool and convenient.

Tanel went over his famous snapper and finished with is latchprofX script for analyzing latch issues. The latchprofX script is a beautiful script and I'vce blogged at it before. You read more ot it at
Tanel Poder's Blog

I present my paper on SQL Tuning which is available in my previous post. One part of my paper that I was excited to present was Visual SQL Tuning (VST). Visualization and graphics are a passion of my (as anyone who knows me knows) . Graphics, when used wisely, can convey information more powerfully and easily than the equivalent text. The text of SQL plans and SQL traces have always been a chore for me to read and that's why I'm so excited about VST. VST turns SQL tuning into a fun activity not to mention speeding up my analysis time and improving the depth of my analysis. Since analyzing SQL for performance issues is one of the foundations of DBA and databse development, these new developments in VST is an important new development in database performance optimization.

Tanel, Kamran, me and Greg Rahn at NoCoug