A second A-Row column for input rows in the traces

Martin Preiss

I think the idea has been mentioned many years ago - maybe by Jonathan Lewis in Cost Based Oracle Fundamentals, or by someone else somewhere else...

The A-Rows information displayed by different trace utilities (rowsource statistics in dbms_xplan.display_cursor and sqlmonitor) shows the number of output rows returned by a step in the execution plan: but there are situations in which it would be also of interest to know the number of input rows for a given step. A fitting example is Explain plan control - hash join running long: a hash join returns just a few rows (shown by sqlmonitor as A-Rows), but the intermediate result contains billions of rows that are excluded by a filter.

So it would be a helpful to gather this information with the rowsource instrumentation code (if this is not already done) and display it in the corresponding plans.

16.04.2015: Randolf Geist provided are more complete explanantion of the problem (and possible solutions) in his blog: Oracle related stuff: Combined ACCESS And FILTER Predicates - Excessive Throw-Away. And I also think that AE-rows (Actually Evaluated Rows) would be a good name.

abhinivesh.jain

Interesting Idea.

Randolf Geist

Hi Martin, one minor point regarding wording: I wouldn't call it "input rows" necessarily, but something like "evaluated rows" or similar. The input to the operation is always the output of the previous operation (for branches of the execution plan, for leaf data access it's of course a different story), but what would be great to see is the number of rows "processed / evaluated" within a given operation. This would even add value to simple operations like full table scans where we could see in addition to the number of rows returned by the FTS also the rows evaluated (so how many rows a FILTER operation was applied to as part of the FTS operation)

Randolf

Martin Preiss

Hi Martin, one minor point regarding wording: I wouldn't call it "input rows" necessarily, but something like "evaluated rows" or similar. The input to the operation is always the output of the previous operation (for branches of the execution plan, for leaf data access it's of course a different story), but what would be great to see is the number of rows "processed / evaluated" within a given operation. This would even add value to simple operations like full table scans where we could see in addition to the number of rows returned by the FTS also the rows evaluated (so how many rows a FILTER operation was applied to as part of the FTS operation)

Randolf

Hi Randolf, yes, "evaluated rows" would be more fitting than "input rows" (and even better than "A-Rows-2" ...)

Martin

Randolf Geist

Hi Randolf, yes, "evaluated rows" would be more fitting than "input rows" (and even better than "A-Rows-2" ...)

Martin

Hi Martin,

I've put together a blog post describing the idea in a bit more detail: Oracle related stuff: Combined ACCESS And FILTER Predicates - Excessive Throw-Away

Randolf

Martin Preiss

Hi Martin,

I've put together a blog post describing the idea in a bit more detail: Oracle related stuff: Combined ACCESS And FILTER Predicates - Excessive Throw-Away

Randolf

Hi Randolf,

thank you. I added the link to the description of the idea.

Martin

Jonathan Lewis

This idea applies across the board - an index range scan accesses rows, then filters them; it would be good know how many rows were accessed as well as (the figure we effectively see now) how many rows were left after the filter.

The hash join is interesting because of collisions:

e.g. We may have 10 rows in a bucket because 4 copies of one value from the build table belong there and 6 copies of another value.

This means when we probe we have to examine 10 rows -- we'd like to know that (it's accessed rows step 1)

Of them (let's assume) the 4 copy value survives -- we'd like to know that (it's accessed rows step 2)

Of them (let's assume) that 1 row survives -- and that's what we see in the current actuals.

Regards

Jonathan Lewis

User_KC6Y3

I think this is a great idea!! How do I "upvote" for this??