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@xxxxxxxxxxxxx> 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
