データ更新時のundo使用量

DB

(8.0.27)

-- 1. テストテーブル準備
drop table tab1;
create table tab1(
    col1 int primary key
   ,col2 char(100)
   ,col3 char(100)
   );

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
  declare i int;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1 values(i,0,0);
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);
select count(*) from tab1;


-- 2. undo使用量確認
別セッションから確認

 

select page_type, count(*) from information_schema.innodb_buffer_page
group by page_type
order by page_type
;

 


-- 3. データ更新


drop procedure proc2;

delimiter //
create procedure proc2(in x int)
begin
  declare i int;
  set i = 0;
  while i < x do
    set i = i + 1;
    update tab1 set
         col2 = cast( (floor(rand() * 1000000000000)+1)  as char)
       , col3 = cast( (floor(rand() * 1000000000000)+1)  as char)
       where col1 = i
       ;
  end while;
end
//
delimiter ;

測定前にUNDOを解放するため、DBを停止・起動

start transaction;
call proc2(1000000);

commit;


データサイズ確認
analyze table tab1;
select
  table_name
  ,table_rows
  ,avg_row_length
  ,data_length/1024/1024/1024 tableGB
  ,index_length/1024/1024/1024 indexGB
from information_schema.tables
where table_schema = database()
and table_name = 'tab1'
;

→ 0.23G


undo使用量=14802 x 16 x 1024 /1024/1024/1024 = 0.23G

データサイズと同じくらいのundoページを使用している

 

(19c)

 

-- 1. テストテーブル準備
drop table tab1 purge;
create table tab1(
    col1 int primary key
   ,col2 char(100)
   ,col3 char(100)
   );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(i,0,0);
end loop;
end;
/

commit;
select count(*) from tab1;
select sys_context('userenv','sid') from dual;


-- 2. undo使用量確認
別セッションから確認


bash /u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin/sql test/test@pdb1

select USED_UBLK,USED_UREC from v$transaction where addr = ( select taddr from v$session where sid = 20 );

repeat 1000 1

 

-- 3. データ更新

declare
begin
for i in 1..1000000 loop
  update tab1 set
      col2 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
    , col3 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
    where col1 = i
    ;
end loop;
end;
/

commit;

 


データサイズ確認
select bytes/1024/1024/1024 from user_segments where segment_name = 'TAB1';


→ 0.23G


undo使用量=USED_UBLK x 8 x 1024 /1024/1024/1024 = 0.35G

データサイズと同じくらいのundoページを使用している

 

(14)

undo領域なし

(2019)

 

-- 1. テストテーブル準備
drop table tab1;
create table tab1(
    col1 int primary key
   ,col2 char(100)
   ,col3 char(100)
   );

declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
  insert into tab1 values(@i,0,0);
  set @i = @i + 1;
end
commit;
select count(*) from tab1;


-- 2. トランザクション分離レベルの変更
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON with rollback after 1 seconds;
DBCC USEROPTIONS

-- 3. undo使用量確認
別セッションから確認


select count(*) , sum(record_length_first_part_in_bytes)/1024/1024 mb 
from sys.dm_tran_version_store;


-- 4. データ更新

declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
   update tab1 set
           col2 = cast( (floor(rand() * 1000000000000)+1) as varchar )
         , col3 = cast( (floor(rand() * 1000000000000)+1) as varchar )
         where col1 = @i
         ;
   set @i = @i + 1;
end

commit;

 

データサイズ確認
exec sp_spaceused 'dbo.tab1';

→ 0.4G


undo使用量=0.2G

データサイズの半分くらいのundoページを使用している