10053

10053:
I thought I’d try to spend some of today catching up on old comments – first the easier ones, then the outstanding questions on Oracle Core.

The very first one I looked at was about pushing predicates, and the specific comment prompted me to jot down this little note about the 10053 trace file (the CBO trace).
In “real-life” I don’t often look at 10053 trace files because they tend to be very long and messy and usually you can see all you need from an execution plan – perhaps through SQL Monitoring, or possible with the rowsource execution stats enabled. However there are questions that can sometimes be resolved very quickly by a simple text search of a trace file; questions of the form: “why isn’t the optimizer using feature X”, for example: “why didn’t the optimizer use predicate push for this view?” If you have a question of this type, then search the trace file for the word “bypass” – you may find comments like the following:

JPPD:     JPPD bypassed: Outer query references right side of outer join
        JPPD:     JPPD bypassed: View has a single or group set function.
        JPPD:     JPPD bypassed: OLD_PUSH_PRED hint specified
        JPPD:     JPPD bypassed: User-defined operator
        JPPD:     JPPD bypassed: Push-down not enabled
        JPPD:     JPPD bypassed: View not on right-side of outer join
        JPPD:     JPPD bypassed: View contains a group by.
        JPPD:     JPPD bypassed: View contains a window function.
        JPPD:     JPPD bypassed: View contains a MODEL clause.
        JPPD:     JPPD bypassed: View contains a DISTINCT.
        JPPD:     JPPD bypassed: View contains a rownum reference.
        JPPD:     JPPD bypassed: START WITH query block.
        JPPD:     JPPD bypassed: View is a set query block.
        JPPD:     JPPD bypassed: Negative hint found
        JPPD:     JPPD bypassed: Outline does not contain hint
        JPPD:     JPPD bypassed: Base table missing statistics
        JPPD:     JPPD bypassed: Remote table
        JPPD:     JPPD bypassed: Security violation
        JPPD:     JPPD bypassed: Possible security violation.
        JPPD bypassed: Table level NO_PUSH_PRED hint.
        JPPD bypassed: Table-level hint.
        JPPD bypassed: Query block NO_PUSH_PRED hint.
        JPPD bypassed: View semijoined to table
        JPPD bypassed: Contained view has no table in fro list.
        JPPD bypassed: View contains a START WITH query block.
        JPPD bypassed: Does not contain a view.
        JPPD bypassed: Found branches of different types

According to a note that I’ve got with this list, by the way, I used “strings -a” on the Oracle executable for 10.2.0.1 to generate it. It’s probably about time I did the same for newer versions of Oracle.

Footnote:

My plans for addressing comments have been disrupted somewhat. Just as I published this note, an email holding two 10053 trace files arrived. (The author had asked before sending them, and I was sufficiently curious that I had agreed to take a quick look). So I’ve spent most of the last hour doing what I’ve just said I hardly ever to – looking at 10053 trace files.
The question was “why does this query run serially if I have a particular scalar subquery in the select list, but run parallel if I replace it with a function”.  The immediate answer, after I’d seen the query and thought about it for a bit, was: “because the manuals (10.2 – the relevant version) say that you don’t parallelize if you have scalar subqueries in the select list”; but this changed the question to: “why is it just this one scalar subquery that causes serialization when the other two scalar subqueries don’t”. Of the three scalar subqueries, only one of them cause the query to serialize.
The answer to that question is a little more subtle – and I’ll blog about it when I can find time to model the scenario.



DIGITAL JUICE