RE: Explain Plan vs Actual Execution Plan

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 21 Mar 2003 10:14:40 -0800
Message-Id: <24738.322727@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2EFD5.BD322D90
Content-Type: text/plain;

charset="iso-8859-1"

I'm open to abuse if I'm wrong, but I think that the intended distinction was:
let's assume table T with partitions P1 and P2 analyze table t compute statistics ; <--- global statistics

analyze table t partition (p1) compute statistics ; <--- local statistics on partition p1
analyze table t partition (p2) compute statistics ; <--- local statistics on partition p2

> -----Original Message-----
> From: Odland, Brad [mailto:Brad.Odland_at_qtiworld.com]
> 
> Please exlain the difference between local and global statistics.
> Is this statisitics on a partition? 
> 
> -----Original Message-----
> 
> 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.

------_=_NextPart_001_01C2EFD5.BD322D90
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: Explain Plan vs Actual Execution Plan</TITLE>
</HEAD>
<BODY>

I'm open to abuse if I'm wrong, but I think that the intended distinction was:
 let's assume table T with partitions P1 and P2
 analyze table t compute statistics ; <--- global statistics


analyze table t partition (p1) compute statistics ; <--- local statistics on partition p1
 analyze table t partition (p2) compute statistics ; <--- local statistics on partition p2


> -----Original Message-----
 > From: Odland, Brad [<A HREF="mailto:Brad.Odland_at_qtiworld.com">mailto:Brad.Odland_at_qtiworld.com</A>]
 > 
 > Please exlain the difference between local and global statistics.
 > Is this statisitics on a partition? 
 > 
 > -----Original Message-----
 > 
 > 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
Received on Fri Mar 21 2003 - 12:14:40 CST