(8.0.21)
※8.0.14から一部のSQLについてパラレル実行可能
select @@session.innodb_parallel_read_threads;
SET @@session.innodb_parallel_read_threads=2;
SET @@session.innodb_parallel_read_threads=1;
-- テストデータ作成
drop table tab1;
create table tab1(col11 int primary key,col12 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 1000000 do
insert into tab1 values(i,i);
set i = i+1;
end while;
end
//
delimiter ;
call proc1();
select count(*) from tab1;
【1】CREATE INDEX
SET @@session.innodb_parallel_read_threads=1;
SET @@session.innodb_parallel_read_threads=2;
drop index ind1 on tab1;
create index ind1 on tab1(col12);
シリアル実行の場合 → 1.35 sec
パラレル実行の場合 → 1.32 sec
→DDLはパラレル化対象外の模様
(DDLはexplain構文をサポートしていない)
【2】CREATE TABLE AS SELECT
SET @@session.innodb_parallel_read_threads=1;
SET @@session.innodb_parallel_read_threads=2;
drop table tab2;
create table tab2 as
select t1.*
from tab1 t1, tab1 t2
where t1.col11 <= 1000
and t2.col11 <= 1000
;
シリアル実行の場合 → 3.38 sec
パラレル実行の場合 → 3.41 sec
→DDLはパラレル化対象外の模様
(12cR1)
--パラレルDDL強制
ALTER SESSION FORCE PARALLEL DDL;
--パラレルDDL無効化
ALTER SESSION disable PARALLEL DDL;
-- テストデータ作成
drop table tab1 purge;
create table tab1(col11 int primary key,col12 int);
select table_name,degree from user_tables where table_name ='TAB1';
declare
type t_tab1 is table of tab1%rowtype index by pls_integer;
r_tab1 t_tab1;
begin
for k in 0..10 loop
for i in 1..1000000 loop
r_tab1(i).col11 := k*1000000+i;
r_tab1(i).col12 := k*1000000+i;
end loop;
forall i in 1..1000000
insert into tab1 values r_tab1(i);
commit;
end loop;
end;
/
select count(*) from tab1;
set time on
set timing on
【1】CREATE INDEX
drop index ind1;
explain plan for
create index ind1 on tab1(col12);
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
シリアル実行の場合 → 経過: 00:00:11.29
パラレル実行の場合 → 経過: 00:00:08.03
→パラレル実行されており、処理時間改善
【2】CREATE TABLE AS SELECT
drop table tab2;
explain plan for
create table tab2 as
select t1.*
from tab1 t1, tab1 t2
where t1.col11 <= 1000
and t2.col11 <= 1000
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
シリアル実行の場合 → 経過: 00:00:00.70
パラレル実行の場合 → 経過: 00:00:02.40
→実行計画ではパラレル化されているが、処理時間は悪化
※パラレルDDLを強制してcreate tableするとdegreeがDEFAULTと設定され、
テーブル定義レベルでパラレル実行が有効になる。
(13)
--パラレル実行有効化
show max_worker_processes;
show max_parallel_workers_per_gather;
set max_parallel_workers_per_gather = 2;
show max_parallel_maintenance_workers;
set max_parallel_maintenance_workers = 2;
show max_parallel_workers;
set max_parallel_workers = 8;
show parallel_tuple_cost;
set parallel_tuple_cost = 0;
show parallel_setup_cost;
set parallel_setup_cost = 0;
show force_parallel_mode;
set force_parallel_mode = 'on';
show min_parallel_index_scan_size;
set min_parallel_index_scan_size = 0;
show min_parallel_table_scan_size;
set min_parallel_table_scan_size = 0;
-- テストデータ作成
drop table tab1;
create table tab1(col11 int primary key,col12 int);
insert into tab1 select a,a from generate_series(1,10000000) a;
select count(*) from tab1;
\timing 1
【1】CREATE INDEX
set max_parallel_workers_per_gather = 0;
set max_parallel_workers_per_gather = 2;
drop index ind1;
create index ind1 on tab1(col12);
シリアル実行の場合 → 時間: 4602.367 ミリ秒
パラレル実行の場合 → 時間: 4590.905 ミリ秒
→実行計画は不明、処理時間は相違なし
※CREATE INDEXはexplain実行不可
【2】CREATE TABLE AS SELECT
set max_parallel_workers_per_gather = 0;
set max_parallel_workers_per_gather = 2;
drop table tab2;
explain
create table tab2 as
select t1.*
from tab1 t1, tab1 t2
where t1.col11 <= 1000
and t2.col11 <= 1000
;
シリアル実行の場合 → 時間: 772.598 ミリ秒
パラレル実行の場合 → 時間: 776.940 ミリ秒
→実行計画ではパラレル化されているが、処理時間はほぼ相違なし
※CREATE TABLE AS SELECTはexplain実行可能
(2019)
--パラレル実行有効化
----クエリの並列プランを作成および実行するしきい値を0に設定
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 0;
GO
reconfigure;
GO
----MAXDOP規定値を0(全CPUを使用)に設定
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
-- テストデータ作成
drop table tab1;
create table tab1(col11 int primary key,col12 int);
drop procedure proc1;
create procedure proc1
as
begin
declare @i integer;
set @i = 1;
while @i <= 1000000
begin
insert into tab1 values(@i,@i);
set @i = @i + 1;
end
end
;
exec proc1;
select count(*) from tab1;
set statistics time on;
go
set showplan_all on;
go
【1】CREATE INDEX
drop index ind1 on tab1;
create index ind1 on tab1(col12);
シリアル実行の場合 → 経過時間 = 575 ミリ秒
パラレル実行の場合 → 経過時間 = 1133 ミリ秒
→実行計画ではパラレル化されているが、処理時間は悪化
※create indexはOPTION句が使用できないためグローバルのMAXDOPを変更して測定
【2】CREATE TABLE AS SELECT
drop table tab2;
select t1.*
into tab2
from tab1 t1, tab1 t2
where t1.col11 <= 1000
and t2.col11 <= 1000
OPTION (MAXDOP 1);
select t1.*
into tab2
from tab1 t1, tab1 t2
where t1.col11 <= 1000
and t2.col11 <= 1000
OPTION (MAXDOP 2);
シリアル実行の場合 → 経過時間 = 351 ミリ秒
パラレル実行の場合 → 経過時間 = 482 ミリ秒
→実行計画ではパラレル化されているが、処理時間は悪化
