A poorly performing database application not only costs users time, but also has an impact on other applications running on the same computer or the same network. SQL Tuning provides an essential next step for SQL developers and database administrators who want to extend their SQL tuning expertise and get the most from their database applications.
There are two basic issues to focus on when tuning SQL: how to find and interpret the execution plan of an SQL statement and how to change SQL to get a specific alternate execution plan. SQL Tuning provides answers to these questions and addresses a third issue that's even more important: how to find the optimal execution plan for the query to use.
Author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database platform being used. You'll learn how to understand and control SQL execution plans and how to diagram SQL queries to deduce the best execution plan for a query. Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning concludes by addressing special concerns and unique solutions to "unsolvable problems."
Whether you are a programmer who develops SQL-based applications or a database administrator or other who troubleshoots poorly tuned applications, SQL Tuning will arm you with a reliable and deterministic method for tuning your SQL queries to gain optimal performance.
Chapter 1 Introduction
Why Tune SQL?
Who Should Tune SQL?
How This Book Can Help
Chapter 2 Data-Access Basics
Caching in the Database
Uncommon Database Objects
Single-Table Access Paths
Chapter 3 Viewing and Interpreting Execution Plans
Reading Oracle Execution Plans
Reading DB2 Execution Plans
Reading SQL Server Execution Plans
Chapter 4 Controlling Execution Plans
Universal Techniques for Controlling Plans
Controlling Plans on Oracle
Controlling Plans on DB2
Controlling Plans on SQL Server
Chapter 5 Diagramming Simple SQL Queries
Why a New Method?
Full Query Diagrams
Interpreting Query Diagrams
Simplified Query Diagrams
Exercises (See Section A.1 for the solution to each exercise.)
Chapter 6 Deducing the Best Execution Plan
Robust Execution Plans
Standard Heuristic Join Order
A Special Case
A Complex Example
Special Rules for Special Cases
Exercise (See Section A.2 for the solution to the exercise.)
Chapter 7 Diagramming and Tuning Complex SQL Queries
Abnormal Join Diagrams
Queries with Subqueries
Queries with Views
Queries with Set Operations
Exercise (See Section A.3 for the solution to the exercise.)
Chapter 8 Why the Diagramming Method Works
The Case for Nested Loops
Choosing the Driving Table
Choosing the Next Table to Join
Chapter 9 Special Cases
Merged Join and Filter Indexes
Chapter 10 Outside-the-Box Solutions to Seemingly Unsolvable Problems
Dan Tow is an independent consultant, operating under the banner SingingSQL (www.singingsql.com). His experience solving Oracle-related performance problems goes all the way back to his 1989 hire by Oracle Corporation. During most of his tenure at Oracle, Dan focused on the performance of Oracle Applications, managing the performance group for that division. In this role, he found a fertile testing ground for his SQL tuning method applied to the huge set of complex SQL included in those applications, including both online SQL and diverse batch processes. In 1998, Dan left Oracle to lead performance for TenFold Corporation, where he applied the same methods to tuning questions on DB2, and SQL Server, and Sybase, as well as on Oracle. In 2002, Dan started his own business, SingingSQL, through which he offers diverse database-related tuning services, including SQL tuning and systematically analyzing load to learn which SQL should be tuned. He has introduced his SQL tuning method to over 1,000 people in short lectures, and now offers in-depth courses in the material, using this book as textbook. Dan has a Ph.D. in chemical engineering from the University of Wisconsin at Madison. He lives in Palo Alto, California, and can be reached at firstname.lastname@example.org.
Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of SQL Tuning is a salamander. Though mature salamanders bear a superficial resemblance to small lizards, salamanders are not reptiles; rather, they are amphibians that retain their tails as adults. Like all amphibians, a salamander begins life underwater as a gelatinous egg and develops through a series of stages. Newly hatched salamander larvae resemble tadpoles (the larval form of toads and frogs) and breathe through gills. As they mature, salamanders develop legs and lungs, which allow them to leave the water and breathe air. But they remain in or around streams, rivers, ponds, lakes, or moist woodlands throughout their lives. They must return to a freshwater source to lay their eggs.
The most immediately recognizable difference between adult salamanders and lizards is the former's lack of scales; a salamander's skin is smooth and porous and is used to absorb moisture. Salamanders' skin can be any of a variety of colors--from brown or black to yellow or red-- and is often covered with dark spots, bars, or stripes. As they grow, salamanders molt their skin, usually every few days or every few weeks. Salamanders also have the ability to shed and regrow their tails and other parts of their body that become severed or damaged. Unlike other amphibians, salamanders are carnivorous at every stage of their life cycle (tadpoles are herbivorous), and their diet consists of worms, insects, snails, and small fish.
Mature salamanders are usually about 4 to 8 inches long, though they can be as short as 2 inches and as long as 70 inches. Most have four legs, though some have only two forelegs. Their front feet each have four clawless toes, while hind feet, when present, have five toes. Salamanders are nocturnal and usually divide their time between the land and water, though some live exclusively in the water and a few are purely landdwelling. When they swim, they make little use of their limbs. Instead, they use their laterally compressed (i.e., taller than it is wide) tail and muscle contraction to propel themselves through the water, as eels do. Some tree-dwelling salamanders have prehensile tails, which they can use to grasp branches.
The name salamander (from the Greek salamandra) originally applied to a legendary creature that could live in and extinguish fire. Aristotle is largely responsible for perpetuating this myth; in his History of Animals, he supports the story that the salamander "not only walks through the fire but puts it out in doing so." The application of the name salamander to an actual amphibian was first recorded in 1611, at which time the supernatural characteristics of the mythological animal became attributed to the actual animal. The common belief (mistaken, of course) that salamanders can endure fire persisted well into the 19th century. Brian Sawyer was the production editor and copyeditor for SQL Tuning. Matt Hutchinson was the proofreader. Darren Kelly and Claire Cloutier provided quality control. Angela Howard wrote the index.
Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.
Melanie Wang designed the interior layout, based on a series design by David Futato. This book was converted by Julie Hawks to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9and Adobe Photoshop 6. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Brian Sawyer.
Your first priority should be the design; if you have a good design then it is much easier to tune your DB. If your design is messed up then the next thing are the SQL statements. You will have to learn how to use the profiler and create appropriate indexes on your tables. This book will teach you how the tree structures behind the SQL work so that you will choose the appropriate joins and force index hints if necessary. I highly recommend this book for any SQL developer
SQL Server Code,Tips and Tricks, Performance Tuning (http://sqlservercode.blogspot.com/)
I was lost in the lybarinth of SQLs, they were performing badly and infested with every kind of waits and poor response times and awful costs and cards.
I was drowning and just blindly was putting an index here and a hint there, to no avail.
Then I got this book by Dan Tow and it really changed the way I lived my tuning life. DAN Diagrams not only made things optimal but also provided a common sense way to explain things to others. Dan's lucid explaination of esoteric concepts just made my life colorful. I highly recommend this book to anyone with a real flare to tune SQLs in the way they should be tuned and fixed.
Absolute needed book for a SQL tuning specialist - Highly recommended
By Ertugrul Bayindir
Comments about oreilly SQL Tuning:
As a senior data warehousing specialist, I used several tools and books for tuning SQL so far and none of is giving the methodology needed. Most of them giving information about facilities of the databases but not real life examples of how to use them. Now after reading this book I can tune SQL statements longer than 5 pages in length in just 2-3 hours. I did tuning for SQL statements having more than 20 tables in 2 hours. I am getting 4 to more than 10 times better run time performance regularly. I think this book turns black art of tuning to an engineering method.
I know it will be seem to like I am working for Oreilly but definetely not. I should also recommend another Oreilly book "Optimizing Oracle Performance" together with this book.
If you read both books I am sure you can do a great job.
For anyone interested, I created a small perl script which generates the tree like structures needed to tune SQL statements. It works on top of Business Objects repository(QM) to get the metadata needed. Therefore it is a BO only solution.
By Douglas Rowe from the Columbia Java Users Group
Comments about oreilly SQL Tuning:
If you are a DBA or developer, working daily with databases that support SQL, you need this book. It covers in better detail than any other book I've seen, all the behind the scenes aspects of SQL. What happens when you join, union or correlate queries? How do you know you're hitting your indexes? What new indexes do you need? Unlike most IT reference books, this one should be read cover to cover but it holds up well as a quick reference on a topic as well. Together with O'Reilly's SQL pocket guide by Jonathan Gennick, you have a formidable tool for creating and refining good SQL statements.
For many years the process of SQL Tuning has been considered an "art". SQL tuning books written to date have presented guidelines and "things to try" but "SQL Tuning" by Dan Tow is different. This is the first book I have seen that presents a clear methodology to attack the problem in a scientific/mathematical way and includes a diagramming technique to visualize the components of a query that affect performance. Dan's book has gone a long way in helping to transform SQL tuning from an art to a science.
The first four chapters of the book cover some of the subjects that are found in other SQL tuning books, and focus on the important points without delving too deeply into database internals. The really "good stuff" is covered in chapters 5 through 10, which includes how to "draw" a query diagram, and the procedures to follow to derive an optimal join order. The problems/examples presented and resolved in these chapters help the reader get a start on understanding the methodology, which can then be applied to actual tuning problems as they are encountered.
So far I have applied this technique to about 20 tuning problems I have encountered on the job using the Oracle RDBMS. I still consider myself a relative novice in using the methodology, but for each of the 20 problems I have been able to significantly reduce elapsed time and resource consumption (gets/reads). Also, these results were achieved in a matter or hours, rather than days. The query diagram has even facilitated finding "missing joins" in some of the queries without having a knowledge of the particular application.
This book is a "must have" for DBA's and developers and I highly recommend it.