RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
- From: Xiang Rao <xiang_rao@xxxxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <christopher.taylor2@xxxxxxxxxxxx>, "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>
- Date: Thu, 6 Jun 2013 12:15:04 -0400
Chris,
I have observed similar issues from some of our databases, for CTAS statements
(Microstrategy reports). The tables are either range partitioned by multi
columns or list, and parallel operations are used. For one case with list
partition, the query uses sub query to specify partition key lists. I have been
able to solve this one using PRECOMPUTE_SUBQUERY hint.
Thanks.
Xiang
> From: Christopher.Taylor2@xxxxxxxxxxxx
> To: mohamed.houri@xxxxxxxxx
> CC: oracle-l@xxxxxxxxxxxxx
> Date: Thu, 6 Jun 2013 10:31:11 -0500
> Subject: RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
>
> Well, complete partition pruning definitely did not occur. (See my email
> about running the trace file through orasrp - multiple partitions were
> touched/checked/scanned for the partitioned table in question.
> It threw me off that after execution it still said KEY for the execution plan.
>
> (Unless you're telling me that every partition will be touched in a partition
> pruning scenario anyway which I don't *think* would happen but perhaps it
> might)
>
> Chris
>
> From: Mohamed Houri [mailto:mohamed.houri@xxxxxxxxx]
> Sent: Thursday, June 06, 2013 10:26 AM
> To: Taylor Christopher - Nashville
> Cc: ORACLE-L
> Subject: Re: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
>
> The partition pruning did occur in this case. This is just an indication that
> during the parse time the optimizer couldn't know what is the exact partition
> to prune. This happens when you are using partition keys as bind variable
>
> Best regards
>
> Mohamed Houri
> www.hourim.wordpress.com<http://www.hourim.wordpress.com>
>
> 2013/6/6
> <Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
> Env: 10.2.0.4 RAC Linux 64-bit
> I've got an explain plan with Pstart/Pstop with "KEY" instead of partition
> numbers. I've been reading
> http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
> (The Oracle Optimizer Explain the Explain Plan) and it explains that KEY is
> used when Oracle believes the execution will dynamically partition prune.
>
> So I executed my statement and traced it (of course) and still the explain
> plan shows KEY KEY and running it through orasrp shows that many partitions
> were scanned (perhaps not all of them however - I haven't checked).
>
> So, is it safe to say that *after* execution if the plan has KEY KEY then
> partition pruning did not occur?
>
> Thanks!!
>
> Chris Taylor
> Oracle DBA
> Parallon IT&S
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> Bien Respectueusement
> Mohamed Houri
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
