(12R1)
--データ準備
drop table tab1 purge;
create table tab1(col1 int,col2 int);
declare
begin
for i in 1..10000 loop
insert into tab1 values(i,i);
commit;
end loop;
end;
/
exec dbms_stats.gather_table_stats('TEST','TAB1');
select * from user_tab_statistics where table_name = 'TAB1';
exec dbms_stats.drop_stat_table('TEST','STAT1');
exec dbms_stats.create_stat_table('TEST','STAT1');
exec dbms_stats.export_table_stats(ownname =>'TEST', tabname=>'TAB1',stattab =>'STAT1');
--session1から実行
declare
begin
while true loop
for i in 1..10000 loop
update tab1 set col2 = col2+1 where col1 = i;
commit;
end loop;
end loop;
end;
/
--session2から実行
declare
begin
while true loop
for i in 1..10000 loop
dbms_stats.delete_table_stats(ownname =>'TEST', tabname=>'TAB1');
dbms_stats.import_table_stats(ownname =>'TEST', tabname=>'TAB1',stattab =>'STAT1');
end loop;
end loop;
end;
/
→エラーはでないため、統計情報移行中に更新は可能と思われる
(5.6)
--データ準備
drop table tab1;
create table tab1(col1 int,col2 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 10000 do
insert into tab1 values(i,i);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
analyze table tab1;
select * from mysql.innodb_table_stats where table_name = 'tab1';
select * into outfile '/tmp/stats01.dat'
fields terminated by ',' optionally enclosed by '"'
from mysql.innodb_table_stats where table_name = 'tab1';
drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare i int;
set i = 1;
while true do
while i <= 10000 do
update tab1 set col2 = col2 + 1 where col1 = i;
set i = i + 1;
end while;
end while;
end
//
delimiter ;
--session1から実行
call proc2();
--session2から実行
while true; do
echo "load data infile '/tmp/stats01.dat' into table mysql.innodb_table_stats fields terminated by ',' optionally enclosed by '\"';"
echo "delete from mysql.innodb_table_stats where table_name = 'tab1';"
done | mysql test
→エラーはでないため、統計情報移行中に更新は可能と思われる
(9.4)
--データ準備
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 select generate_series(1,10000),generate_series(1,10000);
analyze tab1;
select * from pg_class where relname = 'tab1';
select * from dbms_stats.relation_stats_effective;
cd /usr/share/doc/pgsql/extension
cp export_effective_stats-9.4.sql.sample export_effective_stats-9.4.sql
vim export_effective_stats-9.4.sql
psql test < /usr/share/doc/pgsql/extension/export_effective_stats-9.4.sql
create or replace function fun1()
returns void as
$$
declare
var_none text;
begin
for i in 1..10000 loop
update tab1 set col2 = col2 + 1 where col1 = i;
end loop;
end
$$ language 'plpgsql';
--session1から実行
while true; do
echo "select * from fun1();"
done | psql test
--session2から実行
while true; do
echo "select dbms_stats.import_table_stats('public','tab1','/usr/share/doc/pgsql/extension/export_stats.dmp');"
done | psql test
→エラーはでないため、統計情報移行中に更新は可能と思われる
統計情報移行の方法はない模様のためテスト不可
