(5.6)
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
declare j int;
declare k int;
set i = 1;
set j = 1;
set k = 1;
while i <= 1000000 do
insert into tab1 values(i,j,rpad('A',100,j));
set i = i + 1;
set k = k + 1;
if k > 10000 then
set j = j + 1;
set k = 1;
end if;
end while;
end
//
delimiter ;
call proc1();
alter table tab1 add constraint tab1p primary key (col1);
create index ind12 on tab1(col2);
analyze table tab1;
--特異レコード追加
insert into tab1 values(0, 1000000, 'tokui') ;
analyze table tab1;
1.リテラルの場合
select max(md5(COL3)) from tab1 where col2 >= 1000000 ;
select max(md5(COL3)) from tab1 where col2 >= 0 ;
explain select max(md5(COL3)) from tab1 where col2 >= 1000000 ;
explain select max(md5(COL3)) from tab1 where col2 >= 0 ;
2.バインド変数の場合
PREPARE stmt1 FROM 'select max(md5(COL3)) from tab1 where col2 >= ?';
SET @i = 1000000;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'select max(md5(COL3)) from tab1 where col2 >= ?';
SET @i = 0;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'explain select max(md5(COL3)) from tab1 where col2 >= ?';
SET @i = 1000000;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'explain select max(md5(COL3)) from tab1 where col2 >= ?';
SET @i = 0;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;
→
バインド変数の場合もSQL実行ごとに実行計画を生成(バインドピーク)
(12cR1)
https://www.oracle.com/technetwork/jp/database/articles/shibacho/index-2405696-ja.html
drop table tab1 purge;
create table tab1(col1 int not null,col2 int,col3 char(100));
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(i,j,rpad('A',100,j));
commit;
k := k+1;
if k > 10000 then
j := j+1;
k := 1;
end if;
end loop;
end;
/
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1p primary key (col1) using index ind11;
create index ind12 on tab1(col2);
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'TAB1');
--特異レコード追加
insert into tab1 values(0, 1000000, 'tokui') ;
commit ;
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'TAB1', method_opt =>'for all columns size 255');
col table_name for a12
col column_name for a12
select table_name, column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'TAB1' and column_name = 'COL2' ;
col column_name for a5
select table_name, column_name, endpoint_number, endpoint_value
from user_tab_histograms
where table_name = 'TAB1' and column_name = 'COL2';
1.リテラルの場合
set timing on
select max(ora_hash(COL3,65535)) from tab1 where col2 >= 1000000 ;
select * from table(dbms_xplan.display_cursor()) ;
select max(ora_hash(COL3,65535)) from tab1 where col2 >= 0 ;
select * from table(dbms_xplan.display_cursor()) ;
2.バインド変数の場合
set timing on
variable col2 number;
execute :col2 := 1000000
select max(ora_hash(COL3,65535)) from tab1 where col2 >= :col2;
select * from table(dbms_xplan.display_cursor()) ;
variable col2 number;
execute :col2 := 0
select max(ora_hash(COL3,65535)) from tab1 where col2 >= :col2;
select * from table(dbms_xplan.display_cursor()) ;
→
最初に実行されたSQLのバインド変数の値によって実行計画が決まる。(バインドピーク)
不適切な実行計画は2回目のSQL実行により適切な実行計画となる。(Adaptive Cursor Sharing)
隠しパラメータ"_optim_peek_user_binds"によりバインドピークを無効化し、
バインド変数に依存しない実行計画に固定化可能
select ksppinm as "Hidden Parameter", ksppstvl as "Value"
from x$ksppi join x$ksppcv using (indx)
where ksppinm = '_optim_peek_user_binds';
alter system set "_optim_peek_user_binds" = false;
(13)
https://www.postgresql.org/docs/current/sql-prepare.html
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
do $$
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(i,j,rpad('A',100,j::char(3)));
k := k+1;
if k > 10000 then
j := j+1;
k := 1;
end if;
end loop;
end
$$
;
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1p primary key using index ind11;
create index ind12 on tab1(col2);
analyze tab1;
--特異レコード追加
insert into tab1 values(0, 1000000, 'tokui') ;
analyze tab1;
select * from pg_stats where tablename = 'tab1';
1.リテラルの場合
\timing on
select max(md5(COL3)) from tab1 where col2 >= 1000000 ;
select max(md5(COL3)) from tab1 where col2 >= 0 ;
explain select max(md5(COL3)) from tab1 where col2 >= 1000000 ;
explain select max(md5(COL3)) from tab1 where col2 >= 0 ;
2.バインド変数の場合
prepare stmt1(int) as select max(md5(COL3)) from tab1 where col2 >= $1;
execute stmt1(1000000);
deallocate stmt1;
prepare stmt1(int) as select max(md5(COL3)) from tab1 where col2 >= $1;
execute stmt1(0);
deallocate stmt1;
prepare stmt1(int) as select max(md5(COL3)) from tab1 where col2 >= $1;
explain execute stmt1(1000000);
deallocate stmt1;
prepare stmt1(int) as select max(md5(COL3)) from tab1 where col2 >= $1;
explain execute stmt1(0);
deallocate stmt1;
(1)plan_cache_mode=auto（デフォルト)の場合、
パラメータのあるプリペアド文に対して、汎用的な計画を使うか独自の計画を使うかを、サーバは自動的に選択します。
最初の5回が独自の計画で実行され、その計画の推定コストの平均が計算される。
それから汎用的な計画が作成され、その推定コストが独自の計画のコストの平均と比較されます。
→今回のケースでは、バインド変数の場合もSQL実行ごとに実行計画を生成(バインドピーク)
(2)plan_cache_mode=force_custom_planの場合、パラメータのあるプリペアド文に対して、独自の計画を使う
→バインド変数の場合もSQL実行ごとに実行計画を生成(バインドピーク)
(3)plan_cache_mode=force_generic_planの場合、パラメータのあるプリペアド文に対して、汎用的な計画を使う
→実行計画は同一でバインドピークは無効となる
(2019)
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
declare @i int;
declare @j int;
declare @k int;
set @i= 1;
set @j= 1;
set @k= 1;
while (@i <= 1000000)
begin
insert into tab1 values (@i,@j,'A' + left(replicate(convert(varchar,@j),100),99));
set @i = @i + 1;
set @k = @k + 1;
if @k > 10000
begin
set @j = @j + 1;
set @k = 1;
end
end
go
alter table tab1 add constraint tab1p primary key (col1);
create index ind12 on tab1(col2);
update statistics tab1;
--特異レコード追加
insert into tab1 values(0, 1000000, 'tokui') ;
update statistics tab1;
DBCC SHOW_STATISTICS (tab1, ind12) WITH HISTOGRAM,NO_INFOMSGS
1.リテラルの場合
set statistics time on
go
set statistics profile on;
go
select max(hashbytes('sha2_256', col3)) from tab1 where col2 >= 1000000 ;
select max(hashbytes('sha2_256', col3)) from tab1 where col2 >= 0 ;
2.バインド変数の場合
set statistics time on
go
set statistics profile on;
go
declare @P1 int
declare @sql nvarchar(max)
declare @param nvarchar(2000)
declare @i int
set @sql = 'select max(hashbytes(''sha2_256'', col3)) from tab1 where col2 >= @i'
set @param = N'@i int'
exec sys.sp_prepare @P1 output, @param, @sql;
set @i = 1000000
exec sys.sp_execute @P1, @i;
set @i = 0
exec sys.sp_execute @P1, @i;
exec sys.sp_unprepare @P1;
-- dbcc freeproccache
→どちらを先に実行しても実行計画は同一でバインドピークは無効と思われる(ドキュメントの記載と異なる)
※
初回実行時のパラメータを使用して実行計画を作成することを「パラメーター スニッフィング」とよぶ。
デフォルトで有効。無効にすると統計的に最も一般的な値を使用する。
下記が対象
Stored procedures
Queries submitted via sp_executesql
Prepared queries
-- 無効にする場合(データベースレベル)
alter database scoped configuration set parameter_sniffing = OFF;
-- 設定確認
select * from sys.database_scoped_configurations;
-- 無効にする場合(クエリレベル)
OPTIMIZE FOR UNKNOWN クエリ ヒント
