Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group – Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based Optimizer, the statistics that feed it, the hints that influence it, and the key tools you need to examine execution plans.
The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the five topics.
Part 1 Understanding the Oracle Optimizer
The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.
Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. Most of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations, including:
- Subquery Unnesting
- Or Expansion
- Table Elimination
- Join Predicate Push down
- Table Expansion
- Star Transformation
- View Merging
You can also download the slides here.
Part 2 Best Practices for Managing Optimizer Statistics
Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”
