Hi Tom,
I have a table having around 5 million records.
Table Structure :
DESC RPT_MSG_CHANGE
Name Null Type
---------- -------- --------------
OID NOT NULL NUMBER
PRODUCT NOT NULL VARCHAR2(20)
OPERATION NOT NULL CHAR(1)
STATUS NOT NULL VARCHAR2(30)
I need to call a procedure for every record where STATUS='AVAILABLE' that in turn will do some compuation/insertion/deletion on other tables and after that it will mark the STATUS as 'PROCESSED' or 'FAILED'. I am using the below script to call :
DECLARE
......
CURSOR c1 IS
select distinct PRODUCT, OID
from RPT_MSG_CHANGE WHERE STATUS='AVAILABLE' ORDER BY OID;
BEGIN
FOR v1 IN c1 LOOP
BEGIN
pkg_rpt_asr_orders.proc_process_oid(v1.OID, l_data_ord_tbl); --- calling procedure
..........
..........
.......... ----Rest Code here
END;
END LOOP;
COMMIT;
END; Now I run above plsql block that executes the procedure for around 20 K records having the status as AVAILABLE. In the mid (suppose after processing 5k records) if I want to stop the execution, I simply update the table with status as 'PROCESSED'. Now I am not sure why the session is still running and taking almost the same time that is required to process every record.
Could you please let me know how oracle behaves in this case, Is it simply open a buffer -> copy all records in it -> process all records in the buffer (irrespective the the main table record updation) OR there is some other action going in the background ?
Thanks
Amit
So in a second session you run:
update RPT_MSG_CHANGE
set status = 'PROCESSED'
where status = 'AVAILABLE';
commit;
In the hope that your process will have no more rows to process and so stop?
It's not gonna work that way...
Remember:
The results of a cursor are fixed at the time you open it. Any changes saved by other transactions are invisible to it!
The example below opens the cursor when all the rows have y = 1. It then uses a separate transaction to set all the y values to zero.
After doing this it displays the contents of the cursor. But the results are consistent to the time
before then update. So you see all the table's rows!
It's only after closing and re-opening the cursor that you see the effects of the update:
create table t (
x int, y int
);
insert into t
select level, 1
from dual connect by level <= 5;
commit;
declare
type cur_tp is ref cursor return t%rowtype;
cur cur_tp;
rw t%rowtype;
procedure upd_t as
pragma autonomous_transaction;
begin
update t
set y = 0;
commit;
end upd_t;
procedure display_rows ( c sys_refcursor ) as
rw cur%rowtype;
begin
loop
fetch c into rw;
exit when c%notfound;
dbms_output.put_line ( rw.x );
end loop;
end display_rows;
begin
open cur for select * from t where y = 1;
upd_t;
dbms_output.put_line ( '*** 1st try ***' );
display_rows ( cur );
close cur;
open cur for select * from t where y = 1;
dbms_output.put_line ( '*** 2nd try ***' );
display_rows ( cur );
close cur;
end;
/
*** 1st try ***
1
2
3
4
5
*** 2nd try ***So your code is still processing all 20k available rows. If you want to stop it, you need to either:
- kill it
- change your code to have a "stop processing" flag where you lookup a value in from another table during the loop.
Changing your code is the better option ;) While you're doing that, switch to bulk processing. This should help it run faster too:
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html