Why are optimizer hints required?
The House Dawg
Prior to putting PL/SQL into production appropriate indexes are created
and verified that they are being used with EXPLAIN PLAN. The DBA's
calculate statistics every weekend using a 20% sample size on all
tables in the schema, but invariably some of the PL/SQL degrades into
doing full tables scans. This has been isolated to a table that has
around 25 columns. In particular, one column in the table is updated 4
times as the row goes through various state transitions and the index
on this column is eventually ignored.
The DBA's wish to take the easy way out and force all software
developers to use optimizer hints but I'd rather not hardcode index
names into my source code for several reasons.
Are there ways to ensure that when SQL has been put into production and
is using indexes that the SQL doesn't eventually degrade into full
table scans?
TIA,
Matt
Jonathan Lewis
There are several strategies you could adopt.
Your underlying problem is that from your
perception, you are confident that this index
is definitely the one to use. The optimizer, however,
cannot work this out for itself, possibly because
it is always working with statistics that are rapidly
going out of date.
One thought is simply to create a set of index
statistics for that index when it is looking its best,
and store them (you can create a 'statistics table'
using dbms_stats.create_stat_table (check sp.)).
Then, add a job to your gather stats job to copy
this indexes stats from the stat table back onto
the index.
(There are variations and refinements on this theme
that might be more appropriate for your site - but
you get the general idea).
Oracle 10g has a nice little feature that once
you got 'nice' stats on an object, you can lock
them so that they don't get overwritten.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
"The House Dawg" <mhou...@ix.netcom.com> wrote in message
news:1110220323....@z14g2000cwz.googlegroups.com...
Frank van Bortel
Well , before you apply Jonathan's wonderful hints
on hints, is this particular FTS really bad?
It may not be; the CBO will leave index FFS and index
range scans for what they are worth, and switch to FTS
if anywhere between 4 and 25% of the table needs to be read.
(Some documentation claims 4%, other 25%, and there will
probably be occasions where it's something else, completely).
Remember index and table reads are serialized - it's not
something that happens in parallel! So an index read,
followed by a table read may be less I/O friendly that
a FTS.
--
Regards,
Frank van Bortel
Arun Mathur
Time for a new DBA.
Is using hints really the easy way out? Hardly.
Is reading from an index always better than a full table scan? Nope.
Could it be possible that maybe, just maybe, the DBA can spend a little
more time in figuring out the best way to analyze the tables and
indices?
For argument's sake, let's assume that the development and production
instances are similar in volume, if not identical. Then, say the hints
put in place work well, both in development and production. In other
words, the DBA is happy because the optimizer favors index scans over
full table scans. But then, what happens if at some point in time, the
performance of the applications degrade because
1) The data has changed, and the hints are now inappropriate.
2) The optimizer can't help because the query has hints?
Let's try the easy route that the DBA proposes. First of all, how does
he know which path to take for each query? Then, when he figures out
which hint(s) to use, how did he come to his conclusions? Apart from
that, does this mean you have to go back and change your code with
different hints each time a problem occurs in production? This
doesn't sound so easy.
In a nutshell, the easy way is to use the Cost Based Optimizer. It's
there for a reason.
Regards,
Arun
Sybrand Bakker
<mhou...@ix.netcom.com> wrote:
Thomas Kyte in his seminar advises against hinting every statement.
He also advises to use generic hints only, like the ORDERED hint.
Implicitly he advises against forcing the optimizer to use a specific
hint.
He warns hints are nothing more and nothing less than what they are:
hints! If the optimizer comes up with a better plan ignoring hints, it
will use that.
Hth
--
Sybrand Bakker, Senior Oracle DBA
hpuxrac
snip
> The DBA's
> calculate statistics every weekend using a 20% sample size on all
> tables in the schema
This strategy is probably the cause of the problem. ( It will work for
some applications, not all. DBA's sometimes get in the habit of trying
to enforce a standard that works for most everything. The gather
everything at 20 percent was kind of a defacto standard several years
or more ago but has been superceded in many shops (my opinion)).
You can set MONITORING on for a table (or all tables in a schema). and
then use DBMS_STATS package to do a GATHER STALE.
Look at the ask tom site and look at hits for GATHER STALE and GATHER
AUTO as well as other relevant keywords.
Niall Litchfield
news:d0iame$5r7$1...@sparta.btinternet.com...
> Oracle 10g has a nice little feature that once
> you got 'nice' stats on an object, you can lock
> them so that they don't get overwritten.
I believe that it also has a similarly nice feature that allows you to hint
for an index based not on the index name, but the table and column names. I
haven't however used this feature so cannot comment on its real-life
usefulness.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Noons
> Are there ways to ensure that when SQL has been put into production
and
> is using indexes that the SQL doesn't eventually degrade into full
> table scans?
See here:
http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96533/outlines.htm
(watch the wrapping)
for "plan stability". DAGS on the same string and see the references.
This works. If it is too complex, then you can try to snapshot
stats and reset them for the relevant objects as described in
Jonathan's reply. Interestingly, this is a technique that Jonathan
called "cheating" not so long ago. But it works and works good.
If all else fails then there are ways of writing hints without
explicit names for anything and yet still make them work.
But you need to change how you write SQL. Mostly, you need to
associate a table alias with every table in every SQL statement.
Then you can use the alias to hint for index usage, rather than
explicit index names. This will bypass SOME of the problems.
But it is far from perfect.
Jonathan Lewis
Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
"Noons" <wizo...@yahoo.com.au> wrote in message
news:1110350653.1...@g14g2000cwa.googlegroups.com...
> If it is too complex, then you can try to snapshot
> stats and reset them for the relevant objects as described in
> Jonathan's reply. Interestingly, this is a technique that Jonathan
> called "cheating" not so long ago. But it works and works good.
>
With a little luck, I called it "cheating" rather than cheating.
i.e. the quote marks are important.
I think I've also said "you need to tell the optimizer the
truth, even if you have to lie to do so". Apologies for
the whimsy.
> If all else fails then there are ways of writing hints without
> explicit names for anything and yet still make them work.
> But you need to change how you write SQL. Mostly, you need to
> associate a table alias with every table in every SQL statement.
> Then you can use the alias to hint for index usage, rather than
> explicit index names. This will bypass SOME of the problems.
> But it is far from perfect.
>
The most significant nasty with index hints is that you
have to name the index - and occasionally people decide
to rename, or drop, or combine indexes so that the name
in a hint no longer refers to a specific index.
So it's worth knowing that in 10g, you can specify an index
by 'describing' it. e.g.
/*+ index(tab1 tab1(col1, col2)) */
Meaning 'use an index starting with (col1, col2).
Noons
> I think I've also said "you need to tell the optimizer the
> truth, even if you have to lie to do so". Apologies for
> the whimsy.
Hehehe! Touche!
;)
> The most significant nasty with index hints is that you
> have to name the index - and occasionally people decide
> to rename, or drop, or combine indexes so that the name
> in a hint no longer refers to a specific index.
Akshally, beg to differ. If you type the hint like this:
/*+ INDEX(TAB1) */
the CBO will pick the most suitable index for the columns
used in your predicate (assuming some are indexed, of course)
without you having to explicitly name the index. At least in
8i and 9i. Of course if you change the indexed columns to some
not in your predicate, the hint will either be inactive or it
will FTS anyway.
> by 'describing' it. e.g.
> /*+ index(tab1 tab1(col1, col2)) */
THAT, is handy! Thanks.
Jonathan Lewis
Sorry,
Failed to state my intention precisely enough.
If you use just the index(tab_alias) hint, the optimizer
will pick whichever index it thinks is the most
appropriate index. This won't necessarily be
the one you know is the best choice. In the case
of the OP, the query does an FTS when unhinted:
With just an index(tab_alias) the optimizer might
still choose to ignore the index the OP wanted it to
use and use an index that makes things even worse
than an FTS.
The effect of just /*+ index(tab_alias) */ is also a little
unstable as you upgrade. For example, it MIGHT
make oracle choose the right index in 8i, but then
choose the wrong index in 9i because the wrong
index can be used with a skip_scan.
My pet comment on hints is that if you're going to
use them at all, you should be as accurate as possible
in describing EXACTLY what you want the optimizer
to do - otherwise it may find a way to obey the hints
and still do something appalling.
(Question: why does the spell-checker suggest
"unhinged" for "unhinted"
is this Microsoft trying to tell me something about
the Oracle optimizer).
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
"Noons" <wizo...@yahoo.com.au> wrote in message
news:422ecbce$0$31490$5a62...@per-qv1-newsreader-01.iinet.net.au...
Noons
> in describing EXACTLY what you want the optimizer
> to do - otherwise it may find a way to obey the hints
> and still do something appalling.
Yup, very much my experience as well.
:)
> (Question: why does the spell-checker suggest
> "unhinged" for "unhinted"
> is this Microsoft trying to tell me something about
> the Oracle optimizer).
I get the same with Thunderbird. Heck, it doesn't
even like its *own* name! Guess spell-checkers
can't take a hint, eh?
--
Cheers
Nuno Souto
in sunny Sydney, Australia
wizo...@yahoo.com.au.nospam
Niall Litchfield
> So it's worth knowing that in 10g, you can specify an index
> by 'describing' it. e.g.
> /*+ index(tab1 tab1(col1, col2)) */
> Meaning 'use an index starting with (col1, col2).
This was what I was hinting at (sorry) with my earlier comment. I'm
interested if it means 'starting with col1,col2' or 'including both col1 and
col2 with one of them as a leading column' or 'any usable index that
includes col1 and|or col2' - I'm sure this is all documented somewhere.
In addition I'd be interested in any bugs/usage oddities (bitmap indexes)
etc that people have come across with this syntax.
I guess what I'm saying is that the feature sounds very attractive, but I
wonder what its quirks are.
Jonathan Lewis
I don't think it's documented - but I may be out of date.
I have a few test cases with 10.1.0.1 that I haven't yet
re-run on 10.1.0.3:
index i1 (n1, n2)
The index was used for a particular query if
put in the hint:
/*+index (t1 t1(n1)) */
but not if the hint was
/*+index (t1 t1(n2, n1)) */
So it looked like:
partial specification of correctly ordered leading edge
Looking at my hint, though, it could be that the comma
is not supposed to be there. So when I re-install 10g,
I'll run a more thorough set of tests.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005
"Niall Litchfield" <niall.li...@dial.pipex.com> wrote in message
news:422f4e8e$0$10946$cc9e...@news-text.dial.pipex.com...