2021.10.22

SQL Profileを利用したSQL Plan変更

SQL Profileを利用したSQL Plan変更

エクセムコンサルティング本部/DBコンサルティングチーム

本文書はDBMS_SQLTUNE Packageを利用してSQL Planを変更するPLAN_SET_OUTLINE (仮称) User Define Procedureに関する文で、次のような内容を含みます。

・SQL Plan変更の必要性
・SQL Plan変更のための機能は9iから提供
・何が違っているか?
・SQL Profileとは
・OUTLINEとは
・PLAN_SET_OUTLINE Sourc
・使用例
・注意事項 & 高度な使用

1. SQL Plan変更の必要性

Oracleを使うサイトならSQL性能問題を一度は経験してるはずです。

普通、非効率SQLを修正してApplicationに反映して正常なサービスを運営するのには多くの時間が必要となるなります。

該当のSQLが実行される業務と関連する業務が影響を受けるためサービスを一時停止しなければならない場合もあって、非効率SQLがどのソースで実行されるのかを把握する時間によっては、容易に解決される事が障害状況まで発展することもあります。

最もありがちなのは統計情報生成による場合から、ソース配布過程で検証されないSQLが配布される場合など非効率SQLが発生するケースは多様です。

2. SQL Plan変更のための機能は9iから提供

Oracleは、9iバージョンからStored Outlineを利用してSQLプランを変更する方法を提供しました。

皮肉なことにOracle 9iではStored Outlineを利用したプラン変更は実運用環境で(少なくとも筆者の経験では)正常動作しません。

9iで登場したStored Outlineを利用したSQLプラン変更は10gバージョンから正常に動作します。

Oracle 10gにはSQL Profileという機能も新しく登場します。

簡単に言えば、SQL ProfileはStored Outlineを含むさらに大きいフレームと言うことができます。

3. 何が違うか?

すでにオンライン上にはSQL Plan変更のための理論と方法が整理された数々の技術文書が存在します。しかし残念ながら実環境で使うには、色々と複雑で手間がかかります。SQL Plan変更には、はやい解決が重要なポイントとなります。 10gでは、SQL Profileを利用してプランを変更しようとするとOutlineを一つ一つ書いていかなければならないが、面倒な作業ではありません。

筆者のProcedureは使用が非常に簡単です。

EXEC PLAN_SET_OUTLINE(「改善前 SQLID」、「改良後 SQLID」);

プランが変更されました

SQL Profileとは

SQL Profileは特定SQL文に接続することができるSQLコンパイラー統計の集合を言います。 SQL Profileが現在のSQL文に生成されれば、SQL分析(コンパイル)を実行する時プロファイルの統計はコンパイラーによって使われます。

SQL文章の正規化されたテキストがSQLプログラムファイル作成時に提供されたSQLテキストと一致する場合、プログラムファイルがSQLコンパイルに使われます。

SQLテキストの正規化とは、すべての非Literalテキストを大文字に変更して、空きスペースを無くすことを言います

コンパイルを実行したセッションではSQLプログラムファイルが生成されたカテゴリー及びParameterに対して等しい値を持っていなければなりません。

カテゴリーは、複数のプロファイルが同一SQL文に存在するのを許容します。
一セッションで特定のカテゴリーネームスペースのプロファイル作成し、非公開プロファイルをテストできます。

SQLプログラムファイルは次のようなSQL文に対して使用可能です。

SELECT statements

UPDATE statements

INSERT (but only with a SELECT clause) statements

DELETE statements

CREATE TABLE (but only with the AS SELECT clause)

MERGE statements (the upsert operation)

Data Dictionaryで内部的に実行されたSQL門(再帰SQL)に対してはプロファイルを使うことができません。

また、データベースがオープンになる前に遂行されるすべてのSQLもプロファイルを検索、使用することができません。

OUTLINEとは

百聞は一見にしかずです。生成されたOutlineを先によく見ましょう。

/*+

BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’) DB_VERSION(‘11.2.0.3’)

OPT_PARAM(‘_optim_peek_user_binds’ ‘false’) PUSH_PRED(@”SEL$1″ “PLAN”@”SEL$1″ 11 10 9) OUTLINE(@”SEL$1”)

NO_ACCESS(@”SEL$1″ “PLAN”@”SEL$1″) USE_NL(@”SEL$1” “C”@”SEL$1”)

PUSH_SUBQ(@”SEL$4″) NLJ_BATCHING(@”SEL$4″ “PL”@”SEL$4”)

END_OUTLINE_DATA

OutlineはOracle Optimizerが最適のプランを作るために実行した作業の結果と見ることができます。

実際にOUTLINE情報は10053 Traceで終わりに確認できます。

OUTLINEはDBMS_XPLAN Packageを通じても確認可能です。

PLAN_SET_OUTLINE Source

— GRANT ADMINISTER SQL MANAGEMENT OBJECT to USER;アクセス許可が必要です

create or replace procedure PLAN_SET_OUTLINE(v_sql_id_asis in varchar2, v_sql_id_tobe in varchar2)

l_sql_fulltext clob;

l_hint_table sys.dbms_debug_vc2coll;

l_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();i pls_integer;

begin

select sql_fulltext

INTO l_sql_fulltext

from v$sqlarea

where sql_id = v_sql_id_asis;

with a as ( select

from

rownum as r_no

, a.*

table(

dbms_xplan.display_cursor( v_sql_id_tobe

, null

, ‘OUTLINE’

)

) a

)

select substr(plan_table_output, 7) outln bulk collect into l_hint_table

from a

where r_no between ( select min(r_no) from a where instr(a.PLAN_TABLE_OUTPUT, ‘BEGIN_OUTLINE_DATA’) = 7)

and ( select min(r_no) from a where instr(a.PLAN_TABLE_OUTPUT, ‘END_OUTLINE_DATA’) = 7)

order by r_no;

i := l_hint_table.first;while i is not null loop

if l_hint_table.exists(i + 1) then

if substr(l_hint_table(i + 1), 1, 1) = ‘ ‘ then

l_hint_table(i) := l_hint_table(i) ||trim(l_hint_table(i + 1));l_hint_table.delete(i + 1);

end if;end if;

i := l_hint_table.next(i);end loop;

i := l_hint_table.first;while i is not null

loop

l_profile_hints.extend;l_profile_hints(l_profile_hints.count) := l_hint_table(i);i := l_hint_table.next(i);

end loop;

dbms_sqltune.import_sql_profile( name => ‘PROFILE_’||v_sql_id_asis

,replace => TRUE

,sql_text => l_sql_fulltext

,profile => l_profile_hints

);

end PLAN_SET_OUTLINE;

7. 使用例

[1] TEST用データ生成

create table exem_t1 as

select level c1, level c2, LPAD(LEVEl, 100, ‘_’) C3 from dual connect by level <= 1000;

create index ix_exem_t1_01 on exem_t1(c1);create index ix_exem_t1_02 on exem_t1(c2);

<Trace遂行のためのParameter適用>

alter session set statistics_level = ‘ALL’;

[2] 非効率SQLのSQL ID確認。

select * from exem_t1 t1

where c1 in (select c1 from exem_t1 where c2 <= 10);

select prev_sql_id, (select substr(sql_text,1,64) text from v$sqlarea where sql_id = prev_sql_id) text from v$session where sid = userenv(‘sid’);

PREV_SQL_ID TEXT

————– ———————————————————————

0h60tr4xc2mub select * from exem_t1 t1 where c1 in (select c1 from exem_t1 whe

select * from table(dbms_xplan.display_cursor(‘0h60tr4xc2mub’, null, ‘typical allstats last’));

———————————————————————————–

——————————————————————————————

\|	0 \|SELECT STATEMENT \|	\|	1 \|	10 \|00:00:00.01 \|
\|*	1 \|HASH JOIN RIGHT SEMI \|	\|	1 \|	10 \|00:00:00.01 \|
\|	2 \| TABLE ACCESS BY INDEX ROWID\|EXEM_T1	\|	1 \|	10 \|00:00:00.01 \|
\|*	3 \| INDEX RANGE SCAN \|IX_EXEM_T1_02	\|	1 \|	10 \|00:00:00.01 \|
\|	4 \| TABLE ACCESS FULL \|EXEM_T1	\|	1 \|	1000 \|00:00:00.01 \|

——————————————————————————————-

[3] 改善したSQLのSQL ID確認

select /*+ LEADING(exem_t1@SEL$2) USE_NL(t1) */ * from exem_t1 t1 where c1 in (select /*+ UNNEST */ c1 from exem_t1 where c2 <= 10);

select prev_sql_id, (select substr(sql_text,1,64) text from v$sqlarea where sql_id = prev_sql_id) text from v$session where sid = userenv(‘sid’);

PREV_SQL_ID TEXT

————– ———————————————————————

0bjfkr111hxpa select /*+ LEADING(exem_t1@SEL$2) USE_NL(t1) */ * from exem_t1 A where c1

select * from table(dbms_xplan.display_cursor(‘0bjfkr111hxpa’, 0, ‘typical allstats last’));

———————————————————————————–

———————————————————————————–

\|	0 \|SELECT STATEMENT \| \|	1 \|	10 \|	00:00:00.01	\|
\|	1 \|NESTED LOOPS \| \|	1 \|	10 \|	00:00:00.01	\|
\|	2 \| NESTED LOOPS \| \|	1 \|	10 \|	00:00:00.01	\|
\|	3 \| SORT UNIQUE \| \|	1 \|	10 \|	00:00:00.01	\|
\|	4 \| TABLE ACCESS BY INDEX ROWID\|EXEM_T1 \|	1 \|	10 \|	00:00:00.01	\|
\|*	5 \| INDEX RANGE SCAN \|IX_EXEM_T1_02 \|	1 \|	10 \|	00:00:00.01	\|
\|*	6 \| INDEX RANGE SCAN \|IX_EXEM_T1_01 \|	10 \|	10 \|	00:00:00.01	\|
\|	7 \| TABLE ACCESS BY INDEX ROWID \|EXEM_T1 \|	10 \|	10 \|	00:00:00.01	\|

———————————————————————————–

[4] SQL Profile交替

— PLAN_SET_OUTLINE(‘ASIS_SQL_ID’, ‘TOBE_SQL_ID’)

exec PLAN_SET_OUTLINE(‘0h60tr4xc2mub’, ‘0bjfkr111hxpa’);

[5] Outline適用確認

<DBMS_XPLAN note 項目を通じた確認>

————————————————————————————-

————————————————————————————–

| 0 |SELECT STATEMENT | | 1 | 10 |00:00:00.01 |

\|	1 \|	NESTED LOOPS \| \|	1 \|	10 \|00:00:00.01 \|
\|	2 \|	NESTED LOOPS \| \|	1 \|	10 \|00:00:00.01 \|
\|	3 \|	SORT UNIQUE \| \|	1 \|	10 \|00:00:00.01 \|
\|	4 \|	TABLE ACCESS BY INDEX ROWID\|EXEM_T1 \|	1 \|	10 \|00:00:00.01 \|
\|*	5 \|	INDEX RANGE SCAN \|IX_EXEM_T1_02 \|	1 \|	10 \|00:00:00.01 \|
\|*	6 \|	INDEX RANGE SCAN \|IX_EXEM_T1_01 \|	10 \|	10 \|00:00:00.01 \|
\|	7 \|	TABLE ACCESS BY INDEX ROWID \|EXEM_T1 \|	10 \|	10 \|00:00:00.01 \|

————————————————————————————–

Note

—–

– SQL profile PROFILE_0h60tr4xc2mub used for this statement

<V$SQLAREA.sql_profileを利用した確>

select sql_id, sql_profile

from v$sqlarea

where sql_id = ‘0h60tr4xc2mub’;

SQL_ID	SQL_PROFILE
————- 0h60tr4xc2mub	——————— PROFILE_0h60tr4xc2mub	<<– プロファイル適用された以後にクエリが実行してれます

sql_profileコラムに表示されます。