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
"Mark Townsend" <markbtownsend_at_attbi.com> wrote in message
news:yEohb.724947$uu5.120144_at_sccrnsc04...
> Jonathan Lewis wrote:
> > But how are Profiles different from Outlines ?
>
> Profiles are a set of uber-statistics (and other additional
information)
> that allow the optimizer to better optimize the particular SQL
statement
> next time it sees it.
>
> So for instance, correlations between different specific data values
is
> something that may be stored in a profile (Make = 'Ford',
> Model="Taurus", for example).
>
Now wouldn't it have been nice if Oracle had coded up a nice
static dbms_stats.correlate (tab1 col1, col2) still not perfect,
but it avoids the combinatorial size of collecting all correlation
co-efficients.
I'm still checking, but do profiles store generic correlation
information, or do they store the same information once
for every use of a particular table in each different SQL
statement ? I suspect it is the latter.
> Note that you typically can't collect them beforehand, because the
> possible permutations could get very high very quickly, and you
opnly
> want to collect the ones that are actually used - so you really need
a
> 'bad' SQL statement in your workload (or a set of SQL statement taht
> makes up a bounded tuning set) to identify that further analysis and
> optimization may be beneficial.
>
> They are also not 'frozen' like an outline.
>
I haven't put the bits together yet, but I have an unpleasant
feeling that you are telling me I really can run the advisor
in pseudo-real time to update profiles and make execution
paths change dynamically every 30 minutes if I want to.
Bye-bye deterministic trouble-shooting ! Why was performance
slow yesterday afternoon - I don't know, the execution plans
were different.
> The cost discussion is interesting - the general belief is that
> capturing and comparing the performance automatically to identify
the
> bad SQL statements (ADDM) is the cost, Profiles are the payback.
>
Paying the cost on the side-lines is fine - and I really like the
fact that ADDM works against the collected information of a
proper payload. But it brings me back to the original point,
which is that Outlines (can) also guarantee you the correct
execution path for a given SQL statement, but there is a
cost associated with USING them inappropriately - will
Profiles have the same (potential) problem.
I think some of the advice I've been getting on test cases
is terrific, by the way. The whole thing looks very promising.
Received on Fri Oct 10 2003 - 02:57:33 CDT
