I have another suggestion (or two) as to why the execution plan may have
changed:
* Bind variables - these often cause grief. Of course, choosing a
different execution plan may be correct behaviour based on what the
histograms say.
* FGAC (Fine Grained Access Control) - this caught me out once. We had
fgac (or rls - row level security - as it was often called on our project)
enabled and the execution plan includes the effects of the access control.
You could grab a query executed by a different user and ask for an explain
plan under your own schema and get a different result. This made tuning
difficult since the access control could do all sorts of things to queries,
and it is easy to ignore when you don't see it on screen.
I guess at the end of the day it's important to remember that the
optimisor's job is to choose the best execution path for a given query at a
given moment in time. A lot of people get concerned when execution plans
change, and whilst this is sometimes with good reason it is also Oracle
simply changing to a better approach. If the plan changes and the query
takes forever to run, but if the plan changes and performance is still fine
then you typically don't have a problem.
Regards,
Mark.
"Kevin Toepke"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
racle.com> cc:
Sent by: Subject: RE: Explain Plan vs Actual
Execution Plan
[EMAIL PROTECTED]
22/03/2003 05:29
Please respond
to ORACLE-L
Yes, local statistics are the partition specific statitics. Global stats
are
on the partitioned object as a whole. Global stats are vitally important
when you are accessing more than one partition.
If you are accessing > 1 partition and you do not have global stats, then
the optimizer will use the default statistics. Very bad.
Kevin
-----Original Message-----
Brad
Sent: Friday, March 21, 2003 12:19 PM
To: Multiple recipients of list ORACLE-L
Please exlain the difference between local and global statistics.
Is this statisitics on a partition?
Brad Odland
-----Original Message-----
Sent: Thursday, March 20, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Brad, you're absolutely correct. Explain plan is what is intended, but
isn't what always happens. It probably is most of the time, but not
always.
I recently had a circumstance in which a long running job (the explain plan
of) was telling me it was reading from a big partitioned table and using
the
index that it should have been using, but a 10046 trace showed reads from a
different index and no reads from the index identified by explain plan. A
full analyze compute (using dbms_stats) later, this job works fine.
I suspect in my case the problem was with statistics. The job was
originally set up by the developer to add data each night, then analyze the
current partition. The last time global stats were gathered on this table
was last October. I suspect that over time with data loads and updates the
data distribution, etc. got skewed as compared to the global stats, so the
optimizer didn't have enough valid information to make a good choice.
This query was reading data from 5 or 6 partitions of a 54 partition table.
That's important information because a couple of weeks ago I was reading
the
"Oracle 8i Designing and Tuning for Performance" document and came across
this statement:
"Unless the query predicate narrows the query to a single partition, the
optimizer uses the global statistics. Because most queries are not likely
to be this restrictive, it is most important to have accurate global
statistics."
Pretty interesting to think about. It's gather stats global and local from
now on for me.
>>> [EMAIL PROTECTED] 03/20/03 02:24PM >>>
Hello,
Heres a question to ponder. While tuning a SQL statment for a user I
noticed
that the explain plan from SQL Analyze was not the same plan that was found
when I used OEM Top Sessions (9.2.0.1) upon executing to collect execution
stats. Database is on HP/UX 11 version Oracle 8.1.7
The stats were not "stale" yet. Monitoring is on for the tables in the
query. The query would actually never return. I suspected that the stats
were a bit off so I ran new ones and then SQL Analyze displayed a different
explain plan and the plan reteived from top sessions while the SQL was
running matched.
My question is is the Explain Plan and estimate or is the actual plan. I
suspect that when an explain plan is created it uses statistics and the
optimizer to determine the estimated plan and cost. However when the SQL is
actually executed I suspect that a different plan may be generated as
actual
execution begins....or am I just wacked.
Either way the statistics when run created a proper plan that worked fine.
But I wonder why the difference in plans...
Brad O.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Odland, Brad
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Odland, Brad
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
