-
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.
-
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
Last edited by DaPi; 01-14-2006 at 03: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
-
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.
-
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 01:23 PM.
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|