drop table t;
drop table log;
set echo on

clear screen
create table t
as
select * 
  from all_objects
/
exec dbms_stats.gather_table_stats( user, 'T' );
select blocks, blocks/10 from user_tables where table_name = 'T';
pause

clear screen
create table log
( lo_rowid  rowid,
  hi_rowid  rowid,
  nrows     number,
  stime     timestamp,
  etime     timestamp
)
/
pause

clear screen
create or replace 
procedure do_update( p_lo_rowid in rowid, p_hi_rowid in rowid )
as
	l_rid rowid;
	l_cnt number;
begin
	insert into log (lo_rowid,hi_rowid,stime) 
	values (p_lo_rowid,p_hi_rowid,systimestamp) 
	returning rowid into l_rid;

	update t set object_name = lower(object_name) 
	 where rowid between p_lo_rowid and p_hi_rowid;
	l_cnt := sql%rowcount;

	update log 
	   set etime = systimestamp, 
	       nrows = l_cnt
	 where rowid = l_rid;
end;
/
pause

clear screen
begin
	dbms_parallel_execute.create_task('update t');
	dbms_parallel_execute.create_chunks_by_rowid
	( task_name   => 'update t', 
	  table_owner => user, 
	  table_name  => 'T', 
	  by_row      => false, 
	  chunk_size  => 100);
end;
/
select chunk_id, status, start_rowid, end_rowid
  from dba_parallel_execute_chunks
 where task_name = 'update t'
/
pause

clear screen
begin
	dbms_parallel_execute.run_task
	( task_name      => 'update t', 
	  sql_stmt       => 'begin do_update( :start_id, :end_id ); end;',
	  language_flag  => DBMS_SQL.NATIVE,
	  parallel_level => 2 );
end;
/
select chunk_id, status, start_rowid, end_rowid
  from dba_parallel_execute_chunks
 where task_name = 'update t'
/
pause


clear screen
begin
  	dbms_parallel_execute.drop_task('update t');
end;
/
select chunk_id, status, start_rowid, end_rowid
  from dba_parallel_execute_chunks
 where task_name = 'update t'
/
pause

column stime format a30
column ela format a30
clear screen
select nrows, stime, etime-stime ela from log;

