2025.02.25
SQLチューニング 2nd Season（第29回）
第6章 大量のデータ処理性能改善方法 （2/5）
今回のSQLチューニング 2nd Season（第29回）は「第6章 大量のデータ処理性能改善方法」と題しまして
計5回シリーズで解説しています。今回はその第2回目「大量のデータ変更作業の性能改善戦略」です。
では、早速始めましょう。
6.2 大量のデータ変更作業の性能改善戦略
大量のデータを変更しなければならない作業（大量のDML）は、それだけで大きな負担になります。
さらに、変更作業自体に限定されるものではなく、変更前のデータと変更後のデータをそれぞれ保存･管理しなければならないと言う
Oracleのメカニズムで考えるのであれば、パフォーマンスの改善をしなければならない立場においては、より大きな頭痛の種となります。
文字通り大量のデータを変更しなければならない作業では、その特性上において、SQL Tuningだけで性能を改善する余地は多くないだけではなく、改善したとしても劇的な効果を期待するのは難しいところがあります。
また、必然的に発生するredoとundoデータによる追加の負荷は、Oracleの合理的な同時性保証のメカニズムがむしろ恨まれることになるかもしれません。
したがって、今回のテーマでは、大量のDMLを処理する時、先に説明したパーティションテーブルを活用した構成及び変更やDirect Path Insertを誘導する方法などを通じて、DML作業自体の性能改善はもちろん、Redoデータを減少させてシステムのDown Timeの最小化にも貢献することができる方法についての話をしたいと思います。
6.2.1 大量のDelete
月単位、あるいは特定の期間単位でデータを定期的に削除する業務は、どのシステムにおいても従来から良く見られる作業の1つであり、
慣れ親しんだ作業です。しかし、その作業の性能を改善することは容易ではありません。
大量のDelete作業は、Table Full ScanやIndex Scanなどの様な当該テーブルにAccessする方法による非効率よりも、必然的に発生するredo, undoデータ生成に加え、当該テーブルにIndexが存在する場合、Indexの数によって追加のOverheadが発生することが性能低下を
招く主な理由となるからです。
Note
Indexで発生する追加のOverheadとは、削除対象のデータをIndexでも削除する必要があるのと同時に、この過程でもredoとundoデータが
生成されることを意味します。 上記のように負荷を抑えながら大量のDelete作業の実行速度を改善するにはどんな方法があるでしょうか？
答えはやはりPartition Tableの構成にあります。
定期的に削除する期間が決まっている場合、そのカラムをKeyにするRange Partitionを構成し、削除対象となる期間に該当するPartition TableをAlter Table [Table_Name] Drop Partition [Partition_Name]; コマンドでDMLではなくDDLで作業を置き換えることができます。
DDLへのDelete操作の代替は、redoデータが生成されず、単にそのPartitionをDropする作業のみ行われるため、速度の差は比較できない
ほど大きく、大量のredo / undoデータが発生しないため、Down Timeの最小化にも大きく貢献することができます。
以下のテスト結果からその違いについて詳しく見てみましょう。
[テーブル生成DDL(Partition_test) ]。
CREATE TABLE partition_test (
TIME_ID DATE 、
DAY_NAME VARCHAR2(9)、
DAY_NUMBER_IN_WEEK NUMBER(1,0) 、
...中略...
END_OF_FIS_YEAR DATE )
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'))
)；
[テーブル生成DDL(Partition_test2) ]。
CREATE TABLE partition_test2 (TIME_ID DATE 、
DAY_NAME VARCHAR2(9)、
...中略...
END_OF_FIS_YEAR DATE )；
[データ入力(2つのテーブルは同じ) ]
INSERT /*+ append */
INTO partition_test
SELECT a.*。
FROM sh.times a 、
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10000
) b； あくまでもテストであるため、パーティションテーブルと一般テーブルを生成して、両方のテーブルに同じデータを入力しました。
今度は大量のデータを削除する作業をそれぞれ実行してみましょう。
[一般テーブル(Partition_test2)のデータを削除します。］
delete partition_test2 where time_id <= (to_date('2000-12-31','YYYY-MM-DD'));
Execution Plan
----------------------------------------------------------
Plan hash value: 3385385008
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5718 | 51462 | 141K (1)| 00:28:15 |
| 1 | DELETE | PARTITION_TEST2 | | | | |
|* 2 | TABLE ACCESS FULL| PARTITION_TEST2 | 5718 | 51462 | 141K (1)| 00:28:15 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
5516 recursive calls
13289330 db block gets
521869 consistent gets
520081 physical reads
5831072716 redo size
565 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
10960000 rows processed PARTITION_TEST2テーブルの3年分のデータをDeleteする作業を行った結果、注目すべき部分としては、その作業時に発生したRedoデータの量と応答時間です。
3年分のデータを消去するのにかかった時間は約7分程度であり、この過程で発生したRedoデータは約5.5GB程度であることが分かります。
[パーティションテーブル(Partition_test)のデータ削除 ]
SQL> alter table partition_test drop partition partition_test1;
テーブルが変更されました。
経 過: 00:00:00.13
SQL> alter table partition_test drop partition partition_test2;
テーブルが変更されました。
経 過: 00:00:00.04
SQL> alter table partition_test drop partition partition_test3;
テーブルが変更されました。
経 過: 00:00:00.04 一方で、PARTITION_TESTテーブルは年単位のRange Partitionで構成されており、Alter Table [Table_Name] Drop Partition [Partition_Name]; コマンドで3年間のデータに該当する3つのPartitionをDropするDDL文を実行するため、Redoデータが発生せず、1秒以内に作業が完了しました。
このように、Partition Tableへの移行は、先に紹介した内容のように単純に読み取り作業だけに限定されず、DML作業にも業務によって性能上有利な構造を提供します。続くInsert関連作業もPartition Tableへの転換が可能な場合、パフォーマンスの観点から有利な様々な方法の考案が可能です。
Partition Tableの移行が大容量データを扱う上で有利な点を多く提供するので、積極的な導入の必要性を改めて強調したいと考えます。
6.2.2 大量のインサート
大量のInsert操作の性能改善策のうち、広く知られている /*+ append */ ヒントを活用する方法については、以前に「 チューニングとヒントのInsert制御ヒントの部分」で詳しく説明しました。この方法を用いない性能改善方法について紹介します。
Index SplitによるDirect Path Insertの性能差
常に増加する値をInsertする必要があるカラムが存在することがあります。
代表的な例は、日付カラムにSYSDATEが入力される状況でしょう。
もし、このカラムにIndexが生成されていると仮定して、大量のInsert作業が行われると、9:1 Index Splitが頻繁に発生する現象が自然に現れるでしょう。前述したように、Index Splitは決して軽いものではありません。
では、なぜそうなのか、その過程を見てみましょう。
まず、9:1 Splitとは、Max値が入力される状況で、右側の最下位Index Leaf Blockにこれ以上保存するスペースがない場合、Free ListのFree Blockを持ってきて、既存の最下位Index Leaf Blockの最小限のデータを新しいFree Blockに移して、新しく保存されるデータをそのBlockに入力する過程を指します。
これは、データが増加する過程で必然的にIndex Sizeが増加し、Index Splitはこの過程で発生する自然な現象です。
ただし、Index Splitプロセスは無料ではありません。
Index Splitの全過程は redoが発生し、Splitが開始され、完了する時点までLockingを通じてこの作業を保護します。
この時、すべてのTransactionはEnq: TX – Index ContentionというWait EventでSplitが完了する時点まで待機します。
したがって、常に値が増加する性格のカラムにIndexが生成されていて、そのテーブルに大量のデータが入力される場合、Index SplitによるWait Timeの増加に加え、redoの発生などでInsert速度の低下は必然的に発生します。
では、これを改善する方法はあるのでしょうか？
結論から言うと、問題のあるIndexに別のカラムを追加して再作成する方法は存在します。
この方法の目的は、大量のデータが入力された時、既存の右下位Index Leaf Blockに競合が集中していたのが、結合Indexによってその競合がある程度分散されるからです。
新しく結合するIndex構成対象カラムのNDV(Number of Distinct Value)値が大きいほど、Index Split減少の効果はさらに大きくなります。
ただし、この部分で注意することは、Index Clustering Factorが大きく減少する可能性があるので、NDV値が大きいカラムを優先的に構成する必要はありません。
Note.
Index Clustering Factor(CF)とは、整列が保証されたIndexの整列度とテーブルの整列度を数値化して表現したもので、CBO(Cost Based Optimizer)がIndex ScanのCost計算に決定的な影響を与える尺度である。CFの数値がテーブル全体のBlock数に近いほど性能上有利であり、テーブル全体のrows数に近いほど性能上不利な特徴を持つ。 6.2.2.1 アレイ処理
大量のDML作業は、データをLoadしたり、Update(Delete)する時、一般的にLOOP文を使って処理することが多くあります。
この方法は、毎Loopごとに1回ずつDML作業が行われ、それだけDBMS Callが発生するため、性能上で不利です。
しかし、Array Processingを利用したBulk SQLを使用すると、Loopなしで一度のSQL実行だけで処理が可能です。
つまり、大量のDMLを一度(Limit予約語を使用して一度に処理できる件数を制限しない場合)に処理することができるので、DBMS Callを減らしてLoopで処理されていた方法に比べ、大きな性能改善効果を見ることができます。
以下のテスト結果は、その性能差をよく示していると思います。
Array Processing vs. Looping Insert :
[Array Processingを活用してInsertするProcedure Source - P_BULK_INSERT_TEST1 ]
CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST1 IS
CURSOR sales_cur IS
SELECT PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
FROM SH.SALES;
TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;
SALES_TBL SALES_TBL_TYPE;
BEGIN
OPEN sales_cur;
LOOP
FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000;
-- PGA消費を小さく保つために1,000件ずつしか処理しません。
FOR i IN sales_tbl.FIRST..sales_tbl.LAST
LOOP ---各種計算はこちらから
sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;
END LOOP;
-- FETCHされた1,000件をSQL1回で処理
FORALL i IN sales_tbl.FIRST..sales_tbl.LAST
INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,
sales_tbl(i).CUST_ID,
sales_tbl(i).TIME_ID,
sales_tbl(i).CHANNEL_ID,
sales_tbl(i).PROMO_ID,
sales_tbl(i).QUANTITY_SOLD,
sales_tbl(i).AMOUNT_SOLD
);
EXIT WHEN sales_cur%NOTFOUND;
END LOOP;
CLOSE sales_cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));
END;
/
[一つ一つLoopを介してInsertするProcedure Source - P_BULK_INSERT_TEST2 ]
CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST2 IS
CURSOR sales_cur IS
SELECT PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
FROM SH.SALES;
TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;
SALES_TBL SALES_TBL_TYPE;
BEGIN
OPEN sales_cur;
LOOP
FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000;
-- PGA消費を小さく保つために1000件ずつしか処理しません。
FOR i IN sales_tbl.FIRST..sales_tbl.LAST
LOOP -各種計算はこちらから
sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;
END LOOP;
-- LOOP を使う方法; INSERT SQL 1,000 回実行する方法
FOR i IN sales_tbl.FIRST..sales_tbl.LAST
LOOP -- FOR ALL予約語除外
INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,
sales_tbl(i).CUST_ID,
sales_tbl(i).TIME_ID,
sales_tbl(i).CHANNEL_ID,
sales_tbl(i).PROMO_ID,
sales_tbl(i).QUANTITY_SOLD,
sales_tbl(i).AMOUNT_SOLD
);
END LOOP;
EXIT WHEN sales_cur%NOTFOUND;
END LOOP;
CLOSE sales_cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));
END;
/ P_BULK_INSERT_TEST1はArrary Processingを活用してInsert作業を行うプロシージャであり、P_BULK_INSERT_TEST2は、1つ1つのInsert作業を行うプロシージャです。
同じ件数をInsertするとき、2つの方法の性能差がどのように現れるか確認してみましょう。
[ P_BULK_INSERT_TEST1 Vs. P_BULK_INSERT_TEST2 ]
SQL> exec P_BULK_INSERT_TEST1;
PL/SQL処理が正常に完了しました。
経 過: 00:00:02.20
SQL> exec P_BULK_INSERT_TEST2;
PL/SQL処理が正常に完了しました。
経 過: 00:00:35.08 この場合、Insert操作を実行するよりもArray Processingを活用する方が性能上、はるかに有利な結果が出ました。
ちなみに、Array ProcessingはInsert操作だけでなく、UpdateやDelete操作でも効果を見ることができます。
[参考: Array Processingを活用したUpdate Procedure Source ]
CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST2 IS
CURSOR sales_cur IS
SELECT PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
FROM SH.SALES;
TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;
SALES_TBL SALES_TBL_TYPE;
BEGIN
OPEN sales_cur;
LOOP
FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA 소모를 작게 유지하기 위해 1000건씩만 처리
FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP --各種計算はこちらから
sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;
END LOOP;
-- UPDATE / DELETE も可能
FORALL i IN sales_tbl.FIRST..sales_tbl.LAST
UPDATE SALES SET AMOUNT_SOLD = sales_tbl(i).AMOUNT_SOLD
WHERE PROD_ID = sales_tbl(i).PROD_ID
AND CUST_ID = sales_tbl(i).CUST_ID
AND TIME_ID = sales_tbl(i).TIME_ID
AND CHANNEL_ID = sales_tbl(i).CHANNEL_ID
AND PROMO_ID = sales_tbl(i).PROMO_ID;
EXIT WHEN sales_cur%NOTFOUND;
END LOOP;
CLOSE sales_cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));
END;
/ 6.2.3 Merge構文の活用
Merge構文はUpdate, Delete, InsertなどのMultiple DML作業を一度に処理する方法を提示します。
Sourceテーブルから抽出したデータをTargetテーブルへDMLを処理する形で動作するのが大きな特徴です。
MERGE構文を理解するため、構文の構成要素について説明します。
INTO節：Targetテーブルの定義とヒント構文の適用
USING節：Sourceテーブル定義とINTO節と同様にヒント構文が適用可能。
ON節 : SourceテーブルとTargetテーブル間のジョイン条件が明示され、このジョインは必ず1:1ジョインでなければならない。
ON節は下記のように3つの部分に分かれています。
Join Condition
WHEN MACHED THEN è UPDATE or UPDATE & DELETE
WHEN NOT MATCHED THEN INSERT
Note.
Merge構文使用時の制約事項
On節で指定されたSourceテーブルとTargetテーブル間の結合関係は必ず1:1結合でなければなりません。
また、Update対象カラムはOn節に使用することができません。 Merge構文が大量のデータを変更する作業で便利な場合は下記のような場合です。
上記のCASE(1), CASE(2), CASE(3)は共通的にCursor(Source Table)からデータを抽出し、抽出されたデータ数だけFOR文を繰り返し実行してUPDATE & INSERT構文を実行するパターンのプログラムです。
CASE(1),CASE(2)と違ってCASE(3)の場合は、物理ファイルを読み込んでファイルのRowずつFetchしてUPDATE構文を実行するのですが、これはFile Open + Fetch Row部分がCASE(1),CASE(2)のCursor部分と同じだと考えてください。
このようなパターンのプログラムはCursorから抽出されるデータ件数が性能を左右しますが、通常BatchプログラムはCursorから抽出される件数が最低数十万件から数百万件である場合が多く、CASE(1), CASE(2).CASE(3)のようなロジックで実行される場合、プログラムの実行時間はそれほど速くありません。
このような場合、MERGE構文を活用して性能問題を改善することができます。
MERGE構文は、Oracle 9iまではUPSERT(UPDATE+ INSERT)構文としてのみ使用されましたが、10g以降はOnly UPDATE、Only INSERT、UPSERT文など様々なパターンのDMLを処理できるように、MERGE構文の活用できる幅が広がり、CASE(1), CASE(2), CASE(3)のようなパターンのBatchプログラムの性能改善のために多く使用されています。
ただし、CASE(3)の場合、物理ファイルを読み込んでUPDATEやINSERT文を繰り返し実行するプログラムであるため、Oracle DWチューニングのために9iに新しく登場した機能であExternal TableとMERGE構文を一緒に利用すれば、性能を改善することができます。
以下は性格が似ているCASE(1), (2)とCASE(3)を分けてそれぞれMerge構文に変更して性能を改善した事例です。
Case (1), Case (2) – For Loop処理をMergeに変更する
[ TEST TABLE/INDEX CRREATE ]
* SOURCE_TABLE 作成
CREATE TABLE TAB_TEST1 AS
SELECT ROWNUM seq ,
a.*
FROM sh.sales a;
CREATE UNIQUE INDEX TAB_TEST1_UX_01 ON TAB_TEST1(SEQ);
* TARGET_TABLE
CREATE TABLE TAB_TEST2 AS
SELECT ROWNUM seq ,
sysdate time_id
FROM sh.sales a
CREATE UNIQUE INDEX TAB_TEST2_UX_01 ON TAB_TEST2(SEQ);
[ TEST – For Loopスクリプト ]
DECLARE
CURSOR C1 is SELECT * FROM TAB_TEST1;
BEGIN
FOR c_rec IN C1
LOOP
UPDATE /*+ index(t2 tab_test2_ux_01) */
tab_test2 t2
SET t2.time_id = c_rec.time_id
WHERE t2.seq = c_rec.seq ;
END LOOP;
COMMIT;
END;
/
[ For Loop スクリプトのTrace結果]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 5 50 0 0
Execute 1 50.54 50.67 9478 2771164 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 50.54 50.68 9483 2771214 0 1
* Cursor 部分
SELECT *
FROM
TAB_TEST1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9189 1.87 1.80 5067 14215 0 918843
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9191 1.87 1.80 5068 14216 0 918843
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
918843 TABLE ACCESS FULL TAB_TEST1 (cr=14215 pr=5067 pw=0 time=342217 us cost=1411 size=78648300 card=786483)
* Update 部分
UPDATE /*+ index(t2 tab_test2_ux_01) */ TAB_TEST2 T2 SET T2.TIME_ID = :B2
WHERE
T2.SEQ = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 918843 101.19 102.47 4212 2756594 939913 918843
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 918844 101.21 102.47 4212 2756594 939913 918843
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE TAB_TEST2 (cr=3 pr=11 pw=0 time=2571 us)
1 INDEX UNIQUE SCAN TAB_TEST2_UX_01 (cr=3 pr=6 pw=0 time=2116 us cost=2 size=22 card=1)(object id 84633) 上記のテストは、CASE (1), CASE (2) に該当するプログラムをテストするために作成したPL/SQL文です。
ロジックは、TAB_TEST1からデータを抽出し、抽出されたデータ数だけFOR文を繰り返し実行して処理するように構成されています。Trace結果を見ると、ほとんどの処理時間を消費した部分はUpdate構文です。
テーブルTAB_TEST1の抽出件数である918,843だけ繰り返し実行し、合計2,756,594 Block、約102秒のシステムリソースを使用したことが確認できます。
それでは、上のCursor For Loop構文をMerge Into文に変更した場合を見てみましょう。
[MERGE構文に変更及びTrace結果 ]
MERGE /*+ use hash(t1 t2) full(t1) full(t2) */ INTO TAB_TEST2 T2
USING TAB_TEST1 T1
ON (T2.SEQ = T1.SEQ)
WHEN MATCHED THEN
UPDATE SET T2.TIME_ID = T1.TIME_ID;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 6 9 0 0
Execute 1 11.51 17.26 6872 7431 940043 918843
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.51 17.27 6878 7440 940043 918843
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Rows Row Source Operation
------- ---------------------------------------------------
0 MERGE TAB_TEST2 (cr=7626 pr=6872 pw=0 time=17271455 us)
918843 VIEW (cr=7366 pr=6871 pw=0 time=3092107 us)
918843 HASH JOIN (cr=7366 pr=6871 pw=0 time=2796072 us cost=8140 size=105388722 card=786483)
918843 TABLE ACCESS FULL TAB_TEST2 (cr=2291 pr=1997 pw=0 time=234970 us cost=641 size=29837244)
918843 TABLE ACCESS FULL TAB_TEST1 (cr=5075 pr=4874 pw=0 time=780321 us cost=1411 size=78648300) Merge文を使用した場合、合計7,626 Block、約17秒のシステムリソースを使用し、従来のCursor For Loop構文に比べて大幅な改善効果を示しました。
2つの構文の性能差が発生する最も大きな理由は、大量のデータを処理する方法にあります。
Cursor For Loopの場合は、データを1つ1つ処理し、過度なDBMS Callの発生及び大量のLoop処理によるI/Oが過度に発生したのに対し、Merge構文の場合は、全体のデータを一度に処理するため、このような負荷が解消されたのです。
大量のデータ処理時において、全体のデータを一度に処理できる場合では、Merge構文の積極的な適用により該当業務をより良い性能で処理することができることを、ここではしっかりと覚えておいてください。
Case(3) – External Table + Merge活用
大量のデータに対するFileを開いてDMLを処理することも、先に説明したFor Loop処理と似たような脈絡を持ちます。 したがって、効率的な処理のためにMerge文で変更するには、そのFile形式のデータをテーブルに変更する必要があります。この時、便利な機能がExternal Tableです。
External Tableとは、Database外部のOSファイルをDatabaseのテーブルに変更して使うようにするのが核心です。
ただし、External TableにはIndexを生成することができず、Select、Join、SortなどのRead Only機能しか提供されないという制約があります。それでも、FileをExternal Tableに変換する作業の高速性を保証し、Parallel Processingが可能で、大量のデータを一度に処理するのに適した性質を持っています。
以下は、File Open + For Loop UpdateをExternal TableとMerge文を活用して性能を改善したテストケースです。
[ File Opne → For Loop → Update ]
更新対象データ : MergeTest.txt
DECLARE
v_line varchar2(2000);
v_c1 varchar2(100);
v_c2 varchar2(100);
v_seperator varchar2(1) := ',';
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := utl_file.FOPEN( 'EXT_TAB_DIR', 'MergeTest.txt','r');
UTL_FILE.GET_LINE(v_file, v_line);
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
v_c1 := substr(v_line,1,instr(v_line,v_seperator,1,1)-1);
v_c2 := substr(v_line,instr(v_line,v_seperator,1,1)+1,
instr(v_line,v_seperator,1,2)-
instr(v_line,v_seperator,1,1)-1);
UPDATE /*+ index(t2 tab_test3_ix_01) */
sh.tab_test2 t2
SET t2.time_id = to_date( v_c2 ,'YYYYMMDD')
WHERE t2.seq = to_number(v_c1) ;
END LOOP;
UTL_FILE.FCLOSE(v_file);
COMMIT;
END;
[ File Opne → For Loop → Update – Trace ]
UPDATE /*+ index(t2 tab_test3_ix_01) */ SH.TAB_TEST2 T2 SET T2.TIME_ID =
TO_DATE( :B2 ,'YYYYMMDD')
WHERE
T2.SEQ = TO_NUMBER(:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 918843 82.88 83.71 1915 2756594 939915 918843
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 918844 82.88 83.71 1915 2756594 939915 918843
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE TAB_TEST2 (cr=3 pr=0 pw=0 time=156 us)
1 INDEX UNIQUE SCAN TAB_TEST2_UX_01 (cr=3 pr=0 pw=0 time=38 us cost=2 size=22 card=1)(object id 84638)
[ External table Create ]
CREATE TABLE EXTERNAL_TAB_EX (
SEQ NUMBER,
PROD_ID NUMBER,
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID NUMBER,
PROMO_ID NUMBER,
QUANTITY_SOLD NUMBER,
AMOUNT_SOLD NUMBER )
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL)
LOCATION ('Mergetest.txt'))
REJECT LIMIT UNLIMITED;
[ External table exec – Trace ]
MERGE /*+ use hash(t1 t2) full(t1) full(t2) */ INTO TAB_TEST2 T2
USING EXTERNAL_TAB T1
ON (T2.SEQ = T1.SEQ)
WHEN MATCHED THEN
UPDATE SET T2.TIME_ID = T1.TIME_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 678 0 0
Execute 1 8.86 17.60 0 2225 940055 918843
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.93 17.67 0 2903 940055 918843
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Rows Row Source Operation
------- ---------------------------------------------------
0 MERGE TAB_TEST2 (cr=2723 pr=0 pw=0 time=17700181 us)
918843 VIEW (cr=2459 pr=0 pw=0 time=2100011 us)
918843 HASH JOIN (cr=2459 pr=0 pw=0 time=1860918 us cost=675 size=1094512 card=8168)
918843 EXTERNAL TABLE ACCESS FULL EXTERNAL_TAB (cr=168 pr=0 pw=0 time=180759 us cost=29 size=816800 card=8168)
918843 TABLE ACCESS FULL TAB_TEST2 (cr=2291 pr=0 pw=0 time=204340 us cost=641 size=29837244 card=877566) 大量のデータを変更する作業における性能改善の余地はやはり少ないのです。
Partitioningの有無と業務の特性によって性能改善の幅が左右されることが多くあります。
性能改善作業は、共通的に適用できる性格のものとそうでない性格のものがあり、大量のデータを変更する作業は特に後者に属する代表的な例です。
簡単に改善できるわけではないものの、意外と簡単に解決できる場合も多くあります。
もちろん、簡単に解決できる場合では、十分な関連知識の理解がベースになっているはずです。
今回紹介した要素について、日頃からよく理解していれば、関連する作業における閃くアイデアの提供者になれるものと私は確信しています。
SQLチューニングブログ 2nd Season（第29回） 終
次回のSQLチューニングブログは・・・
SQLチューニングブログ 2nd Season（第30回）
第6章「 Parallel Processing戦略 」
～ 次回は全5回シリーズの3回目をお送りしていきます ～
私たちは 日本のITインフラ における
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
日本エクセムのデータベースソリューション
最新情報は公式SNSでも配信中
SQLチューニングブログについてのお問い合わせは
