2024.11.26
SQLチューニング 2nd Season（第23回）第4章 SQL TuningとHINTの関係 （6/8）
今回の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チューニング2nd Season（第23回）終
次回のSQLチューニングブログは・・・
SQLチューニングブログ2nd Season（第4章）
「SQL Tuning と HINTの関係」（7/8）
～ PARMETER制御のヒント・SQL PERFORMANCEに関するヒント・QUARY OPTIMIZINGに関するヒント ～
私たちはITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
日本エクセムのデータベースソリューション
日本エクセムの最新情報は公式SNSでも配信中
SQLチューニングブログについてのお問い合わせは
