非効率な実行計画が選択されたことで、SQLの性能が悪かった場合、Oracle DatabaseではSQLにヒント句を付与することで、効率的な実行計画を選択させる手段を採ることが多いかと思います。
一方で、パッケージ製品が発行するSQLや、様々な事情によりアプリケーション(が発行するSQL文)の改修が困難な場合、SQLにヒント句を付与する対応ができない場合もあります。
Oracle DatabaseにはSQLの実行計画を管理する、SQL Plan Management(SPM: SQL計画管理)という機能があり、これを使用することでSQLを書き換えることなく、意図した実行計画を選択させることができるので、今回はこのやり方について調べてみました。
SPMを用いた実行計画の変更手順は、以下の様な流れになります。この流れに沿って実行するSQLや出力結果を記載していきます。今回はLinux x86-64版のOracle19c(19.20.0)を用いて実機確認を行いました。
- SQL計画ベースラインへの実行計画の登録
- 改善された実行計画で動くSQLの実行
- 改善された実行計画のSQL計画ベースラインへの追加登録
- SQL計画ベースラインから改善前の実行計画の削除
- 改善結果の確認
※ここではSPMの詳細については割愛しますので、知りたい方はこちらをご参照ください。
1.SQL計画ベースラインへの実行計画の登録
まずSQL計画ベースラインへ改善対象SQLの実行計画を登録します。登録方法はいくつかありますが、ここでは1回SQLを実行してカーソルキャッシュを生成して、それをSQL計画ベースラインへ登録します。
大量のレコードを返却する様なSELECT文が改善対象の場合、返却レコードの表示で時間を要してしまうため、下記の様にAUTOTRACE機能を使うことで返却レコードの表示処理の省略と実行計画の確認ができつつ、カーソルキャッシュを生成することができます。
SQL> SET AUTOTRACE TRACEONLY SQL> SELECT t1.id, t1.ym, t2.val 2 FROM tab01 t1, tab02 t2 3 WHERE t1.id = t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 2225763386 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 500K| 10M| | 5921 (1)| 00:00:01 | |* 1 | HASH JOIN | | 500K| 10M| 11M| 5921 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| TAB01 | 500K| 5859K| | 1437 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| TAB02 | 1000K| 9765K| | 2865 (1)| 00:00:01 | ------------------------------------------------------------------------------------ (以下省略) SQL> SET AUTOTRACE OFF
次に、SQL計画ベースラインへ実行計画を登録するために必要となる、改善対象SQLのsql_idと、対応する実行計画のplan_hash_valueの情報が必要となるため、下記のSQLを用いてv$sqlから確認します。対象のSQLの情報であるかは、sql_textの内容から判断します。
SQL> SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT%tab01%'; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT ------------------------------------------------------------------------------------------------------------------------ 9ybt9khrghd88 2225763386 SELECT t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id 1dv1a90twm827 903671040 SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT%tab01%'
SQL計画ベースラインへの実行計画の登録は、入手したsql_idとplan_hash_valueを用いて、dbms_spm.load_plans_from_cursor_cacheファンクションを下記のように使用します。このファンクションは登録できた実行計画数を返すので、返り値が1になっているかで、正常に登録できたか確認することができます。
SQL> VAR ret NUMBER SQL> EXEC :ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '9ybt9khrghd88', plan_hash_value => 2225763386); PL/SQL procedure successfully completed. SQL> PRINT ret RET ---------- 1
登録できたSQL計画ベースラインのsql_handleとplan_nameの情報が後続作業で必要となるため、dba_sql_plan_baselinesディクショナリビューから下記の様に確認します。
SQL> SELECT sql_handle, plan_name, TO_CHAR(created, 'YYYY/MM/DD HH24:MI:SS') created, sql_text 2 FROM dba_sql_plan_baselines 3 WHERE sql_text LIKE 'SELECT%tab01%' 4 ORDER BY created; SQL_HANDLE PLAN_NAME CREATED ------------------------------ ------------------------------ ------------------- SQL_TEXT -------------------------------------------------------------------------------- SQL_97d8bd93365c1637 SQL_PLAN_9gq5xkcv5s5jr77607b76 2024/01/07 20:57:10 SELECT t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id
sql_handleはSQLを識別するための識別子で、SQL文が同じであればsql_handleも同じになります。plan_nameは実行計画を識別するための識別子で、実行計画が同じであればplan_nameも同じになります。
2.改善された実行計画で動くSQLの実行
次に改善された実行計画で動くSQLを実行します。このときのSQL文は、ヒント句を付与するなどして、改善対象SQLとは形を変えたSQL文となっていて問題ありません。今回は、HASH結合をしている実行計画を、Nested Loop結合に変更してみるため、その様になるヒント句を付与したSQLを実行してみました。
SQL> SET AUTOTRACE TRACEONLY SQL> SELECT /*+ LEADING(t1 t2) USE_NL(t2) */ t1.id, t1.ym, t2.val 2 FROM tab01 t1, tab02 t2 3 WHERE t1.id = t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 872721807 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 500K| 10M| 1501K (1)| 00:00:59 | | 1 | NESTED LOOPS | | 500K| 10M| 1501K (1)| 00:00:59 | | 2 | NESTED LOOPS | | 500K| 10M| 1501K (1)| 00:00:59 | | 3 | TABLE ACCESS FULL | TAB01 | 500K| 5859K| 1437 (1)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX01TAB02 | 1 | | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB02 | 1 | 10 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- (以下省略) SQL> SET AUTOTRACE OFF
※今回のSQLについては、HASH結合の方が性能面で優位なのですが、実行計画を強制的に変更するのが目的なので、性能については見逃してください。。。
3.改善された実行計画のSQL計画ベースラインへの追加登録
改善された実行計画をSQL計画ベースラインへ追加するために、先程実行したヒント句付きSQLのsql_idと、対応する実行計画のplan_hash_valueの情報をv$sqlから確認します。
SQL> SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT%tab01%'; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT ------------------------------------------------------------------------------------------------------------------------ gc2wkzfp0hcxx 2893590550 SELECT sql_handle, plan_name, TO_CHAR(created, 'YYYY/MM/DD HH24:MI:SS') created, sql_text FROM dba_sql_plan_baselines WH ERE sql_text LIKE 'SELECT%tab01%' ORDER BY created 9ybt9khrghd88 2225763386 SELECT t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id dh1xby4m1r2xa 872721807 SELECT /*+ LEADING(t1 t2) USE_NL(t2) */ t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id 1dv1a90twm827 903671040 SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT%tab01%'
改善された実行計画は、最初に作成した改善対象SQLのSQL計画ベースラインへ追加登録します。dbms_spm.load_plans_from_cursor_cacheファンクションを使用しますが、最初に登録したときと異なり、下記の様に改善対象SQLのsql_handleを指定して実行します。
SQL> EXEC :ret := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dh1xby4m1r2xa', plan_hash_value => 872721807, sql_handle => 'SQL_97d8bd93365c1637'); PL/SQL procedure successfully completed. SQL> PRINT ret RET ---------- 1
SQL計画ベースラインをdba_sql_plan_baselinesディクショナリビューで確認すると、改善前と改善後の実行計画が登録されていることがわかります。
SQL> SELECT plan_name, TO_CHAR(created, 'YYYY/MM/DD HH24:MI:SS') created, enabled, accepted, fixed 2 FROM dba_sql_plan_baselines 3 WHERE sql_handle = 'SQL_97d8bd93365c1637' 4 ORDER BY created; PLAN_NAME CREATED ENA ACC FIX ------------------------------ ------------------- --- --- --- SQL_PLAN_9gq5xkcv5s5jr77607b76 2024/01/07 20:57:10 YES YES NO SQL_PLAN_9gq5xkcv5s5jrb3e367a7 2024/01/07 20:58:47 YES YES NO
4.SQL計画ベースラインから改善前の実行計画の削除
ようやく変更作業の最終ステップです。改善対象SQLが改善された実行計画で動作できるように、SQL計画ベースラインに登録されている、改善前の実行計画をSQL計画ベースラインから削除します。削除は下記の様にdbms_spm.drop_sql_plan_baselineファンクションを使用して、削除する実行計画(plan_name)を指定します。
SQL> EXEC :ret := dbms_spm.drop_sql_plan_baseline('SQL_97d8bd93365c1637', 'SQL_PLAN_9gq5xkcv5s5jr77607b76'); PL/SQL procedure successfully completed. SQL> PRINT ret RET ---------- 1
削除するだけでもよいのですが、SQL計画ベースラインが更新されないように固定化します。固定化はdbms_spm.alter_sql_plan_baselineファンクションを使用して、FIXED属性をYESに変更します。
SQL> EXEC :ret := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_97d8bd93365c1637', plan_name =>'SQL_PLAN_9gq5xkcv5s5jrb3e367a7', attribute_name => 'FIXED', attribute_value => 'YES'); PL/SQL procedure successfully completed. SQL> PRINT ret RET ---------- 1
改善前の実行計画が削除され、改善版の実行計画が固定化されていることを確認します。改善後の実行計画だけが表示され、dba_sql_plan_baselinesディクショナリビューのfixed列がYESになっていることを確認します。
SQL> SELECT plan_name, TO_CHAR(created, 'YYYY/MM/DD HH24:MI:SS') created, enabled, accepted, fixed 2 FROM dba_sql_plan_baselines 3 WHERE sql_handle = 'SQL_97d8bd93365c1637' 4 ORDER BY created; PLAN_NAME CREATED ENA ACC FIX ------------------------------ ------------------- --- --- --- SQL_PLAN_9gq5xkcv5s5jrb3e367a7 2024/01/07 20:58:47 YES YES YES
5.改善結果の確認
SPMにより実行計画の変更ができているかを確認します。AUTOTRACE機能で実行計画を確認すると、SQL文は変えてないのに、Nested Loop結合に変わっていることが確認できました。
SQL> SET AUTOTRACE TRACEONLY EXPLAIN SQL> SELECT t1.id, t1.ym, t2.val 2 FROM tab01 t1, tab02 t2 3 WHERE t1.id = t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 872721807 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 500K| 10M| 1501K (1)| 00:00:59 | | 1 | NESTED LOOPS | | 500K| 10M| 1501K (1)| 00:00:59 | | 2 | NESTED LOOPS | | 500K| 10M| 1501K (1)| 00:00:59 | | 3 | TABLE ACCESS FULL | TAB01 | 500K| 5859K| 1437 (1)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX01TAB02 | 1 | | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB02 | 1 | 10 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."ID"="T2"."ID") Note ----- - SQL plan baseline "SQL_PLAN_9gq5xkcv5s5jrb3e367a7" used for this statement
SQL計画ベースラインに登録された実行計画が用いられると、Noteに「SQL plan baseline "<使用された実行計画(plan_name)>" used for this statement」と表示されます。
念のため、SQL計画ベースラインが使用されなければ、元のHASH結合に戻るか確認してみました。一時的にSQL計画ベースライン機能を無効化するために、初期化パラメータoptimizer_use_sql_plan_baselines(デフォルト:TRUE)をFALSEに変更します。
SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = FALSE; Session altered. SQL> SELECT t1.id, t1.ym, t2.val 2 FROM tab01 t1, tab02 t2 3 WHERE t1.id = t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 2225763386 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 500K| 10M| | 5921 (1)| 00:00:01 | |* 1 | HASH JOIN | | 500K| 10M| 11M| 5921 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| TAB01 | 500K| 5859K| | 1437 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| TAB02 | 1000K| 9765K| | 2865 (1)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."ID"="T2"."ID") Note ----- - this is an adaptive plan
実行計画は元のHASH結合に戻り、Noteにも「SQL plan baseline...」が表示されなくなりました。
Standard Editionでの注意点
今回調査したSPMを用いたSQLの実行計画を変更する方法は、Standard Editionでは使用できない点に、ご注意ください。
Oracle Database 18cから、Standard EditionでもSPMを使用することができるようになりましたが、1つのSQL文で1つのSQL計画ベースラインしか登録が許可されていません。しかし今回の方法では、一時的に1つのSQL文に対して2つのSQL計画ベースラインを登録する必要があるため、Standard Editionではライセンス違反になってしまいます。
Standard EditionでSQL文を変更せずに実行計画を変更したい場合、dbms_sqldiag.create_sql_patchファンクションを使用する方法を検討する必要があります。
さいごに
SPMを用いて無事SQL文を変更することなく、SQLの実行計画を変更するチューニングをEnterprise Edition環境で実現する方法がわかりました。実システムではSQL文の変更(アプリケーションの変更)は、即座に実施できないことが結構多いので、応急処置的にこの方法を使えるのではないかと思います。
Standard Editionでも使用できる、dbms_sqldiag.create_sql_patchファンクションを使用した方法については、機会を見つけて調査したいと思います。
次の記事：統合監査を使用するための設定などについて調べてみた
