Paperless Oracle‎ > ‎SSTO‎ > ‎

02. block cleanout

@ukja102

drop table t1 purge;

create table t1(c1 int, c2 char(2000));

-- fast commit - cleanout immediately
insert into t1 values(1, 'x');
insert into t1 values(2, 'x');

commit;

col f# new_value file_no
col b# new_value blk_no

select dbms_rowid.rowid_relative_fno(rowid) as f#,
      dbms_rowid.rowid_block_number(rowid) as b#
from t1
;

alter system dump datafile &file_no block &blk_no;

@trace_file
ho copy &trace_file trc1.trc

@ukja102

-- subsequent DML on the same block?
update t1 set c2 = 'y' where c1 = 1;

commit;

alter system dump datafile &file_no block &blk_no;

@trace_file
ho copy &trace_file trc2.trc

@diff trc1.trc trc2.trc


@ukja102

update t1 set c2 = 'y' where c1 = 2;

commit;

alter system dump datafile &file_no block &blk_no;

@trace_file
ho copy &trace_file trc3.trc

@diff trc2.trc trc3.trc

-- lazy commit - delayed block cleanout
alter system set sga_target=0;
alter system set db_cache_size=1m;

drop table t1 purge;

create table t1(c1 int, c2 char(2000));

insert into t1
select level, 'x' from dual
connect by level <= 10000
;

commit;

col min_rowid new_value v_min_rowid
col max_rowid new_value v_max_rowid

select min(rowid) as min_rowid, max(rowid) as max_rowid
from t1;


update t1 set c2 = 'y';

commit;


col f# new_value file_no
col b# new_value blk_no

select dbms_rowid.rowid_relative_fno('&v_min_rowid') as f#,
      dbms_rowid.rowid_block_number('&v_min_rowid') as b#
from dual
;

@ukja102

alter system dump datafile &file_no block &blk_no;

@trace_file
ho copy &trace_file trc1.trc


col f# new_value file_no
col b# new_value blk_no

select dbms_rowid.rowid_relative_fno('&v_max_rowid') as f#,
      dbms_rowid.rowid_block_number('&v_max_rowid') as b#
from dual
;

@ukja102

alter system dump datafile &file_no block &blk_no;

@trace_file
ho copy &trace_file trc2.trc

@diff trc1.trc trc2.trc


-- see that select statement generates redo
set autot on
select count(*) from t1;
set autot off

@ukja102

-- and the blocks got cleaned out
alter system dump datafile &file_no block &blk_no;

@trace_view

-- see that select statement generates redo
set autot on
select count(*) from t1;
set autot off

-- dump undo segment header
col file_id new_value undo_file_no
col block_id new_value undo_block_no

select segment_name, segment_id, file_id, block_id
from dba_rollback_segs
where segment_id = &segment_id
;

@ukja102

alter system dump datafile &undo_file_no block &undo_block_no;

@trace_view


alter system set sga_target=500M;


Comments