Re: Is Partitioning Used By The Optimizer?
- From: Stefan Koehler <contact@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, fmhabash@xxxxxxxxx
- Date: Wed, 8 Oct 2014 18:58:03 +0200 (CEST)
Hi Fred,
i assume a few things are mixed up here (CBO and runtime engine) or i did not
get your question right.
You can verify with a CBO trace (10053) by searching for phrase "Using
composite stats" (e.g.), if the optimizer is using global or partition based
statistics (= pruning) for its estimation.
For example the CBO also uses global stats (and not pruned / aggregated to a
specific partition range), if you have specified a range predicate (not
based on all partitions) and p_start/p_stop cover only a few valid integers (=
using literals). This results in a global stats usage with "PARTITION
RANGE ITERATOR" as well for example.
You may want to share your example with us and specify some more details what
you really want to know (cost based optimizer or runtime engine
behavior).
Best Regards
Stefan Koehler
Oracle performance consultant and researcher
http://www.soocs.de
> Fred Habash <fmhabash@xxxxxxxxx> hat am 8. Oktober 2014 um 18:07 geschrieben:
>
> How can we decide with some certainty if partitioning is or is no being used
> by the optimizer in an Oracle database?
>
> My research led me to inspect the values in
> V$SQL_PLAN.P_START|P_STOP|OPTIONS|OPERATION.
>
> Pruning is not used when ...
> 1) p_start/p_stop are null
> 2) p_start/p_stop have integers and they indicate a range that includes all
> partitions
> 3) OPERATION reveals 'ARTITION RANGE ALL'.
>
> The DB I'm looking at has 'ROW LOCATION' for p_start/stop. It is obvious
> that this indicates dynamic pruning. However, the the OPERATION column
> shows 'BY GLOBAL INDEX ROWID' for OPERATION 'TABLE ACCESS.
>
> Does this really mean that partition pruning was used? Is there any other
> empirical evidence we can produce to make such decision?
>
> Thank you ...
> ----------------------------------------
> Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
