DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 16

Thread: Error in v$sql.rows_processed. 1,000,000 rows per second

Threaded View

  1. #1
    Join Date
    Jan 2006
    Location
    Denver, Colorado
    Posts
    9

    Question Error in v$sql.rows_processed. 1,000,000 rows per second

    I am running oracle 9i on a Unix box ( 32 bit )
    I have a small pl/sql procedure that uses a database link in the cursor and a commit command in the body. When I monitor the value of v$sql.rows_processed, that number increases about 1,000,000 rows per second until it reaches the maximum value for a pls_integer, 2,147,483,647 and then it becomes negative 2,147,483,647. The row count increases until it passes zero and then, +2,147,483,647. This cycle will continue based on the number of rows in the queried table (users).
    I am looking for a reason this takes place and I have four concerns. What is the actual number of rows I am processing, I do not want a cartesian situation. Does this take place in 10G. Is there a work around. Should I be concerned about the accuracy of the v$ tables.
    I posted an SR (TAR 5015075.993) with Oracle on Jan 3 but they have not offered any help.
    Thanks, Eric
    ---------------- The procedural code ------------------
    Code:
    CREATE OR REPLACE PROCEDURE update_monthly_age
    IS
       UID                           monthly_age.userid%TYPE;
       uage                          monthly_age.months%TYPE;
       utab                          INTEGER;
       dtab                          INTEGER;
       sql_string                    VARCHAR2 ( 200 ) := '';
    
       CURSOR c1
       IS
          SELECT userid,
                 age
            FROM users@pidm_ltus25d;
    BEGIN
       OPEN c1;
    
       LOOP
          FETCH c1
           INTO UID,
                uage;
    
          EXIT WHEN c1%NOTFOUND;
          uage :=   uage
                  * 12;
          sql_string :=    'update monthly_age set months = '
                        || uage
                        || ' where userid = '
                        || UID;
          utab := DBMS_SQL.open_cursor;
          DBMS_SQL.parse ( utab,
                           sql_string,
                           DBMS_SQL.v7
                         );
          dtab := DBMS_SQL.EXECUTE ( utab );
          DBMS_SQL.close_cursor ( utab );
          COMMIT;
       END LOOP;
    
       CLOSE c1;
    END update_monthly_age;
    /
    ---------------- Oracle Version Information --------------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    PL/SQL Release 9.2.0.4.0 - Production
    CORE 9.2.0.3.0 Production
    TNS for Solaris: Version 9.2.0.4.0 - Production
    LSRTL Version 9.2.0.4.0 – Production
    Last edited by gandolf989; 01-16-2006 at 01:25 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width