What is expected behavior in v$session_longops for simple update

Folks,
 I'm trying to determine what I should be seeing in V$SESSION_LONGOPS  for a simple update statement.

 We have a perl program that executes the following code in a loop until the number of
 affected rows = 0. This is perl and DBD:Oracle on a shared connection.

 SQL
===

  UPDATE TABLE_NAME
    SET COLUMN_NAME = NULL
  WHERE COLUMN_NAME IS NOT NULL and ROWNUM <= 500000;

 My expectation was that if I wanted to clear out this column for 2 million customers, this query
 should execute 4 times. I also expected to see 4 tablescans in V$SESSION_LONGOPS and after each FTS completes, the V$SQL.executions should increment by 1 and
SQL.rows_processed increment by 500,000.

 In my scenario, this program ran for about 12 hrs and updated 9 million records. Both
 V$SQL.executions and V$SQL.rows_processed were correct, but these values did not necessarily increment after each FTS. In some cases, after a FTS completed, a new table scan would start without the V$SQL values being updated.

 With the above statement I would expect that V$SQL values to be incremented per
 full tablescan, but I believe that in some cases, Oracle did more than 1 full tablescan
 before incrementing V$SQL .. why would this happen?

 If anyone has any ideas, please let me know.

-peter Received on Fri Dec 08 2006 - 15:01:16 CST