- Paperback: 338 pages
- Publisher: O'Reilly Media; 1st edition (December 1, 2003)
- Language: English
- ISBN-10: 9780596005733
- ISBN-13: 978-0596005733
- ASIN: 0596005733
- Product Dimensions: 7 x 0.8 x 9.2 inches
- Shipping Weight: 1.4 pounds (View shipping rates and policies)
- Average Customer Review: 26 customer reviews
- Amazon Best Sellers Rank: #950,818 in Books (See Top 100 in Books)
Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
Other Sellers on Amazon
+ $3.99 shipping
+ Free Shipping
+ $3.99 shipping
SQL Tuning: Generating Optimal Execution Plans Paperback – December 1, 2003
|New from||Used from|
"Maybe You Should Talk to Someone" by Lori Gottlieb
"This is a daring, delightful, and transformative book." ―Arianna Huffington, Founder, Huffington Post Learn more
Frequently bought together
Customers who bought this item also bought
About the Author
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 email@example.com.
Try the Kindle edition and experience these great reading features:
There was a problem filtering reviews right now. Please try again later.
1) learning how to do query diagrams is great for people who have never done them and this book is the best (maybe even the only) book for learning this. In learning to do the diagrams, you will understand the basic logic and basic math that an optimizer must do in order to get you a good plan. Do not worry. It is not a deep math book and there are no complex calculations. Indeed Dan shows you how basic it all really is. This gives you a deeper foundation in optimization and brings you closer to having that INFORMATIONAL CRITICAL MASS which is required to do good tuning.
2) the book is outdated. As I said, I have been doing Oracle since 1984. For two decades, Dan's method of tuning and relying mostly on NESTED LOOP JOIN was the only real game in town. And Dan explains it well. Unfortunately about the time Dan published the book (2003), Oracle was entering a revolution in its design. Most notably, databases were increasing in size dramatically and this made the need for PARTITIONING strategies and PARTITION WISE HASH JOINS very high. Dan's techniques of query diagraming are still useful for understanding a query's needs, but the book is today outdated because of its lack of treatement for PARTITIONING and HASH JOIN techniques. Don't get me wrong, even outdated, this book has great value because it clarifies what you need to tell other people, why certain things happened.
3) Even outdated, I learned one neat join trick which alone is worth the 20 dollars I paid for this book. Page 146 has a gem of a trick that I never considered but which I can see as offering massive benefits in a few special situations which I have faced in the past but could not solve. Thanks Dan. It is even easier to do in today's 10g and 11g databases because you need no hints to make it happen.
This brings me to my last commentary. The reason I say the book is outdated is because Oracle has come so far in the last five years with its optimizer that the trick to getting Oracle to give you the right plan is to feed it good information. If you do this then it will do what Dan shows you in this book (only better than you could) and give you a great if not best plan. Thus you will rarely build a query diagram these days. Instead you will try to figure out where information was not provided to Oracle correctly and fix that. If this turns out not to be the case, then the problem is likely because the SQL is crappy and you need to rewrite it. I would say that given the right information (stats collected right, constraints created for PK,UK,FK, indexes built to support the constraints, datatypes correct for columns, not null defined when data is actually required), Oracle will produce a great or best plan 99% of the time. The other 1% of the time you will 99% of the time be doing SQL rewrites. That leaves 1/100th of 1% of queries to be a real problem. Many times your system won't be that complicated.
Not being an expert in DB2 or Sql Server I cannot say how up-to-date the material remains on these databases but since I doubt their Optimizer Technology is as advanced as Oracle's there may yet be significant value retained in these two spaces. I can say that it is true that Dan's diagramming methods are universal for any database and that reading the book will provide you with that boost towards critical mass I mentioned earlier regardless of what database you use.
If you are an Oracle Developer/DBA and you are new to tuning, or want to see what you know and don't know, you can't go wrong by getting this book. After reading the book, start reading about PARTITIONING and PARTITION WISE HASH JOINS. Understanding what is in this book and understanding the details of PARTITIONING and PARTITION WISE HASH JOINS in various scenarios will put you at the forefront of tuners. Maybe its time for Dan to do an updated version of the book.
One last comment. I don't want this to seem like a negative review. Remember I said I have been doing Oracle since 1984. As "expert" as I am with Oracle and in tuning it (I am a reasonably good tuner for most things), I learned stuff from Dan and his book. Twenty Five years of doing it and this book still taught me things. I am very happy to have purchased this book and you will be too.
1. funny, easy-to-understand method of tuning;
2. explanation of basic concepts.
1. the book is not easy to read. You have to read some paragraphs two or three times to understand what author wanted to say;
2. author claims that the books can be used by all platform developers, although only Oracle examples, provided in the book, are more or less satisfying;
3. the examples don't match results author provided in the book. Why provide them then? What's the point of giving test db that can only be partially filled with data? The point is supposed to be in comparing the results and trying by yourself and not in trying somehow to match the result to the book and wondering why author gave you scripts that don't work;
4. expressing formula for filter selectivity with SQL-syntax is a nightmare. And this form won't work in SQL server without alterations;
5. not a word about SQL Server- specific tuning tools.
The query in question now runs in a few seconds, not a few minutes. Our customers are quite happy.
Note that I'm using Postgres, a database that isn't explicitly "supported" by this book.
The DB is irrelevant. This book tries from the very start to be as language-agnostic as possible, and succeeds quite well. The information is presented from a theoretical standpoint, before teaching an ordered, logical system to derive the most efficient query plan, given the constraints.
I'm very pleased with my purchase. Bravo, Dan Tow.