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

From: <p.santos000_at_gmail.com>
Date: 9 Dec 2006 20:03:07 -0800
Message-ID: <1165723387.844142.222330@j72g2000cwa.googlegroups.com>

DA Morgan wrote:
> 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

Daniel,
  No I'm not using the package you mentioned. Basically I ran repeated queries
against the V$SESSION_LONGOPS and saw that values in SOFAR increment all the way till TOTALWORK. After each loop the v$SQL.execution and rows_processed would increment .. but if a few cases this didn't happen.

The thing is that the tablescan operation took about 30 minutes. So If i'm updating 9 million records, I would have expected the process to last 9 hours. But it took 16 hrs to complete. My thought was that each tablescan would equate to 500K records being updated, but that may not be the case. I just can't understand why.

I also captured all the V$SESSTAT values for this session, and the statistic
 "user commits" matches the number of loops that I expected, but the statistic
 "table scans (long tables)" was over 50 .. which I can't understand either.

Have you ever heard of a case where oracle might have to FTS a table more than once to complete a statement similar to the one I listed?

-peter Received on Sat Dec 09 2006 - 22:03:07 CST