2025.02.12
SQLチューニング 2nd Season（第28回）
第6章 大量のデータ処理性能改善方法 （1/5）
今回のSQLチューニング 2nd Season（第28回）は「第6章 大量のデータ処理性能改善方法」と題しまして
計5回シリーズで解説していきますが、今回はその第1回目となります。
では、早速始めましょう。
ITパラダイムの変化するスピードは、私たちが考えているよりもずうっと早いです。
例えば、DBの業務処理1つにおいても、慣れ親しんだ方式であるOLTP、DWなどで正確に区分されるのではなく、業務自体の区分が曖昧になっており、同時処理量の大量化現象が目立ってきています。大量のデータ読み取り、演算、ソートをして目的の情報を抽出する業務が主流となっています。このように抽出されたデータは、各企業の新たな利益創出や生産コスト削減などの意思決定に活用されたり、政府傘下である機関の政策の妥当性についての検査に用いられており、様々な機関や企業での様々な方法による分析や統計作業をより簡単に提供できるようになりました。
このようなパラダイムの変化によって、DBシステムに与える影響もかなり大きいものがあります。
大量のデータ処理に伴う負荷の増加はおそらく避けられないのが現実ではあるものの、同時に最大限に克服しなければならない課題でもあります。
大量のデータを扱わなければならないという不変の前提条件を持つ環境で発生する性能の問題を、果たしてどの程度改善できるのでしょうか。そのためにはまず、認識の転換が必要となってきます。
これまで持っていた偏見をすべて打ち破り、理論だけで知っていた知識を活用することができれば、大量のデータを扱う業務のパフォーマンス改善は、もはや難題にとどまらず、希望するレベルでの改善を実現できるのではないか？と考えます。
今回のテーマを通じて、大量のデータ処理におけるパフォーマンスの向上に貢献できる要素について紹介していきます。
しかし、ここから紹介する内容は、まるで数学の公式のようにすべての状況で当てはめることはできません。
業務の特性に合わせて積極的に考慮できる部分はありますが、逆にそうでない場合もあります。
本テーマでは、性能改善のための各要素の原理についてのテストを通じて、理解を深めることに焦点を当てています。
大量のデータ処理に関連する性能問題を抱えていたり、この部分に関心のある読者がいれば、性能改善要素の理解と業務の理解を伴って、その解決策を見つけることに貢献できるきっかけになってもらえたら幸いです。
6.1 積極的なPartitioning戦略
大量のデータを保存した一般テーブルを照会するためのSQLにおける照会のパターンを調査してみると、一般的に日付期間条件のように共通的に見られる照会条件が存在します。これは、V$SQL_PLANビューなどを通じて、Access Patternを抽出する方法での情報抽出が可能ですが、共通分母があった場合には、一般テーブルのPartitionテーブルへの積極的な転換を考慮しなければなりません。
［図6-1］は、PARTITION_TESTというテーブルのSizeが50GBであると仮定した状況です。
しかし、そのテーブルを照会するパターンを調べてみると、月単位の照会が行われていた場合、PARTITION_TESTテーブルが持つ構造的な非効率が存在します。必要なデータは、1ヶ月に該当するデータに過ぎないのに、もしTable Full Scanが実行された場合、不必要に50GBに該当する全データについてのI/Oをしなければならないと言った非効率な処理が発生することになるでしょう。そして、照会条件としてIndex Scanを実行したとしても、1ヶ月に相当する数十あるいは数百万件のデータだけTable Random Accessが発生すると言った非効率な処理が発生することが予測されます。
パフォーマンスの観点から見た場合の最も効率的な方法としては、テーブルを月単位でPartitioningした上で、該当する特定のPartition Table一つだけをTable Full Scan （Partition Range Single）で読むことこそが、I/Oや応答時間の面においての最も効率的な方法と言えるでしょう。このような場合、業務的にPartition Tableへの移行が可能な場合と、そうでない場合に分けて実際の性能を比較するテストを行って、その結果について詳しく説明したいと思います。
6.1.1 パーティションの切り替えが可能な場合
6.1.1.1 Non Partition TableをPartition Tableに変換する
テストテーブルとデータ生成スクリプト ：
[テーブル生成DDL (生成全文はAppendix参照) ]
CREATE TABLE partition_test (
TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
..中略...
END_OF_CAL_YEAR DATE ,
END_OF_FIS_YEAR DATE )
PARTITION BY RANGE( time_id )(
PARTITION partition_test_p1 VALUES less than (to_date('1998-01-01','YYYY-MM-DD')),
PARTITION partition_test_p2 VALUES less than (to_date('1998-02-01','YYYY-MM-DD')),
..中略...
PARTITION partition_test_p71 VALUES less than (to_date('2003-11-01','YYYY-MM-DD')),
PARTITION partition_test_p72 VALUES less than (to_date('2003-12-01','YYYY-MM-DD')) );
[ テーブル生成DDL (生成全文はAppendix参照) ]
CREATE TABLE partition_test2 (
TIME_ID DATE,
DAY_NAME VARCHAR2(9),
..中略...
END_OF_CAL_YEAR DATE,
END_OF_FIS_YEAR DATE );
[Partition_test2 インデックス生成スクリプト]
CREATE INDEX partition_test_idx_01 ON partition_test2 (time_id) ;
[データ入力(2つのテーブルが同じ)]
INSERT /*+ append */
INTO partition_test
SELECT a.*
FROM sh.times a ,
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10000
) b;
COMMIT;
INSERT /*+ append */
INTO partition_test2
SELECT a.*
FROM sh.times a ,
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10000
) b;
COMMIT;
上のスクリプトを実行することで、同じデータをパーティションテーブル（Partition_test）と一般テーブル（Partition_test2）の２つを
生成し、月単位の照会条件で実行されるSQLの性能をそれぞれで比較してみましょう。
一般テーブルvs.Partitionテーブルの性能比較テスト ：
[データ全体を読み込む場合 - Non Partition ]
* Bind Value
FROM_DATE = '1998-01-01'
TO_DATE = '1998-02-01'
SELECT day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM partition_test2
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
----------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 |00:00:08.39 | 518K| 518K|
| 1 | HASH GROUP BY | | 31 |00:00:08.39 | 518K| 518K|
|* 2 | FILTER | | 310K|00:00:08.30 | 518K| 518K|
|* 3 | TABLE ACCESS FULL| PARTITION_TEST2 | 310K|00:00:08.24 | 518K| 518K|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
3 - filter(("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd')))
[インデックスの使用 - Non Partition］
* Bind Value
FROM_DATE = '1998-01-01'
TO_DATE = '1998-02-01'
SELECT day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM partition_test2
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 |00:00:05.75 | 310K| 34915 |
| 1 | HASH GROUP BY | | 31 |00:00:05.75 | 310K| 34915 |
|* 2 | FILTER | | 310K|00:00:05.62 | 310K| 34915 |
| 3 | TABLE ACCESS BY INDEX ROWID| PARTITION_TEST2 | 310K|00:00:05.53 | 310K| 34915 |
|* 4 | INDEX RANGE SCAN | PARTITION_TEST_IDX_01 | 310K|00:00:00.14 | 823 | 805 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
4 - access("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
まず、Non Partition Tableの場合を見てみましょう。
全データをTable Full Scanして欲しいデータ（1ヶ月に相当するデータ）だけをFilteringしました。
その結果、データを抽出するSQLは8.31秒の応答時間と約510,000 Block以上をDisk I/Oで処理したことが分かりました。
性能面から見ると、明らかな非効率が存在していることがわかります。
上記のテスト結果からわかった非効率を改善するために考えられる一般的な方法が Indexの生成 です。
そこで、TIME_IDカラムにIndexを生成してIndex Scanを誘導してみることにしました。
その結果として、7.62秒の応答時間と合計320,000 BlockほどのI/Oが発生しました。
この過程においてのDisk I/Oは、約39,000 Block程度が含まれていることが分かり、このDisk I/OはIndexからPARTITION_TEST2テーブルへのTable Random Accessを実行する部分で誘発されたことが分かります。
Index Scanは、Single Block I/O 方式で処理していますが、Disk I/O の量が多くなる分だけ、応答時間の面においては大きな非効率が発生してしまうのが特徴です。したがって、39,000 Block以上の Disk I/O が発生した場合には、応答速度は上記の結果より更にかかってしまう可能性があります。
[必要なパーティションだけ読む場合 - Partition Range Iterator ]
* Bind Value
FROM_DATE = '1998-01-01'
TO_DATE = '1998-02-01'
SELECT day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM partition_test
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 |00:00:00.29 | 9216 | 9216 |
| 1 | HASH GROUP BY | | 31 |00:00:00.29 | 9216 | 9216 |
|* 2 | FILTER | | 310K|00:00:00.21 | 9216 | 9216 |
| 3 | PARTITION RANGE ITERATOR| | 310K|00:00:00.14 | 9216 | 9216 |
|* 4 | TABLE ACCESS FULL | PARTITION_TEST | 310K|00:00:00.08 | 9216 | 9216 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
4 - filter(("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd')))
次にPartition Tableのテスト結果を見てみましょう。
Partitioningの基準は、テーブル生成スクリプトで紹介したようにTIME_IDカラムの月単位で構成されています。
上のSQLのTIME_ID期間条件は、1ヶ月に該当するデータが入力されます。
まず、実行計画を見てみると、PARTITION RANGE ITERATORというオペレーションが目立っています。
このオペレーションは、Rang Partition KeyとなるTIME_ID条件に入力されるバインド変数によって、該当するPartition Tableだけを選択的にAccessするという意味を含んでいます。
つまり、該当バインド変数の期間が1ヶ月であることに該当するため、PARTITION_TESTテーブルの特定のPartition Table一つだけにAccessする結果が予想されるのです。
実行結果を見ると、9,216 BlockをDisk I/OでTable Full Scanを実行し、この過程における所要の応答時間は0.08秒に過ぎず、データを抽出してGroupingを実行するのに約0.2秒が追加でかかり、合計0.29秒程度でNon Partition Tableに比べて性能面で優れた結果を確認することができます。
下記の記事（上記の実行計画上の9216ブロックは、下記のように実際のパーティションサイズであることが確認できます。そのため、複数のパーティションを生成して、バインド変数の値に該当するパーティションだけアクセスするテストを作ってPartition Pruningを説明する必要があるようです）
Partition TableのI/O SizeとPARTITION_TESTテーブルの特定のPartition Table Sizeの比較 ：
[Size比較SQLの実行 ]Size比較SQL
SELECT partition_name ,
blocks ,
bytes/1024/1024 MB
FROM dba_segments
WHERE partition_name = UPPER( 'PARTITION_TEST_P2
')
AND segment_name = UPPER( 'PARTITION_TEST' )；
PARTITION_NAME BLOCKS MB
----------------- ------- -----
PARTITION_TEST_P2 9216 72
上記の結果から、PARTITION_TESTテーブルの特定のPartition Table一つだけに正確にAccessしたことを推測することができます。
このように、抽出対象のデータが入った特定のPartition Tableだけを選択的にAccessすることをPartition Pruning効果といいます。
つまり、Partition Pruning効果が、Non Partition TableとPartition Table間の性能差の原因として作用したことを意味します。
業務的にPartition Tableへの移行が可能であれば、積極的に移行を検討することで、Partition Pruning効果によるI/O及び応答時間の効率を最大化することができます。
6.1.1.2 Composite Partition戦略
Partition Tableの必要性を性能的な側面から理解した皆様は今、そのサイズをさらに分割して分割がどの程度までできるのか？についての余地について考えるようになることでしょう。
まさにこの考えを可能にする方法はありますが、すでに広く知られているComposite Partitioning の技法です。
例えば、すでに月単位のPartition Tableで運営しているテーブルに対する業務の性質と、Access Patternを調査してみると、常に曜日条件が追加で入力されることを確認したと仮定しましょう。
この場合、各月別のPartition Tableを曜日別のデータでPartitionを追加で重複適用してComposite Partition Tableを構成すると、最終的に所望のデータをAccessするPartition TableのSize自体が従来に比べて約1/7程度より減少する効果を見ることができるでしょう。
（ただし、曜日別にデータの分布度が一定であるという前提条件がある場合を想定した状況である）
以下のテストを見てみましょう。
テストテーブルとデータ生成スクリプト ：
[単一Partition構成 - Partition_Testテーブル生成スクリプトとデータ入力 ]単一のPartition構成
- 上記テストに使われたPARTITION_TESTテーブルスクリプト参照
[単一Partition - SQLの実行とXPLANの結果 ]
FROM_DATE = '1998-01-01'
TO_DATE = '1998-02-01'
DAY_NAME = 'Monday'
SELECT day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM partition_test
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
AND day_name = :day_name
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 |00:00:00.27 | 8860 | 8858 |
| 1 | HASH GROUP BY | | 4 |00:00:00.27 | 8860 | 8858 |
|* 2 | FILTER | | 40000 |00:00:00.26 | 8860 | 8858 |
| 3 | PARTITION RANGE ITERATOR| | 40000 |00:00:00.25 | 8860 | 8858 |
|* 4 | TABLE ACCESS FULL | PARTITION_TEST | 40000 |00:00:00.24 | 8860 | 8858 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
4 - filter(("DAY_NAME"=:DAY_NAME AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd')
AND "TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd')))
単一のPartition Tableで構成した場合、結果として8,860 BlockのI/Oが発生しました。
これは、Partition Keyとして活用されたTime_id条件でのみAccessした結果です。
条件が追加されたDay_nameカラムもPartition Keyで構成した場合、その性能差はどうなるでしょうか？
[ Composite Partition構成スクリプト(作成全文はAppendix参照) ]
CREATE TABLE composite_partition (
TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
...中略...
END_OF_FIS_YEAR DATE )
PARTITION BY RANGE (time_id)
SUBPARTITION BY LIST (day_name)
(
PARTITION composite_partition01 VALUES less than (to_date('1998-01-01','YYYY-MM-DD'))
(
SUBPARTITION partition01_monday VALUES ('Monday'),
SUBPARTITION partition01_tuesday VALUES ('Tuesday'),
SUBPARTITION partition01_wednesday VALUES ('Wednesday'),
SUBPARTITION partition01_thursday VALUES ('Thursday'),
SUBPARTITION partition01_friday VALUES ('Friday') ,
SUBPARTITION partition01_saturday VALUES ('Saturday') ,
SUBPARTITION partition01_sunday VALUES ('Sunday')
),
PARTITION composite_partition02 VALUES less than (to_date('1998-02-01','YYYY-MM-DD'))
(
SUBPARTITION partition02_monday VALUES ('Monday'),
SUBPARTITION partition02_tuesday VALUES ('Tuesday'),
SUBPARTITION partition02_wednesday VALUES ('Wednesday'),
SUBPARTITION partition02_thursday VALUES ('Thursday'),
SUBPARTITION partition02_friday VALUES ('Friday') ,
SUBPARTITION partition02_saturday VALUES ('Saturday') ,
SUBPARTITION partition02_sunday VALUES ('Sunday')
),
...中略...
PARTITION composite_partition72 VALUES less than (to_date('2003-12-01','YYYY-MM-DD'))
(
SUBPARTITION partition72_monday VALUES ('Monday'),
SUBPARTITION partition72_tuesday VALUES ('Tuesday'),
SUBPARTITION partition72_wednesday VALUES ('Wednesday'),
SUBPARTITION partition72_thursday VALUES ('Thursday'),
SUBPARTITION partition72_friday VALUES ('Friday') ,
SUBPARTITION partition72_saturday VALUES ('Saturday') ,
SUBPARTITION partition72_sunday VALUES ('Sunday')
)
);
既存のMain Partition KeyであるTime_idにSub Partition KeyでDay_nameを追加したRange-List Partition Tableを生成したスクリプトです。今、Range_List Partition Tableで構成したComposite_partitionテーブルを対象に同じSQLを実行した場合、どんな結果が出るのか？について見てみましょう。
[Composite Partition - SQLの実行とXPLANの結果 ]。
FROM_DATE = '1998-01-01'
TO_DATE = '1998-02-01'
DAY_NAME = 'Monday'
SELECT day_number_in_month 、
day_number_in_week 、
day_name 、
COUNT( * )
FROM composite_partition
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
AND day_name = :day_name
GROUP BY day_number_in_month 、
day_number_in_week 、
day_name；
---------------------------------------------------------------------------------------------------
| ID｜操作｜名前｜A-Rows｜A-Time｜バッファ｜読み取り｜読み取り｜読み出し
---------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 4 |00:00:00:00.07 | 1149 | 1143 | 1149 | 1143
| 1 | HASH GROUP BY | | 4 |00:00:00:00.07 | 1149 | 1143 | 1149 | 1143
|* 2 | FILTER | | 40000 |00:00:00:00.06 | 1149 | 1143 | 1143 |
3 | PARTITION RANGE ITERATOR| | 40000 |00:00:00:00.05 | 1149 | 1143 | 1143 |
| 4 | PARTITION LIST SINGLE | | 40000 | 00:00:00:00.04 | 1149 | 1143 | 1143
|* 5 | TABLE ACCESS FULL | COMPOSITE_PARTITION | 40000 |00:00:00:00.04 | 1149 | 1143 | 1149 | 1143
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
5 - filter(("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd'))))
結論から言うと、単一Partition Tableを照会する場合に比べてComposite Partition Table（Range – List）で構成した場合、約1/7程度のI/Oが減少することが分かりました。つまり、従来までは1つの条件だけでAccessしていたものを、2つの条件でAccessしたことで必要なPartition Tableだけを読み込むようになり、性能改善効果が得られたのです。
Oracle 11gバージョンを基準として Composite Partition はさらに多様化しました。
これらの性質を把握すると同時に、Access Pattern分析を実行して適用可能なテーブルがあれば、単一PartitionよりComposite Partition構成を積極的に検討する価値が大いにあります。
6.1.1.3 Partition Splitを活用したSizeストック
積極的Partitioningに関連するもう一つの焦点は、すでにPartitioningされたテーブルがどれだけよく管理されているかです。Partition Tableには、Default Partitionを指定する場合が最も一般的です。
これは、入力しようとするデータが現在分割されているどのPartition Tableにも属さない場合、Default Partition Tableを指定すると、そのデータはDefault Partition Tableに入力されます。
つまり、データ入力時に発生するエラーを最小化するためにDefault Partition Tableを指定するということです。
しかし、Default Partition Tableを指定した状況下で、そのPartition Tableに対する細心の管理が行われない場合には、Default Partition Tableが他のPartition Tableに比べて飛躍的にSizeが増加し、パフォーマンス問題につながる場合もあります。
この状況を例に挙げて説明していきます。
Default Partition Tableの管理が必要なケース ：
[Partition Table生成スクリプト］
CREATE TABLE partition_test3 (TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
...中略...
END_OF_FIS_YEAR DATE )
PARTITION BY RANGE( time_id )(
PARTITION partition_test01 VALUES less than (to_date('1998-01-01','YYYY-MM-DD')),
PARTITION partition_test02 VALUES less than (to_date('1998-02-01','YYYY-MM-DD')),
...中略...
PARTITION partition_test25 VALUES less than (to_date('2000-01-01','YYYY-MM-DD')),
PARTITION partition_test_default VALUES less THAN (maxvalue)
);
[データ入力］
INSERT /*+ append */ INTO partition_test3
SELECT a.*
FROM sh.times a ,
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10000
) b;
COMMIT;
必要なPartition Tableを生成した上でデータ入力をしてみました。
今、各Partition別にデータがどれくらい保存されたのか？についてSizeを照会してみることにしましょう。
[Partition Table別Size比較 ]
SELECT partition_name ,
blocks ,
bytes/1024/1024 mb
FROM dba_segments
WHERE segment_name = UPPER( 'partition_test3' );
PARTITION_NAME BLOCKS MB
---------------------- ------- ------
PARTITION_TEST_DEFAULT 316928 2476
PARTITION_TEST25 9600 75
PARTITION_TEST24 9600 75
PARTITION_TEST23 9984 78
PARTITION_TEST22 9728 76
PARTITION_TEST21 9984 78
PARTITION_TEST20 9472 74
PARTITION_TEST19 9088 71
PARTITION_TEST18 9472 74
PARTITION_TEST17 9088 71
PARTITION_TEST16 9984 78
PARTITION_TEST15 8960 70
PARTITION_TEST14 9984 78
PARTITION_TEST13 9600 75
PARTITION_TEST12 8960 70
PARTITION_TEST11 9984 78
PARTITION_TEST10 8960 70
PARTITION_TEST09 9984 78
PARTITION_TEST08 9216 72
PARTITION_TEST07 9088 71
PARTITION_TEST06 9216 72
PARTITION_TEST05 9088 71
PARTITION_TEST04 9984 78
PARTITION_TEST03 8576 67
PARTITION_TEST02 9984 78
PARTITION_TEST3テーブルは、月単位のRange Partitionで1998年1月から2001年1月までのPartition Tableを構成しており、これ以降のデータはMaxvalueで指定されてDefault Partition Tableに入力されるように構成されています。
データ入力後、Partition別Sizeを比較してみると、他のPartition Tableと比較してDefault Partition TableのSizeが圧倒的に大きいことを確認することができます。つまりこの結果から、2001年1月以降については当該テーブルに対するPartitioningが行われなかったことを意味しており、時間が経つにつれて、Default Partition Tableのサイズはさらに増加したことがわかりました。
これにより、2001年1月以降のデータを照会する業務では、約1ヶ月の期間に該当するデータだけを照会した場合においても、約2.4GBに相当する量のI/Oが必然的に伴うのです。
このような状況を招くのは、そのほとんどの場合において、管理の怠慢によるDefault Partition Tableのサイズが増加が主要因となりますが、Partition Split技法を活用することで、サイズが飛躍的に大きくなったDefault Partition Tableを再び簡単に月単位のPartition Tableに分割することができます。
Partition Split実行と結果確認 ：
[Partition Splitの実行]
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-02-01','YYYY-MM-DD') ) INTO (PARTITION partition_test26, PARTITION partition_test_default);
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-03-01','YYYY-MM-DD') ) INTO (PARTITION partition_test27, PARTITION partition_test_default);
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-04-01','YYYY-MM-DD') ) INTO (PARTITION partition_test28, PARTITION partition_test_default);
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-05-01','YYYY-MM-DD') ) INTO (PARTITION partition_test29, PARTITION partition_test_default);
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-06-01','YYYY-MM-DD') ) INTO (PARTITION partition_test30, PARTITION partition_test_default);
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2000-07-01','YYYY-MM-DD') ) INTO (PARTITION partition_test31, PARTITION partition_test_default);
...中略...
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default AT ( to_date('2003-12-01','YYYY-MM-DD') ) INTO (PARTITION partition_test72, PARTITION partition_test_default);
[サイズ変更確認］
PARTITION_NAME BLOCKS BYTES/1024/1024
------------------- ------- ---------------
PARTITION_TEST72 10240 80
PARTITION_TEST71 9216 72
PARTITION_TEST70 9216 72
...中略...
PARTITION_TEST39 9216 72
PARTITION_TEST38 9216 72
PARTITION_TEST37 9216 72
PARTITION_TEST36 9216 72
PARTITION_TEST35 9216 72
PARTITION_TEST34 9216 72
PARTITION_TEST33 9216 72
PARTITION_TEST32 9216 72
PARTITION_TEST31 9216 72
PARTITION_TEST30 9216 72
PARTITION_TEST29 9216 72
PARTITION_TEST28 9216 72
PARTITION_TEST27 9216 72
PARTITION_TEST26 9216 72
上記の結果を見てみましょう。
Split作業を通じて、異常なDefault Partition TableのSize増加による非効率が解消されたことを確認することができます。参考までに、下記のようにOracle 12C New Featureでは複数のPartitionに対するSplit作業を一度に実行できるようにしました。
[参考 Oracle 12C New Feature ]
ALTER TABLE partition_test3 SPLIT PARTITION partition_test_default INTO
(PARTITION PARTITION_TEST26 VALUES LESS THAN TO_DATE('2000-02-01','YYYY-MM-DD') ),
(PARTITION PARTITION_TEST27 VALUES LESS THAN TO_DATE('2000-03-01','YYYY-MM-DD') ),
...中略
(PARTITION PARTITION_TEST72 VALUES LESS THAN TO_DATE('2003-12-01','YYYY-MM-DD') ),
(PARTITION PARTITION_TEST_DEFAULT );
Partition Tableをあらかじめ十分に生成しておいたり、Procedureなどを通じたPartition Tableの自動管理技法などを活用することで、このような非効率の発生を未然に防ぐことができます。
6.1.2 パーティションの切り替えができない場合
大量のデータを処理するSQLの性能改善戦略の中で最初に挙げたのは、積極的なPartitioningです。
しかし、すべてのテーブルに対して適用できる事項ではありません。
特定のテーブルにアクセスするSQLのAccess Patternを調査した結果、常に共通的に入力される条件が存在しないか、現実的にPartition作業を遂行することが難しい業務環境である場合もあるだろうという理由からです。
この場合、広い範囲処理によるIndex ScanあるいはTable Full Scanでの非効率が発生することは自明なのです。
この時、テーブルのPartition切り替えをしなくても同じ性能効果を出すことができる案がGlobal Partitioned Indexの存在です。
図［6-2］左の図にある Index は､一般テーブルに生成された一般的なIndex構造です。
そして、真ん中の図が、Partition Tableの Local Index と Global Index を図式化したもの、右の図では、Global Partitioned Indexが図式化されています。
Global Partitioned Index は、図にあるようにテーブルはPartition Tableではなく、一般テーブルです。
そして、Indexは Partition Table の Local Index と構造的に同じであることが特徴です。
これは、テーブルの Partition転換 が不可能な場合、IndexだけをPartitionの概念を適用して生成すると、特定の業務に限っては、あたかもPartitioningを通じた性能改善効果が見られるように適用することができます。
以下のテストで詳しく調べてみましょう。
Global Partitioned Indexの性能改善効果テスト ：
[ テーブル生成スクリプト ]
CREATE TABLE global_partition_idx_test (
TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
DAY_NUMBER_IN_WEEK NUMBER(1,0) ,
...中略...
END_OF_CAL_YEAR DATE ,
END_OF_FIS_YEAR DATE );
[データ入力 ]
INSERT /*+ append */
INTO global_partition_idx_test
SELECT a.*
FROM sh.times a ,
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10000
) b;
COMMIT;
下記のSQL実行結果を確認してみると、約51万BlockをI/Oし、8秒以上かかった結果が出ました。
また、全体の31万件を抽出した後に、Groupingを通じて31件が最終的に抽出されました。
[一般テーブル - SQLの実行とXPLANの結果 ]
FROM_DATE = '2000-01-01'
TO_DATE = '2000-02-01'
SELECT day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM global_partition_idx_test a
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 |00:00:08.39 | 518K| 518K|
| 1 | HASH GROUP BY | | 31 |00:00:08.39 | 518K| 518K|
|* 2 | FILTER | | 310K|00:00:08.30 | 518K| 518K|
|* 3 | TABLE ACCESS FULL| GLOBAL_PARTITION_IDX_TEST | 310K|00:00:08.24 | 518K| 518K|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
3 - filter(("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd')))
このテーブルにGlobal Partitioned Indexを生成して同じSQLを実行してみましょう。
[Global Partitioned Index生成スクリプト］
CREATE INDEX global_part_idx01 ON global_partition_idx_test
(time_id, day_number_in_month ,day_number_in_week ,day_name )
GLOBAL PARTITION BY RANGE(time_id)
(
PARTITION global_part_idx_test001 VALUES less than (to_date('1998-01-01','YYYY-MM-DD')),
PARTITION global_part_idx_test002 VALUES less than (to_date('1998-02-01','YYYY-MM-DD')),
PARTITION global_part_idx_test003 VALUES less than (to_date('1998-03-01','YYYY-MM-DD')),
PARTITION global_part_idx_test004 VALUES less than (to_date('1998-04-01','YYYY-MM-DD')),
PARTITION global_part_idx_test005 VALUES less than (to_date('1998-05-01','YYYY-MM-DD')),
PARTITION global_part_idx_test006 VALUES less than (to_date('1998-06-01','YYYY-MM-DD')),
...중략...
PARTITION global_part_idx_test0066 VALUES less than (to_date('2003-06-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0067 VALUES less than (to_date('2003-07-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0068 VALUES less than (to_date('2003-08-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0069 VALUES less than (to_date('2003-09-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0070 VALUES less than (to_date('2003-10-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0071 VALUES less than (to_date('2003-11-01','YYYY-MM-DD')),
PARTITION global_part_idx_test0072 VALUES less than (to_date('2003-12-01','YYYY-MM-DD')),
PARTITION global_part_idx_max VALUES less than (maxvalue)
);
[Global Partitioned Index - SQLの実行とXPLANの結果 ]
FROM_DATE = '2000-01-01'
TO_DATE = '2000-02-01'
SELECT /*+ index_ffs(a) */
day_number_in_month ,
day_number_in_week ,
day_name ,
COUNT( * )
FROM global_partition_idx_test a
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id < TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY day_number_in_month ,
day_number_in_week ,
day_name
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 |00:00:00.33 | 1433 | 1420 |
| 1 | HASH GROUP BY | | 31 |00:00:00.33 | 1433 | 1420 |
|* 2 | FILTER | | 310K|00:00:00.27 | 1433 | 1420 |
| 3 | PARTITION RANGE ITERATOR| | 310K|00:00:00.20 | 1433 | 1420 |
|* 4 | INDEX FAST FULL SCAN | GLOBAL_PART_IDX01 | 310K|00:00:00.14 | 1433 | 1420 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
4 - access("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<TO_DATE(:TO_DATE,'yyyy-mm-dd'))
Global Partitioned Indexを生成する以前では、Index Scanが可能なIndexも存在せず、Indexが存在しても31万件のTable Random Accessを避けることができないので、非効率が発生する可能性が高くなります。
そのため、Table Full Scanを実行するのですが、これもTable全体をScanしなければならない負担があります。
この時、Global Partitioned Indexを生成すると、入力されるTIME_ID条件に該当するPartition IndexセグメントのみAccessするため、まるでPartition Tableに切り替えたのと同じ効果を見ることができます。
また、Global Partitioned Index生成スクリプトで確認できるように、SQLに必要なカラムだけを記述したので、I/O対象セグメントのサイズがPartition Tableに切り替えたよりも小さい量をScanすることになります。
このように、Global Partitioned Indexの生成を適用することができれば、特定の業務ではPartition Tableに切り替えた場合よりも、より良い性能改善効果を見ることができるでしょう。
ただし、この点にフォーカスして言うと、Global Partitioned Indexの限界も明らかになってきます。
つまり、Global Partitioned Indexを生成して効率を見ることができる業務は限られているということです。
ここで、上のSQLを例にして考えてみましょう。
業務が変更されて抽出されるカラムが変わる場合はどうなるでしょうか？
Where節のTIME_ID条件が削除されなければ、Global Partitioned Indexをそのまま使うことができます。
しかし、Index Scanの方法は、従来のようにIndex Fast Full Scanで実行することはできなくなります。
Note. Index Fast Full Scanの動作方式は、Table Random Accessが必要な場合は、当該Scan方式を使用できないという特徴があります。したがって、Index Fast Full Scan方式からIndex Range Scan方式に変更して実行すると同時に、31万件に相当する件数分だけGLOBAL_PARTITON_IDX_TESTテーブルをTable Random Accessするオペレーションが必然的に追加されることになります。こうなると、Global Partitioned Indexを生成して性能改善を図った本来の趣旨は無意味になってしまいます。作成意図を振り返ってみると、必要なデータだけの該当IndexセグメントだけをAccessするためだったためです。結局、SQLの変更が発生すると、変更されるカラムでGlobal Partitioned Indexを再作成しなければ、本来の趣旨に沿った改善効果を見ることができます。しかし、業務が変更されるたびにGlobal Partitioned Indexを再作成することは現実的に不可能であるため、結局、Global Partitioned Indexを適用できる業務環境がかなり制約的であるという欠点があります。
Global Partitioned Indexを適用する場合、Partition Tableへの移行が難しい場合において、特定の業務に限定して適用することができる改善策の1つに過ぎないと言う事を理解した上で適用を検討する必要があります。
6.1.3 Partition Tableの切り替え方法
先に紹介した方法の適用することが可能であるならば、大量のデータを扱う作業に対する性能改善 Processを取ることができるでしょう。しかしその一方で、もう一つの克服すべき難関がまだ待っています。
その克服すべき難関とは、Partition Tableへの変更が必要な部分に該当するものであり、膨大な量のデータを保存している既存のテーブルをPartition Tableに変更する作業が非常に大変であると言うことです。
一定時間Offlineが可能であれば、変更作業を試みることもできますが、常にサービス状態でなければならない環境において、Partition Tableを変更しなければならないとするならば、性能的な面においては、選択肢の１つとして正しいと言えるかもしれないものの、実務を担当するDBAにとっては、大きな戸惑いを与える結果を生む可能性があります。 しかし、この難解な問題も克服できる方法があります。
その方法はPartition Exchange（以下PE）機能です。
PE機能は文字通り、一般テーブルをPartition Tableに変更できることを意味します。
また、この作業による追加スペースも不要で、とても効率的な方法でPartition Tableへの転換を実現することができます。
以下のテストでは、PE機能を活用して一般テーブルをPartition Tableへ簡単に変換できることを証明しています。
[テーブル生成DDL (生成全文はAppendix参照) ]
CREATE TABLE partition_test2 (
TIME_ID DATE,
DAY_NAME VARCHAR2(9),
...中略...
END_OF_CAL_YEAR DATE,
END_OF_FIS_YEAR DATE );
[ Partition Tableに移行するPartition Tableを作成(作成全文はAppendixを参照) ]
CREATE TABLE partition_ex_test (
TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
...中略...
END_OF_FIS_YEAR DATE )
PARTITION BY RANGE( time_id )(
-- Partiton Table로 전환될 테이블의 Max 값을 입력
PARTITION part1 VALUES less than (to_date('2002-12-31','YYYY-MM-DD'))
);
[Partition Exchangeの実行 ]
SQL> ALTER TABLE partition_ex_test exchange partition part1 WITH TABLE partition_test2 without validation ;
[既存のテーブルを削除した後、Rename］
SQL> DROP TABLE partition_test2;
SQL> RENAME partition_test to partition_test2;
上記のテスト結果から、既存のテーブルを簡単にPartition Tableに変更できることが分かります。
ただ一点だけ注意するとすれば、既存の一般テーブルで持っていた制約条件（Primary Key）やIndex構成は、その全てを同じように作成しなければ、正常なPEを実行することができないので注意してください。
Partitioning技法は、大量データを扱う業務における性能改善のための前提条件です。
Partitioningが不可能な状況での性能改善には、様々な制約事項が存在するため、容易ではありません。
業務への十分な理解と、Access Pattern調査などの実行結果を踏まえて、性能改善が可能と判断した場合においてPartition Tableへの転換が必要となってくるのです。
SQLチューニングブログ 2nd Season（第28回）終
次回のSQLチューニングブログは・・・
SQLチューニングブログ 2nd Season（第29回）
第6章「大量のデータ変更作業の性能改善戦略」
※ 次回は全5回シリーズの2回目をお送りしていきます。
私たちは 日本のITインフラ における
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
日本エクセムのデータベースソリューション
最新情報は更新SNSでも配信中
SQLチューニングブログについてのお問い合わせは
