プランガイド

DB

SQL文を変更せずに、実行計画を修正する機能

調べた限りなし

(19c)

[1] プランスタビリティ( ストアドアウトライン )
※レガシーのため、SQL計画管理への移行が推奨される


-- 検証方法
ストアドアウトラインでヒント句を使用せずにフルスキャンの実行計画を作成し、
意図的にインデックスを使用しない実行計画を強制する

-- テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int, col2 int);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind12 on tab1(col2);

-- ストアドアウトライン用データ作成
truncate table tab1;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,0);
commit;
end loop;
end;
/

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

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));


-- ストアドアウトライン作成

create or replace public outline outline01 for category cat01
on select count(col1) from tab1 where col2 = 0;


select * from all_outlines where category='CAT01';
select * from all_outline_hints where name='OUTLINE01';


-- 検証用データ作成

truncate table tab1;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,i);
commit;
end loop;
end;
/

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

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

→実行計画はインデックスを使用している


-- ストアドアウトライン動作確認

alter session set USE_STORED_OUTLINES = CAT01;

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

→実行計画がFULL SCANに変化


[2] ストアドアウトラインのヒント句を出力して直接SQLに埋め込む方法
https://bismarc256.hateblo.jp/entry/2021/09/13/200000

-- テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int, col2 int);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind12 on tab1(col2);

-- ストアドアウトライン用データ作成
truncate table tab1;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,0);
commit;
end loop;
end;
/

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

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));


-- ヒント句を取得

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'OUTLINE'));

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TAB1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


↑
BEGIN_OUTLINE_DATA
all_outline_hints.nameの内容
END_OUTLINE_DATA

-- 検証用データ作成

truncate table tab1;

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,i);
commit;
end loop;
end;
/

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

explain plan for
select count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

→実行計画はインデックスを使用している

explain plan for
select 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TAB1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
count(col1) from tab1 where col2 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

→実行計画がFULL SCANに変化

 

 

 

 

(12)

-- pg_hint_planのテーブルでヒント句を指定する方法


-- インストール

yum install https://ja.osdn.net/projects/pghintplan/downloads/72398/pg_hint_plan12-1.3.5-1.el7.x86_64.rpm

CREATE EXTENSION pg_hint_plan;

 

vim postgresql.conf
shared_preload_libraries = 'pg_hint_plan'


-- 検証方法
pg_hint_planのテーブル指定による方法でフルスキャンのヒント句を指定し、
意図的にインデックスを使用しない実行計画を強制する

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int, col2 int);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind12 on tab1(col2);

-- テスト用データ作成
truncate table tab1;

do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,i);
end loop;
end
$$
;

analyze tab1;


explain analyze
select count(col1) from tab1 where col2 = 0;

-- ヒント句動作確認

/*+ SeqScan(tab1) */
explain analyze
select count(col1) from tab1 where col2 = 0;


-- ヒント用のテーブルにヒントを登録

select * from hint_plan.hints;


INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
VALUES (
'explain analyze select count(col1) from tab1 where col2 = ?;',
'',
'SeqScan(tab1)'
);


-- テーブルで指定するヒント句の動作確認
show pg_hint_plan.enable_hint_table;
set pg_hint_plan.enable_hint_table = 'on';

explain analyze select count(col1) from tab1 where col2 = 0;

→実行計画がFULL SCANに変化

 

(2019)
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/plan-guides?view=sql-server-ver15
https://qiita.com/HirokiSakonju/items/010344f0b6d7f4560ce4

 

-- 検証方法
プランガイドでヒント句を適用し、ハッシュジョインを強制する

※マニュアルには下記のような記載があるが、試したかぎり指定できない
「INDEX、FORCESCAN、および FORCESEEK のテーブル ヒントをクエリ ヒントとして指定できます」

※スペースや改行も含めて完全一致しないとプランガイドは適用されない
(SSMSで実行する場合、プランガイドで指定したSQL文と同じ範囲を選択して実行する必要がある)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int not null, col2 int);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind12 on tab1(col2);

drop table tab2;
create table tab2(col1 int not null, col2 int);
alter table tab2 add constraint tab2pk primary key(col1);


-- 検証データ作成
truncate table tab1;
truncate table tab2;

declare @i int;
set @i = 1;

while (@i <= 100)
begin
insert into tab1 values (@i,@i);
insert into tab2 values (@i,@i);
set @i = @i + 1;
end


update statistics tab1;
update statistics tab2;

 

SET STATISTICS PROFILE ON;
go

select count(*) from tab1 t1 inner join tab2 t2 on t1.col2 = t2.col2 where t2.col2 = 10

→結合方式はNestedLoop

SET STATISTICS PROFILE OFF;
go

-- プランガイド作成

EXEC sp_create_plan_guide
@name = N'plan01',
@stmt = N'select count(*) from tab1 t1 inner join tab2 t2 on t1.col2 = t2.col2 where t2.col2 = 10',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (HASH JOIN)'
;

select * from sys.plan_guides;

-- 動作確認

SET STATISTICS PROFILE ON;
go

select count(*) from tab1 t1 inner join tab2 t2 on t1.col2 = t2.col2 where t2.col2 = 10

→結合方式はHash Joinに変化

SET STATISTICS PROFILE OFF;
go