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:

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
... big gap here with "non-feature" abbreviations
AP - adaptive plans

 

Powered by WordPress.com.