Oracle Scratchpad

May 5, 2016

E-rows / A-rows

Filed under: Execution plans,Oracle — Jonathan Lewis @ 1:26 pm BST May 5,2016

A recent post on the OTN database forum reminded me how easy it is to forget to keep repeating a piece of information after the first couple of hundred times you’ve explained it. No matter how “intuitively obvious” it is for one person, it’s new to someone else.

Here’s an execution plan that raised the question that prompted this note – it comes from calling dbms_xplan.display_cursor() with the ‘allstats last’ format option after enabling rowsource execution statisics (using hint gather_plan_statistics, or setting parameter statistics_level to all, or setting hidden parameter “_rowsource_execution_statistics” to true):

| Id  | Operation                             | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
|   0 | SELECT STATEMENT                      |                        |      1 |        |   1989 |00:00:04.96 |    9280 |    897 |
|   1 |  NESTED LOOPS OUTER                   |                        |      1 |   2125 |   1989 |00:00:04.96 |    9280 |    897 |
|   2 |   NESTED LOOPS OUTER                  |                        |      1 |   2125 |   1989 |00:00:04.93 |    9271 |    895 |
|   3 |    NESTED LOOPS OUTER                 |                        |      1 |   2125 |   1989 |00:00:00.03 |    5732 |      0 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH |                        |      1 |   1989 |   1989 |00:00:00.01 |       0 |      0 |
|*  5 |     TABLE ACCESS BY INDEX ROWID       | TABLE1                 |   1989 |      1 |   1178 |00:00:00.03 |    5732 |      0 |
|*  6 |      INDEX RANGE SCAN                 | IDX_TABLE1             |   1989 |      2 |   2197 |00:00:00.02 |    3545 |      0 |
|   7 |    TABLE ACCESS BY INDEX ROWID        | TABLE2                 |   1989 |      1 |   1178 |00:00:03.26 |    3539 |    895 |
|*  8 |     INDEX UNIQUE SCAN                 | IDX_TABLE2_PK          |   1989 |      1 |   1178 |00:00:03.25 |    2359 |    895 |
|   9 |   TABLE ACCESS BY INDEX ROWID         | TABLE3                 |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |
|* 10 |    INDEX UNIQUE SCAN                  | IDX_TABLE3_PK          |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |

Many people have heard about “tuning by cardinality feedback” (a term first used, I think, by Wolfgang Breitling many years ago), and it’s fairly common knowledge that this requires you to compare the predicted (estimated) E-rows with the actual A-rows generated by a line of the plan. A critical detail that often fails to appear when this point is being explained is that there is a very important difference between the meaning of E-rows and A-rows. Although this is a point I make very clear in my presentations on how to read execution plans, it was only when I found myself explaining it to Tony Hasler a couple of years ago [ed: see comment 3 – at least 6 years ago!] that I realised that it’s not commonly known and that, in casual conversation, it’s easy to overlook the fact that it’s something that you might need to mention explicitly. So here’s the critical detail:

  • E-Rows is the number of rows the optimizer is expecting the operation to produce each time it is called
  • A-Rows is the cumulative number of rows summed across all calls to the operation

As a consequence of this difference any comparison you make should not be comparing A-Rows with E-rows, it should be comparing A-Rows with E-Rows * Starts so, for example,  operation 6 in the plan above (index range scan idx_table1) shows a reasonable estimate because 1,989 starts at 2 rows per start is fairly close to the final 2,197 rows produced (especially when you consider that there’s a lot of integer rounding going on to display the values).



  1. Most excellent, JL! line 8 is also interesting line showing unique scan with starts greater than actual.

    Comment by rsiz — May 5, 2016 @ 7:01 pm BST May 5,2016 | Reply

    • Mark,

      Thanks for the comment.
      It’s quite surprising how much one could say about even a very simple execution plan once you start to look closely.

      In this case “Rows < Starts" can be explained/justified by the outer join.

      More significant, perhaps, might be the comparison of operations 5 and 6. Because of the outer joins from the rowsource in line 4 (which produced 1989 rows) both operations had to start 1,989 times – but the index range scan produced a total of 2,197 rowids, while the table access produced a total of only 1,178 rows. This tells us we HAD to visit the table 2,197 times but threw away 1,019 rows because of a filter predicate we applied after the reached the table – so we have a handle on how much benefit we might get from adding and extra column to the index.

      At the same time we note that that particular join seems to be to a well buffered table and index, which suggests that the performance threat of the throwaway might be ignorable – especially when compared to the work we do for operation 8 (which makes me wonder if there's something inappropriate about the index definition).

      Comment by Jonathan Lewis — May 6, 2016 @ 9:32 am BST May 6,2016 | Reply

  2. It’s also important to remember that any operations executed by a parallel slave will not count toward the a-rows when using ‘allstats last’. ‘allstats all’ will give you the information from all executions of the cursor including parallel slaves (and any other executions which had row source level statistics enabled) .

    Comment by exploc — May 5, 2016 @ 7:16 pm BST May 5,2016 | Reply

    • exploc,

      Thanks for the comment.

      That, in itself, is another little demonstration of how easy it is to NOT say some of the little things that might be very important. In this case a failure to make any comment about the fact that the note was implicitly about a serial query could lead to someone being very confused by trying the same call on a parallel query and seeing lots of zeros.

      The ‘all’ option, by the way, isn’t necessary; ‘allstats’ on its own sums across all executions and that captures the parallel workload; adding ‘all’ means you get the qb_name/alias information and the projection information as well, with the E-bytes and E-time columns added to the plan making it even wider; on the other hand you this does lose the TQ, IN/OUT, and DISTRIBUTION columns from the parallel plan so the nicest formatting option for most people is likely to be ‘allstats parallel’.

      Your comment prompted me to check if I’d ever mentioned the significance of ‘allstats all’ and parallel plans before – which led me to a note I wrote nearly 10 years ago where I first explained the E-rows/A-rows difference.

      Comment by Jonathan Lewis — May 6, 2016 @ 9:41 am BST May 6,2016 | Reply

  3. Hi Jonathan,

    You must have explained this to me more than a couple of years as I blogged about it in January 2010!

    Are we both getting old :-)

    One other little point that may be worth mentioning (if it isn’t too much of a digression) is that STARTS or LAST_STARTS when applied to a BUFFER SORT or SORT JOIN operation reflects the number of times the sorted data is probed, not the number of times the data is sorted! Similarly, OUTPUT_ROWS/LAST_OUTPUT_ROWS/A-ROWS reflects the number of rows returned from the probes not the number of rows sorted.


    Comment by tonyhasler — May 6, 2016 @ 8:09 pm BST May 6,2016 | Reply

    • Tony,

      Six years! Where did they go? I’ve just checked your book and even that was published two years ago (if anyone had asked I’d have said about one year).

      It would be interesting to see how many other “surprises” people can spot in comparing E-rows and A-rows.

      The SORT JOIN example introduces another “philosophical” point to the discussion, or maybe a reminder about how and when to pass on bits of information – if I explain point A, and then explain point B, how often do I need to include an explicit explanation of point C when point C is the “obvious” consequence of points A and B ? (The answer, I think, is probably “depends on context, but quite often”).

      If you know that the SORT JOIN is actually “probe an in-memory sorted data set, but acquire it and sort it if it’s not already in memory” ( ), and know about the “A-Rows = starts * E-Rows” target then the conclusion is self-evident; but sometimes it still needs to be stated explicitly.

      Comment by Jonathan Lewis — May 7, 2016 @ 10:02 am BST May 7,2016 | Reply

  4. […] indexes making the invisible one visible and vice versa and repeats the query. I’ve enabled rowsource execution statistics and pulled the execution plans from memory to make sure I don’t get fooled by any odd […]

    Pingback by Descending bug | Oracle Scratchpad — August 22, 2018 @ 1:21 pm BST Aug 22,2018 | Reply

  5. […] when we run the query we want to see the session statistics, pick up the actual execution plan with rowsource execution statistics, and enable the 10032 and 10033 (sort) traces. So let’s fold the query into a longer script, […]

    Pingback by Quiz Night | Oracle Scratchpad — August 30, 2018 @ 10:14 am BST Aug 30,2018 | Reply

  6. […] the Starts and the A-rows: (reminder: for comparison, we expect A-rows to be approximately E-rows * Starts) for both operations we see 33,459 starts and 21,808 rows. The index range scans return (on […]

    Pingback by Case Study | Oracle Scratchpad — September 30, 2018 @ 7:59 pm BST Sep 30,2018 | Reply

  7. […] join to 4 rows in t1 thanks to the simple sequential nature of the data. In 12.2 the query, with rowsource execution stats enabled, completed in 1.48 seconds. In the query, with rowsource execution stats off, took […]

    Pingback by Band Join 12c | Oracle Scratchpad — November 8, 2018 @ 9:34 am GMT Nov 8,2018 | Reply

  8. […] predicted data volumes with the actual data volumes from an execution plan – and I wrote a short note about how to make that comparison last week; and you’re probably familiar with making a call to dbms_xplan.display_cursor() […]

    Pingback by dbms_xplan | Oracle Scratchpad — December 28, 2019 @ 6:30 pm GMT Dec 28,2019 | Reply

  9. […] */ and finally in a genuine environment. In all three cases I enabled rowsource execution stats (‘alter session set statistics_level = all’) and pulled the plans from memory – […]

    Pingback by Recursive WITH upgrade | Oracle Scratchpad — July 10, 2020 @ 4:19 pm BST Jul 10,2020 | Reply

  10. […] the function get reported in the execution plan when you trace the query or look at the in-memory rowsource execution statistics. Let’s take a look at a simple […]

    Pingback by Sorting | Oracle Scratchpad — December 2, 2021 @ 10:47 am GMT Dec 2,2021 | Reply

  11. I’m impressed your blogs and your books.
    I have a question that I couldn’t be solved.
    Do you have any contents of improving x$ views?
    I use the log miner in the Oracle.
    But, our database makes much more dml transactions than the log miner can read.
    I want to know how can I improve the log miner’s performance.
    Please help me.

    Comment by Lisa — September 11, 2022 @ 2:12 am BST Sep 11,2022 | Reply

    • Lisa,

      It’s been a long time since I did anything with the log miner, so I doubt if I can help. The key detail about performance, I think, is that a query against v$logmnr_contents has to read through all the log files that you’ve added to the list so it’s best if you know exactly what you’re looking for and have a good idea of when it happened so that start a session that has to scan the minimum number of log files .

      It’s worth checking what’s going on in the session (v$sesstat and V$session_event) to see if there are any clues about time being lost on unexpected activity.

      Jonathan Lewis

      Comment by Jonathan Lewis — September 12, 2022 @ 11:23 am BST Sep 12,2022 | Reply

  12. […] A common oversight when talking about comparing estimates and actuals is to forget that to mention that the E-rows is per call to the operation while A-rows is summed over all calls, so your should be thinking of A-rows = E-rows * Starts. (See this blog note) […]

    Pingback by Cardinality feedback | Oracle Scratchpad — February 7, 2023 @ 10:46 am GMT Feb 7,2023 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: