Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Explain Plan vs Actual Execution Plan

RE: Explain Plan vs Actual Execution Plan

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 24 Mar 2003 14:42:41 +1100
Message-Id: <24756.322833@fatcity.com>


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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).

--

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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).
--

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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).

--

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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US