|
"Writing Optimal SQL"
Friday,September 3rd 2010 and Saturday, September 4th 2010, By Jonathan Lewis |
 |
This tutorial has two targets. First: how do you improve the performance of a production system by attacking inefficient SQL; secondly: how do you design a system so that you can get data into and out of it efficiently. For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly. As you struggle to address a particularly inefficient piece of SQL you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself. The course will focus on the SQL – falling back to structural issues as an aid to improving the mechanical efficiency of the SQL solution – and will spend some time explaining the pros and cons of various structural options as they become relevant to the SQL. The course will cover methods of reviewing data distribution patterns, use of indexes, use of views (stored and inline), analytic functions, subquery factoring, as well as a brief review on hints and execution plans. |  |
 |
Jonathan Lewis is an independent consultant based in the UK, with a client list stretching across America, Europe and Asia down to Australia. His expertise covers design, strategy, and trouble-shooting, and he is often available at very short notice to handle issues that need a short, intense, period of high-level skill.
Jonathan has more than 20+ years of experience using the Oracle database, and spends a significant fraction of his time passing on the information he has acquired over those years. Apart from the books "Practical Oracle 8i - Building Efficient Databases" published by Addison-Wesley; and Cost Based Oracle - Fundamentals , published by Apress, Jonathan has contributed chapters to 'Oracle Insights - Tales of the Oak Table (Apress)' and 'Expert Oracle Practices - Oracle database administration from the Oak Table (Apress)', supplied commentary for 'Oracle 10g New Features (Oracle Press) by Rob Freeman, and has also published numerous articles on-line and in computing magazines. He blogs at http://jonathanlewis.wordpress.com. His other credentials and industry recognitions include: |
 |
Member of the Oak Table Network | |
|
Oracle ACE Director | |
|
Oracle Author of the year 2006 | |
|
"Select" Editor's choice award 2007 | |
|
Friday, 3rd September 2010 9:30 AM to 12:30 PM |
|
|
Session 1 |
Background. In which we examine the need for a global view point and a general strategy for minimising work while recognizing that there will always be a conflict between local and global optimization. A quick review of use of indexes, including some of the less common uses and common errors in index design. Closing with the need to be able to investigate and understand the data and business requirements. |
|
Session 2 |
Basic Practices How to approach the task of translating business requirements into SQL. The need for clarity in code, with a suggested set of standards for presentation to improve ease of understanding. An introduction to the essential strategy for writing SQL that gets its result by doing the minimum work, and a model for investigating and re-engineering badly performing statements. |
|
Saturday, 4th September 2010 8:30 AM to 12:30 PM |
|
|
Session 3 |
Sundry Methods A brief discussion of the mechanics (and costing) of nested loop and hash joins, and why it’s so easy it is for the optimizer to pick the wrong option. A few examples demonstrating classic problems, and solutions that take advantage of some of the slightly exotic options of SQL and Oracle’s available features. And a few closing comments about the need for the front-end code to co-operate with the database engine in certain circumstances. |
|
Session 4 |
More Methods Fixing the code without touching the code – the latest options, including a little time looking at the ways in which you can use OEM to assist with tuning. Problems and options with knowing internal mechanisms and taking advantage of them. A look at a couple of popular requirements, and strategies for addressing them as efficiently as possible. And a final look at the threats of hints and how to use them properly. |
|
Sponsors: |
| ||