Re: Never seen this line in an execution plan before

Have you been given a workaround ?
If not you could try setting "_and_pruning_enabled" = false - it MIGHT be relevant.

The KEY(I) to KEY(AP) thing MIGHT be linked to the finer granularity you get from moving from 32 partitions to 128 partitions - something along the lines of need (say) partitions 1 and 5 when you have 32 partitions but this turns into two partition ranges e.g. 1-3 and 21-22 when every partition splits into 4.

Regards
Jonathan Lewis

On Fri, Aug 14, 2020 at 2:43 PM <Jay.Miller_at_tdameritrade.com> wrote:

> Never mind, turns out to be an unpublished Oracle bug - Bug 29665260
> <https://support.oracle.com/epmos/faces/BugDisplay?id=29665260&parent=SrDetailText&sourceId=3-23781833841>
>
>
>
>
> They still didn’t explain the PARTITION REFERENCE AND though 😊.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *dmarc-noreply_at_freelists.org
> *Sent:* Friday, August 14, 2020 9:13 AM
> *To:* jlewisoracle_at_gmail.com
> *Cc:* oracle-l_at_freelists.org
> *Subject:* RE: Never seen this line in an execution plan before
>
>
>
> He just reported back that the pstart/pstop did move to KEY(AP) but that
> the number of hash partitions changed from 32 to 128 so it’s still a power
> of 2
>
>
>
>
>
> *From:* Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Sent:* Thursday, August 13, 2020 3:29 AM
> *To:* Miller, Jay <Jay.Miller_at_tdameritrade.com>
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *Subject:* Re: Never seen this line in an execution plan before
>
>
>
>
>
> I've not seen exactly this operation before, but I'll take a guess.
>
>
>
> You didn't show the pstart/pstop columns of the plan but I'm going to
> guess that they changed from KEY(I) to KEY(AP), and I'm going to guess that
> the increase in hash partitions means you've moved from a power of 2 to a
> number of partitions that hasn't yet reached the next power of 2.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Thu, Aug 13, 2020 at 12:04 AM Redacted sender Jay.Miller for DMARC <
> dmarc-noreply_at_freelists.org> wrote:
>
> A fellow DBA asked me to take a look at this. He had increased the number
> of hash partitions in a table in our performance testing environment. The
> plan changed and performance got much, much worse (all in cpu usage).
>
>
>
> What is weird is that the only thing in the plan that changed was the line
> PARTITION REFERENCE ITERATOR became PARTITION REFERENCE AND. I’ve never
> heard of PARTITION REFERENCE AND. Googling and searching Metalink did not
> produce any hits. We have a ticket open with Oracle but they aren’t
> answering the question of what the significance is of that difference so I
> thought I’d throw it out to the collective wisdom of the freelisters.
> Anyone know what this line means?
>
>
>
> Oracle 12.2.0.1
>
> RH Linux 6.10
>
>
>
>
>
>
>
> Here are the old and new plans:
>
>
>
> Pred Op Par.
> #Sib
> Query Block ms spent in Consistent Rowsource Real #rows
> Est. rows Opt. Card. Current Physical Physical Memory
> Workarea Optimizer
>
> #Col ID ID ling Row
> Source
> name this operation gets starts
> returned total misestimate gets read blks write blks
> used (MB) Passes Cost
>
> ----- ----- ----- -----
> ------------------------------------------------------------------------
> -------------------- -------------- ---------- ---------- -----------
> ------------- --------------- ---------- ---------- ----------
> ------------- ------------- -----------
>
> 0 SELECT
> STATEMENT >>> Plan
> totals
> >>>
> 202
>
> F 1 0 1
> FILTER
> SEL$1
>
> 2 1 1 INLIST ITERATOR
>
> * 3 2 1 PARTITION REFERENCE
> ITERATOR
> *
> 202
>
> 4 3 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED
> [ORDER_ACTIVITY]
> SEL$1
> 202
>
> A#1 5 4 1 INDEX RANGE SCAN
> [ORDER_STRATEGY_ORDER_ACTIVI_FK]
> SEL$1
> 58
>
> 6 1 2 SORT
> AGGREGATE SEL$2
>
> 7 6 1 PARTITION REFERENCE
> SINGLE
> 7
>
> F 8 7 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED
> [ORDER_ACTIVITY] SEL$2
> 7
>
> A#1 9 8 1 INDEX RANGE SCAN
> [ORDER_STRATEGY_ORDER_ACTIVI_FK] SEL$2
> 4
>
>
>
>
>
>
>
> Pred Op Par.
> #Sib
> Query Block ms spent in Consistent Rowsource Real #rows
> Est. rows Opt. Card. Current Physical Physical Memory
> Workarea Optimizer
>
> #Col ID ID ling Row
> Source
> name this operation gets starts
> returned total misestimate gets read blks write blks
> used (MB) Passes Cost
>
> ----- ----- ----- -----
> ------------------------------------------------------------------------
> -------------------- -------------- ---------- ---------- -----------
> ------------- --------------- ---------- ---------- ----------
> ------------- ------------- -----------
>
> 0 SELECT STATEMENT
> >>> Plan totals
> >>>
> 364
>
> F 1 0 1 FILTER
> SEL$1
>
> 2 1 1 INLIST ITERATOR
>
> * 3 2 1 PARTITION REFERENCE
> AND
> *
> 364
>
> 4 3 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED
> [ORDER_ACTIVITY] SEL$1
> 364
>
> A#1 5 4 1 INDEX RANGE SCAN
> [ORDER_STRATEGY_ORDER_ACTIVI_FK]
> SEL$1
> 103
>
> 6 1 2 SORT
> AGGREGATE SEL$2
>
> 7 6 1 PARTITION REFERENCE
> SINGLE
> 7
>
> F 8 7 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED
> [ORDER_ACTIVITY]
> SEL$2
> 7
>
> A#1 9 8 1 INDEX RANGE SCAN
> [ORDER_STRATEGY_ORDER_ACTIVI_FK]
> SEL$2
> 4
>
>
>
>
>
> Predicate information and outline hints are identical between the 2 plans.
>
>
>
>
>
> Thank you!
>
> Jay Miller
>
>

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