RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution

From: Don Seiler <don_at_seiler.us>
Date: Thu, 6 Jun 2013 10:43:21 -0500
Message-ID: <CAHJZqBCiGkFcvw4OYC7W2pmHdpUgrFnEP3PmqmzaKfgm4yaQNA_at_mail.gmail.com>

Are you using bind variables on the partition keys? I normally see the key: key values when that happens. You don't want to use binds on partition key fields.
Don.
On Jun 6, 2013 10:33 AM, <Christopher.Taylor2_at_parallon.net> wrote:

> 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_at_gmail.com]
> 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_at_parallon.net<mailto:
> Christopher.Taylor2_at_parallon.net>>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> Bien Respectueusement
> Mohamed Houri
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 06 2013 - 17:43:21 CEST