シバタツ流！ DWHチューニングの極意　
第1回　初期化パラメータと表領域作成

　はじめまして、この連載を担当するExadata技術部の柴田と申します。Oracle Exadataリリース当初から、お客様のSQLやデータを使用したPoC (Proof of Concept) を実施し続けています。本番稼働しているたくさんのシステムのパフォーマンス・チューニングを行ってきましたが、いつも思うことは「もっとシンプルでいいのに」ということです。Oracle Databaseにはたくさんのパラメータやらなんやらがありますが、OLTPでは効果があっても、データウェアハウスではほとんど効果がないどころか、逆に遅くなるだけということも。

　そこでこの連載では、データウェアハウスのパフォーマンス・チューニングに本当に効果があることだけにポイントを絞ってご紹介していきたいと思います。これらのチューニング手法はExadataに限ったものではなく、Oracle Databaseすべてにおいて使えるものですので、多くの方の参考になればと思います。

　第1回では、設定しようと思えばいくらでもできてしまう、初期化パラメータと表領域のチューニングをシンプルに行う方法をご紹介します。

■初期化パラメータ

　Oracle Database 11g Release 2 (11.2.0) の初期化パラメータはリファレンスに載っているだけでも260個もあります。これらをすべて細かく設定していくのは時間が掛かりますし、劇的に効果があるものもあればデータウェアハウスでは実はまったく性能に影響してないなんてものもあるかもしれません。私が行うPoCの期間は2週間程度のものが多いので、初期化パラメータひとつひとつを調整している時間はあまりないこともあって、以下のパラメータだけをデフォルトから変更しています。

・SGA_TARGET
　OLTPでは索引を作って、その索引やデータをSGAにキャッシュして......というチューニングが一般的ですが、大量のデータをIOしないといけないデータウェアハウスにはこの手法は向いていません。ストレージからデータを読んだらSGAにキャッシュせずにそのまま結果を返すダイレクト・パス処理を行うほうが高速です。つまり、ダイレクト・パス処理ではSGAは使用しないので、SGA_TARGETをやたらと大きくする必要はないということです。96GBのメモリーを搭載しているサーバーであれば16GB (17%) を設定すればよいでしょう。

・PGA_AGGREGATE_TARGET
　データウェアハウスではSGAを使わない一方で、ソートなどで一時領域を大量に使用します。そのため、できるだけ一時表領域に書き込まないでPGAだけで処理できるように大きめに設定します。96GBのメモリーを搭載しているサーバーであれば24GB (25%) を設定すればよいでしょう。SGA_TARGETと違って、PGA_AGGREGATE_TARGETは設定値を超えて使用される可能性があります。特にLinuxでは超える量が多いので、実際のPGAサイズはPGA_AGGREGATE_TARGETに設定した値の最大3倍くらいになるくらいのつもりで設定しましょう。実際のPGAサイズはAWRやV$PGASTAT表で分かりますので、想定以上に使われていないか監視しておくことを勧めます。

・PARALLEL_MIN_SERVERS
　誰も使っていないアイドル時間があるシステムの場合、パラレル実行で使われるpnnnプロセスの生成時間が無視できない場合があります。ここで指定した数のpnnnプロセスはアイドル時にも削除されずに常に維持されますので、プロセス生成時間が掛からなくなります。サーバー1台当たりのOSから見える論理CPU数×4程度を設定しておけばよいでしょう。たとえばHyper Threadingが有効なXeon 5670を2ソケット搭載しているサーバーであれば、2ソケット×6コア×2スレッド×4=96です。

・PARALLEL_MAX_SERVERS
　パラレル度の高いパラレル実行が同時に多数実行されると、メモリー不足やOSスケジューラのコンテキスト・スイッチがボトルネックになってくるので、PARALLEL_MIN_SERVERSの2.5倍を設定しておきましょう。

・PROCESSES
　PARALLEL_MAX_SERVERSをいくら大きくしてもパラレル度がPROCESSESの値を超えることはないので、PROCESSESにはPARALLEL_MAX_SERVERSより大きい値を設定しておきましょう。私はPARALLEL_MAX_SERVERSの2倍を設定しています。

・PARALLEL_THREAD_PER_CPU
　OSから見える論理CPU数×PARALLEL_THREAD_PER_CPU（RACの場合はさらに×ノード数）の値がパラレル実行時の自動パラレル度になるので、Hyper Threadingを使用している場合は1を設定しています。SPARC Tシリーズなどの場合はデフォルトのままで構いません。

・PARALLEL_EXECUTION_MESSAGE_SIZE
　11.2.0未満のPARALLEL_EXECUTION_MESSAGE_SIZEのデフォルト値は小さすぎるので、11.2.0未満を使用している場合は16384に設定しましょう。11.2.0のデフォルト値は16384になっています。

・DB_BLOCK_SIZE
　大量のIOが発生するデータウェアハウスではDB_BLOCK_SIZEは非常に重要そうですが、実際にはそれほど大きな影響はありませんので、デフォルト値の8192を使っています。ただし、列数が非常に多かったりして一行が2048バイト (25%) を超えるときに限って、16384などに大きくしています。

■表領域

　データウェアハウスではダイレクト・パス処理でストレージに書き込むことが多いため、表領域の属性についてはOLTPよりも少し意識する点があります。

　エクステント管理方法にはもちろんローカル管理を使用しますが、ローカル管理の場合、エクステントの大きさをシステム管理するかユーザー管理するかが選べます。システム管理ではセグメントの大きさなどによってエクステントの大きさが自動で決まり、たとえば1MB以内のセグメントであればエクステントは64KBになることが多いです。つまり、空の表にデータをロードする場合、最初のわずか1MBをロードするのに16個 (=1024KB÷64KB) もエクステントを作る必要があるということになります。これでは非常に効率が悪いので、まとめてデータをロードすることの多いデータウェアハウスでは4MBでユーザー指定することをお勧めします。

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M

　ASMを使用している場合は割当て単位 (AU) も4MBに設定しましょう。また、表領域の自動拡張時の増分サイズを割当て単位×ディスク本数にすると、ディスク間のサイズのバランスが取れます。たとえば12本のディスクを使用しているのであれば、4MB×12本=48MBです。

AUTOEXTEND ON NEXT 48MB

　パラレル・ロードするときのファイル・ヘッダー・ブロック競合を抑えるために、1つのbigfile表領域で容量は十分でも、大きなパーティション表には複数の表領域を割り当てることをお勧めします。最大128パラレルで実行される場合は、4個以上の表領域があるとよいでしょう。

CREATE TABLE largetab (
　id NUMBER(10),
　name VARCHAR2(50)
)
PARTITION BY HASH (id)
PARTITIONS 128
STORE IN (ts1, ts2, ts3, ts4)
;

　次回は「データ・ローディング」に関する極意を伝授します！

■シバタツより
Oracle Exadataリリース当初から、お客様のSQLやデータを使用したPoC (Proof of Concept) を実施し続け、本番稼働しているたくさんのシステムのパフォーマンス・チューニングを行ってきました。2010年には米オラクルの開発部門に所属し、米国のお客様のPoCを実施しつつ、そこから見えてきたOracle Databaseのパフォーマンス課題の解決に取り組みました。

日米どちらのPoCでも共通に、いつも思うことは「もっとシンプルでいいのに」ということです。Oracle Databaseにはたくさんのパラメーターやらなんやらがありますが、OLTPでは効果があっても、データウェアハウスではほとんど効果がないどころか、逆に遅くなるだけということも。そこでこの連載では、データウェアハウスのパフォーマンス・チューニングに本当に効果があることだけにポイントを絞ってご紹介していきたいと思います。これらのチューニング手法はExadataに限ったものではなく、Oracle Databaseすべてにおいて使えるものですので、多くの方の参考になればと思います。

日本オラクル株式会社 テクノロジー製品事業統括本部 技術本部 Exadata技術部
プリンシパルエンジニア　柴田竜典（しばたたつのり）