Re: Is Partitioning Used By The Optimizer?
Date: Wed, 8 Oct 2014 18:58:03 +0200 (CEST)
Message-ID: <1264192958.263005.1412787483347.open-xchange_at_app09.ox.hosteurope.de>
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_at_gmail.com> 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)
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 08 2014 - 18:58:03 CEST