| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Explain Plan vs Actual Execution Plan
I have another suggestion (or two) as to why the execution plan may have
changed:
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"
<ktoepke_at_tuningo To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
racle.com> cc:
Sent by: Subject: RE: Explain Plan vs Actual Execution Plan
root_at_fatcity.com
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.
>>> Brad.Odland_at_qtiworld.com 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: Brad.Odland_at_qtiworld.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: DLANDRUM_at_zalecorp.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
INET: ktoepke_at_tuningoracle.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
Received on Sun Mar 23 2003 - 21:42:41 CST
 |
 |