Re: Book Review: "SQL Tuning" by Dan Tow

i dont think you have to give amazon a credit card number to make an account...

my responses to your review below...

"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:JSEIb.1$Mf4.30_at_news.oracle.com...
> Amazon doesn't let me entering review without account, so it goes here.
>
> Optimizing a single SQL statement is the most important part in database
> performance arena. Unlike other performance problems it is proven to be
> difficult, so that no automatic solution is lurking in the future. This is
> why database performanca analyst would find Dan's book indispensable.
>
> Dan headed Oracle Apps performace group for quite a while, where he got
> plenty of material for his book. (Apparently, the only utility of unfamous
> Oracle Apps being an excellent test bed for their Server product).

> Written from practical experience Dan's book debunks many myths
established
> by SQL optimization community. He introduces a concept of robust execution
> plan, that is the plan that may be not the optimal but which would never
> loose too much to the optimal solution. In overwhelming majority of OLTP
> cases Indexed Nested Loops (NL) is a robust execution plan, while Hash
Join
> (HJ) and Sorted Merge Join (MJ) are not. Indeed, the often cited argument
in
> favour of HJ or MJ is the advantage of leveraging disk sequential io vs.
> random io. Today's systems, however, have lots of memory, so that caching
> effect relieves this effect to the extent when it becomes extremely hard
to
> find a testcase where HJ outperforms NL in a meager 10 times. Given that
> indexed NL routinely outperforms the other join methods at many orders of
> magnitude, this establishes the idea of robust execution. To be fair to
> theoretical community the idea of robust execution is not quite new:
> http://citeseer.nj.nec.com/chu99least.html

Let me get this straight are you saying that NL almost always outperforms HJ and MJ? Yes I know NL outperforms the other two types of queries in OLTPs... how about stating WHY! This is misleading because it makes people think they should automatically avoid HJs and MJs.

The reason is simple. NL is optimized for returning a relatively small

number of rows. MJs and HJs are optimized for returning a relatively large
number of rows. In OLTPs most of the time you only need a relatively small
number of rows. Its batch processing where you use MJs and HJs most of the

Does Dan Tow discuss this?

I have never heard about the 'leverage' of HJ over MJ. Who said that? Who are the often 'sited'? This sounds inaccurate.
>
> There are many other optimization ideas introduced by Dan. He also
mentiones
> areas that have negligeable effect: sorting, grouping, post group
filtering
> with "having", etc. Emphasis on the important aspects of SQL optimization
is
> IMO the strongest feature of Dan's book. A reader would certainly benefit
> knowing what to focus attention to in overwhelming amount of information
> associated with query optimization problems.

The book intrigues me. It sounds different from the other SQL Tuning books which just give you 'tips' on things to try without an approach. However, your review about join types is not correct.

I hope this book is accurate...
>
> The book has few missing areas. Understanding cost model is essential for
> performace analysis, but this topic is omitted in the book. The good
> references are Wolfgang Breitling's papers at www.centrexcc.com. The other
> large missing area is query transformations, but I'm not aware of any good
> source covering it anyway.
>
>
>
Received on Thu Jan 01 2004 - 08:53:04 CST