Striving for Optimal Performance

In the first post about this topic I wrote: “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.”

What I forgot to mention is that depending on how you interpret the documentation, another behavior might also be seen as buggy. In fact, in the documentation you can read the following description for the column LAST_OUTPUT_ROWS of the view V$SQL_PLAN_STATISTICS: “Number of rows produced by the row source, during the last execution”. The question is: what does “produced” in this context mean?

According to the interpretation given by Oracle, for “top-level” operations (e.g. SELECT, UPDATE, DELETE, …) it’s the number of rows retrieved through a SELECT statement.

Another interpretation could be the number of rows returned through any SQL statement. And that, independently on how the rows are produced. For example, because of the RETURNING clause, the UPDATE statement in the following PL/SQL block actually returns 14 rows to the PL/SQL engine. But, as you can see, also in this case 0 is shown.

SQL> DECLARE
  2    TYPE t_emp IS TABLE OF scott.emp%ROWTYPE;
  3    l_emp t_emp;
  4  BEGIN
  5    UPDATE /*+ gather_plan_statistics */ scott.emp SET sal = sal * 1.15
  6    RETURNING empno, ename, job, mgr, hiredate, sal, comm, deptno
  7    BULK COLLECT INTO l_emp;
  8  END;
  9  /

SQL> SELECT sql_id
  2  FROM v$sqlarea
  3  WHERE sql_text LIKE 'UPDATE%RETURNING%';

SQL_ID
-------------
02x15ba008dt9

SQL> SELECT *
  2  FROM table(dbms_xplan.display_cursor(sql_id=>'02x15ba008dt9', format=>'iostats last'));

SQL_ID  02x15ba008dt9, child number 0
-------------------------------------
UPDATE /*+ gather_plan_statistics */ SCOTT.EMP SET SAL = SAL * 1.15
RETURNING EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO INTO :O0
,:O1 ,:O2 ,:O3 ,:O4 ,:O5 ,:O6 ,:O7

Plan hash value: 1494045816

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

Either these things that are returned cannot be called rows, or the current implementation is not very consistent. I already shared your opinion with you… I don’t like the current situation.