A-Rows and DML Statements

Today’s post is dedicated to the Metalink SR identified by the number 6468252.994. I know, this number says nothing to you. For me, however, it’s a very well known number. The reason is quite simple… Even if I open this SR almost two years ago (to be precise, September 5, 2007), it was closed few days ago. By far the most long-lasting SR I even experienced.

Let me explain why I opened it.

When assessing execution plans I like to use DBMS_XPLAN or, when necessary, to directly look at views like V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL. I like them because they provide a lot of information about what’s going on. In other words, they help me avoiding guesswork as much as possible. One of the most interesting information they provide is the number of rows returned by a given operation. DBMS_XPLAN provides this information in the column “A-Rows”. Be careful to not confuse it with the columns “Rows” and “E-Rows”. While “A-Rows” shows the actual number of rows, the other two shows the estimated number of rows. So far, so good.

What I don’t like about the column “A-Rows” (or the underlying columns LAST_OUTPUT_ROWS in the V$ views), is that for the operations modifying a table 0 is shown. By the way, according to the documentation it is not a bug. In my book I point out this behavior at page 233. For example, as the following SQL statements show, even if 14 rows are modified the value of “A-Rows” for the operation UPDATE (Id=1) is 0:

SQL> UPDATE /*+ gather_plan_statistics */ scott.emp SET sal = sal * 1.15;

14 rows updated.

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last'));

SQL_ID  4cs72g2hp6j67, child number 0
-------------------------------------
UPDATE /*+ gather_plan_statistics */ scott.emp SET sal = sal * 1.15

Plan hash value: 1494045816

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:00.01 |      25 |
|   1 |  UPDATE            | EMP  |      1 |        |      0 |00:00:00.01 |      25 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------

Back to the SR. What I tried to do with the SR is to convince Oracle that it would be much better to have the information about the number of modified rows. Unfortunately, they confirmed that the current behavior is the best one. My guess is that doing such a modification is not trivial and, therefore, they decided not doing it. In fact, also the SQL trace files have a similar behavior. For example, via SQL trace, the execution plan for the UPDATE statement shown before is the following:

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  EMP (cr=7 pr=0 pw=0 time=0 us)
     14   TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=56 card=14)

It is also interesting to note that while analyzing the code (probably for checking whether the change I proposed was doable) a developer discovered a bug. Based on the information I received (see bug number 6410147) it seems that in some situation the value of “A-Rows” is different than 0. But, since it is a bug, they fixed it.

Update 2009-08-03: part 2 of this post is available here.

3 Comments

  1. Timur Akhmadeev's Gravatar Timur Akhmadeev
    July 31, 2009    

    Christian,

    thanks for the info. I guess the most difficult part associated with the change was handling statement restarts. Since restarts might happen during UPDATE which result in partial rollbacks one or more times per execution, it is no longer possible to do just an increment of associated runtime statistic in fixed views on call to update subroutine.
    One more critical case where Oracle does not provide runtime execution statistics is PX:

    --------------------------------------------------------------------------------------
    | Id  | Operation                 | Name     | Starts | E-Rows | A-Rows |   A-Time   |
    --------------------------------------------------------------------------------------
    |   1 |  SORT AGGREGATE           |          |      1 |      1 |      1 |00:00:04.57 |
    |   2 |   PX COORDINATOR          |          |      1 |        |      2 |00:00:04.55 |
    |   3 |    PX SEND QC (RANDOM)    | :TQ10001 |      0 |      1 |      0 |00:00:00.01 |
    |   4 |     SORT AGGREGATE        |          |      0 |      1 |      0 |00:00:00.01 |
    |*  5 |      HASH JOIN            |          |      0 |     10M|      0 |00:00:00.01 |
    |   6 |       PX BLOCK ITERATOR   |          |      0 |  10000 |      0 |00:00:00.01 |
    |*  7 |        TABLE ACCESS FULL  | T        |      0 |  10000 |      0 |00:00:00.01 |
    |   8 |       PX RECEIVE          |          |      0 |  10000 |      0 |00:00:00.01 |
    |   9 |        PX SEND BROADCAST  | :TQ10000 |      0 |  10000 |      0 |00:00:00.01 |
    |  10 |         PX BLOCK ITERATOR |          |      0 |  10000 |      0 |00:00:00.01 |
    |* 11 |          TABLE ACCESS FULL| T        |      0 |  10000 |      0 |00:00:00.01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("T1"."ID"="T2"."ID")
       7 - access(:Z>=:Z AND :Z< =:Z)
      11 - access(:Z>=:Z AND :Z< =:Z)
    
  2. July 31, 2009    

    Hi Timur

    Yep, execution plans with PX are even worse ;-) But, honestly, aggregating execution statistics coming from several processes is also not always easy or even meaningful. For example, while it is not a problem to provide the total number of rows returned by a given PX operation (a sum would be ok), what to do with statistics like “A-Time”? I mean, neither an average nor a sum would be good…

    Cheers,
    Chris

  3. Timur Akhmadeev's Gravatar Timur Akhmadeev
    July 31, 2009    

    >I mean, neither an average nor a sum would be good…
    Well, sum or sum and avg would be better than nothing.

  1. Blogroll Report 24/07/2009 – 31/07/2009 « Coskan’s Approach to Oracle on August 1, 2009 at 02:54

Leave a Reply

Your email address will not be published. Required fields are marked *