MOSCのMVC(Most Valued..｜Oracle 23c freeのオプティ.. ブログトップ

SQLプロファイルによるリテラルSQLのチューニング　[オプティマイザ]

１．はじめに


　SQLチューニングでアプリケーションのSQLにヒント句を追加することは、開発中のアプリケーションであればそれほど難しくない。しかし、運用中システムにおいて、アプリケーションのSQLに変更を加えることはまま難しいことがある。パッケージ等のサードパーティ製のアプリケーションだけでなく、様々なできない理由によりアプリケーションをリリースすることができず、故にデータベース層で実行計画の変更が必要となる。
　Oracleにおいて、このようなときに使える一般的な機能はSPM（SQL Plan Management）であろう。SPMは特定のSQLIDに紐づく実行計画を（複数）保持し、オプティマイザはその中で許可された実行計画を選択するようにすることができる。実行計画の固定化のために利用するのが一般的だが、上記のような状況においては、ヒント句により得られた所望の実行計画を特定のSQLIDに紐づけることができる（参考[1])。SPMはEEであれば利用できる。
　SQLパッチは任意のSQLにヒントを付与するOracleの機能である。dbms_sqldiag.create_sql_patchプロシージャを使い、特定のSQLIDに対し指定したヒント句を埋め込むことができる。汎用性は高いが、ヒント句の指定方法にクエリーブロックを利用する必要があり、複雑なSQLでは利用することが難しい（参考[2]）。SQLパッチは特段ライセンスの縛りはない。
　しかし、バインド変数を利用しない、いわゆるリテラルSQLの場合は上記いずれの方法も厳しい。もちろんcursor_sharingをFORCEにしていれば、リテラルSQLは自動的にバインド変数に変換され、その変換されたSQLIDに対してSQLパッチを作ることは可能である。しかし、商用のOracleデータベースにおいては、cursor_sharingはEXACTとするのが一般的であり、特定のSQLのチューニングのためにデータベース全体の挙動を変えるような設定変更をすることは本末転倒であろう。
　先日たまたまMike Dietrich氏の講演を聞く機会があり、リテラルSQLのチューニングにSQLプロファイルが使えるという話を聞いた。SQLプロファイルは、SQLチューニングアドバイザにより推奨されたチューニング案をアクセプトすると作成され、特定のSQLの実行計画を制御することができる（SQL Tuning Packライセンスが必要）。SPMとの大きな違いはFORCE_MATCHパラメータをTRUEにすることで、リテラルSQLにたいしてヒントを入れることができることである。ここでは、実際にリテラルSQLに対してFORCE_MATCHを有効にして、SQLプロファイルでヒントを入れる検証をしてみよう。

２．検証モデル


　検証環境はOracle19.11、cursor_sharingはEXACTとする。
SQL> select BANNER_LEGACY from v$version;
BANNER_LEGACY
_________________________________________________________________________
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show parameter cursor_sharing
NAME           TYPE   VALUE
-------------- ------ -----
cursor_sharing string EXACT

　検証用の表としてempを100万件,dept10万件を利用する。
SQL> select count(*) from emp;
   COUNT(*)
___________
    1000000
SQL> select count(*) from dept;
   COUNT(*)
___________
     100000

　以下のように2つの表を結合し、empnoで絞り込むシンプルなクエリを考える。リテラルでempnoを絞り込む条件を10件、10万件、90万の3パターンで変更すると、リテラルの違いによりSQL_IDはそれぞれ異なる（cursor_sharingがEXACTであるため）。また、統計情報からemp表の絞り込みの件数を推定できるため、それぞれの条件で実行計画が異なる。

SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10;

9 rows selected.


PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID  dwfrphnyytybk, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10　★

Plan hash value: 2259671826

------------------------------------------------------------------
| Id  | Operation                             | Name    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |        |
|   1 |  NESTED LOOPS                         |         |      9 |
|   2 |   NESTED LOOPS                        |         |      9 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |      9 |
|*  4 |     INDEX RANGE SCAN                  | PK_EMP  |      9 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT    |      1 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"<10)
   5 - access("T1"."DEPTNO"="T2"."DEPTNO")


SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<100000;　★

99,999 rows selected.


PLAN_TABLE_OUTPUT
______________________________________________________________________________________________
SQL_ID  1zay58r4dxstz, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and
t1.empno<100000

Plan hash value: 3839690129

-------------------------------------------------------------------------------------------
| Id  | Operation                            | Name   | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |        |       |       |          |
|*  1 |  HASH JOIN                           |        |  99999 |  9465K|  3157K| 9866K (0)|
|   2 |   TABLE ACCESS FULL                  | DEPT   |    100K|       |       |          |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |  99999 |       |       |          |
|*  4 |    INDEX RANGE SCAN                  | PK_EMP |  99999 |       |       |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   4 - access("T1"."EMPNO"<100000)

SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<900000;　★

899,999 rows selected.


PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID  11smn0k8ffaam, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and
t1.empno<900000

Plan hash value: 615168685

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|*  1 |  HASH JOIN         |      |    892K|  9465K|  3157K| 9978K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |    100K|       |       |          |
|*  3 |   TABLE ACCESS FULL| EMP  |    900K|       |       |          |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
   3 - filter("T1"."EMPNO"<900000)


３．SQLプロファイルによるチューニング


　このような状況で、SQLを変更することなく、実行計画をチューニング（ヒント付与）してみよう。ここでは、リテラルがどのような値であったとしても、1番目のNestedLoop結合としてみたい。リテラルの値によりSQLIDが異なるため、SPMもSQLパッチも使えないので、SQLプロファイルを使う。

　まず、所望の実行計画を作成するためのアウトラインを確認する。方法は以下のようにexplain plan forを使う。
SQL> explain plan for select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10

Explained.

Elapsed: 00:00:00.006
SQL> select * from table(dbms_xplan.display(null,null,'+outline'));

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________
Plan hash value: 2259671826

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     9 |   792 |    13   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |         |     9 |   792 |    13   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |         |     9 |   792 |    13   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     9 |   486 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | PK_EMP  |     9 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT    |     1 |    34 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
      USE_NL(@"SEL$1" "T2"@"SEL$1")　★
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")　★
      INDEX(@"SEL$1" "T2"@"SEL$1" ("DEPT"."DEPTNO"))　★
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("EMP"."EMPNO"))　★
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('_optim_peek_user_binds' 'false')
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"<10)
   5 - access("T1"."DEPTNO"="T2"."DEPTNO")


　PDBのSYSで接続し、IMPORT_SQL_PROFILEプロシージャを実行する。ここで引数として、SQL文と上記アウトラインで必要と思われるヒント句（★の部分）を指定する。また、リテラルが変わってもこのヒントが有効となるよう、FORCE_MATCHをtrue（有効）とする。
DECLARE
 sql_text CLOB;
BEGIN
 select sql_fulltext into sql_text from v$sqlarea where sql_id='dwfrphnyytybk';
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  SQL_TEXT => sql_text,　★
  PROFILE => SQLPROF_ATTR('USE_NL(@"SEL$1" "T2"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") INDEX(@"SEL$1" "T2"@"SEL$1" ("DEPT"."DEPTNO")) INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("EMP"."EMPNO"))'),　★
  NAME => 'pf_dwfrphnyytybk',
  REPLACE => true,
  FORCE_MATCH => true　★
);
END;
/

SQL> select name,sql_text,status,force_matching from dba_sql_profiles;

NAME                SQL_TEXT                                                                   STATUS     FORCE_MATCHING
___________________ __________________________________________________________________________ __________ _________________
pf_dwfrphnyytybk    select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10    ENABLED    YES

　この状態で先に実行した３つの条件でSQLを実行してみよう。
SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10;

9 rows selected.


PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID  dwfrphnyytybk, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<10

Plan hash value: 2259671826　★

------------------------------------------------------------------
| Id  | Operation                             | Name    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |        |
|   1 |  NESTED LOOPS                         |         |      9 |
|   2 |   NESTED LOOPS                        |         |      9 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |      9 |
|*  4 |     INDEX RANGE SCAN                  | PK_EMP  |      9 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT    |      1 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"<10)
   5 - access("T1"."DEPTNO"="T2"."DEPTNO")

Note
-----
   - SQL profile pf_dwfrphnyytybk used for this statement　★

SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<100000;

99,999 rows selected.


PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID  1zay58r4dxstz, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and
t1.empno<100000

Plan hash value: 2259671826　★

------------------------------------------------------------------
| Id  | Operation                             | Name    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |        |
|   1 |  NESTED LOOPS                         |         |  99999 |
|   2 |   NESTED LOOPS                        |         |  99999 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |  99999 |
|*  4 |     INDEX RANGE SCAN                  | PK_EMP  |  99999 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT    |      1 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"<100000)
   5 - access("T1"."DEPTNO"="T2"."DEPTNO")

Note
-----
   - SQL profile pf_dwfrphnyytybk used for this statement　★

SQL> select * from emp t1, dept t2 where t1.deptno=t2.deptno and t1.empno<900000;

899,999 rows selected.


PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID  11smn0k8ffaam, child number 0
-------------------------------------
select * from emp t1, dept t2 where t1.deptno=t2.deptno and
t1.empno<900000

Plan hash value: 2259671826　★

------------------------------------------------------------------
| Id  | Operation                             | Name    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |        |
|   1 |  NESTED LOOPS                         |         |    892K|
|   2 |   NESTED LOOPS                        |         |    900K|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |    900K|
|*  4 |     INDEX RANGE SCAN                  | PK_EMP  |    900K|
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT    |      1 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"<900000)
   5 - access("T1"."DEPTNO"="T2"."DEPTNO")

Note
-----
   - SQL profile pf_dwfrphnyytybk used for this statement　★

　上記3つのケース全てNested Loop、同一PHVとなり、Noteに作成したSQLプロファイルが使われていることがわかる。

４．SQLプロファイルの削除


不要となったSQLプロファイルは以下のように削除する。
exec dbms_sqltune.drop_sql_profile('pf_dwfrphnyytybk');

SQL> exec dbms_sqltune.drop_sql_profile('pf_dwfrphnyytybk');

PL/SQL procedure successfully completed.

SQL> select name,sql_text,status,force_matching from dba_sql_profiles;

no rows selected
SQL>

５．まとめ


　本稿では、リテラルSQLの実行計画をDBで変更する方法についてSQLプロファイルを使う方法について検証した。リテラルSQLについてはSPMやSQLパッチを効果的に使うことが難しいため、SQLに手が入れられないとするとこの方法がほぼ唯一の方法となる可能性がある。もちろん、リテラルのSQLであれば、オプティマイザは統計情報からかなり正確にカーディナリティの見積もりができるため、性能面ではこのようなチューニングが必要となるケースは少ないかもしれない。それでも、リテラルSQLの実行計画を固定化したい場合に、SQL Tuning Packライセンスがあれば、覚えておきたい手法である。

　なお、このプロシージャはPL/SQLのマニュアル（参考[3]）に記載がないので、恐らく通常の使い方としてはSQLチューニングアドバイザから内部的に使われるものなのかもしれず、利用に際してはあくまでも自己責任となるのかもしれない。
PROCEDURE IMPORT_SQL_PROFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE                        SQLPROF_ATTR            IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
PROCEDURE IMPORT_SQL_PROFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE_XML                    CLOB                    IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT

◇参考


[1]Oracle Database 19cのSQL計画管理
https://www.oracle.com/technetwork/jp/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207-ja.pdf
[2]Adding and Disabling Hints Using SQL Patch
https://blogs.oracle.com/optimizer/post/adding-and-disabling-hints-using-sql-patch
[3]Oracle Database PL/SQL Packages and Types Reference　DBMS_SQLTUNE
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLTUNE.html#GUID-421606DC-588B-4BCD-8ED8-192F8B93C070

2023-03-11 01:37  コメント(0) 

コメント 0

コメントを書く

お名前：
URL：
コメント：
画像認証：
下の画像に表示されている文字を入力してください。

※ブログオーナーが承認したコメントのみ表示されます。
MOSCのMVC(Most Valued..｜Oracle 23c freeのオプティ.. ブログトップ

Kazuhiro

データベーススペシャリスト
Oracle ACE
Oracle Community
Linked-in