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

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sat, 21 Jun 2003 12:47:50 +0100
Message-ID: <3ef4458c$0$964$cc9e4d1f@news.dial.pipex.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0306201312.45101570_at_posting.google.com...
> > I'm also don't understand why most people would spend the effort/time to
study
> > something like this, which can change at any time with the release of
patches.
> > The Oracle product set is so big, a person can easily spend years in
thoroughly
> > learning the 'legitimate' areas. (Operative word "most" excludes the
Connors
> > and Jonathans of our world.)
>
> I would think we would all want to emulate the Connors and Jonathons
> of the world, I would hope they aren't qualitatively different in
> their thought processes (not that we are all exactly the same...).

I'd hope that was true - though to get even halfway there seems to require a hell of a lot of time playing with stuff. This is not always family or employment friendly. But yes in general I'd hope that DBAs were both curious and methodical, and rather suspect that the natural conservative tendency of DBAs (Why *should* I let you do X to my data what effect will it have) ought to help here.

> I have been wondering if hints are going to continue to be the way of
> Oracle. Anybody care to hazard a guess? (Or point me towards
> something that says if I've missed something). The reason I ask is I
> work with a language that kind of sticks the users with RBO, partly
> because there is no way to pass through hints.

I'd certainly hazard that the answer is yes. For the following reasons.

Hints are already been used for features and database internals example 1. Plan Stability. example 2. DBA_FREE_SPACE which in 9.0.1 (and I guess from 8i in fact) has the definition below

select ts.name, fi.file#, f.block#,

       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#

  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */

       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno

from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

2. Hints are widely used (some might say abused) by Oracle customers.

3. I suspect that determining the optimal execution plan is an example of the halting problem. That is it *may* always be possible to determine the best execution plan by a method other than trying all of them out and picking the fastest (which could take an infinite amount of time), but I doubt it.

The first two reasons I give suggest that removing hints will be hard both for Oracle and its customers. The third suggestion might explain why the two methods used so far (use a fixed set of rules for evaluating possible access paths and find as many access paths as possible within a given timeout and assign a number to them based on expected IO) essentially try to short circuit the problem. If determining optimal execution plans is an undecideable problem then we are bound to have some sort of short circuit method, for a new one to be adopted over the ones we have now it would have to show significant benefits (and almost certainly coexist for a while).

This last leads me at least to speculate that the system statistics introduced in 9i (so probably useful in 10ir2) are a way forward that Oracle will develop. This and the likelihood of more clever data structures and access paths IMO tend to suggest that there will be more hints rather than an elimination of them.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Received on Sat Jun 21 2003 - 06:47:50 CDT