(8.0.28)
-- 1. テストテーブル準備
drop table tab1;
create table tab1(
col1 int primary key
,col2 char(100)
,col3 char(100)
,col4 char(100)
,col5 char(100)
,col6 char(100)
,col7 char(100)
,col8 char(100)
,col9 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
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
);
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
;
: > a.log
while true;do mysql test < a.sql >> a.log ;sleep 3;done
-- 3. データ更新
測定前にUNDOを解放するため、DBを停止・起動
update tab1 set
col2 = cast( (floor(rand() * 1000000000000)+1) as char)
, col3 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col4 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col5 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col6 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col7 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col8 = cast( (floor(rand() * 1000000000000)+1) as char)
-- , col9 = cast( (floor(rand() * 1000000000000)+1) as char)
;
データサイズ確認
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. 更新カラム数2個の場合
tableGB | indexGB
---------------+----------------
0.849609375000 | 0.000000000000
undo使用量 = 14942 x 16 x 1024 /1024/1024/1024 = 0.23G
-- 4.2. 更新カラム数4個の場合
tableGB | indexGB
---------------+---------------
0.849609375000 | 0.000000000000
undo使用量 = 27482 x 16 x 1024 /1024/1024/1024 = 0.42G
-- 4.3. 更新カラム数8個の場合
tableGB | indexGB
---------------+----------------
0.849609375000 | 0.000000000000
undo使用量 = 45179 x 16 x 1024 /1024/1024/1024 = 0.69G
undo使用量は実際に更新されるカラムサイズにおよそ比例する
(19c)
-- 1. テストテーブル準備
drop table tab1 purge;
create table tab1(
col1 int primary key
,col2 char(100)
,col3 char(100)
,col4 char(100)
,col5 char(100)
,col6 char(100)
,col7 char(100)
,col8 char(100)
,col9 char(100)
);
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
, to_char(floor(dbms_random.value(1, 1000000000001) ) )
);
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 = 49 );
repeat 1000 1
-- 3. データ更新
update tab1 set
col2 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
, col3 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col4 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col5 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col6 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col7 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col8 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
-- , col9 = to_char(floor(dbms_random.value(1, 1000000000001) ) )
;
commit;
データサイズ確認
col segment_name for a5
select segment_name,bytes/1024/1024/1024 gbytes
from user_segments where segment_name in ('TAB1') order by segment_name;
-- 4. 結果
-- 4.1. 更新カラム数2個の場合
SEGME GBYTES
----- ----------
TAB1 .9609375
undo使用量 = 43900 x 8 x 1024 /1024/1024/1024 = 0.33G
-- 4.2. 更新カラム数4個の場合
SEGME GBYTES
----- ----------
TAB1 .9609375
undo使用量 = 70184 x 8 x 1024 /1024/1024/1024 = 0.54G
-- 4.3. 更新カラム数8個の場合
SEGME GBYTES
----- ----------
TAB1 .9609375
undo使用量 = 123257 x 8 x 1024 /1024/1024/1024 = 0.94G
undo使用量は実際に更新されるカラムサイズにおよそ比例する
(14)
undo領域なし
(2019)
-- 1. テストテーブル準備
drop table tab1;
create table tab1(
col1 int primary key
,col2 char(100)
,col3 char(100)
,col4 char(100)
,col5 char(100)
,col6 char(100)
,col7 char(100)
,col8 char(100)
,col9 char(100)
);
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1 values(
@i
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
);
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;
del a.log
@echo off
:LOOP
sqlcmd -d test -b -i a.sql >> a.log
timeout 3 > nul
goto :LOOP
exit /b 0
-- 4. データ更新
update tab1 set
col2 = cast( (floor(rand() * 1000000000000)+1) as varchar )
, col3 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col4 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col5 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col6 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col7 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col8 = cast( (floor(rand() * 1000000000000)+1) as varchar )
-- , col9 = cast( (floor(rand() * 1000000000000)+1) as varchar )
;
データサイズ確認
exec sp_spaceused 'dbo.tab1';
-- 5. 結果
-- 5.1. 更新カラム数2個の場合
data index_size
888896 KB 3312 KB
undo使用量=0.8G
-- 5.2. 更新カラム数4個の場合
data index_size
888896 KB 3312 KB
undo使用量=0.8G
-- 5.3. 更新カラム数8個の場合
data index_size
888896 KB 3312 KB
undo使用量=0.8G
undo使用量は実際に更新されるカラムサイズとは関係なく、全カラムサイズに依存する
