A-Rows in Explain Plan

Question

I'm trying to understand, why the number of rows the A-rows column of the explain plan (query executed with the hint /*+ GATHER_PLAN_STATISTICS */) gives me a result that is very far from being real.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 163 |                    TABLE ACCESS FULL            | LU_MY_TABLE                 |  29568 |   1017 |     31M|00:00:03.51 |    1094K|      0 |      0 |       |       |          |

It seems that there is a table access full on LU_MY_TABLE, but if i do a simple select count(*) from LU_MY_TABLE the result is 10169...

Could some one help me in understanding A-Rows?

Thanks and regards. Marco

Answer 1

A-Rows is the total number of rows produced by all starts of that operation. But E-Rows is an estimate of the number of rows produced by a single start of an operation. Your previous comment is correct - you probably want to divide A-Rows by Starts to determine if the cardinality estimate is good.

In your example, Oracle estimated 1017 rows per operation run. But the actual rows per operation run was 31M (A-Rows) / 29568 (Starts) = 1048 (actual rows per start). Those numbers are very close.

(Although I don't understand why they are so different than the actual number of rows in the table, 10169. There is no * next to the Id in the execution plan so there is no obvious filtering on that table. But maybe some other operation is limiting results. Either way, this is enough information to imply that Oracle is estimating this operation well so maybe the problem is with another step.)

Test Schema

Create a table with 10 rows, then run a query that does a count on the table 10 times.

drop table test1 purge;
create table test1(a number);
insert into test1 select level from dual connect by level <= 10;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

select /*+ gather_plan_statistics */ (select count(*) from test1 where a > b)
from (select - level b from dual connect by level <= 10);

select * from table(dbms_xplan.display_cursor(format => 'allstats last'));

Results:

The A-Rows in Id 2 is 100. This is 10 rows per full table scan multiplied by 10 runs.

(The plan below was slightly modified to fit the screen.)

Plan hash value: 2073232735

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |     10 |00:00:00.01 |
|   1 |  SORT AGGREGATE               |       |     10 |      1 |     10 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL           | TEST1 |     10 |      1 |    100 |00:00:00.01 |
|   3 |  VIEW                         |       |      1 |      1 |     10 |00:00:00.01 |
|   4 |   CONNECT BY WITHOUT FILTERING|       |      1 |        |     10 |00:00:00.01 |
|   5 |    FAST DUAL                  |       |      1 |      1 |      1 |00:00:00.01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A">:B1)