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

Home -> Community -> Usenet -> c.d.o.server -> Re: Where can I get a complete list of all SQL Hints?

Re: Where can I get a complete list of all SQL Hints?

From: TM <tonym101_at_hotmail.com>
Date: 20 Jun 2003 16:55:57 -0700
Message-ID: <7b718442.0306201555.17bf34b1@posting.google.com>


"Bosco Ng" <boscong_at_leccotech.com> wrote in message news:<3ef31ddc_at_shknews01>...
> > It's a database configuration parameter.
>
> They can be configured at session level. I guess there must be some reasons
> for IBM lets you do so, right?

Using the analogy of the optimizer being like a chess computer, rather like a chess computer it can be slow if you don't place some limits on what it should consider. For this reason, at lower optimization levels IBM include a greater number of what they call "heuristic limits" on how much to consider. These include algorithms that judge how much effort to spend on the optimisation - probably based on factors such as number of tables joined (but that's speculation not published information).

The default level of 5 works well, being good for most complex queries, yet not a huge overhead in OLTP environments. That's not to say you can't squeeze out better performance playing around with the level. If all your queries are certain to be trivial OLTP ones on well-indexed tables, you really don't need the optimizer to work out that it doesn't need to spend much time optimising trivia - you can just tell it by setting a lower level.

If you hit an optimization problem, fiddling around with the optimization level might help - then again it might not. But playing around with environmental settings to fix a single query has none of the certainty of placing hints in SQL, and may well have unwanted side effects. So like I said before, it's just not normal practice in the DB2 UDB world.

> Look at what you said:
> > DB2 doesn't have hints. At least, not DB2 UDB For Multiplatforms
> > (UNIX, Linux, Windows, OS/2)... i.e. the common code base flavour
> > that's gobbling market share. DB2 UDB is widely recognised as having
> > the leading optimizer though.
>
> The DB2 market share is still below that of Oracle even after the Informix
> acquistion (if you just count DB2 for UDB, that would be even far smaller).

I didn't say DB2 had overtaken Oracle yet. But is has narrowed the gap to a very small one. DB2 also retails a lot cheaper than Oracle, so in terms of new installations DB2 is doing even better.

> ...so-called "Universal"-DB (haha, common code base, I can't believe
> someone really believe this crab, they don't even have the same syntax (for
> some very rare features) for christ sake and just for your information, UDB
> on different platform are developed almost independently by different labs
> across the world, look at the Explain tables in different UDB platform and
> see their naming convention mess you will see what I mean!)

I've never heard anyone say that DB2 has a single code base. But DB2 UDB for Multiplatforms (i.e the platforms that Oracle runs on... UNIX flavours, Linux, Windows) *does* have a common code base, and therefore has identical syntax, utilities, comands and tools across those platforms.

> > Actually affects optimization post-rewrite. It's basically like
> > telling a chess computer how long to spend enumerating possibilities.
>
> So how long is good enough? DB2 doesn't dare to take this responsibility to
> determine for you (as this would make it seem 'so dumb just like that of
> Oracle'). So it puts the responsibility on you, the DBA. Hey, this is the
> optimizer level you choose so if I make a wrong decision, then it must be
> you haven't given me enough time on such a complex query. Your fault, not
> DB2's. Woooolala.

Simply not true. The vast majority of shops run the default level 5, which (as stated above) emphatically "takes responsibility" by determining the amount of optimisation to do.

> Forget about it and yes, that's what IBM wants you to think (and it seems
> they really work in your case). In practice, they do change them from level
> to level. Ask a DB2 DBA that finds his query performs poorly and he tries
> different level like gambling and only finds the DB2 optimizer is so rigid,
> even in Level 0 it's trying to do a view merging, a predicate pushing or
> etc, only after using some very ugly trick that it starts behaving properly

Any DBA worth his or her salt would assess the reason the optimizer was going wrong before taking any action (and changing the optimisation level would be an unlikely action). There's a shortage of experienced DB2 UDB DBA's because of the continued high sales growth.

> It's the same case. Under normal and ideal case, Oracle DBA don't use HINTS
> and DB2 DBA don't change level but in case of need, they come into play to
> rectify poor optimizer decision. Neither is smarter nor dumber.

What is it with Oracle and IBM, a cartel? Did they duplicate their optimizers so that they were both *exactly* the same standard? Not very likely is it, so what are the odds of them being exactly the same and why do you keep insisting on your silly claim that "neither is smarter or dumber?"

DB2 optimizer is "smarter" because IBM decided to go for cost-based from the start, about 15 years before Oracle realised its mistake. Oracle can't duplicate something of the complexity, sophistication and sheer ambition of the DB2 optimizer overnight.

In a sense the DB2 optimizer also needs to be smarter, to handle the complexity of both SMP and MPP parallelism working together on a single query. As of v7, it also has to optimize federated queries.

TM Received on Fri Jun 20 2003 - 18:55:57 CDT

Original text of this message

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