Oracle Scratchpad

May 14, 2014

Feature Bypass

Filed under: CBO,Oracle,Troubleshooting — Jonathan Lewis @ 1:23 pm BST May 14,2014

Here’s a little tip that might be helpful occasionally when you’re trying to work out why the optimizer transformation you were expecting isn’t appearing

If you’ve ever checked the 10053 trace (not that you would really want to do that for a complex query) you may have noticed lines like the following:

SU: SU bypassed: Remote table referenced.

So now you know that SU – Subquery Unnesting – has limitations in distributed queries.

When I first saw a line like this, it crossed my mind that it would be useful to keep a reference list of features that could be reported as bypassed, which I do through a simple unix line:

strings -a oracle | grep -i bypassed | sort -u > bypassed.txt

Unfortunately some of the results don’t report explicit reasons and you get several lines like: “CVM: CVM bypassed: %s.” In cases like this you may find a list of the possible strings that Oracle can substitute for “%s” close by in the (fairly large) output from a call to “strings -a oracle >strings.txt”.

If you need a reference for the various short codes for transformations you can find most of them near the top of the 10053 trace, looking like this (updated to 12.2.0.1):

Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
AAT - Approximate Aggregate Transformation
ORE - CBQT OR-Expansion
LORE - Legacy OR-Expansion
... big gap here with "non-feature" abbreviations
AP - adaptive plans

VT and AP appeared in 12.1

AAT, ORE, and LORE appeared in 12.2

The rest of them were all available by 11.2.0.4

It is a little unfortunate that there are a few other “bypassed” clues in the executable that don’t appear in the legend:

  • BJ – Bushy Join
  • CSE – Common Sub-expression
  • DCL – Decorrelation of lateral view
  • GBP – Group by placement
  • JE[V2] – Join Elimination version 2 ?
  • NN – ??
  • PJE – Partial Join Evaluation
  • PPU – (Filter) Predicate Pull-up
  • SHRD – Sharding
  • SVM – Simple View Merge
  • TE – Table expansion
  • VE – Vector Encode
  • ZM – Zone Map

 

Powered by WordPress.com.