Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message
news:3f881704$0$28122$afc38c87_at_news.optusnet.com.au...
>
> Ah yes, I think I know which one that was.
> As an aside: the ORDERED in most cases solves
> the problems and obviates the need for use_nl
> hints. Have you found this as well? It's
> almost like the CBO is thinking: "I'll do a
> hash or merge join, but if you change the
> order of table evaluation then I'll go for NLs".
> It's been my experience since the latest patched
> levels of 8ir3 and 9ir2 (I didn't play much with 9ir1).
>
"ordered" often works, because when you use it
sensibly you have probably put the tables into a
'business-oriented" order, and therefore the hint
is actually telling Oracle a little more about the
data in a subliminal type of way.
Of course, if you have subqueries that are
unnested, things can get very bloody, as
the unnest seems to be applied before the
ordered hint is applied, and the unnest seems
to move the subquery tables to the top of the
FROM list.
>
> I lost you here: "side-swap" optimisation?
> Not familiar with the term.
>
Oracle can decide to reverse the roles of the
two tables in a hash join - in mid operation if
the join is not optimal hash - the jargon for
this is 'sides swapped' when it appears in the
trace file.
>
> > But describe it like
> > that and, hey, I can already to that with stored outlines.
> > In both cases, I actually have to invalidate the current
> > cursor, though.
> >
>
> Yes, that is my understanding too. They just made it
> look very slick with OEM. But there must be something
> else going on given that it appeared to start from a
> totally "vanilla" statement. Ie, outside of any prior
> outline.
>
It is very much smarter - and of course the optimiser
can hack through table, index, column, correlation,
and cache, statistics to generate an optimal plan,
as well as considering concurrent workload so that
it doesn't optimise locally and 'pessimise' (sounds
like something you do when constipated, doesn't it)
globally. The default tuning time for 'comprehensive'
automated tuning seems to be 30 minutes, so it's
not supposed to be a quick tweak exercise, it's supposed
to be a carefully considered event. (In fact, the tests I've
been running so far have been very small, and for a single
statement I've been getting sensible results in less than
a couple of seconds).
>
> If I can do it without risk, then it is a very
> good alternative. However, I'm not always given the
> privilege of fixing the fall-out. Often if I make a
> change to the optimizer pars and it fixes one problem
> and creates others, the only option I'm given is
> "put it back the way it was". Unless I have prior
> agreement from damagement that there will be a subsequent
> effort, I have to stay with the "fix just this one, now!"
> approach.
>
>
> > My point here was that if you can do anything to tell
> > the truth it helps. Long-term, deliberate lying does
> > not help (because Oracle changes code to fix bad
> > systems, and one day will change something that
> > makes your well-intentioned lie very bad news).
>
> I think we'll have to agree to disagree on this one.
> I fail to see how a globally non-intrusive technique
> such as changing one table's stats can cause such havoc
> a few years down the line.
>
An example, though not dramatic one. If you use 'small table'
statistics on a critical table, you get the effect you want. You
upgrade from v8 to v9, and Oracle adds one to the cost of
a table scan. Suddenly the costing on your 'pseudo-small'
table behaves differently and every execution path on that
table could change.
What about the case:
Fix table A to deal with lots of queries, including several
which join A, B, C.
Someone else eventually decides to fix table B to deal
with queries on B,D,F
Your A, B, C query now has two 'fixed' tables, and Oracle
fancies a two-step hash join apparently running through
memory but actually both turning into a multi-pass hash.
> Not more nor less than any of the other techniques
> that deal with changing global tuning parameters.
> Both impose a risk. Both have to be managed, in the
> absence of a perfect optimizer. We may prefer one
> or the other, but using the term "lie" to define one is
> a bit strong, IMHO.
>
And in conclusion, I am quite happy to use the dbms_stats
package to put whatever statistics I think are appropriate
(i.e. lie) into the system if I think Oracle has a misleading
numerical image of what my data looks like - so we're not
too divergent in our views.
Received on Sat Oct 11 2003 - 10:36:14 CDT
