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
