Oracle SQLチューニング Season2（第23回）第4章 SQL TuningとHINTの関係（6/8）

2025-05-02 02:11Oracle技術情報

　今回のSQLチューニングブログ2nd Season（第23回）は、「SQL Tunig と HINTの関係」の第6回目です。
　～　INSERT制御のヒント　～　について解説していきます。

　それでは早速、はじめていきましょう。

4.2.9　INSERTコントロールヒント

　Insert文にヒントを適用する事は、Insert操作の対象データが多く、操作の速度を改善するためのヒントを適用することを意味します。
　大量のデータをInsertする作業は、他のDML作業とは違って性能改善の余地が多くあります。

　なぜそのように考えるべきなのでしょうか？・・・・
　それは、データ入力対象テーブルの属性変更(Nologging)とヒント(/+ append */)だけでDirect Path Insertを誘導してREDO発生量を抑制する方法があるからです。

・APPEND
　使用バージョン：8.1.0〜。
　使用方法: /+ APPEND */ (Inverse: NOAPPEND)
　ヒント意味: Insert ~ Select構文がDirect Mode Insertで実行されるようにするヒント。

　APPENDヒントを適用すると、対象セグメントのHigh Water Markの後にInsertとなり、Direct LoadでINSERTが実行されます。
　APPEND使用時、Archive Log ModeであるDBにおいて明示的にNOLOGGINGオプションを一緒に追加する形でヒントを使用することで、リードデータを発生させること無く、より速いINSERT作業が期待できます。
　しかし、テーブルに複数のインデックスが存在する場合、期待した効果に満たない性能が発生することがあります。
　これは、大量のINSERT作業時、データがテーブルに入力される作業よりも、インデックスの数と性質によって性能が左右されると言っても過言ではないからです。
　下記の2つの例を使って、Direct Path Insertが可能な環境における大量のINSERT作業の性能差と、これを改善するための方法について説明していきたいと思います。

TEST 1 : Appendヒントの使用有無によるInset性能比較

[ テーブル/Index生成DDL ]

CREATE TABLE partition_test (
TIME_ID DATE,
	 DAY_NAME VARCHAR2(9),
        ... 中略
 )
 PARTITION BY RANGE( time_id )(
 PARTITION partition_test1 VALUES less than (to_date('1998-12-31','YYYY-MM-DD')),
 PARTITION partition_test2 VALUES less than (to_date('1999-12-31','YYYY-MM-DD')),
 PARTITION partition_test3 VALUES less than (to_date('2000-12-31','YYYY-MM-DD')),
 PARTITION partition_test4 VALUES less than (to_date('2001-12-31','YYYY-MM-DD')),
 PARTITION partition_test5 VALUES less than (to_date('2003-12-31','YYYY-MM-DD')) );

create index idx_test1 on partition_test(TIME_ID) local;
create index idx_test2 on partition_test(DAY_NAME) local;
create index idx_test3 on partition_test(WEEK_ENDING_DAY) local;


[ Append ヒントを付与していない場合の一括 Insert ]

SQL> alter table partition_test logging;
SQL> INSERT 
  2  INTO   partition_test
  3  SELECT a.*
  4  FROM   sh.times a ,
  5         (
  6          SELECT LEVEL
  7          FROM   dual
  8          CONNECT BY LEVEL <= 2000
  9         ) b;

3652000 rows created.

Elapsed: 00:00:42.69

Statistics
----------------------------------------------------------
      54440  recursive calls
    1247476  db block gets
     342807  consistent gets
    	895  physical reads
  838193092  redo size
    	842  bytes sent via SQL*Net to client
    	929  bytes received via SQL*Net from client
    	  3  SQL*Net roundtrips to/from client
    	  3  sorts (memory)
0  sorts (disk)


[ 既存データの削除 ]

SQL> truncate table partition_test;

[ Append ヒントを付与していない場合の一括 Insert ]

SQL> alter table partition_test logging;
SQL> INSERT /*+ APPEND */
  2  INTO   partition_test
  3  SELECT a.*
  4  FROM   sh.times a ,
  5         (
  6          SELECT LEVEL
  7          FROM   dual
  8          CONNECT BY LEVEL <= 2000
  9         ) b;

3652000 rows created.

Elapsed: 00:00:07.12


Statistics
----------------------------------------------------------
      56400  recursive calls
     155777  db block gets
      16377  consistent gets
    	184  physical reads
    4321824  redo size
    	829  bytes sent via SQL*Net to client
    	930  bytes received via SQL*Net from client
    	  3  SQL*Net roundtrips to/from client
    	  4  sorts (memory)
0  sorts (disk)

　約365万件に相当する大量のデータをINSERTする際、APPENDヒントを使用せずにREDOデータの生成を含めてINSERT作業を行った結果、約42秒程度の応答時間と約800MBのREDOデータが生成されました。
　一方、テーブル属性を”Nologging”に変更してから APPENDヒントを適用すると、応答時間は7秒程度、約4MBのREDOデータが生成される結果となりました。
　このように大量のデータをInsertする作業においては、APPENDヒントの存在感は絶対的なのです。
　したがって、このような作業を行う場合には、APPENDヒントの積極的な使用を検討する必要があります。

TEST 2 : Indexが存在する場合のInsert性能比較

［ テーブル/Index生成DDL ］

CREATE TABLE partition_test (
TIME_ID DATE,
	 DAY_NAME VARCHAR2(9),
       ... 中略
 )
 PARTITION BY RANGE( time_id )(
 PARTITION partition_test1 VALUES less than (to_date('1998-12-31','YYYY-MM-DD')),
 PARTITION partition_test2 VALUES less than (to_date('1999-12-31','YYYY-MM-DD')),
 PARTITION partition_test3 VALUES less than (to_date('2000-12-31','YYYY-MM-DD')),
 PARTITION partition_test4 VALUES less than (to_date('2001-12-31','YYYY-MM-DD')),
 PARTITION partition_test5 VALUES less than (to_date('2003-12-31','YYYY-MM-DD')) );

create index idx_test1 on partition_test(TIME_ID) local;
create index idx_test2 on partition_test(DAY_NAME) local;
create index idx_test3 on partition_test(WEEK_ENDING_DAY) local;

[ データ削除 ]

SQL> truncate table partition_test;

[ 3つのIndexが存在する場合の一括 Insert ]

SQL> alter table partition_test nologging;
SQL> INSERT /*+ append */
  2  INTO   partition_test
  3  SELECT a.*
  4  FROM   sh.times a ,
  5         (
  6          SELECT LEVEL
  7          FROM   dual
  8          CONNECT BY LEVEL <= 10000
  9         ) b;

Statistics
----------------------------------------------------------
      74457  recursive calls
    3758784  db block gets
     281512  consistent gets
     442189  physical reads
 4415169344  redo size
	829  bytes sent via SQL*Net to client
	929  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	 10  sorts (memory)
	  3  sorts (disk)

18260000 行が作成されました。

経 過: 00:08:50.51

[ 既存データの削除 ]

SQL> truncate table partition_test;

[ 既存のインデックスを削除 Insert後にインデックスを作成 ]

SQL> drop index idx_test1;
SQL> drop index idx_test2;
SQL> drop index idx_test3;
SQL> INSERT /*+ append */
  2  INTO   partition_test
  3  SELECT a.*
  4  FROM   sh.times a ,
  5         (
  6          SELECT LEVEL
  7          FROM   dual
  8          CONNECT BY LEVEL <= 10000
  9         ) b;

18260000 行が作成されました

経 過: 00:00:24.49

Statistics
----------------------------------------------------------
      13820  recursive calls
     532991  db block gets
       4210  consistent gets
          7  physical reads
    1975520  redo size
        566  bytes sent via SQL*Net to client
        636  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
   18260000  rows processed

[ 削除したインデックスの作成 ]

SQL> create index idx_test1 on partition_test(time_id, day_name) local;
経 過: 00:00:26.87

SQL> create index idx_test2 on partition_test(DAY_NUMBER_IN_WEEK,DAY_NUMBER_IN_MONTH) local;
経 過: 00:00:27.42

SQL> create index idx_test3 on partition_test(WEEK_ENDING_DAY,WEEK_ENDING_DAY_ID) local;
経 過: 00:00:26.72

　上記のテスト結果から、大量のデータがINSERTされる時、そのテーブルに複数のインデックスが作成されているとパフォーマンスに大きな影響を与えることが分かりました。
　インデックスが3つ存在する場合、18,260,000件をINSERTするのにかかった時間は8分50秒ほどです。
　一方、既存のインデックスを削除後、同じデータをINSERTした場合では、所要時間は25秒程度となり、大幅な時間短縮が実現できることがわかりました。（その後、削除した3つのインデックスを再作成するのにかかった時間を含めて計算した場合でも短時間で実行されました）

　上記のテスト結果を踏まえて、焦点に当てて見ていくべき要素には、実行時間以外のREDO発生量があります。
　インデックスがある場合、約210 MBのREDOが発生し、インデックスがない場合では、REDO発生量は約1.8 MBとなり大幅に減少することがわかりました。つまりこの結果から、REDO発生量の違いによるINSERT性能に差が発生したと結論付けることができます。
　上記のテストでは、単純に大量のデータが入力されるすべての作業にインデックスをDROPしたり、UNUSABLE状態に変更して作業しなさいという意味ではなく、APPENDヒントはテーブルにINSERTされるデータがDirece Loadで入力され、それだけREDO発生量が減ることになりますが、インデックスが発生させるREDOは制御されないため、インデックスがたくさん生成されているテーブルに対するAPPENDヒントは性能改善に限界があることを示すためことを意味します。

・APPEND_VALUES
　使用バージョン：11.2.0.1〜。
　使用方法: /*+ APPEND_VALUES */ (Inverse: NOAPPEND)
　ヒント意味: Insert ~ Values (1件) 構文がDirect Mode Insertになるようにするヒント。

　Direct Path ModeのInsertは、Oracle 11g以前までは”INSERT ~ SELECT”構文に/*+ APPEND */ヒントを使用することで可能でしたが、”INSERT ~ VALUES”構文(1 Row Insert)には対応していませんでした。
　ところが、Oracle 11gからはAPPENDヒントとAPPEND_VALUESヒントを適用すると、”INSERT ~ VALUES” (1 ROW INSERT) InsertをDirect Path Modeで実行できるようになりました。Oracle 11gR1までは”INSERT ~ VALUES”構文にAPPENDヒントを付与すればよく、Oracle 11gR2はAPPENDヒントに代わるAPPEND_VALUESヒントが新たに追加され、これを使用すればよいことになります。
しかし、少数のデータをInsertする時、本当にAPPEND_VALUESヒントを適用することが性能上で有利となのか考えてみる必要があります。
　Direct Path ModeでInsert操作を実行することは、同じテーブルに他のInsert操作が実行されるときにBlockingが発生し、Direct Path Modeの特性上、Physical I/Oが発生し、High Water Mark以降にデータが入力されるため、ある程度のFragmentation(断片化)現象のオーバーヘッドが発生することを意味します。したがって、少ない数のデータに対するInsert作業をDirect Path Modeで実行することは、むしろ性能上不利であると言えます。
　しかし、Insert ~ Select ~構文と似たような動作をするArray Processingを活用したBulk Insertを実行する場合は話が違ってきます。下記の例を通じて、1件のデータがInsertされる場合とArray Processingを活用したBulk Insertが実行される場合にConventional Path ModeとDirect Path Modeの性能の違いをそれぞれ見てみましょう。

テスト［1］.1 Rowに対するREDO発生量の比較

■ テスト目的 : APPEND_VALUES hint の効率性チェック(Only 1 Row)

--------------------------------------------------------------------------------
-- TEST TABLE CREATION
--------------------------------------------------------------------------------
CREATE TABLE T1_APPEND (C1 NUMBER) ;
CREATE TABLE T1_APPEND_VALUES (C1 NUMBER) ;

--------------------------------------------------------------------------------
-- Table Nologging -> INSERT -> Data Check(Redo Size)
--------------------------------------------------------------------------------
alter table T2_APPEND nologging ;
alter table T2_APPEND_VALUES nologging ;

▷ STEP1. Conventional Path Mode INSERT実行時REDO SIZEチェック

* NSERT後、Redo Sizeの増加量をチェック

SQL> INSERT INTO T1_APPEND VALUES (1) ;
SQL> SELECT (I.VALUE - &REDO_SIZE) AS DIFF_REDO_SIZE
     FROM   V$MYSTAT I, V$STATNAME S
     WHERE  I.STATISTIC# = S.STATISTIC#
     AND    S.NAME = 'redo size';

DIFF_REDO_SIZE
--------------
5440

▷ STEP2. Direct Path Mode INSERT実行時REDO SIZEチェック

* INSERT(APPEND_VALUES ヒントを適用)

SQL> INSERT /*+ APPEND_VALUES */ INTO T1_APPEND_VALUES VALUES (1) ;

* INSERT(WITH APPEND VALUES HINT)後にRedo Sizeの増加量をチェック

SQL> SELECT (I.VALUE - &REDO_SIZE) AS DIFF_REDO_SIZE
     FROM   V$MYSTAT I, V$STATNAME S
     WHERE  I.STATISTIC# = S.STATISTIC#
     AND    S.NAME = 'redo size';

DIFF_REDO_SIZE
--------------
5664          ---> 単純INSERT時のREDO発生量 = 5440

　上記のテスト[1]の結果のように、1RowデータをINSERTする場合は、Direct Path Modeに比べてConventional Path ModeでINSERTする方がより良い性能を示しました。つまり、1ROWずつデータINSERTにはAPPEND_VALUESヒントは効用を持ちません。

テスト［2］.BULK INSERTに対するREDO発生量の比較

■ テスト目的 : FORALLを利用したBULK COLLECT INSERT時のAPPEND_VALUESヒントの効率チェック

--------------------------------------------------------------------------------
-- TEST TABLE 生成スクリプト
--------------------------------------------------------------------------------
CREATE TABLE T1_INSERT (C1 NUMBER) ;
CREATE TABLE T1_APPEND_VALUES (C1 NUMBER) ;
CREATE TABLE T2_INSERT (C1 NUMBER) ;
CREATE TABLE T2_APPEND_VALUES (C1 NUMBER) ;

INSERT INTO T1_INSERT
SELECT LEVEL as C1
 FROM DUAL
 CONNECT BY LEVEL <= 100000 ;

INSERT INTO T1_APPEND_VALUES
SELECT LEVEL
 FROM DUAL
 CONNECT BY LEVEL <= 100000 ;

COMMIT ;

--------------------------------------------------------------------------------
-- APPEND Procedure 生成構文 : P_INSERT
--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE P_INSERT (T_ARRAY_SIZE IN PLS_INTEGER DEFAULT 100)
IS
   CURSOR ARRAY_INSERT IS
     SELECT *
     FROM T1_INSERT;

   TYPE FETCH_ARRAY IS TABLE OF ARRAY_INSERT%ROWTYPE;
   T1_DATA  FETCH_ARRAY;

BEGIN
     OPEN ARRAY_INSERT;
       LOOP
         FETCH ARRAY_INSERT BULK COLLECT INTO T1_DATA LIMIT T_ARRAY_SIZE;
            FORALL i IN 1..T1_DATA.COUNT
                   INSERT INTO T2_INSERT VALUES T1_DATA(i);
                   COMMIT;
         EXIT WHEN ARRAY_INSERT%NOTFOUND;
       END LOOP;
    CLOSE ARRAY_INSERT;
END P_INSERT;
/

--------------------------------------------------------------------------------
-- APPEND_VALUES Procedure 生成構文 : P_APPEND_VALUES
--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE P_APPEND_VALUES (T_ARRAY_SIZE IN PLS_INTEGER DEFAULT 100)
IS
   CURSOR ARRAY_INSERT IS
     SELECT *
     FROM T1_APPEND_VALUES;

   TYPE FETCH_ARRAY IS TABLE OF ARRAY_INSERT%ROWTYPE;
   T1_DATA  FETCH_ARRAY;

BEGIN
     OPEN ARRAY_INSERT;
       LOOP
         FETCH ARRAY_INSERT BULK COLLECT INTO T1_DATA LIMIT T_ARRAY_SIZE;
    FORALL i IN 1..T1_DATA.COUNT
                     INSERT /*+ APPEND_VALUES */ INTO T2_APPEND_VALUES VALUES T1_DATA(i);
                     COMMIT;
           EXIT WHEN ARRAY_INSERT%NOTFOUND;
        END LOOP;
     CLOSE ARRAY_INSERT;
END P_APPEND_VALUES;
/

--------------------------------------------------------------------------------
-- Table Nologging -> Procedure Test -> Data Check(Redo Size)
--------------------------------------------------------------------------------
alter table T2_INSERT nologging ;
alter table T2_APPEND_VALUES nologging ;


▷ STEP1. Conventional Path Mode INSERT 수행 시 REDO SIZE 체크

* Procedure Run : P_INSERT

SQL> EXEC P_INSERT ;

* Redo Size の増加量チェック

SQL> SELECT (I.VALUE - &REDO_SIZE) AS DIFF_REDO_SIZE
     FROM   V$MYSTAT I, V$STATNAME S
     WHERE  I.STATISTIC# = S.STATISTIC#
     AND    S.NAME = 'redo size';

DIFF_REDO_SIZE
--------------
2011088

▷ STEP2. Direct Path Mode INSERT 수행 시 REDO SIZE 체크

* Procedure Run : P_APPEND_VALUES

SQL> EXEC P_APPEND_VALUES ;

* Redo Size の増加量チェック

SQL> SELECT (I.VALUE - &REDO_SIZE) AS DIFF_REDO_SIZE
     FROM   V$MYSTAT I, V$STATNAME S
     WHERE  I.STATISTIC# = S.STATISTIC#
     AND    S.NAME = 'redo size';

DIFF_REDO_SIZE
--------------
1740776       ---> P_INSERT実行時のリドゥ発生量 = 2011088

　テスト[2]のFORALLを利用したBULK COLLECT INSERT時、APPEND_VALUESヒントの効率性チェック部分を見ると、2つのプロシージャは全て同じ量のデータをINSERTするArray Processingを活用したBULK INSERT構文です。
　それぞれを実行した後、REDO発生量を調査してみると、P_INSERTプロシージャは約1.9 MB程度で、P_APPEND_VALUES(APPEND_VALUESヒント適用)プロシージャは約1.6 MB程度で多少減少した結果となりました。
　このテスト結果から推測すると、多くのデータに対するINSERT作業時、大きな違いは見られませんが、BULK INSERT作業にはAPPEND_VALUESヒントを使用すれば、REDO発生量を減らすことができることが証明されました。

次回のSQLチューニングブログは・・・

SQLチューニングブログ2nd Season（第4章）

「SQL Tuning と HINTの関係」（7/8）
～　PARMETER制御のヒント・SQL PERFORMANCEに関するヒント・QUARY OPTIMIZINGに関するヒント　～

Oracle SQLチューニング Season2（第23回）第4章 SQL TuningとHINTの関係（6/8）

4.2.9　INSERTコントロールヒント

前の記事

次の記事

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

人気記事ランキング

タグ一覧

Oracle SQLチューニング Season2（第23回）第4章 SQL TuningとHINTの関係 （6/8）

4.2.9 INSERTコントロールヒント

前の記事

次の記事

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

人気記事ランキング

タグ一覧

Oracle SQLチューニング Season2（第23回）第4章 SQL TuningとHINTの関係（6/8）

4.2.9　INSERTコントロールヒント

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