Forum Stats

  • 3,825,244 Users
  • 2,260,486 Discussions
  • 7,896,465 Comments

Discussions

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

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited Jan 11, 2016 6:12PM in Database Ideas - Ideas

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.

Martin PreissSturla Thorabhinivesh.jainctriebRandolf GeistRichard Harrison .Mohamed HouriManish ChaturvediRaj JamadagnicaadecarvalhoberxZlatko SirotichimmyFranck Pachotmichaelrozar17tmbeetzPravin TakpireJonathan LewisborneselulohmannAish13top.gunJagadekaraAndreas Buckenhoferkulikouskiuser12238076-KE-Rainer StenzelSven W.Toon_Koppelaars-OracleMautro PaganosensoftAparna Dutta-OracleMKJ10930279Racer I.User_5UK3OTimur AkhmadeevNenad NoveljicUser_DUSJ8Sayan MalakshinovAnton Spitsynuser9977143ngruzintsevIgorUsoltsevUser_LHB5IUser_PLTUNUser_67QPLLothar FlatzMatthiasRogelUser_ZB5W9User_7LIC8William Robertson
53 votes

Active · Last Updated

Comments

  • abhinivesh.jain
    abhinivesh.jain Member Posts: 307 Blue Ribbon

    Interesting Idea.

    Anton Spitsyn
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    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

    Sayan Malakshinov
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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
    Randolf Geist Member Posts: 2,214 Silver Trophy

    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

    berxSayan Malakshinov
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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
    Jonathan Lewis Member Posts: 9,974 Blue Diamond

    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

    William RobertsonSayan Malakshinov
  • User_KC6Y3
    User_KC6Y3 Member Posts: 57 Green Ribbon

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