(8.0.18)
パラレル処理をサポートしていない
(12cR1)
--パラレル実行有効化
alter session enable parallel dml;
【1】insert
drop table tab1 purge;
create table tab1(col1 int,col2 int);
drop table tab2 purge;
create table tab2(col1 int,col2 int);
drop table tab3 purge;
create table tab3(col1 int,col2 int);
declare
type t_tab1 is table of tab1%rowtype index by pls_integer;
r_tab1 t_tab1;
begin
for k in 1..10 loop
for i in 1..1000000 loop
r_tab1(i).col1 := k*1000000+i;
r_tab1(i).col2 := k*1000000+i;
end loop;
forall i in 1..1000000
insert into tab1 values r_tab1(i);
forall i in 1..1000000
insert into tab2 values r_tab1(i);
commit;
end loop;
end;
/
set time on
set timing on
explain plan for
insert into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
insert /*+ parallel(4) */ into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
→パラレル実行されており、処理時間改善
【2】update
drop table tab1 purge;
create table tab1(col1 int,col2 int);
drop table tab2 purge;
create table tab2(col1 int,col2 int);
declare
begin
for i in 1..10000 loop
insert into tab1 values(i,i);
insert into tab2 values(i,i);
commit;
end loop;
end;
/
set time on
set timing on
explain plan for
update tab1 t1
set col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
update /*+ parallel(4) */ tab1 t1
set col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
→パラレル実行されており、処理時間改善
【3】delete
drop table tab1 purge;
create table tab1(col1 int,col2 int);
drop table tab2 purge;
create table tab2(col1 int,col2 int);
declare
begin
for i in 1..10000 loop
insert into tab1 values(i,i);
insert into tab2 values(i,i);
commit;
end loop;
end;
/
set time on
set timing on
explain plan for
delete from tab1 t1
where col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
delete /*+ parallel(4) */ from tab1 t1
where col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
→パラレル実行されており、処理時間改善
(12)
マニュアルに下記記載があるため、パラレルDMLは動作しないはず、念のため確認する
"一般にパラレルクエリプランが生成可能な場合でも、
以下のうち一つでも真であると、プランナはクエリに対するパラレルクエリプランを生成しません。
クエリがデータを書き込むか、データベースの行をロックする場合。"
--パラレル実行有効化
show max_worker_processes;
show max_parallel_workers_per_gather;
set max_parallel_workers_per_gather = 4;
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;
【1】insert
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
drop table tab3;
create table tab3(col1 int,col2 int);
insert into tab1 select a,a from generate_series(1,1000000) a;
insert into tab2 select a,a from generate_series(1,1000000) a;
\timing 1
set max_parallel_workers_per_gather = 0;
explain
insert into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1;
set max_parallel_workers_per_gather = 4;
explain
insert into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1;
→パラレル実行なし
【2】update
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
insert into tab1 select a,a from generate_series(1,10000) a;
insert into tab2 select a,a from generate_series(1,10000) a;
\timing 1
set max_parallel_workers_per_gather = 0;
explain
update tab1 t1
set col2 = t2.col2
from tab2 t2
where t2.col1 = t1.col1
;
set max_parallel_workers_per_gather = 4;
explain
update tab1 t1
set col2 = t2.col2
from tab2 t2
where t2.col1 = t1.col1
;
→パラレル実行なし
【3】delete
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
insert into tab1 select a,a from generate_series(1,10000) a;
insert into tab2 select a,a from generate_series(1,10000) a;
\timing 1
set max_parallel_workers_per_gather = 0;
explain
delete from tab1 t1
using tab2 t2
where t2.col1 = t1.col1
;
set max_parallel_workers_per_gather = 4;
explain
delete from tab1 t1
using tab2 t2
where t2.col1 = t1.col1
;
→パラレル実行なし
(2014)
--パラレル実行有効化
----クエリの並列プランを作成および実行するしきい値を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
【1】insert
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
drop table tab3;
create table tab3(col1 int,col2 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);
insert into tab2 values(@i,@i);
set @i = @i + 1;
end
end
;
exec proc1;
set statistics time on;
go
set showplan_all on;
go
insert into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1 OPTION (MAXDOP 1);
insert into tab3 select t1.col1,t2.col2 from tab1 t1 inner join tab2 t2 on t1.col1 = t2.col1 OPTION (MAXDOP 4);
set showplan_all off;
go
→パラレル実行されており、処理時間改善
【2】update
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
drop procedure proc1;
create procedure proc1
as
begin
declare @i integer;
set @i = 1;
while @i <= 10000
begin
insert into tab1 values(@i,@i);
insert into tab2 values(@i,@i);
set @i = @i + 1;
end
end
;
exec proc1;
set statistics time on;
go
set showplan_all on;
go
update t1
set t1.col2 = t2.col2
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
OPTION (MAXDOP 1);
update t1
set t1.col2 = t2.col2
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
OPTION (MAXDOP 4);
set showplan_all off;
go
→パラレル実行されているが、処理時間やや悪化
【3】delete
drop table tab1;
create table tab1(col1 int,col2 int);
drop table tab2;
create table tab2(col1 int,col2 int);
drop procedure proc1;
create procedure proc1
as
begin
declare @i integer;
set @i = 1;
while @i <= 10000
begin
insert into tab1 values(@i,@i);
insert into tab2 values(@i,@i);
set @i = @i + 1;
end
end
;
exec proc1;
set statistics time on;
go
set showplan_all on;
go
delete tab1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
OPTION (MAXDOP 1);
delete tab1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
OPTION (MAXDOP 4);
set showplan_all off;
go
→パラレル実行なし
