The GV$SESSION_LONGOPS view can save you if you are nervous about how much time a long running operation has remaining, and are asked for an update from someone in a higher “zip code”. Oracle posts an entry to this memory structure whenever an operation is expected to last more than six seconds. Note the term “operation”. If the “operation” is one of 50 billion single block reads, it won’t be externalized in the view, since it isn’t expected to take that long (and if it does, you have bigger problems :)). This was somewhat of a pain in release 10g, if you were issuing an
alter table foo shrink space;
command, and wanted to provide an update as to how long it would take.
However, if you are running literally anything in your own procedure, and know your progress at any given point, you can post the view by using a procedure in the dbms_application_info package. In the example below, we had a delete to run that resulted in a fairly large referential integrity tree being traversed. In other words, a delete from table1 resulted in a delete from table2, which resulted in a delete from table3, etc. While we could have done this delete from table1 in parallel, it still was estimated to take several hours based on testing. We had no idea when it would complete, as most of the work was single row lookups as the delete progressed. We decided to delete each parent row in a cursor, so that we could post the GV$SESSION_LONGOPS view after each delete and see how much work remained.
DISCLAIMER: I understand that a straight SQL set based delete is always faster, but we like to see periodic progress, so we made the decision to have a longer run time and receive progressive feedback.
Below is the complete and actual PLSQL block we ran to delete the rows.
set serveroutput on size unlimited
set echo on
alter session set current_schema = ngen_prod
/
declare
type t_ng_file_ids is table of number;
l_ng_file_ids t_ng_file_ids := t_ng_file_ids();
i number := 0;
j number := 0;
l_index number;
l_slno number;
begin
for cur in (select ng_file_id
from ng_files
where date_file_received not
between to_date('2009-12-16', 'yyyy-mm-dd')
and to_date('2009-12-17', 'yyyy-mm-dd')) loop
l_ng_file_ids.extend(1);
i := i + 1;
l_ng_file_ids(i) := cur.ng_file_id;
end loop;
if i > 0 then
while j < i loop
j := j + 1;
delete from ng_files where ng_file_id = l_ng_file_ids(j);
commit;
dbms_application_info.set_action(j || ' of ' || i || ' have been processed.');
dbms_application_info.set_session_longops(rindex => l_index,
slno => l_slno,
op_name => 'DELETING',
sofar => j,
totalwork => i,
target_desc => 'CLEANING UP NG_FILES' );
end loop;
end if;
end;
/
As soon as this is running, you can query GV$SESSION_LONGOPS for updates, or query GV$SESSION where the ACTION column is like ‘%processed.’