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:3f87c47a$0$28119$afc38c87_at_news.optusnet.com.au...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:bm4a94$ijj$1$8300dec7_at_news.demon.co.uk...
>
> True, of course. But we must give some lag to the app makers.
> In most cases they have to make the darn things work well
> with minimal changes across a number of databases. It's not
> that easy at all. Particularly with the enormous applications
> we see nowadays, where 5000+ tables are not uncommon.
>
Lag is allowable.
Claiming an application is database independent
by choosing to design it very badly is not.
> Clearly, something more definite than just "good design"
> needs to be done to make life easier for these folks.
And Oracle Corp. does try. It is quite clear in recent
versions of the database that features, and internal
mechanisms have been introduced or changed to
allow Oracle to deal with design problems. The
'cursor_sharing' parameter you mention is an obvious
example. But so much change introduces the kernel
instability that you complain about. If Oracle changes
code to fix problems that should never have existed,
some people will start having problems.
> To me, one of the main things to avoid in designing or
> tuning a system is complexity. If using hints is so
> complex that only a "hint expert" can handle them, then I
> know exactly what I won't be using.
You don't have to be an expert - as with so many things
in Oracle, you just have to stop listening to the wrong
'expert' and do a few simple tests.
From a relatively recent publication:
<quote>
As another example, the following query uses an ordered hint
to join the tables in a specific order: emp, dept, sal, and finally,
bonus.
I further refine the execution plan by specifying that
the emp to dept join should use a hash join and the
sal to bonus join should use a nested loop join.
select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
emp,
dept,
sal,
bonus
</end quote>
If you read this, you may "learn" that the hint
use_nl(sal, bonus) is a directive to Oracle to
join sal to bonus using a nested loop - but it
isn't. It's a directive to Oracle to use a nested
loop to ENTER sal, plus a directive to Oracle
to use a nested loop to ENTER bonus. In
this case, because of the 'ordered' hint, the
side effect is that the directive appears to
force a nested loop join in that order between
the two.
In passing - the execution path will probably
suggest that the 'ordered' hint has been ignored
anyway, as the intuitively obvious path is
dept, emp, sal, bonus
anyway - and if it is a good path, Oracle will
take is (without disobeying any of the hints
supplied).
The ACTUAL effect of the use_nl(sal) component
of the hint is that in combination with the ordered
hint is that it stops the (apparent) path:
sal, dept, emp, bonus
from appearing by blocking the option for a hash
join into salary, hence blocking the 'sides_swap'
optimisation from kicking in.
Blame a lack of good documentation - not the
complexity of hints. Say what you want properly,
and it happens.
>
> Strange you haven't heard about it, I thought it would have
> been public domain now? In 10g according too Ken Jacobs'
> presentation here last month, it is possible with OEM to
> pick a badly behaving statement and tune it (with hints),
> *on the fly*. Ie, without stopping anything, directly in
> production.
>
It's all in the description, isn't it. Yes I do know about
the options available in profiling - but your one-liner
was open to mis-interpretation. 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.
> Great idea, but IMHO open to "tweak city". And God only knows
> what happens when the database is shut down: do we have to
> re-tune them all again next time?
The profile gets stored - so if you enable it, it's there whenever
the statement hits the system.
>
> Oracle has to bite the bullet and put the whole thing into
> policies. Which can be applied to a group of users, or schemas,
> or apps, or SQL statements of a specific class. Then, it will
> be useful. Until then, it's nothing more than a vehicle for
hacking.
>
But if you are running 3rd party apps that can be fixed at
all, it is ofren just the 'fix by hack' that you have to hand.
One of the questions I ask myself on seeing any new
feature is "who's it for ?' I see profiles as being used
by the DBA who needs to care for lots of 3rd party
systems, and currently has to create outlines, or
indexes, the hard way. The advisor is there as a
sort of expert system to give clues about (possibly legal)
structural changes, and produce profiles when all else
fails.
> > Hacking the statistics on one table to solve one query
> > is is just as likely to create a bucket load of new
> > problem as fiddling with a significant optimizer
> > parameter.
>
> Oh no it isn't! Changing an optimizer parameter affects
> ALL tables and ALL SQL. And it usually requires a bounce.
Oh yes it is !! (I win , I used two !)
First - fouling up 120 SQL statements is not 'better'
than fouling up 2,000 - the immediate fix is just as
quick.
Second - to go back to your expression - sometimes
you HAVE to bite the bullet. Do the right thing and
then fix the fall-out. Otherwise you can waste time
on localised surgery and still have to change the
parameter one day - and re-fix all the patchwork.
> The same applies to changing optimizer parameters:
> unless we know the impact of it on the TOTALITY
> of the system(and in most cases that is not possible),
> we may cause problems. I'd rather limit the problems,
> potential or real.
>
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).
Received on Sat Oct 11 2003 - 05:58:25 CDT
