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

[emilton]

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.
-------------------------------------------

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.