実行計画へのメモリサイズの影響

SQL Server以外はメモリサイズで実行計画が変化する可能性がある

• MySQL

(8.0.26)

innodb_buffer_pool_sizeとjoin_buffer_sizeとを変更し、
オプティマイザトレースを比較する

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   );

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
    ,floor(rand() * 1000000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

select count(*) from tab1;
select * from tab1 order by rand() limit 20;

analyze table tab1;

-- 2. パラメータ変更 

select @@innodb_buffer_pool_size;
select @@join_buffer_size;

innodb_buffer_pool_size = 800M
join_buffer_size = 128K

-- 3. 実行計画取得

set session optimizer_trace = 1;

select count(*) from tab1 t1
inner join tab1 t2 on t1.col1 = t2.col1
inner join tab1 t3 on t1.col1 = t3.col1
;

set session optimizer_trace = 0;

pager cat > trace_800_256_.log

select * from information_schema.optimizer_trace\G

-- 4. 結果
/root/trace_800_128_.log
/root/trace_400_128_.log
/root/trace_800_256_.log

innodb_buffer_pool_size -> コストに影響なし
join_buffer_size -> コストに影響あり★

• Oracle

(19c)

sga_targetとpga_aggregate_targetとを変更し、
オプティマイザトレースを比較する

-- 1. テストデータ作成

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
  );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,floor(dbms_random.value(1, 1000001) )
   );
  
end loop;
end;
/

commit;

select count(*) from tab1;
select * from tab1 order by dbms_random.value()  fetch first 20 rows only;

set time on
set timing on

exec dbms_stats.gather_table_stats(user,'TAB1');

-- 2. 初期化パラメータ変更 

show parameter memory;
show parameter sga;
show parameter pga;

alter system set memory_target= 0 scope=spfile;
alter system set sga_target= 900M scope=spfile;
alter system set pga_aggregate_target= 200M scope=spfile;

-- 3. オプティマイザトレース取得

alter system flush shared_pool;

alter session set TRACEFILE_IDENTIFIER = 'trace_900_200_';
alter session set events '10053 trace name context forever, level 1';

select count(*) from tab1 t1
inner join tab1 t2 on t1.col1 = t2.col1
inner join tab1 t3 on t1.col1 = t3.col1
;

alter session set events '10053 trace name context off';

-- 4. 結果
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3090_trace_1000_100_.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3571_trace_1000_200_.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4011_trace_900_200_.trc

sga_target -> コストに影響なし
pga_aggregate_target -> コストに影響あり★

実行計画に影響のあるパラメータは下記ビューで確認可能

select name from v$sys_optimizer_env order by name;

このビューにはpga_aggregate_targetは含むが、sga_targetは含まれない。
今回の確認結果と一致している

• PostgreSQL

(14)

shared_buffersとwork_memとを変更し、
実行計画を比較する

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
  );

start transaction;
insert into tab1 select
   g
  ,floor(random() * 1000000)+1
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;

commit;

select count(*) from tab1;
select * from tab1 order by random() limit 20;

\timing 1
\pset pager 0

analyze tab1;

-- 2. パラメータ変更 

show shared_buffers;
show work_mem;

shared_buffers = 128MB
set work_mem = '100MB';

-- 3. 実行計画取得

explain ( ANALYZE , VERBOSE , COSTS , SETTINGS , BUFFERS , TIMING , SUMMARY )
select t1.col3,t1.col4,t1.col5,count(*) from tab1 t1
inner join tab1 t2 on t1.col1 = t2.col1
inner join tab1 t3 on t1.col1 = t3.col1
group by t1.col3,t1.col4,t1.col5
order by t1.col3,t1.col4,t1.col5
;

-- 4. 結果

shared_buffers -> コストに影響あり★
work_mem -> コストに影響あり★

• SQL Server

(2019)
https://docs.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-min-memory-per-query-server-configuration-option?view=sql-server-ver15

max server memory (MB) ＝ 最大サーバーメモリ
min memory per query = クエリ毎に使用する最小メモリ

max server memoryとmin memory per queryとを変更し、
実行計画を比較する

-- 1. テストデータ作成
drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 bigint
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
  );

set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
  insert into tab1 values(
     @i
    ,floor(rand() * 100000)+1
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
 set @i = @i + 1;
end
commit;

select count(*) from tab1;
select top 20 * from tab1 order by newid();

update statistics tab1;

-- 2. パラメータ変更 

exec sp_configure 'show advanced options', 1;
go
RECONFIGURE WITH OVERRIDE;  
go

exec sp_configure 'max server memory (MB)', 1000;
go
RECONFIGURE WITH OVERRIDE;  
go

exec sp_configure 'min memory per query', 512;
go
RECONFIGURE WITH OVERRIDE;  
go

exec sp_configure ;
go

-- 3. 実行計画取得

select t1.col3,t1.col4,t1.col5,count(*) from tab1 t1
inner join tab1 t2 on t1.col1 = t2.col1
inner join tab1 t3 on t1.col1 = t3.col1
group by t1.col3,t1.col4,t1.col5
order by t1.col3,t1.col4,t1.col5
;

-- 4. 結果

max server memory (MB) -> コストに影響なし
min memory per query -> コストに影響なし