津島博士のパフォーマンス講座 
第66回 パーティションのまとめとして

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年のゴールデンウィークは夏のような暑い日が多かったですが、リフレッシュはできましたでしょうか。
今回は、パーティション化を使用していないことで、とても苦労しているような方に、是非とも再検討して欲しいと思い、パーティション化の良さと問題点についてまとめてみました。後半に、第22回の続きとしてパーティションの決め方の補足についても説明していますので、参考にしてください。

1. パーティションの良さと問題点
データベースのパフォーマンスは、索引を使用するのが一般的ですが、すべてを索引で解決できると勘違いされている方がいます。索引だけでは、データ量が増えてくると、様々な処理でパフォーマンス問題になってしまうことがあり、運用で苦労しているシステムも多いです。そのようなシステムのために、以下のようなパーティション化の代表的な良さと問題点について説明します。

  • 大規模表のデータ・アクセス
  • データ・メンテナンス
  • アクセス頻度が異なるデータ
  • コストの問題

(1)大規模表のデータ・アクセス
大規模表は、何を行うにも時間が掛かるので、作ることは避けたいのですが、一般的なシステムでは少数の表にデータが集中することが多く、避けるのは難しいように思います。そのような表に対して、バッチ処理などで多くの行をアクセスすると、以下のようなパフォーマンスの問題になってしまい、メモリを増やすなどでは改善しなくなります。

  • 索引スキャンでは、アクセスするデータ量に比例して遅くなります。
  • フル・スキャンでは、すべての行に対してフィルタ処理を行うので、CPUオーバーヘッドが高くなり、サイズが大きいとダイレクト・パス・リードによるストレージ性能の影響も大きくなります。

このとき問題になるのは、データ量が増えて遅くなってきた場合に、簡単に改善できないということです(大規模表の分割などを行いますが、アプリケーションに影響するため簡単には行えません)。そのため、バッチ処理の時間が長くなって、苦労されているシステムを何度か見かけることがあります。そのようなことからも、アプリケーションに透過的なパーティション化を検討して欲しいと思います(つまり、大規模DBでも表サイズが大きくなければ問題にならないということです)。

(2)データ・メンテナンス
大規模表だと、データ・メンテナンスも大変になります。例えば、大量のデータをロードするときに、表ロックを嫌がりダイレクト・パス・ロードを行わない場合です。これもデータ量が増えると問題になるので、DDL(パーティション・メンテナンス)の使用をお勧めします。具体的には、第33回で説明したEXCHANGE PARTITIONを使用することで、表ロックで他のユーザーがアクセスできないことをなくします(これでオプティマイザ統計の収集時間も短縮できます)。また、以下のようなデータ・ロード以外にも使用できるので、参考のために載せておきます。

  • データ削除
    パーティション単位には、DROP PARTITIONやTRUNCATE PARTITIONでも可能ですが、第43回で説明したようにミニ・チェックポイントが発生するので、その時間が問題になる場合、以下のように空表とEXCHANGE PARTITIONを行います(空表は、第65回の「(4)交換用の表作成」で説明した表も使用できます)。
    -- 空表を作成
    SQL> CREATE TABLE <空表名> AS SELECT * FROM <表名> WHERE 1=2 ;
    -- パーティション交換
    SQL> ALTER TABLE <表名> EXCHANGE PARTITION <パーティション名> WITH TABLE <空表名> ;
    -- 空のパーティションを削除(TRUNCATE PARTITIONのときは不要)
    SQL> ALTER TABLE <表名> DROP PARTITION <パーティション名> ;
    
  • パーティションの部分削除
    パーティションの一部分を削除する場合は、以下のような部分削除も行うことができます(これとパーティション・メンテナンスを同時に行うのが、第65回の「(2)フィルタ付きパーティション・メンテナンス操作」です)。また、第15回の「3.REDOログについて」で説明したように行うと、UPDATEなどでも使用することが可能になります。
    -- パーティションに対するロック
    SQL> LOCK TABLE <表名> PARTITION (<パーティション名>) IN EXCLUSIVE MODE ;
    -- 削除しないデータの表を作成
    SQL> CREATE TABLE <残り表名> AS SELECT * FROM <表名> PARTITION ( … ) WHERE <削除しないデータの条件> ;
    -- パーティション交換
    SQL> ALTER TABLE <表名> EXCHANGE PARTITION <パーティション名> WITH TABLE <残り表名> ;
    

このようにEXCHANGE PARTITIONは、使い方を工夫すると様々な使用方法ができる便利な機能です。

(3)アクセス頻度が異なるデータ
表には、アクセス頻度が異なるようなデータがあります。例えば、最新のデータだけが頻繁に更新されて、その他は殆ど更新されないような場合です。更新処理は、索引や圧縮に対してオーバーヘッドが発生するので、索引をあまり多く作成できない、圧縮してデータを削減できないことになります。どちらもパフォーマンスに大きく影響する機能なので、有効な使い方とは言えません。そこで、これもパーティション化を使用して、更新の多い最新のパーティションは必要最小限の索引(その他はUNUSABLE)や未圧縮にして、それ以外のパーティションだけに必要な索引や圧縮を指定するようにします。特に速くしたいデータは、DBIM(Database In-Memory)を使用するなどの使い方も可能になる訳です(第53回で説明した表拡張で、それぞれのパーティションに効果的なアクセスができます)。このように、データごとにいろいろなアクセスができるのも、パーティション化の良さです。

(4)コストの問題
パーティション化は、オプション機能であるのも使用しない原因だと思います。できるだけ安価にシステムを作成したいと思うのも事実ですから、索引だけで頑張っている方も多いのではないでしょうか。ただし、使用せずに同じことを行うには、大変な作業になってしまうので、以下のようにトータル・コストとして考える必要があることも忘れないでください(このようなことを知らない方が、未使用を決めることも多いと思うので、そのような方の説得にも活用してください)。
パーティション表の代わりに、表を分割してプログラムで解決するなどを行いますが、逆にプログラム開発のコストが増えることになり、コスト効果は少ないように思います(遅くなる度に、プログラムの変更が必要になるので、逆にコストが増えることもあります)。コストという意味では、「(3)アクセス頻度が異なるデータ」のように、圧縮による格納サイズの削減やアクセスが少ないパーティションを安価なストレージに格納するなどで、ストレージのコストを削減するような使い方も可能です。どうしてもコスト的に無理な場合には、クラウドの使用を検討してみるのも良い方法だと思うので、使用を再検討してみてください。

2. パーティションの決め方の補足

ここでは、第22回のパーティションの決め方で説明していなかった内容を補足します。
パーティションを決めるには、パーティション数や主キーの決め方を難しいと感じるときがあります。また、パーティション・キーを一つの列に絞れない場合には、複数列パーティションやゾーン・マップの併用も有効なときがあるので、そのような効果的な使い方も含めて説明します(第22回の「パーティションの決め方」も少し修正しましたので、そちらも参照してください)。

(1)パーティション数
まずは、パーティション数の決め方について説明します。
パーティション数は、多くなるとディクショナリのオーバーヘッド(SGA内を増加させる)が発生するので、バランスを考えて決める必要があります。そのため、以下のことを意識してパーティション・サイズから決めていきます。判断できない場合は、10Gバイト未満のパーティション・サイズを目安に検討するのが良いと思います。

  • アクセス・パターン
    まずは、アクセスが多いパターンから考えます。例えば、日付単位にアクセスするのが多いのであれば、日付ごとのレンジ・パーティションを基本に考えます。
  • 性能要件
    次に、フル・スキャンに対する性能要件から平均パーティション・サイズを決めて、表サイズからパーティション数を求めます。例えば、日付単位にアクセスするのが多いが、性能要件が日付単位の必要がないのであれば、月単位などでも良いことになります(フル・スキャンの性能が5GB/秒の場合、月当たりのサイズが30Gバイトだとすると、フル・スキャンの時間は6秒となります)。

(2)パーティション表の主キー
次に、これも重要なパーティション表に対する主キーについて説明します。
主キーまたは一意索引は、第10回の「(4)主キーの作成について」で説明したように、ローカル索引にするにはパーティション・キーを含んでいる必要があります。そのため、パーティション・キーと異なるような場合には、どのようにするか悩んでしまいます。例えば、以下のように、売上表を注文日でレンジ・パーティションしたとします。しかし、主キーは注文番号になるため、ローカル索引で作成することができないという場合です。

SQL> CREATE TABLE "売上" ( … ,
  2    PRIMARY KEY ("注文番号"))
  3  PARTITION BY RANGE ("注文日")
  4  (PARTITION p01 … ) ;

毎日データが増えるような売上表などは、メンテナンス(データの削除など)を考えると、注文日でレンジ・パーティションを行う方が効果的なので、このようなことは良くあることだと思います。このような場合には、以下の二つからシステムの特性によって決定してください。

  • 注文番号でグローバル索引を使用する(索引もパーティション化する必要があれば注文番号で行う)
  • 複数列(注文番号、注文日)でローカル索引を使用する(注文番号だけでは一意性を保証できないが、他のシステムで一意性が保証されていて、そこからデータを移行するようなシステムだと問題はない)

索引メンテナスのことを考えると、ローカル索引にしたいところですが、一意性を保証することができなくなります。そのため、OLTPシステムなどでは、グローバル索引を使用する必要になるかと思います(主キーの定義は、以下のようにUSING INDEX句でローカル索引を指定しないと、内部的にグローバル索引が作成されるので注意してください)。

SQL> CREATE UNIQUE INDEX "売上_pk" ON "売上" ("注文番号"、"注文日") LOCAL;
SQL> ALTER TABLE "売上" ADD CONSTRAINT "売上_pk" PRIMARY KEY ("注文番号"、"注文日") USING INDEX "売上_pk";

グローバル索引の問題は、パーティション・メンテナンス時の索引メンテナンスになりますが、第43回で説明したようにだいぶ改善されているので、そんなに気にする必要もないかと思います。

(3)複数列パーティション
次に、それぞれの複数列パーティションの動作について説明します。
一つの列に絞れない場合には、複数列パーティションが効果的な場合がありますが、すべてが同じように動作する訳ではありません。そのため、それぞれの複数列パーティションに対して、指定された列によってどのように動作するかをまとめてみました。
以下は、列(c1,c2)でパーティション化した場合に、それぞれの検索条件の動作をまとめたものになります。これを見ると、レンジ・パーティションとリスト・パーティションは、それぞれの列単独だけでもパーティション・プルーニングの効果を得ることができますが、ハッシュ・パーティションは、すべての列を指定しないとパーティション・プルーニングされないので注意してください(ハッシュ・パーティションは、競合回避や負荷分散で使用するのが主な目的になります)。

パーティション方法 検索条件 対象パーティション
複数列レンジ・
パーティション
WHERE c1=10 c1のパーティション境界が10より大きい最小パーティション
WHERE c1=10 AND c2=20 c1のパーティション境界が10より大きいか、10と等しくC2の境界が20より大きい最小パーティション
WHERE c2=20 c2=20が含まれるパーティションが対象になる(動的パーティション・プルーニング'KEY(MC)'が行われる)
複数列リスト・
パーティション
WHERE c1=10 c1=10が含まれるパーティションが対象になる(動的パーティション・プルーニング'KEY'が行われる)
WHERE c1=10 AND c2=20 c1=10とc2=20が含まれるパーティションが対象になる
WHERE c2=20 c2=20が含まれるパーティションが対象になる(動的パーティション・プルーニング'KEY(MC)'が行われる)
複数列ハッシュ・
パーティション
WHERE c1=10 AND c2=20 c1=10とc2=20が含まれるパーティションが対象になる
WHERE c1=10 一つの列だけの指定ではパーティション・プルーニングされない
WHERE c2=20


(4)ゾーン・マップとの併用
最後に、ゾーン・パップと併用して、効果的にパーティション・プルーニングする方法について説明します。
関連性のある列が存在するような場合にも、パーティション・キーを決めるのを悩みます。例えば、ほとんどが注文日から1週間以内に出荷する、注文日と出荷日の関係などです。どちらの列もアクセス条件で指定する場合に、両方の列をパーティション・キーにするのは勿体ない気がします。そこで、Oracle Database 12cからのゾーン・マップを使用します(ただし、Oracle Engineered Systemだけしか使用できません)。
以下のように、注文日でレンジ・パーティションを行い、出荷日でゾーン・マップを作成すると、出荷日の条件で検索するときに、ゾーン・マップでパーティション・プルーニングされるようになります。このとき、以下のような動的パーティション・プルーニングの実行計画になります(PstartとPstop列にKEY(ZM)が出力されます)。

SQL> CREATE TABLE "売上" ( ... )
  2  PARTITION BY RANGE ("注文日")
  3  (PARTITION sal999q1 VALUES LESS THAN (TO_DATE('1999/04/01','YYYY/MM/DD')),
  4   PARTITION sal999q2 VALUES LESS THAN (TO_DATE('1999/07/01','YYYY/MM/DD')),
  5   PARTITION sal999q3 VALUES LESS THAN (TO_DATE('1999/10/01','YYYY/MM/DD')),
  6   PARTITION sal999q4 VALUES LESS THAN (TO_DATE('2000/01/01','YYYY/MM/DD')));
SQL> CREATE MATERIALIZED ZONEMAP "売上ZMAP" ON "売上" ("出荷日");

SQL> SELECT … WHERE "出荷日" = TO_DATE('1999/04/01','YYYY/MM/DD');
------------------------------------------------------------------
| Id  | Operation                       | Nam  | | Pstart| Pstop |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      | |       |       |
|   1 |  PARTITION RANGE ITERATOR       |      | |KEY(ZM)|KEY(ZM)|
|*  2 |   TABLE ACCESS FULL WITH ZONEMAP| 売上 | |KEY(ZM)|KEY(ZM)|

ただし、更新したときには、そのゾーンは失効状態となり、リフレッシュ・モードによってリフレッシュされるタイミングが異なります(デフォルトは、ロードとデータ移行後に行う'ON LOAD DATA MOVEMENT'です)。

ゾーン・マップについて
ご存知ない方のために、ここでOracle Database 12cからのゾーン・マップについて簡単に説明します。
ゾーン・マップは、一定範囲のブロック(ゾーン)ごとに、指定した列の最小値と最大値を格納することで、列の述語に適合しないデータ・ブロックを、データベースがプルーニングできる機能です(つまり、ExadataやDBIMのストレージ索引と同じように使用できます)。1つの表につき、最大1つのゾーン・マップを定義でき、ゾーン・マップを作成する表の列以外にも、以下のような結合先の表の列が指定できます(この例は、表'売上'に対する表'顧客'の列'都道府県'と'市'を追跡するゾーン・マップです)。
SQL> CREATE MATERIALIZED ZONEMAP "売上ZMAP"
  2    AS SELECT SYS_OP_ZONE_ID(s.rowid), MIN("都道府県"), MAX("都道府県"), MIN("市"), MAX("市")
  3       FROM "売上" s LEFT OUTER JOIN "顧客" c ON s."顧客ID" = c."顧客ID"
  4       GROUP BY SYS_OP_ZONE_ID(s.rowid);
パーティション表の場合、パーティションごとに格納されるので、ゾーンごと、パーティション(またはサブパーティション)ごとの最小値と最大値を追跡するので、パーティション・プルーニングとしても使用することができます。第58回で説明した属性クラスタリングと使用することで、プルーニングの効果を高めることが可能になります。

3. おわりに
今回はパーティションのまとめとしていろいろ説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。