10132 (again)

If you ever enable event 10132, don’t be surprised to find some of the resulting execution plans looking a little messy, for example (from a 9i trace file):

Buffer Sorts – 2

Just a little follow-up on my earlier note on buffer sorts. The following is an extract from a a tkprof output showing the rowsource operation for a query. Note especially the  rows information. 

Resc/Resp

If you look at a 10053 trace file, you will find references to Resc and Resp (sometimes rsc and rsp) lurking in the background whenever the trace file mentions Cost. When you get to the 10gR2 trace file, you see these described in the “Legend” section as:

Bind Variables

I have made a few comments in previous articles about the use of bind variables and some of the peripheral details that can introduce surprises; and in the article on superfluous updates I made a throwaway comment about getting multiple child cursors for a single statement if you had columns of varchar2() or nvarchar2() defined to be longer than 32 bytes. It’s worth expanding on this point.

Buffer Sorts

In an earlier article I mentioned the buffer sort in a footnote; I thought I would expand a little more on what I think it does and why it appears as a buffer sort in an execution plan rather than the more traditional sort (join).

Consider the trivial script:

Bind Peeking

I’ve just received an email from someone who had been on my “masterclass”, and he had a question about one of the comments I had made, namely: “bind variable peeking is always done, even if histograms are not generated”.

They quoted a reference to an Oracle white paper which said, on page 15:

Event 10132

From 9i onwards, if you enable event 10132 in your session, then every statement you subsequently optimise will be dumped into your trace file, along with the structure of the actual execution plan that was used. For example, from an early version of 9i:

Table Order

Did you know that it is possible for the cost-based optimizer to come up with a different execution plan simply because you changed the order of the tables in the from clause.

Although this can happen, in real-life, with real data and real indexes,  it probably doesn’t happen often. Moreover, when it does happen you might not notice it because it’s likely to happen only in cases where the change doesn’t affect the performance of the query.