Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
A second A-Row column for input rows in the traces

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.
Comments
-
-
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 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
-
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
-
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
-
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
-
I think this is a great idea!! How do I "upvote" for this??