|
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|