(12R1)
--データ準備
set time on
set timing on
drop table tab1 purge;
create table tab1(col1 int,col2 timestamp);
alter table tab1 add constraint tab1p primary key (col1);
declare
begin
for i in 1..100000 loop
insert into tab1 values(i,NULL);
commit;
end loop;
end;
/
--session1から実行(カラム追加・削除)
declare
sSQL varchar2(1000);
begin
while true loop
sSQL :='alter table tab1 add (col3 int)';
execute immediate sSQL;
sSQL :='alter table tab1 drop (col3)';
execute immediate sSQL;
end loop;
end;
/
--session2から実行(更新)
declare
begin
for i in 1..100000 loop
update tab1 set col2 = systimestamp where col1 = i;
commit;
end loop;
end;
/
--結果
開始後しばらくして、カラム追加・削除側で「ORA-00054: リソース・ビジー」発生
※下記SQLをカラム追加・削除側で実行することでリソースビジーは回避可能
alter session set ddl_lock_timeout = 60;
カラム追加・削除有の場合
→経過: 00:00:49.98
カラム追加・削除なしの場合
→経過: 00:00:10.99
(5.6)
--データ準備
drop table tab1;
create table tab1(col1 int,col2 timestamp(3));
alter table tab1 add constraint tab1p primary key(col1);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab1 values(i,null);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
--session1から実行(カラム追加・削除)
drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare vSQL varchar(1000);
while true do
set @q := 'alter table tab1 add (col3 int)';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set @q := 'alter table tab1 drop col3';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
end while;
end
//
delimiter ;
call proc2();
--session2から実行(更新)
drop procedure proc3;
delimiter //
create procedure proc3()
begin
declare i int;
set i = 1;
while i <= 100000 do
update tab1 set col2 = CURRENT_TIMESTAMP(3) where col1 = i;
set i = i + 1;
end while;
end
//
delimiter ;
call proc3();
--結果
エラー発生なし
カラム追加・削除有の場合
→2 min 56.68 sec
カラム追加・削除なしの場合
→32.83 sec
(9.4)
--データ準備
\timing
drop table tab1;
create table tab1(col1 int,col2 timestamp);
create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1p primary key using index ind1;
insert into tab1 select generate_series(1,1000),NULL;
--session1から実行(カラム追加・削除)
drop function fun1();
create or replace function fun1()
returns void as
$$
DECLARE
BEGIN
execute 'alter table tab1 add col3 int';
EXCEPTION
WHEN others THEN
RAISE NOTICE 'SQLERRM= %,SQLSTATE=%',SQLERRM,SQLSTATE;
END
$$ language 'plpgsql';
drop function fun2();
create or replace function fun2()
returns void as
$$
DECLARE
BEGIN
execute 'alter table tab1 drop col3';
EXCEPTION
WHEN others THEN
RAISE NOTICE 'SQLERRM= %,SQLSTATE=%',SQLERRM,SQLSTATE;
END
$$ language 'plpgsql';
--
for i in `seq 1000`
do
SQL="psql -c 'select fun1()' test"
eval ${SQL}
SQL="psql -c 'select fun2()' test"
eval ${SQL}
done
--
--session2から実行(更新)
drop function fun3();
create or replace function fun3(int)
returns void as
$$
DECLARE
vSQL varchar(1000);
num int;
BEGIN
num := $1;
vSQL := 'update tab1 set col2 = clock_timestamp() where col1 = ' || cast(num as varchar);
execute vSQL;
EXCEPTION
WHEN others THEN
RAISE NOTICE 'SQLERRM= %,SQLSTATE=%',SQLERRM,SQLSTATE;
END
$$ language 'plpgsql';
--
time for i in `seq 1000`
do
SQL="psql -c 'select fun3("${i}")' test"
eval ${SQL}
done
--
--結果
ループ回数10000の場合、
ERROR: tables can have at most 1600 columns
カラムを削除してもトータルで1600回カラム定義を行うとエラーとなる模様。
バキュームしても改善せず。
→ループ回数を1000に変更して実施
カラム追加・削除有の場合
→
real 0m32.405s
user 0m2.717s
sys 0m4.498s
カラム追加・削除なしの場合
→
real 0m16.554s
user 0m2.642s
sys 0m4.532s
(2014)
SET STATISTICS TIME ON
--データ準備
drop table tab1;
create table tab1(col1 int not null,col2 datetime2);
alter table tab1 add constraint tab1p primary key(col1);
DECLARE @i integer;
SET @i = 1;
WHILE @i <= 100000
BEGIN
insert into tab1 values(@i,NULL);
SET @i = @i + 1;
END
--session1から実行(カラム追加・削除)
DROP PROCEDURE dbo.proc1;
GO
CREATE PROCEDURE dbo.proc1
AS
BEGIN
DECLARE @vSQL varchar(1000);
set @vSQL = 'alter table tab1 add col3 int';
execute(@vSQL);
END
GO
DROP PROCEDURE dbo.proc2;
GO
CREATE PROCEDURE dbo.proc2
AS
BEGIN
DECLARE @vSQL varchar(1000);
set @vSQL = 'alter table tab1 drop column col3';
execute(@vSQL);
END
GO
--
@echo off
:LOOP
sqlcmd -d test -b -Q "set nocount on; exec test.dbo.proc1"
sqlcmd -d test -b -Q "set nocount on; exec test.dbo.proc2"
goto :LOOP
exit /b 0
--
--session2から実行(更新)
DROP PROCEDURE dbo.proc3;
GO
CREATE PROCEDURE dbo.proc3
AS
BEGIN
DECLARE @i integer;
SET @i = 1;
WHILE @i <= 100000
BEGIN
update tab1 set col2 = GETDATE() where col1 = @i;
SET @i = @i + 1;
END
END
GO
EXEC test.dbo.proc3;
GO
--結果
エラー発生なし
カラム追加・削除有の場合
→CPU 時間 = 12625 ミリ秒、経過時間 = 54685 ミリ秒
カラム追加・削除なしの場合
→CPU 時間 = 9953 ミリ秒、経過時間 = 32534 ミリ秒
