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: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Sat, 21 Jun 2003 11:00:44 +1000
Message-ID: <3ef3af7a$0$24424$afc38c87@news.optusnet.com.au>


"TM" <tonym101_at_hotmail.com> wrote in message news:7b718442.0306201555.17bf34b1_at_posting.google.com...

> 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).

Actually, it's in the UDB V7 manual. To do with number of tables joined. Higher levels are recommended as the number of tables in a join increases. So the two are definitely related. Personally, I find that incompatible with the notion of an "advanced optimizer", but what the hey.

> 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.

You see, this is where it doesn't add up. Let me explain.

No one develops or designs an OLTP system with lots of large joins and lots of complex queries. It's mostly very simple SQL, in most cases single table. Sometimes very large tables, granted. But usually only a few. Now, in such a situation, why would level 5 ever be redundant or cause too much work? It's simple SQL, it should just breeze along and not dwell too much in finding an optimal plan. IOW, it should never need a level below it. The amount of work in optimizing SQL is proportional to the length and complexity of the SQL, not the nature of the operational environment of the application!

Another related thing: aren't the packages supposed to obviate the need for the optimizer doing anything at runtime? Ie, you store an execution plan, why change it later on the fly?

If using stored plans (which you must if you want any kind of reliable performance levels and you want to meet an SLA), then what's the point of "balancing the optimizer load"? One would think that the execution plan sorted out and stored away in a package, that could be done offline or without interfering with production. No need for any balancing.

Ie, no need for different levels of the optimizer to avoid too much CPU power wasted getting the plan sorted out.

Do it once offline (overnight, whatever) at max optimize level, store it away and re-use it without further overhead in production. No need for multiple levels of optimizer to stop it working out things not needed.

> If you hit an optimization problem, fiddling around with the

You mean there *are* optimization problems? I thought that was already all worked out in the "superior" CBO...

> 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.

Exactly. Just grin and bear it when things go wrong. Or scale up. Or re-develop. Because there is not much more you can do effectively. I prefer Oracle's way of having the same options of stored plans, but leave me the way out of further manual tuning if things go "clunk".

Which I can't do in DB2. I have to accept what IBM considers a "balanced optimization". Their opinion might differ from real life.

>
> 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.

Have you priced in Standard Oracle instead of just costing the more expensive EE? You might find it's not that much more expensive than any other DB out there. And given that one of the usual claims is that people don't "need" the full blown functionality provided in EE Oracle, then maybe these pricing "comparisons" should be based on Standard Oracle, no?

Actually, the gap has widened. DB2 came down in market share of late once the Informix "accounting" was taken for what it was. In Unix. The only winner overall in market share was M$. Not IBM, not Oracle. Take note, it IS important. Once again, Microsoft gained market share. At the expense of BOTH IBM and Oracle: the market hasn't grown itself. Why? Think about it.

Another minor point you may find interesting: yesterday, I spent a couple of hours in a major bookshop in Sydney. One of the largest, very well stocked. There were 8 shelves of Oracle books, just on the RDBMS side. 4 shelves of SQL Server books.

And 3 books (three books!!!!!) on DB2/UDB. Lousy show for a product that is about to overtake the industry, aint' it?

>
> I've never heard anyone say that DB2 has a single code base.

You must be deaf. Go back a coupla years here in google. You know, THAT discussion thread. Still there...

> 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.

The guy just explained to you that even in the Unix world, the darn thing IS different. That's what he said. You still insist on this one? Look, why is it that the internal username length limitations in Windoze UDB are different from UNIX UDB and all other "flavours"? Because of the "common code base"?

> 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.

Good point. And quite true, I vouch for that. And quite true as well that the vast majority of Oracle shops don't use hints. They don't need them, the CBO does the job OK. So, what exactly was your contention about CBOs?

>
> 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).

How can he/she, if the darn thing is a closed environment whose only option is a level setting?

> There's a shortage
> of experienced DB2 UDB DBA's because of the continued high sales
> growth.
>

Funny. In the last two years, there have been hundreds of adds asking for SQL Server and Oracle DBAs. I saw about a dozen DB2 or UDB adds, mostly DB2 mainframe. Fact. Go to jobnet.com.au and check for yourself.

>
> 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?"

How many different ways do you think a CBO can be implemented? Aren't they both relational databases? Using binary-based computers (von-Neumann boxes) to store the data? How many theories of optimization do you think can be cited on each side? The darn things are the same, none smarter nor 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.

So, was that the "common code base" DB2 15 years ago?

You see, you just CANNOT keep quoting DB2 and UDB as different products and then claim they are the same product.

They aren't, they never were. CEASE quoting DB2 and UDB as if they were the same product and then as different products. You are fooling no-one.

>
> 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.

Yes, that's real fun. Particularly since Oracle's version of "federated" queries has been in their non-advanced, backwards optimizer since V8. About 9 years ago...

--
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Fri Jun 20 2003 - 20:00:44 CDT

Original text of this message

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