| Oracle | OLTP環境のOracleチューニング | |
| 2001/03/18 - 2002/01/11 |
●処理はサーバに集中(ストアドプロシージャの活用)
クライアントで処理させようとすると、
必要な全データがサーバとクライアントの間を往復することになり、
他の条件が同じでもそれだけで性能上非常に大きなハンデとなります。
ネットワーク上のデータのやりとりはできるだけ少なくなるようにアプリケーションを設計します。
ツールや開発言語の選択もこの点では非常に重要です。
●パッケージの活用
パッケージ化されたコードは、最初に使われたときにメモリにロードされ、
以後できるだけ常駐しようとするので、入出力の削減に効果があります。
●正規化を行う
正規化を行わないと…
●制約の活用
データの妥当性を保証するために、プログラムコードでチェックを行うより、
制約を設定する方が速度の向上に貢献します。
ただし、制約の策定にはビジネスルールの正しい理解と正しい正規設計が前提になってきます。
設備(ハードウェア)のチューニング
●ディスクI/Oの分散化
ディスクI/Oを分散させるため、
構成ファイルを次のようにディスク装置に分散させるとよいです。
●表領域の分離
断片化しやすいセグメントとそうでないセグメントを同一の表領域に混在させると、
アクセス速度が低下します。これを防ぐため、セグメントの種類別に次のような表領域を作成します。
●RAID化の検討
ランダムアクセスの多いディスクでは、ディスクI/Oがボトルネックとなりがちです。
このようなディスクドライブにはRAID0(ストライピング)の導入を検討します。
ただし、ディスクが2ドライブしかない場合は、ストライピングはしない方が賢明です。
ストライピングは3ドライブ以上ある場合から検討します。
●RAIDの種類
一般に、ソフトウェアRAIDよりハードウェアRAIDの方が高速です。
●サイズ見積もりの基礎知識
Oracleデータブロックの構造は、次の通りです。
●行サイズの計算方法
●ブロック領域使用率パラメータ
表領域単位では指定できません（セグメント単位で指定）。
PCTFREE = (平均行サイズ-初期行サイズ) * 100 / 平均行サイズ
PCTUSED = 100 - PCTFREE - (平均行サイズ*100) / (DB_BLOCK_SIZE-ヘッダAのサイズ)
●記憶領域パラメータ
表領域に対しデフォルト指定、セグメントに対しさらに指定可能です。
できるだけINITIALエクステントにすべてのデータが入るように計算します。
●索引の領域見積
B*Tree索引はデータ量が増えると比例して多くのディスク領域を消費します。
索引にはPCTUSEDは指定できません。
●CACHE句
全表走査の頻度が非常に高い、小さな表にはCACHE句をつけて
CREATEすることを検討します。
●索引のメンテナンス
B*Tree索引では、
定期的にANALYZEを行って索引の高さ(BLEVEL)の値を監視するようにします。
BLEVELが4を超えた場合には、索引をリビルドします。
●索引の有効性の検査
データディクショナリのINDEX_STATS表を見ることで、
再構築の必要性を諮ることができます。例えば、
DEL_LF_ROWSのLF_ROWSに対する比率が30%を越えたら、
再構築したほうがよいでしょう。
●フリーリスト競合の検出
フリーリスト競合が発生すると、アプリケーションの速度性能は低下します。
フリーリスト競合回数は、次のように求められます。
select class, count from v$waitstat where class = 'free list';これをデータ要求総回数と比べてみます。
select sum(value) from v$sysstat
where name in ('db block gets', 'consistent gets');
フリーリスト競合回数が1%を超えるようであれば、
STORAGE句にFREE LISTSオプションをつけて表を再構築すべきです。
●プロセスの数
同時に稼動できる最大プロセス数はパラメータPROCESSESで決まります。
同時接続ユーザー数に応じて、増やします。
デフォルトは50ですが、大規模システムでは不足するので、
80などに上げるのが普通です。
●ブロックサイズの選択
パラメータDB_BLOCK_SIZEで決まります。
デフォルト値はOSによって異なりますが、
Solarisでは2048がデフォルトです。１行のサイズが長い場合、
大きくするほど読み込みが高速になりますが、メモリの無駄が増えます。
OLTP系では、レコード長が極端に長いか短いかしない限り、
性能に影響することはあまりないです。現在では4Kか8Kが普通です。
●DBWRの数
同時に動作できるDBWRの数は、パラメータDB_WRITERSで指定できます。
多くのUNIXでは、CPUの数などハードウェア的なスペックにに関係なく、
この数を増やすことでデータファイルへの書きこみが著しく向上します。
ものの本では、データファイルの数の倍の値にすることを推奨しています。
●REDOログバッファのサイズ
LOG_BUFFERでバイト単位で指定します。
不足するとLGWRが頻繁に書き込みを行うので性能が低下します。
更新頻度が高い場合、最低64KBはとるべき。
●REDOログファイルのサイズ
一般的な見積方法として、
V$SYSSTATを見てそのアプリケーションで出されるREDOエントリのサイズを調査します。
次にALERT.LOGを見てログスイッチの間隔を調べ、
約１時間間隔になるようにサイズを決めるとよいと言われます。
また、
1日分のトランザクションが入る容量という基準を使うこともあります。
●LOG_CHECKPOINT_INTERVALとLOG_CHECKPOINT_TIMEOUT
チェックポイントの発生頻度の調整に使用。頻度が高いとインスタンス回復が
短時間で終わりますが、日頃の運用の性能はやや落ちます。
頻度が低い場合の利害はちょうど逆です。
●一時セグメントのサイズ
TEMPORARYで指定した専用一時表領域の場合は、
MAXEXTENTSは必ずUNLIMITEDとなります。
INITIAL=NEXT=(SORT_AREA_SIZEの整数倍)+DB_BLOCK_SIZEが最も望ましいといえます。 また、全てのエクステントが同じサイズが望ましいので、 INITIAL=NEXTかつ、PCTINCREASE=0とするのがよいです。
●ロールバックセグメントのチューニング
ロールバックセグメントは、OLTP環境ではサイズを小さめにし、
数を多くすることが効果的です。
目安は、4トランザクションにつき1セグメントです。
●ロールバックセグメントのパラメータとサイズ
ロールバックセグメントのパラメータは、
拡張頻度を押さえるため、MINEXTENTS=20程度を指定するとよいです。
また、全てのエクステントが同じサイズが望ましいので、必ず
INITIAL=NEXTとします。
MAXENTENTSをUNLIMITEDにしてはいけません。
ロールバックセグメントが無秩序に拡大するおそれがあります。
OPTIMALは、最初はデフォルトにしておき、
最も長いトランザクションを実行してロールバックセグメントのサイズをチェックして調整するようにします。
OPTIMALが小さすぎると、頻繁に拡縮が発生するので性能低下の原因になります。
SQL>set transaction use rollback segment {big_rbs}
を実行します。
●最適なDBバッファキャッシュサイズ
DB_BLOCK_SIZEとDB_BLOCK_BUFFERS
の積がデータベースバッファキャッシュの容量になります。
一般に大きくすればするほど性能は上がりますが、
キャッシュヒット率が100%に近くなったら、
それ以上上げても意味はあまりありません。
また大きくとりすぎてOSがスラッシングを発生させるようなら逆効果です。
普通2000ぐらいでスタートし、
テスト実行によるヒット率を見ながら調整します。
DBバッファキャッシュのヒット率は次のSQL文で算出できます。
select ROUND(100-sum(decode(name, 'physical reads', value, 0)) /
( sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0)) ) * 100, 2)
as "Buffer Cache Hit Ratio"
from v$sysstat
この値が90%に満たない場合は、DB_BLOCK_BUFFERSを増やします。
●ライブラリキャッシュのチューニング(キャッシュヒット率の調査)
ライブラリキャッシュのヒット率は、次のSQL文で算出できます。
select sum(pins) "Executions", sum(reloads) "Cache Misses", 100 - round(sum(reloads)/sum(pins), 2) "Ratio" from v$librarycacheRATIOの値が90%に満たない場合は、次の対策をとります。
●ディクショナリキャッシュのチューニング(キャッシュヒット率の調査)
データディクショナリキャッシュのヒット率は、次のSQL文で算出できます。
select sum(gets) "Gets", sum(getmisses) "Misses",
round(100-sum(getmisses)/sum(gets)*100, 2) "Ratio"
from v$rowcache
ヒット率が低いと、再帰SQLが多発するため性能が大きく低下するおそれがあります。
ヒット率が低い場合には、共有プールのサイズを増やす必要があります。
●共有プールの利用率
共有プールの空き容量を、次のSQL文で調べることができます。
select name, bytes from v$sgastat where name = 'free memory' and pool = 'shared pool'（POOLはOra8iで追加されたものなので、Ora8ではこの条件は不要です） これをinitSID.oraのSHARED_POOL_SIZEと比較します。 ピーク時の空き容量の比率が極端に大きい場合には、 共有プールのサイズを小さくします。
●アーカイブ処理のチューニング
ARCHがREDOログをアーカイブする間、LGWRがブロックされる時間をできるだけ短縮するために、
パラメータLOG_ARCHIVE_BUFFER_SIZEを大きくすることを検討します(最大128まで)。
また、LOG_ARCHIVE_BUFFERSを4より大きめに増やすと、
ARCHの性能が大きく向上します。
ただし、これにより他のバックグラウンドプロセスの負荷を圧迫するので、
バランスを調整する必要があります。
●ソート領域のサイズ
ソートがメモリ上で完了しているか、またはディスク(一時表領域)
を使っているかは次のSQL文で調べることができます。
select name, value from v$sysstat where name like '%sort%';「sorts (disk)」の値が大きい場合には、パラメータSORT_AREA_SIZE の値を増やしてメモリでソートを完了させやすくします。 これはソート処理の所要時間短縮にに劇的な効果がありますが、 セッションごとにこの値だけメモリをとっていくので、 同時接続数が大きいと一気にメモリを圧迫します。 バランスをとって値を決める必要があります。
●システム統計情報の取得(UTLBSTATとUTLESTAT)
●負荷の高いSQL文の発見(トレースとTKPROF)
alter session set sql_trace=true; alter session set timed_statistics=true;
SHELL% tkprof トレースファイル.trc result.txtresult.txtに結果が出てきます。
●FROM句の書き方
FROM句では、データの多い表から順に書きます。
●WHERE句の書き方
WHERE句では、できるだけ列名の側は加工しないようにします。
●ヒントの使用
高速化すべきSQL文が既に特定されている場合は、
「ヒント」の使用を検討します。
●EXPLAIN PLANと実行計画
実行計画の最も初歩的な使い方は、
複雑なSQL文に対して適用し、索引が使われているかいないかを知ることです。
●行移行の検出
表をANALYZE (COMPUTE STATISTICS) して、
USER_TABLES表の項目「CHAIN_CNT」を見ます。
この値は零が理想です。移行が多いと、性能低下の大きな原因になります。
これを直すには、一旦表をエクスポートし、インポートし直します。
たびたび行移行が起きるようであれば、その表のPCTFREEを大きくすることで解決できます。
●行連鎖の検出
行連鎖を抽出するには、$ORACLE_HOME/rdbms/admin/utlchain.sqlを実行し、
CHAINED_ROWS表を作っておき、
「ANALYZE TABLE 表名 LIST CHAINED ROWS」を実行します。
結果がその表に入るので、
連鎖行が多い場合は、DB_BLOCK_SIZEを増やしてデータベース自体を
作り直すか、連鎖の多い表を複数の列長の短い表に分割する必要があります。
この点からも、行サイズの大きな表は不利といえます。
●全表走査の高速化
全表走査が多いときは、パラメータDB_BLOCK_MULTIBLOCK_READ_COUNT
を増やすと効果的です。
| もくじ | ||
| Oracle | (C) 2002 Project Radkraft (URANO Misumi) |