ダイレクトパス

(8.0.27)

LOAD DATA文がダイレクトパスの動作をしている可能性がないか調査

-- テストテーブル作成
drop table tab1;
create table tab1(
    col1 int
   ,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
    ,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;
analyze table tab1;

drop table tab2;
create table tab2(
    col1 int
   ,col2 char(100)
   ,col3 char(100)
   );

select * into outfile '/var/lib/mysql-files/tab1.csv'
fields terminated by ',' optionally enclosed by '"'
from tab1;

load data infile '/var/lib/mysql-files/tab1.csv' into table tab2
fields terminated by ',' optionally enclosed by '"'
;

 


-- 確認
show session status like 'Innodb_buffer_pool_read_requests';
show session status like 'Innodb_pages_read';

show session status like 'Innodb_buffer_pool_write_requests';
show session status like 'Innodb_pages_written';


LOAD DATA文実行で「Innodb_buffer_pool_write_requests」の値が増えているので
ダイレクトパスインサートの動作はない模様

 

 

 

(19c)
https://tech-oracle.blog.ss-blog.jp/2020-12-22

-- テストテーブル作成
drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 varchar(100)
   ,col3 varchar(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) ) )
  );
end loop;
end;
/

commit;
select count(*) from tab1;
exec dbms_stats.gather_table_stats(user,'TAB1');

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 varchar(100)
   ,col3 varchar(100)
   );

-- パラレルダイレクトパスリード

select /*+ parallel */ count(*) from tab1;

※パラレル実行の場合は、"_serial_direct_read" を neverにしてもダイレクトパスリードとなる

-- シリアルダイレクトパスリード

alter session set "_serial_direct_read" = always;
show parameter "_serial_direct_read";

select count(*) from tab1;

 

 

-- パラレルダイレクトパスインサート

alter session force parallel dml;
insert /*+ append parallel */ into tab2 select * from tab1;
commit;

※パラレル実行はデフォルトでダイレクトパスとなるのでappendヒントはなくてもよい

-- シリアルダイレクトパスインサート
alter session disable parallel dml;
insert /*+ append */ into tab2 select * from tab1;
commit;

※ダイレクトパスインサート実行後、インサートしたテーブル(tab2)に対してselectやupdate等を実行すると下記エラーとなる。
ORA-12838: オブジェクトは、パラレルで変更された後は読取り/変更できません。


-- 確認
select a.name,b.value 
from v$statname a inner join v$mystat b 
on a.statistic# = b.statistic# 
where a.name in( 'physical reads','physical reads direct','physical writes direct','physical writes');

 

 

(14)

COPY文がダイレクトパスの動作をしている可能性がないか調査

-- pg_stat_statementsの有効化
vim postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 2000
pg_stat_statements.track = top
pg_stat_statements.save = on

sudo systemctl restart postgresql-14

create extension pg_stat_statements;

-- テストテーブル作成

drop table tab1;
create table tab1(
    col1 int
   ,col2 char(200)
   ,col3 char(200)
   );


insert into tab1 select
   g
  ,(floor(random() * 1000000000000)+1)::varchar
  ,(floor(random() * 1000000000000)+1)::varchar
from generate_series(1,1000000) g;

select count(*) from tab1;
analyze tab1;


drop table tab2;
create table tab2(
    col1 int
   ,col2 char(200)
   ,col3 char(200)
   );


\COPY tab1 TO '/tmp/tab1.csv' WITH CSV NULL AS '';

\COPY tab2 FROM '/tmp/tab1.csv' WITH CSV NULL AS '';

-- 確認

select pg_stat_statements_reset();

select 
queryid, query, calls ,
shared_blks_hit ,
shared_blks_read ,
shared_blks_dirtied ,
shared_blks_written 
from pg_stat_statements
where query like '%tab2%'
;


COPY文実行で「shared_blks_dirtied」の値が増えているので
ダイレクトパスインサートの動作はない模様

 

------
pg_bulkload
https://github.com/ossc-db/pg_bulkload
https://qiita.com/mkyz08/items/afac974a3d19d12729a8

 

dnf -y install pg_bulkload_14

CREATE EXTENSION pg_bulkload;

vim pg_bulkload.ctl

OUTPUT = tab2
TYPE = CSV
INPUT = /tmp/tab1.csv
LOADER = DIRECT
SKIP = 0
LIMIT = INFINITE
CHECK_CONSTRAINTS = NO
PARSE_ERRORS = 0
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
LOGFILE = ./pg_bulkload.log
PARSE_BADFILE = ./pg_bulkload.bad
DUPLICATE_BADFILE = ./pg_bulkload.dup
TRUNCATE = NO
MULTI_PROCESS = NO
DELIMITER = ","
QUOTE = "\""
ESCAPE = \


/usr/pgsql-14/bin/pg_bulkload -d test -h 127.0.0.1 -U postgres pg_bulkload.ctl


/tmp/tab1.csvのサイズ = 434 MB
COPYコマンドの場合 -> 6.69 sec
pg_bulkloadの場合 -> 3.55 sec

------

 

(2019)

bcp文がダイレクトパスの動作をしている可能性がないか調査

-- テストテーブル作成
drop table tab1;
create table tab1(
    col1 int
   ,col2 char(100)
   ,col3 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 )
    );
  set @i = @i + 1;
end
commit;
select count(*) from tab1;
update statistics dbo.tab1;

drop table tab2;
create table tab2(
    col1 int
   ,col2 char(100)
   ,col3 char(100)
   );


bcp test.dbo.tab1 out "C:\tab1.csv"  -T -c -t,

bcp test.dbo.tab2 in "C:\tab1.csv"  -T -c -t,

 

-- 確認
select t1.sql_handle ,t2.text ,
t1.last_logical_reads ,
t1.last_physical_reads ,
t1.last_logical_writes
from sys.dm_exec_query_stats t1 
cross apply sys.dm_exec_sql_text(t1.sql_handle) t2 
where t2.text like '%tab2%'
;

bcp文実行で「last_logical_writes」の値が変化ないため
ダイレクトパスインサートの動作をしている可能性あり