Error in v$sql.rows_processed. 1,000,000 rows per second
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  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 12:25 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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?
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2006
    Location
    Denver, Colorado
    Posts
    9
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
    Last edited by DaPi; 01-14-2006 at 02:50 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    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.

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

    Gandolf989, DaPi, Gamyes; checked on your suggestions

    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.
    Last edited by gandolf989; 01-16-2006 at 12:23 PM.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    What is well@pidm_ltus25d and what is its primary key?
    What is users@pidm_ltus25d and what is its primary key?
    this space intentionally left blank

  8. #8
    Join Date
    Jan 2006
    Location
    Denver, Colorado
    Posts
    9
    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.

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

    Tables for Gandolf989

    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

  10. #10
    Join Date
    Feb 2005
    Posts
    158
    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.

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