Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ssfujita(Shinjiro Fujita)in日本オラクル株式会社

Oracle Database 23ai 新機能「リアルタイムSQL計画管理 (SPM)」を試してみた

Last updated at Posted at 2024-12-15

本記事は JPOUG Advent Calendar 2024 15日目の記事となります。
14日目は ketsuji さんの記事『Oracle Live SQL v2が出ました』でした。

23aiから「リアルタイムSQL計画管理」という機能が追加されました。
同じSQLに対して、過去に実行したときの実行計画と、新しく生成された実行計画を比較し、過去のものの方が良ければ自動的に過去の実行計画を選択してくれる機能です。
以下のような仕組みになっています。

  • 事前にリアルタイムSQL計画管理を有効化
  • 実行回数が2回以上のSQLについて、自動SQLチューニングセット (STS) によりSQL統計情報を収集
  • 自動STSに収集されたSQLについてはそれ以降、以下を実施
    • ハード・パースされ新しい実行計画が生成された場合、1回試しに実行
    • 1回試した実行計画が次回も選択された場合、STS内の過去の実行計画と比較
    • 比較した結果、過去の実行計画の方が良ければ、その場で過去の実行計画を選択してSQLを実行

19cでも「自動SQL計画管理」という機能が追加されました。
こちらもより良い実行計画を自動でSQL計画ベースラインに組み込む、という機能です。
一方、自動SQL計画管理は定期実行タスクとしてバックグラウンドで行われる仕組みでした。
それをリアルタイムにして、まさにSQL実行時に機能するよう改善したのが「リアルタイムSQL計画管理」です。

本機能を利用することで、実行計画が急に変わってパフォーマンスが落ちる、という問題をかなり軽減できそうです。
以降は本機能の検証ログです。
なお検証手順は以下の公式ブログに掲載されていたものを使いました。

実際の手順、スクリプトは以下にあります。

検証環境

今回は BaseDB (EE) で作成した23ai環境を使いました。

23ai freeでは本機能は使えません。

DBユーザ作成

検証用にDBユーザを作成します。

CREATE USER rsql IDENTIFIED BY "your password" QUOTA UNLIMITED ON USERS;
GRANT DBA TO rsql;

テーブル、データ作成

検証用テーブル、データを作成します。

SQL> create table sales_area1 (sale_code number(10), b varchar2(2000), amount number(10,2), sale_type number(10), c varchar2(1000));

Table created.

SQL> 
SQL> 
SQL> var str VARCHAR2(1200)
SQL> exec :str := dbms_random.string('u',2000);

PL/SQL procedure successfully completed.

SQL> var str2 VARCHAR2(1200)
SQL> exec :str2 := dbms_random.string('u',10);

PL/SQL procedure successfully completed.

SQL> insert /*+ APPEND */ into sales_area1
  2  select mod(rn,1000), :str, rn/1000, mod(rn,100),:str2
  3  from (
  4  	 select trunc((rownum+1)/2) as rn, mod(rownum+1,2) as parity
  5  	 from (select null from dual connect by level <= 4000)
  6  	    , (select null from dual connect by level <= 500)
  7  	  );

2000000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> create table sales_area2 as select sale_code,b,rownum/1000 amount,sale_type,c from sales_area1;

Table created.

作成した2つの表に対して索引を作ります。

SQL> create index sales_area1i on sales_area1 (sale_code);

Index created.

SQL> create index sales_area2i on sales_area2 (sale_code);

Index created.

SQL> create index sales_typ1i on sales_area1 (sale_type);

Index created.

SQL> create index sales_typ2i on sales_area2 (sale_type);

Index created.

作成した2つの表の統計情報を収集します。

SQL> exec dbms_stats.gather_table_stats(user,'sales_area1',method_opt=>'for all columns size 1',no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'sales_area2',method_opt=>'for all columns size 1',no_invalidate=>false)

PL/SQL procedure successfully completed.

リアルタイムSQL計画管理の有効化

以下コマンドでリアルタイムSQL計画管理を有効化します。

exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'AUTO')

SQL実行

SQL実行の前に、意図的にリアルタイムSQL計画管理を機能させるため、索引利用のコストを調節します。

alter session set optimizer_index_cost_adj = 90

SQLを実行します。

SQL> set timing on
SQL> 
SQL> set echo on
SQL> select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)
  2  from   sales_area1 t1,
  3  	    sales_area2 t2
  4  where  t1.sale_code = t2.sale_code
  5  and    t1.sale_type  = 1;

SUM(T2.AMOUNT)
--------------
    3.9996E+10

Elapsed: 00:00:25.58
SQL> set echo off

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6hqazvppxq6m, child number 0
-------------------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount) from   sales_area1 t1,
    sales_area2 t2 where  t1.sale_code = t2.sale_code and
t1.sale_type  = 1

Plan hash value: 3516044501

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |       |       |   112K(100)|          |
|   1 |  SORT AGGREGATE          |                  |     1 |    17 |            |          |
|*  2 |   HASH JOIN              |                  |    40M|   648M|   112K  (1)| 00:00:05 |
|*  3 |    VIEW                  | index$_join$_001 | 20000 |   136K|  3790   (1)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | SALES_TYP1I      | 20000 |   136K|    38   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SALES_AREA1I     | 20000 |   136K|  4685   (1)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | SALES_AREA2      |  2000K|    19M|   108K  (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

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

   2 - access("T1"."SALE_CODE"="T2"."SALE_CODE")
   3 - filter("T1"."SALE_TYPE"=1)
   4 - access(ROWID=ROWID)
   5 - access("T1"."SALE_TYPE"=1)

Note
-----
   - this is an adaptive plan
 
 
 33 rows selected.
 
 Elapsed: 00:00:00.18

自動STSにキャプチャさせるため、もう一度実行します。

SQL> set timing on
SQL> 
SQL> set echo on
SQL> select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)
  2  from   sales_area1 t1,
  3         sales_area2 t2
  4  where  t1.sale_code = t2.sale_code
  5  and    t1.sale_type  = 1;

SUM(T2.AMOUNT)
--------------
    3.9996E+10

Elapsed: 00:00:26.26
SQL> set echo off

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6hqazvppxq6m, child number 0
-------------------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount) from   sales_area1 t1,
    sales_area2 t2 where  t1.sale_code = t2.sale_code and
t1.sale_type  = 1

Plan hash value: 3516044501

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |       |       |   112K(100)|          |
|   1 |  SORT AGGREGATE          |                  |     1 |    17 |            |          |
|*  2 |   HASH JOIN              |                  |    40M|   648M|   112K  (1)| 00:00:05 |
|*  3 |    VIEW                  | index$_join$_001 | 20000 |   136K|  3790   (1)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | SALES_TYP1I      | 20000 |   136K|    38   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SALES_AREA1I     | 20000 |   136K|  4685   (1)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | SALES_AREA2      |  2000K|    19M|   108K  (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

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

   2 - access("T1"."SALE_CODE"="T2"."SALE_CODE")
   3 - filter("T1"."SALE_TYPE"=1)
   4 - access(ROWID=ROWID)
   5 - access("T1"."SALE_TYPE"=1)

Note
-----
   - this is an adaptive plan


33 rows selected.

Elapsed: 00:00:00.02

自動STSによるSTSへの格納を確認

自動STSによる、先ほどのSQL情報のSTS格納を待ちます。
なお以下コマンドで自動STSのキャプチャを早めることができます。

-- デフォルトの15分から2分へ短縮する場合
exec dbms_auto_task_admin.modify_autotask_setting('Auto STS Capture Task', 'INTERVAL', 120)

STSへ格納されたかは以下コマンドで確認できます。

SQL> select substr(sql_text,1,100) txt,executions,decode(executions,0,-1,round(buffer_gets/executions)) bget_per_exec,plan_hash_value,exact_matching_signature
  2  from dba_sqlset_statements
  3  where sqlset_name = 'SYS_AUTO_STS'
  4  and sql_text like 'select /* SPM_TEST_QUERY_Q1 */%'
  5  order by 3;

TXT                                                                                                  EXECUTIONS BGET_PER_EXEC PLAN_HASH_VALUE     EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------------------------------------- ---------- ------------- --------------- ----------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)                                                                 2        404324      3516044501          2325180225210358832
from   sales_area1 t1,
       sale

悪い実行計画になった場合の挙動確認

次に悪い実行計画となった場合にリアルタイムSQL計画管理が機能するか確認します。
以下コマンドで索引利用のコストを上げ、索引が使われないようにします。

alter session set optimizer_index_cost_adj = 10000

この状態で先程と同じSQLを実行します。

SQL> set timing on
SQL> 
SQL> set echo on
SQL> select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)
  2  from   sales_area1 t1,
  3         sales_area2 t2
  4  where  t1.sale_code = t2.sale_code
  5  and    t1.sale_type  = 1;

SUM(T2.AMOUNT)
--------------
    3.9996E+10

Elapsed: 00:00:52.02
SQL> set echo off

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6hqazvppxq6m, child number 1
-------------------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount) from   sales_area1 t1,
    sales_area2 t2 where  t1.sale_code = t2.sale_code and
t1.sale_type  = 1

Plan hash value: 1785747100

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |       |       |   217K(100)|          |
|   1 |  SORT AGGREGATE     |             |     1 |    17 |            |          |
|*  2 |   HASH JOIN         |             |    40M|   648M|   217K  (1)| 00:00:09 |
|*  3 |    TABLE ACCESS FULL| SALES_AREA1 | 20000 |   136K|   108K  (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL| SALES_AREA2 |  2000K|    19M|   108K  (1)| 00:00:05 |
-----------------------------------------------------------------------------------

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

   2 - access("T1"."SALE_CODE"="T2"."SALE_CODE")
   3 - filter("T1"."SALE_TYPE"=1)

Note
-----
   - This is SQL Plan Management Test Plan


28 rows selected.

Elapsed: 00:00:00.07

Noteに「This is SQL Plan Management Test Plan」と出力されています。
ハード・パースされ、以前と異なる実行計画が作成されたため、今回はお試しで実行した、という趣旨です。

ここでもう一度、同じSQLを実行します。

SQL> set timing on
SQL> 
SQL> set echo on
SQL> select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)
  2  from   sales_area1 t1,
  3         sales_area2 t2
  4  where  t1.sale_code = t2.sale_code
  5  and    t1.sale_type  = 1;

SUM(T2.AMOUNT)
--------------
    3.9996E+10

Elapsed: 00:00:26.54
SQL> set echo off

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6hqazvppxq6m, child number 3
-------------------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount) from   sales_area1 t1,
    sales_area2 t2 where  t1.sale_code = t2.sale_code and
t1.sale_type  = 1

Plan hash value: 3516044501

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |       |       |   112K(100)|          |
|   1 |  SORT AGGREGATE          |                  |     1 |    17 |            |          |
|*  2 |   HASH JOIN              |                  |    40M|   648M|   112K  (1)| 00:00:05 |
|*  3 |    VIEW                  | index$_join$_001 | 20000 |   136K|  3790   (1)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | SALES_TYP1I      | 20000 |   136K|    38   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SALES_AREA1I     | 20000 |   136K|  4685   (1)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | SALES_AREA2      |  2000K|    19M|   108K  (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

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

   2 - access("T1"."SALE_CODE"="T2"."SALE_CODE")
   3 - filter("T1"."SALE_TYPE"=1)
   4 - access(ROWID=ROWID)
   5 - access("T1"."SALE_TYPE"=1)

Note
-----
   - SQL plan baseline SQL_PLAN_20j5m31t6u71h5ef7a68d used for this statement


33 rows selected.

Elapsed: 00:00:00.27

次はNoteに「SQL plan baseline SQL_PLAN_20j5m31t6u71h5ef7a68d used for this statement」と出力されました。
リアルタイムSQL計画管理により、索引を利用していた頃の実行計画の方が良いと判断されたようです。
そのため、以前の実行計画をSQL計画ベースラインに承認し、今回の実行計画として採用されました。

念のためハード・パースさせ、索引を利用しない実行計画をもう一度作成させた場合にどうなるか確認します。
共有プールをフラッシュします。

alter system flush shared_pool;

これまでと同じSQLを実行します。

SQL> set timing on
SQL> 
SQL> set echo on
SQL> select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount)
  2  from   sales_area1 t1,
  3         sales_area2 t2
  4  where  t1.sale_code = t2.sale_code
  5  and    t1.sale_type  = 1;

SUM(T2.AMOUNT)
--------------
    3.9996E+10

Elapsed: 00:00:25.77
SQL> set echo off

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6hqazvppxq6m, child number 1
-------------------------------------
select /* SPM_TEST_QUERY_Q1 */ sum(t2.amount) from   sales_area1 t1,
    sales_area2 t2 where  t1.sale_code = t2.sale_code and
t1.sale_type  = 1

Plan hash value: 3516044501

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |       |       |   112K(100)|          |
|   1 |  SORT AGGREGATE          |                  |     1 |    17 |            |          |
|*  2 |   HASH JOIN              |                  |    40M|   648M|   112K  (1)| 00:00:05 |
|*  3 |    VIEW                  | index$_join$_001 | 20000 |   136K|  3790   (1)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | SALES_TYP1I      | 20000 |   136K|    38   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SALES_AREA1I     | 20000 |   136K|  4685   (1)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | SALES_AREA2      |  2000K|    19M|   108K  (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

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

   2 - access("T1"."SALE_CODE"="T2"."SALE_CODE")
   3 - filter("T1"."SALE_TYPE"=1)
   4 - access(ROWID=ROWID)
   5 - access("T1"."SALE_TYPE"=1)

Note
-----
   - SQL plan baseline SQL_PLAN_20j5m31t6u71h5ef7a68d used for this statement


33 rows selected.

Elapsed: 00:00:00.67

Noteにも書いてありますが、SQL計画ベースラインに承認されている索引利用の実行計画が今回も採用されました。

ここでSQL計画ベースラインの状態を確認してみます。

SQL> select p.plan_name, p.foreground_last_verified, pfspm.status result, pfspm.ver verify_type
  2  from dba_sql_plan_baselines p,
  3      XMLTABLE(
  4           '/notes'
  5           passing xmltype(p.notes)
  6           columns
  7               plan_id         NUMBER    path 'plan_id',
  8               flags           NUMBER    path 'flags',
  9               fg_spm          XMLType   path 'fg_spm') pf,
 10       XMLTABLE(
 11           '/fg_spm'
 12           passing pf.fg_spm
 13           columns
 14               ver             VARCHAR2(8)    path 'ver',
 15               status          VARCHAR2(8)    path 'status') pfspm
 16  where notes is not null
 17  and sql_text like 'select /* SPM_TEST_QUERY_Q1%'
 18  order by p.foreground_last_verified
 19  ;

PLAN_NAME                                FOREGROUND_LAST_VERIFIED                                                    RESULT   VERIFY_T
---------------------------------------- --------------------------------------------------------------------------- -------- --------
SQL_PLAN_20j5m31t6u71h8059011b           15-DEC-24 08.50.55.000000 AM                                                worse    normal
SQL_PLAN_20j5m31t6u71h5ef7a68d           15-DEC-24 08.51.22.000000 AM                                                better   reverse

リアルタイムSQL計画管理に関する情報はXML形式で格納されているため、パースして表示しています。
RESULT列の意味は以下の通りです。

  • worse: 自動STSでキャプチャされた実行計画と比較して悪い実行計画
  • better: 自動STSでキャプチャされた実行計画と比較して良い実行計画

またVERIFY_T列の意味は以下の通りです。

  • normal: 実行計画の変化を検知したため、自動STS内の実行計画と比較するため検証された
  • reverse: 以前採用を却下した実行計画よりも、自動STS内の実行計画が優れているのを確認するため、再検証された

検証は以上となります。
リアルタイムSQL計画管理によって、急なSQL性能劣化によるシステム影響を最小限に抑えられそうです。
結果としてシステムの一層の安定稼働や、DBAの方の負荷低減に繋がることを期待できます。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?