データ更新時のundo使用量(インデックスの影響)


(8.0.28)

-- 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;


create index ind1 on tab1(col2);
create index ind2 on tab1(col3);


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

 

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

: > a.log
while true;do mysql test < a.sql >> a.log ;sleep 3;done


-- 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'
;

-- 4. 結果

-- 4.1. インデックスなしの場合

tableGB        | indexGB
---+
0.228256225586 | 0.000000000000

undo使用量 = 15836 x 16 x 1024 /1024/1024/1024 = 0.24G

-- 4.2. インデックス1個の場合

tableGB        | indexGB
---+
0.228256225586 | 0.267837524414

undo使用量 = 22285 x 16 x 1024 /1024/1024/1024 = 0.34G

-- 4.3. インデックス2個の場合

tableGB        | indexGB
---+---
0.228256225586 | 0.570068359375


undo使用量 = 23912 x 16 x 1024 /1024/1024/1024 = 0.36G

 

インデックス1個追加によりundo使用量は1.5倍くらいに増える。
インデックス追加による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;

create index ind1 on tab1(col2);
create index ind2 on tab1(col3);


-- 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 = 48 );

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;

 


データサイズ確認

col segment_name for a5
select segment_name,bytes/1024/1024/1024 gbytes 
from user_segments where segment_name in ('TAB1','IND1','IND2') order by segment_name;


-- 4. 結果

-- 4.1. インデックスなしの場合

SEGME     GBYTES
- --
TAB1    .2265625

undo使用量 = 44885 x 8 x 1024 /1024/1024/1024 = 0.34G

-- 4.2. インデックス1個の場合
SEGME     GBYTES
- --
IND1      .28125
TAB1    .2265625

undo使用量 = 89932 x 8 x 1024 /1024/1024/1024 = 0.67G

-- 4.3. インデックス2個の場合

SEGME     GBYTES
- --
IND1      .28125
IND2      .28125
TAB1    .2265625

undo使用量 = 136038 x 8 x 1024 /1024/1024/1024 = 1.04G


インデックス1個追加によりundo使用量は2倍くらいに増える。
インデックス追加による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;


create index ind1 on tab1(col2);
create index ind2 on tab1(col3);


-- 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;


del a.log

@echo off

:LOOP
    sqlcmd -d test -b -i a.sql >> a.log
    timeout 3 > nul
goto :LOOP

exit /b 0


-- 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';


-- 5. 結果

-- 5.1. インデックスなしの場合

data  index_size
228576 KB    856 KB

undo使用量=0.2G


-- 5.2. インデックス1個の場合

data  index_size
228576 KB  183480 KB

undo使用量=0.3G

-- 5.3. インデックス2個の場合

data  index_size
421048 KB  364880 KB

undo使用量=0.4G


インデックス1個追加によりundo使用量は1.5倍くらいに増える。
インデックス追加によるundo使用量増加分はインデックス数にほぼ比例する