Re: Explain Plan vs Actual Execution Plan

FYI - Following on the point picked up by Darrell Landrum below, and my comment about global histograms in 8i, I decided to test a workaround:

    Could you export a histogram from a non-partitioned table,     then import it to a partitioned table ? If so, would a query     against the partitioned table actually use the global     histogram that had magically appeared ?

The answer to both questions was YES. So if you want Oracle 8i to make use of good histogram information at the global level, you can fake it into the system.

However, in the course of my tests, I discovered that the manual's comments that:

> > Unless the query predicate narrows the query to a single
> > partition, the optimizer uses the global statistics.

is not entirely true. If the predicate involves a column with a histogram, and Oracle decides that multiple partitions will be accessed, then the optimizer MAY use a collection of partition-level histograms to synthesize a table level histogram to estimate the cardinality of the result.

The conditions governing the optimizer's behaviour seem to be the size of the tables (number of rows, perhaps) and the number of partitions. i.e. balancing the cost of acquiring the partition-level information against the potential saving by doing a better job.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: <ORACLE-L_at_fatcity.com> Sent: 20 March 2003 23:52

>
>
> The really cute thing about the need for global statistics
> to be reasonable - a few pages further on you'll find the
> comment that in 8i you can't generate global histograms !
> (Fixed in 9i)
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> One-day tutorials:
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______April 8th
> ____UK_______April 22nd
> ____Denmark May 21-23rd
> ____USA_(FL)_May 2nd
>
>
> The three-day seminar:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____UK_(Manchester)_May
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: "Darrell Landrum" <DLANDRUM_at_zalecorp.com>
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: 20 March 2003 21:28
> Subject: Re: Explain Plan vs Actual Execution Plan
> >
>
> > 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.
> >
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).