Chowrangi

Myths are often associated with the problems, which are hard to understand and even harder to solve. One of the most esoteric problems in the realm of performance tuning of Oracle is SQL tuning. After much ado, everyone seems to agree upon the fact that 90% tuning problems can be rectified by tuning the SQL. Most also tend to agree that tuning SQL shouldnt be an afterthought. SQL Tuning begins with the first select keyword to the last semicolon. But for some weird mythical reasons most of us seem unable to do it right the first time. Hence we have to resort to reactive SQL Tuning.

In a given ailing application, its rather easy to identify the badly-performing SQL, its also easy to get execution plan of the culprit SQL. Execution plan is the way which a query follows to get data. The choice of execution plan is directly proportional to the performance of query. The more optimal the plan, the more the query performs well. For any given query there could be literally billions of different execution plan, the real caveat is the finding of best optimal plan. To find best optimal plan from billions of plans of the same query is real nasty.

But there is a place in the virtual world, called as ‘www.singingsql.com’ and a book in the physical world called as ‘SQL Tuning’ which doesn’t think its nasty to find the best optimal execution plan. Dan Tow is the man who is behind these two oracles for SQL tuning. He has remained with Oracle corporation for a considerable time dealing with issues related to SQL tuning, and nowadays provides consultancy in the same area. He also publishes an occasional newsletter describing gems of his research. In his outstanding book ‘SQL Tuning’ published by O’reilly, he tells very lucidly how to find the best optimal in a very logical way. His method comprises of delineating a query in tree-like graphical diagram, which step-by-step takes us to the best plan possible for that query, without falling in any painful rigmarole. Though I use his method of SQL tuning in an Oracle environment, his method is database-independent. You can use it for any SQL written for any database. His book gives the examples relating to Oracle, SQLSERVER and DB2.

Dan’s Diagrams has made my SQLs’ sing more than often. I fell in love with it, when it reduces my montly pay process from 4 hours to 12 minutes. Unbelievable, isnt it. But thats how it works. For me its easy, sensible, logical and most importantly workable.

Other posts by Kashif Aziz

• Taliban of Punjab Police - March 3rd, 2010
• Chile Earthquake, Hawaii Tsunami and Social Media - February 28th, 2010
• President Zardari apologized for childbirth in rickshaw - February 26th, 2010
• Reason of Traffic Jams in Karachi - February 19th, 2010
• Mullah Abdul Ghani Baradar, Top Most Taliban Leader Caught in Karachi - February 16th, 2010
• PTCL Launches Largest Data Center of Pakistan - February 15th, 2010
• Reflections On Twin Blasts in Karachi - February 6th, 2010
• Aafia Siddiqui convicted, may be sentenced for 60 years - February 4th, 2010
• Dawn News going down the Drain? - January 30th, 2010
• Has Karachi Gone To Dogs? - January 27th, 2010