Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Book Review: "SQL Tuning" by Dan Tow

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

From: Ryan <rgaffuri_at_cox.net>
Date: Thu, 1 Jan 2004 22:22:53 -0500
Message-ID: <gu5Jb.59084$hf1.37259@lakeread06>

"Bob Badour" <bbadour_at_golden.net> wrote in message news:lrOdnZGXoJslImmiRVn-sQ_at_golden.net...
> "Mikito Harakiri" <mikharakiri_nospaum_at_yahoo.com> wrote in message
> news:8a529bb.0401011544.ed69d30_at_posting.google.com...
> > > "Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message
> > > news:JSEIb.1$Mf4.30_at_news.oracle.com...
> > > 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.
> >
> > It is not the percentage of cases where NL outperforms others. It is
> > the skew in performance difference. Once again, if the best join
> > method is, say, HJ but you use indexed nested loops, then, I routinely
> > see 2, 3, and sometimes even 5 times elapsed time difference. Big
> > deal! When NL outperforms HJ we often speak seconds versus hours.
> >
> > > 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
> > > time.
> >
> > Not necessarily. If all the required columns of the inner table are in
> > the index, then we don't incur overhead of table access by row id
> > (random io) and NL would outperform the others on the big relations as
> > well.
> >
> > > Does Dan Tow discuss this?
> >
> > There is a section discussing NL vs other methods, but his arguments
> > aren't necessarily identical to mine.
> >
> > > I have never heard about the 'leverage' of HJ over MJ. Who said that?
> Who
> > > are the often 'sited'? This sounds inaccurate.
> >
> > <quote>
> > the often cited argument in favour of HJ or MJ is the advantage of
> > leveraging disk sequential io vs. random io
> > </quote>
> >
> > There is no "'leverage' of HJ over MJ" in the above quote. I meant
> > that NL is associated with random IO, while HJ and MJ - with
> > sequential io.
> >
> > > 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.
> >
> > AFAIK this is the first book solely dedicated to tuning individual SQL
> > statements, as opposed to competitors where authors compensate their
> > lack of SQL tuning expertise with dull chapters how to tune overall
> > system performace. Once again, SQL optimization is a very nontrivial
> > subject.

>

> Is it fair to say this book is a book on tuning Oracle SQL statements?
>
> In my experience, optimizing Sybase derivative SQL statements often
involves
> breaking them up and using temporary tables, which sounds a little
different
> from your description of the book.
>

> Are you familiar with Shasha's and Bonnet's book on performance tuning? It
> sounds like it would have much greater breadth but not as much depth in
> overcoming this one major flaw in most if not all SQL dbmses.
>
>

you mean this book? Is it really any good? Looks like inter-database stuff and that is always watered down.

http://www.amazon.com/exec/obidos/tg/detail/-/1558607536/qid=1073013652//ref =sr_8_xs_ap_i2_xgl14/002-7457171-4222439?v=glance&s=books&n=507846

The book in question appears to have some kind of 'method' for tuning SQL. Ill grab a copy eventually. There appear to be a number of inaccuracies in it though, but they are the typical inaccuracies and are not prevalent. Received on Thu Jan 01 2004 - 21:22:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US