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

You are defining correct behavior for hexidecimal numbers. It seems odd that you would be processing billions and billions of rows. I would bet cartesian, but I don't know your table structure.

Can you run this through sql_trace and tkprof?

Thanks for your suggestion. In my mind there is no doubt that I am NOT processing that many rows. I have not run the query through tkprof, one reason being I do not know how to pass an entire procedure into tkprof.
If I change the state name from OHIO to TEXAS and remove the commit statement it behaves as expected. I will try the TKPROF suggestion.

If I may be permitted:

- Why are you storing age in a database? If you stored date-of-birth (or start-date if we aren't talking about duration of life) you'd never have to update it.

- Why use a PL/SQL loop when, AFAICS, you can do it with one SQL statement?

- If you must use PL/SQL, use bind variables and only parse the update statement once. And why not COMMIT outside the loop?

P.S. where do OHIO and TEXAS come into this :confused:

One thought, but you SELECT userid , age from users@pidm_ltus25d
If the userid value selected is actually a string containing the text 'userid', then your Dynamic SQL would be something like :
update monthly_age set months = 1234 where userid = userid;
which would update every record in the table.

To Gandolf989, DaPi, Gamyes;I looked at your suggestions and put my comments below. This is still a problem. Could anyone try this is one of your databases?

-- in the remote database
create table well ( entity varchar2(20) , country varchar2(12), province_state varchar2(12));
insert into well select to_char ( object_id ) , 'USA' , '42' from dba_objects where rownum < 10001;


To: Gandolf989. This morning I reran this job in three different data that were taken down for backups over the weekend. To look at your idea of a cartesian join/situtation, I changed the query against v$sql to just "select rows_processed , substr ( sql_text , 1 , 75 ) from v$sql. I am still seeing this absurd number of records processed. In a 10 minute test v$sql said that I processed almost 4 Billlion rows. I have not tried tk_prof yet.

To DaPi: You are totally correct regarding your comments on the lack of the cursor and the construction of the update statements. The original code is 200-300 lines and really does not make any sense to post. This was the smallest example that I could post. In honesty, this problem takes place WITHOUT the update statement and the sql_string assignment but, I put them in to make the example have some meaning. (Please see my new example below; I know the cursor is different but that does not seem to matter). If I move the commit to outside the loop in the cursor, get_well_number, everything works correctly. I need the commit statement because I am processing from 0 to 50,000 rows in the cursor in my code. My appologies on TEXAS and OHIO, that is an earlier example that I did not post.
-------------------------------------------

Code:

---

CREATE OR REPLACE PROCEDURE gather_well_codes
IS
  n                            NUMBER := 1;
  c_code                        well.entity%TYPE;

-- Arrays
  TYPE wcode IS TABLE OF well.entity%TYPE
      INDEX BY BINARY_INTEGER;

  w_code                        wcode;

-- Fetch entity for wells in Texas (state code=42)
  CURSOR get_well_numbers
  IS
      SELECT entity
        FROM well@pidm_ltus25d
      WHERE country = 'USA'
        AND province_state = '42';
BEGIN
  n := 0;

  OPEN get_well_numbers;

  LOOP
      FETCH get_well_numbers
      INTO c_code;

      EXIT WHEN get_well_numbers%NOTFOUND;
      n :=  n
          + 1;
      COMMIT;
  END LOOP;

  CLOSE get_well_numbers;
END gather_well_codes;
/

---

--------------------------------------------------
To Gamyers: I agree with you suggestion for improving the code. Please read what I mentioned to DaPi, just above the example of code in this reply.

Thanks for your ideas. This is such a simple piece of code it is driving me nuts.

What is well@pidm_ltus25d and what is its primary key?
What is users@pidm_ltus25d and what is its primary key?

To Gandolf: Those are two tables in the "remote" database.
What is well@pidm_ltus25d and what is its primary key?
This table contains information about oil and gas wells that are drilled in the U.S. The WELL table has a primary key on the single column, well_identifier.
What is users@pidm_ltus25d and what is its primary key?
Users what just an example table for this posting and is not even used in the last extract of code that I posted. It was the table (username varchar2(30), userid number(8), monthly_age number(8));
From Oracle: I just got a reply from one of their plsql analysts that states the problem is a cartesian join. I do not see any possuible way that join could be in my code, especially since it works when I move the commit statement, and I am waiting for Oracle to confirm that the error is in the 9.2 engine. I have also asked for them to check 10G. I am not sure if Oracle will provide me answer. If you want and have time, I can tar up an ascii file that has the create table statements and script to populate the table and, my code.

To Gandolf989:
In case you want them, here is the script to create the tables.
--
-- In the remote database create the WELL table
--
drop table well
/
create table well ( well_identifier varchar2(20) ,
entity varchar2(40) , operator_name varchar2(20) ,
country varchar2(12) , province_state varchar2(12) )
/
insert into well
select to_char ( object_id )||'0000' , 'ENT'||to_char ( object_id ) ,
'EXXON OIL AND GAS' , 'USA' , '42'
from all_objects where rownum < 10001
/
commit
/
--
-- user_usa is a private database link to the remote database
--
-- In the source database create the WELL table and create the
-- procedure. The WELL table is -- only needed for the c_code
-- variable. That just be "c_code varchar2(20) := '';"
--
--
create or replace procedure gather_well_codes
is
n number := 1;
c_code well.entity%type;

cursor get_well_numbers is
select entity from well@user_usa
where country = 'USA'
and province_state = '42';
begin
n := 0;
open get_well_numbers;
loop
fetch get_well_numbers into c_code;
exit when get_well_numbers%notfound;
n := n + 1;
commit;
end loop;
close get_well_numbers;
end gather_well_codes;
/
Thanks Eric Milton

Hey, I can reproduce it in 9.2.0.4. and XE (which is a baby version of 10GR2) for your test case.
SELECT entity from well@user_usa where country = 'USA' and province_state = '42'
gives a row_processed of 50015000
I just used a loopback database link (ie connect back to the same database and user).
The 'real' SQL on the 'remote' link gives 10000 rows processed.
SELECT "A1"."ENTITY" FROM "WELL" "A1" WHERE "A1"."COUNTRY"='USA' AND "A1"."PROVINCE_STATE"='42'

Adding the following :
dbms_output.put_line(n||':'||get_well_numbers%rowcount);
before the close of the cursor shows 10000 processed.

I don't think there's any problem in the code and its safe to assume the rows_processed is crud. Fetches seems correct, and buffer_gets is more reasonable too.
If you want to monitor progress, look into DBMS_APPLICATION_INFO package. You can set the module, action and client_info columns on v$session, and use one of them to show the current get_well_numbers%rowcount value.

PS. They are talking rubbish about cartesian join.

To: Gamyers;
Thanks goodness I am not crazy. I really appreciate your testing and the fact that you ran the test in 10G is "intertesting". It is sort of hard to believe that this problem exists in such a short piece of code and was not caught by Oracle.
Thanks again.
Eric

I recieved confirmation from gamyes that this behavior had been reproduced. Look at those comments I found that in the remote data base the rows_processed is 100% correct.
In a small test it may not be obvious, but I really believe that extra processing is taking place. This morning I ran a query against 2,000,000 rows and the elapse time was 50 minutes. Without the commit statement the elapsed time was 10 minutes.
I think this is a very substantial difference and, I will pester Oracle to get working on my service request.
Eric

I was able to run tk_prof and the trace file indicates that this problem really exists. Any ideas out there?

Code:

---

call    count        cpu    elapsed  disk query  current        rows
------- ------  --------- -----------  ---- -----  --------  -----------
Parse        6    400.00      756.78    1      4        1            0
Execute  10005  43,400.00  192,205.80    0      0    90,003            0
Fetch    10006  32,300.00  56,990.45    2    11    10,276  50,015,004
------- ------  --------- ----------  ----  ----  -------  ----------
total    20017  76,100.00  249,953.03    3    15  100,280  50,015,004

---

What is the explain plan??? :rolleyes:

Here's a complete test setup script (excepting the username and password for the DB link).

Code:

---

drop table over_link
/
create table over_link ( id number ,val varchar2(40) )
/
insert into over_link
select rownum, 'V'||to_char ( rownum )
from all_objects where rownum < 101
/

create database link ll connect to ..... identified by ..... using 'XE';

create or replace procedure test_link is
  n number := 1;
  c_code varchar2(40);
  cursor c_1 is
    select val from over_link@ll;
  cursor c_stat is
    SELECT name||' = '||value descr
    FROM V$SESSTAT s, v$statname n 
    WHERE n.statistic# = s.statistic# 
    AND name = 'bytes received via SQL*Net from dblink'
    AND s.sid = (select distinct sid from v$mystat);
  cursor c_rp is
    select sql_text, parse_calls, fetches, executions, rows_processed
    from v$sql
    where upper(sql_text) like '%OVER_LINK%@%'
    and upper(sql_text) not like '%V$SQL%';
begin
  n := 0;
  open c_1;
  loop
    fetch c_1 into c_code;
    exit when c_1%notfound;
    n := n + 1;
    if (n/10) = trunc(n/10) then
      for c in c_stat loop
        dbms_output.put_line(n||':'||c.descr);
      end loop;
      for c in c_rp loop
        dbms_output.put_line(n||':'||c.rows_processed);
      end loop;
      commit;
    end if;
  end loop;
  close c_1;
end test_link;
/

---

Here's the script for a test run. You won't want to do the flushing of the shared pool on a serious database. A small sandbox install of XE is perfect for this.
If you can't flush the shared pool, the ROWS_PROCESSED results won't be as clear on subsequent runs as the old stats will still be there.

Code:

---

alter system flush shared_pool;

conn .../....m@xe

set serveroutput on size 1000000

exec test_link;

---

The results I got were

Code:

---

10:bytes received via SQL*Net from dblink = 4449
10:10
20:bytes received via SQL*Net from dblink = 5821
20:30
30:bytes received via SQL*Net from dblink = 7171
30:60
40:bytes received via SQL*Net from dblink = 8521
40:100
50:bytes received via SQL*Net from dblink = 9871
50:150
60:bytes received via SQL*Net from dblink = 11221
60:210
70:bytes received via SQL*Net from dblink = 12571
70:280
80:bytes received via SQL*Net from dblink = 13921
80:360
90:bytes received via SQL*Net from dblink = 15271
90:450
100:bytes received via SQL*Net from dblink = 16622
100:550

---

Note : After every commit, it seems to need to 'go back to the start'. For example, after committing at row 10, when it gets to row 20, it has counted 30 (ie the original 10 plus 20 'new'), and for the next one, it has the thirty it has already done, plus 30 'new'.
However we are NOT getting those rows resent over the DB link.

I don't think you are getting the 'wrong' result. It probably isn't performing as well as it could, but that is probably more because Row by Row processing is slow. At the very least it should be using bulk selects.

Gamyers:
Thanks for you time and information. I did not run any test that looked at the traffic over sqlnet. I thnk that you are correct on the internal counting getting messed up. My son figured out that the value is rows_processed can be calculated by the following ( ( rows in table +1 ) * ( rows in table / 2 ) ) + rows in table. So, 10000 rows gives us ( 10001 * 5000 ) + 10000.
A far as the explain plan, I will have to research how to generate that when using a procedure.
Eric