SQLチューニング（第５回）「サブクエリ動作方式を理解すること」(3/8)

2025-06-09 05:52Oracle技術情報

「サブクエリと性能問題の理解」３回目の今回は、「FILTER動作方式」についてです。それでは、早速内容に入ります。

２.1.２　サブクエリ動作方式を理解すること

サブクエリの動作方式は大きくFilter動作方式と結合動作方式で分けられる。また、どんな動作方式で処理されるかによりSQLの性能差が大きく発生する可能性がある。したがってサブクエリが効率的な実行ができるように動作方式を理解して制御するべきである。

今回は、サブクエリの動作方式を理解する一方、実行計画(動作方式)を制御できるヒントを調べて直接実行計画を制御することにより、サブクエリーによる性能問題が発生しないようにSQLを作成する方法に対して調べてみる。

２.1.２.１　FILTER動作方式

Filter動作はMain SQLで抽出されたデータの件数だけでサブクエリーが繰り返し実行され、処理される方式である。つまり、Main SQLの抽出結果について、各行ごとにサブクエリ結合接続値（以下、Inputの値）に対して処理実行後、結果がTRUEの場合、データを抽出する。

たとえば、Main SQLの抽出結果が100万件であれば、サブクエリは、最大100万回実行される。ところが、もし、上記のようなSQLのサブクエリの結合接続カラムに適切なインデックスがない場合はどうなるだろうか？　読者の方々もおわかりの通り、サブクエリを100万回繰り返しFull Table Scanを実行することになるだろう。このような実行計画を持つSQLが最悪の性能を示すという点はもちろんのことDBサーバの性能にも深刻な悪影響を与える。

反面、Main SQLの抽出件数が2件である場合Filter動作方式で処理されるならば、サブクエリは最大2回だけ実行すれば良い。その上サブクエリの結合連結カラムで構成されたUnique Indexが存在するならば、非常に効率的な処理ができると考えられる。

ところで「Main SQLの抽出結果が多い場合Filter動作方式で処理されるならば、常に性能が良くないだろうか?」という疑問が発生する。結論から言えば、常にそうはならない。 Main SQLの抽出結果が多くてもサブクエリのInput値が皆同一ならば、あたかもMain SQLで1件だけ抽出したことのように1回だけ実行するので性能問題が発生しない。これはOracleが内部的にFilter動作方式に対してFilter Optimization（※注１）と呼ばれる最適化作業を実行するためである。しかし一般的にサブクエリのInput値が皆同じ値である確率は非常に稀である。したがって抽出件数が多い場合サブクエリをFilter動作方式で処理する場合、性能上非効率的な場合が多く存在する。したがってFilter動作方式で実行される場合Input値の種類が少なくて性能に有利なのかを必ず確認しなければならない。

（※注１）　Filter Optimization：サブクォリを実行するInput値をCache値や、同じInput値が使われる場合、追加的な読み取り作業なしでCacheなった値を使う。

これからFilter動作方式がMain SQL抽出結果とInput値の種類によって、どのようにSQLの性能が変わるのか、三種類のテストを通じて詳しく調べてみることにする。

それでは、テストスクリプトを実行することにしよう。
Script. サブクエリテスト

DROP TABLE SUBQUERY_T1 PURGE ;
DROP TABLE SUBQUERY_T2 PURGE ;
DROP TABLE SUBQUERY_T3 PURGE ;


< SUBQUERY_T1 >


■生成要件

-テーブル件数は1,500,000行
-カラムC4は値の種類が250,000種類
-カラムC5は値の種類が26種類


-カラムC6は100,000から始めて、次々と増加して、値の種類は250,000種類

■テーブル生成

CREATE TABLE SUBQUERY_T1 AS
SELECT LEVEL AS C4, CHR(65+MOD(LEVEL,26)) AS C5, LEVEL+99999 AS C6
FROM DUAL
CONNECT BY LEVEL <= 250000 ;

250,000個のデータを生成した後同じテーブル値を6回繰り返してコピーする

BEGIN
      FOR I IN 1..6 LOOP
           INSERT INTO SUBQUERY_T1 SELECT * FROM SUBQUERY_T1;
           COMMIT;
      END LOOP;
END;
/

■各カラムにインデックス生成および統計情報を収集

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'SUBQUERY_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

CREATE INDEX SUBQUERY_T1_IDX_01 ON SUBQUERY_T1 ( C4, C5 ) ;
CREATE INDEX SUBQUERY_T1_IDX_02 ON SUBQUERY_T1 ( C5 ) ;


< SUBQUERY_T2 >

■生成要件

-テーブル データ件数は500,000行
-カラムC1は値の種類が500,000種類すなわち、Uniqueある値である
-カラムC2は値の種類が26種類
-カラムC3は値の種類は500,000種類であり、100,000から次々と増加
-カラムC4は値の種類が26種類

■テーブル生成

CREATE TABLE SUBQUERY_T2 AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3, 
       CHR(65+MOD(LEVEL,26)) AS C4


  FROM DUAL
CONNECT BY LEVEL <= 500000 ;

■各カラムにインデックス生成および統計情報収集

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'SUBQUERY_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

CREATE INDEX SUBQUERY_T2_IDX_01 ON SUBQUERY_T2 ( C2, C1 ) ;
ALTER TABLE SUBQUERY_T2 ADD CONSTRAINT PK_SUQUERY_2 PRIMARY KEY (C1);


< SUBQUERY_T3 >

■生成要件

-テーブル データ件数は500,000行
-カラムC1は値の種類が500,000種類であり、Uniqueある値を持つように生成
-カラムC2は値の種類が26種類になるべく生成
-カラムC3は値の種類は500,000種類であり、100,000から次々と増加するように生成

■テーブル生成

CREATE TABLE SUBQUERY_T3 AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;

■各カラムにインデックス生成および統計情報収集

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'SUBQUERY_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

CREATE INDEX SUBQUERY_T3_IDX_01 ON SUBQUERY_T3 ( C1, C2 ) ;
ALTER TABLE SUBQUERY_T3 ADD CONSTRAINT PK_SUQUERY_3 PRIMARY KEY (C1);

最初のテストはMain SQLの抽出結果が多くてInput値がUniqueある場合である。

テスト[1]. Main SQLの抽出件数が多く、Input値がUniqueである場合

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (  SELECT /*+ NO_UNNEST  */
                        'x'
                   FROM SUBQUERY_T1 t1
                  WHERE t1.c4 = t2.c1   )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch    15335      2.34       2.41          0    1156653          0      230001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15337      2.35       2.42          0    1156653          0      230001

Rows     Row Source OPERATION
-------  ---------------------------------------------------
 230001  FILTER  (cr=1156653 pr=0 pw=0 time=1846157 us)
 380001   FILTER  (cr=16650 pr=0 pw=0 time=3336 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=16650 pr=0 pw=0 time=35462 us)
230001 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=1140003 pr=0 …)

上記SQLの実行計画を説明する。まず実行計画にFILTERオペレーションがあるためにFilter動作方式で実行されたということがわかる。

最初にSUBQUERY_T2テーブルをFull Table Scanで読んで“c1 >=:b1 AND c1 <=:b2”条件に満足するデータを抽出するが、合計380,001件が抽出される。この中で最終データを抽出するためにはサブクエリの結果も満足するかを確認しなければならない。したがってサブクエリでC1値を渡した後サブクエリに結果値が存在するのか確認する。この時サブクエリはC1値をSUBQUERY_T1_IDX_01インデックスを使って合計380,001回反復問い合わせて、最終データ230,001件を抽出した。

テスト[1]のSQLのトレース結果を見れば、Main SQLの抽出結果が38万件なのに、Filter動作方式で実行されて見たらサブクエリが38万回も繰り返し実行された。結局SQL全体I/O発生量の大部分はサブクエリで繰り返し使うSUBQUERY_T1_IDX_01インデックスで発生したのである。

二番目のテストはMain SQLの抽出件数が少ない場合である。

テスト[2]. Main SQLの抽出件数が少なくて、Input値がUniqueである場合

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 20004

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS ( SELECT /*+ NO_UNNEST */
                       'x'
                  FROM SUBQUERY_T1 t1
                 WHERE t1.c4 = t2.c1   )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       1332          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0       1332          0           5

Rows     Row Source OPERATION
-------  ---------------------------------------------------
      5  FILTER  (cr=1332 pr=0 pw=0 time=12743 us)
      5   FILTER  (cr=1317 pr=0 pw=0 time=12657 us)
      5    TABLE ACCESS FULL SUBQUERY_T2 (cr=1317 pr=0 pw=0 time=12652 us)
      5   INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=15 pr=0 pw=0 time=58 us)

上記の場合もまた、Filter動作方式で実行されるが、Main SQLの条件が効率的なので抽出されるデータ件数が合計5件にしかならない。それでサブクエリも5回だけ実行するので、SQLの性能がテスト[1]と比較すると非常に良好である。

最後にMain SQLの抽出結果は多いが、Input値(T1.C5)の種類が少ない(26種類)場合の性能を確認してみることにしよう。

テスト[3]. Main SQLの抽出件数は多いが、Input値の種類が26種類である場合

alter session set "_query_execution_cache_max_size(※注２）  "= 4000000;
var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (  SELECT /*+ NO_UNNEST */
                        'x'
                   FROM SUBQUERY_T1 t1
                  WHERE t1.c5 = t2.c2  )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    25335      1.01       1.25          0      26728          0      380001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25337      1.01       1.25          0      26728          0      380001

Rows     Row Source OPERATION
-------  ---------------------------------------------------
 380001  FILTER  (cr=26728 pr=0 pw=0 time=385929 us)
 380001   FILTER  (cr=26650 pr=0 pw=0 time=3480 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=26650 pr=0 pw=0 time=40002 us)
     26   INDEX RANGE SCAN SUBQUERY_T1_IDX_02 (cr=78 pr=0 pw=0 time=223 us)

(※注２）スカラーサブクエリ、サブクエリで使うMulti BufferのSizeを調節するヒースはパラメーターであり、基本値が64KBである。 Sizeが小さい場合Multi Buffer内でAge Outが発生して、値の種類よりサブクエリがさらに多く実行することができる。テストもAge Outが発生して値の種類(26種類)よりさらに多く実行されることによってSizeを変更してテストした。

レース結果を見れば、Main SQLの抽出件数は380,001件で相当多いが、Input値の種類は26種類に過ぎない。だが、テスト[3]のサブクエリはMain SQLの抽出結果に対して毎回実行しなかった。その理由はサブクエリのInput値をCacheして、Input値が同じである場合サブクエリを実行しないためである。それによりサブクエリの実行回数はInput値の種類の26回だけ実行され性能が良好だということが分かる。

ここまで三種類のテストを通じてサブクエリがFilter動作方式で実行される場合Main SQL抽出結果とInput値の種類によって性能がどのように変るのか調べた。

整理すれば、Filter動作方式はMain SQLの抽出結果が多くて、サブクエリに提供する値(Input値)の種類が多いならば、性能が良くないということが分かった。しかしMain SQLの抽出件数が少なかったり、Main SQLの抽出結果が多かったとしてもInput値の種類が少ない場合Filter動作方式で実行されるSQLも性能が良好だということが分かった。

Filter動作方式の場合、常にMain SQLが先に実行されて、サブクエリはMain SQLで抽出されたデータの値を受けて毎回確認する方式で実行される。このようにFILTERオペレーションは常に一つの方法だけを守るので多様な状況で柔軟に対処するのが難しい動作方式だと見ることができる。このような理由でサブクエリがFilter動作方式で実行される場合SQL性能が良くない場合が多い。

もし、SQLの実行計画を点検してサブクエリがFilter動作方式で実行されているならば、先にサブクエリの結合連結カラムにインデックスが存在するのか確認しなければならない。なぜなら、サブクエリがFilter動作方式で実行されるが、Full Table Scanで処理しているならば深刻な性能問題が発生する可能性があるためである。

今回の「FILTER動作方式」の解説は理解できましたでしょうか？次回は、サブクエリの動作方式の２回目「結合動作方式」についてです。ご期待ください。それでは、See you Next ^-^

SQLチューニング（第５回）「サブクエリ動作方式を理解すること」(3/8)

２.1.２　サブクエリ動作方式を理解すること

２.1.２.１　FILTER動作方式

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

SQLチューニング（第５回）「サブクエリ動作方式を理解すること」(3/8)

２.1.２ サブクエリ動作方式を理解すること

２.1.２.１ FILTER動作方式

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

２.1.２　サブクエリ動作方式を理解すること

２.1.２.１　FILTER動作方式

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします