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<mailto: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