Hi Justin
Didn't know you were on the list
I'm usually about a week behind, so I don't get to participate very often...
> A properly formed hint will cause the CBO to consider the
> hinted path to be
> less costly than it would otherwise consider it, but hints do
> not force a
> query to use that particular plan.
>
> If you want to force Oracle to use a particular plan, plan
> stability is
> orders of magnitude easier!
Umm, but if you look at plan stability you will see that it is
implemented as hints (and query rewrite) - typically loads of them. A
hint *does* force you to do what it says, if it is being 'ignored' then
likely you haven't excluded alternative access paths. Of course for any
sufficiently complex query (in my case that means 3 or more joins) then
manually specifying an access path with hints becomes a too difficult
problem.
I've never looked under the covers on plan stability, so now I'm get confused. My understanding was that plan stability forced a query to follow a particular execution plan. My understanding of hints, however, was that they were only suggestions that the CBO could ignore. Tom Kyte writes (second or third response down):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1951680913800
1) yes -- IF it accepts the hint, hints are just that -- hints. They are NOT directives, they are suggestions. It took the suggestion in this case.
If plan stability is just Oracle applying a bunch of hints, and hints are only suggestions, does that imply that the CBO can ignore plan stability?
Justin Cave
