Re: What is expected behavior in v$session_longops for simple update

p.santos000_at_gmail.com wrote:
> 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

Are you using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS? http://www.psoug.org/reference/dbms_applic_info.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org