Oracle 8i のパラメータチューニング

【この記事の所要時間 : 約 13 分

oracle_8i_memory.gif
Oracle 8i のメモリに関するパラメータチューニングを行ったので、その備忘録である。
2011年2月現在のOracleの最新バージョンは、11g であるが、今回対象とするのは、1999年5月にリリースされた Oracle 8i である。約12年前にリリースされたバージョンであり、まだそんなの使っているのか?と思われるかもしれないが、レガシーシステムでは様々な要件によりバージョンアップできないケースも多いらしく、まだまだ現役でがんばっている。オラクルマスターの試験をはじめて受けた時のバージョンが確か8iだったと記憶している。
まず、一番はじめに認識しておかなければならないのが、「どんなシステムでも必ず有効な手段という物はない」ということである。
その上で、今回取り上げるチューニングの手段は、メモリのチューニングである。メモリのチューニングをする上で理解しておかなければならないのが、Oracleのメモリ構造である。構造を理解したうえで、各メモリ空間の大きさの妥当性を検証していくと良い。
oralce_memory_01.gif
Oracleのメモリ・チューニングで重要なのは、SGAである。SGAには図にあるように大きく、3つのメモリ空間がある。

・共有プール
・データベースバッファキャッシュ
・REDOログ・バッファ

共有プールの中に、ライブラリ・キャッシュとディクショナリ・キャッシュがある。
SGAのチューニングで特に重要なのが、共有プール(ライブラリ・キャッシュ、ディクショナリ・キャッシュ)とデータベースバッファキャッシュである。
ライブラリ・キャッシュとは、

ユーザーから実行されたSQLやPL/SQLプロシージャ、パッケージの実行計画や解析結果をメモリに格納し、同様なSQLに対する処理負担軽減を行うメモリ空間である。

ディクショナリ・キャッシュとは、

データベース内のオブジェクトに関する変更情報や属性、構成情報を保持し、SQLの解析時には、テーブル属性などのオブジェクト構成情報が参照されるメモリ空間である。

データベースバッファキャッシュとは、

ユーザーから実行されたSQL等によってアクセスされたデータファイル内にあるデータをいったんメモリ上に展開し、保持しておくメモリ空間である。

ライブラリ・キャッシュ、ディクショナリ・キャッシュ、データベースバッファキャッシュともに、内容は違えどもやっていることは、「キャッシュ」である。そして、一度、データファイル(ハードディスク)へアクセスして読み取ったデータや実行したSQLなどをメモリ上にキャッシュしておくと、同じデータを利用する際や同じSQLを利用する際にはデータファイルへアクセスせずに、メモリ上にキャッシュしているデータへアクセスすればいい。データファイルへのアクセスに比べて、メモリへのアクセスはパフォーマンス的に数十倍から数百倍速い。
よって、キャッシュを有効利用して、メモリ上でできるだけ多くの処理ができるようなパラメータを設定することが重要である。サーバで利用できる実メモリがあり、それによってSGAに割り当てることが可能なメモリ量が決まる。その決まったSGAのメモリ量の中で最適な値をそれぞれに割り当ててやらなければならない。
まず、現状の確認であるが、SQL*Plusで以下のようにすると確認できる。

SQL> show sga
Total System Global Area ********* bytes
Fixed Size ******** bytes
Variable Size ******** bytes
Database Buffers ******** bytes
Redo Buffers ******** bytes

Oracle 8i は、Oracle 9i以降と違って、SGAのメモリ量を調整するようなパラメータはなく、データベースバッファキャッシュや共有プール、Javaプール、ラージプールなどのSGAに含まれる各種パラメータで設定した値を合計したものがSGAのメモリ量となる。(Oracle 9i以降では、sga_max_size というSGAサイズを調整するパラメータがある)
show sga で表示される各種値の意味は以下の通りである。

1. Total System Global Area – SGAのメモリ量(2+3+4+5)
2. Fixed Size – 静的割当済みメモリ
3. Variable Size – 動的割当可能メモリ(共有プール、Javaプール、ラージプール等)
4. Database Buffers – データベースファッファキャッシュ
5. Redo Buffers – ログバッファ

SGAに含まれる各種パラメータは以下のようにして確認することができる。

SQL> show parameters DB_BLOCK_BUFFERS
SQL> show parameters DB_BLOCK_SIZE
SQL> show parameters SHARED_POOL_SIZE
SQL> show parameters JAVA_POOL_SIZE
SQL> show parameters LARGE_POOL_SIZE
SQL> show parameters LOG_BUFFER

メモリの割り当てに関する基本的な考え方は以下の通りである。

割り当てられたSGAのメモリ量のうち、できるだけ多くデータベースバッファキャッシュにメモリを割り当て、共有プールへの割り当てはパフォーマンスが落ちない程度に小さくする

ライブラリ・キャッシュ、ディクショナリ・キャッシュは、個別に値を設定することはできないので、共有プール全体の値としてパラメータを設定する。設定するパラメータは、SHARED_POOL_SIZEである。
共有プールへの割り当てはパフォーマンスが落ちない程度に小さくするとあるが、パフォーマンスが落ちない程度とはどの程度であるか?目安は以下の通りである。

・ライブラリ・キャッシュ・ヒット率 が 99% 以上。
・ディクショナリ・キャッシュ・ヒット率 が 95% 以上。

ライブラリ・キャッシュ・ヒット率ならびに、ディクショナリ・キャッシュ・ヒット率の取得方法は以下のサイトを参考にするといい。上記の目安をクリアでき、かつできるだけ小さな値を共有プールのメモリ空間として割り当てよう。
最適な共有プールサイズを探っていきましょう。
共有プールに割り当てるメモリ量が決まるとおのずとデータベースバッファキャッシュの値は決まってくる。データベースバッファキャッシュの値は、以下の2つのパラメータによって決まる。

・ DB_BLOCK_BUFFERS
・ DB_BLOCK_SIZE

Oracle 8i では、データベースバッファキャッシュ = DB_BLOCK_BUFFERS × DB_BLOCK_SIZE で計算することができる。そして、DB_BLOCK_SIZE は、通常、4098バイト または、8192バイトであり、データベース作成時に設定して作成後は変更できない。よってデータベースバッファキャッシュの値を調整するには、DB_BLOCK_BUFFERSの値を変えてやる必要がある。
ここまで理解したうえで、ある環境(Linux , Oracle)でのパラメータ設定の例を考えてみよう。

1.実装メモリ
= 4 GB
2.カーネルの占有メモリ量
= 1 GB
3.ユーザー利用可能メモリ量
= 1-2 = 3 GB
4.SGAに割り当てられる最大メモリ量
= 1.75 GB
5.データベースバッファキャッシュのメモリ量
DB_BLOCK_SIZE = 8192
DB_BLOCK_BUFFERS = 98304
DB_BLOCK_BUFFERS×DB_BLOCK_SIZE = 768 MB
6.共有プールのメモリ量
SHARED_POOL_SIZE = 268435456 (256 MB)

まず、実装メモリが 4GB のサーバの場合、Linuxのカーネルが 約1GB のメモリを占有するため、実際にユーザーが利用できるメモリ空間は残り 3GB である。その 3GB のうち SGAに割り当てることができるメモリ量は 約1.75GBである。このSGAへの割当を最大約2.6GBまで拡張する方法もあるみたいだが、今回は無視する。
参考サイト
ちなみに、OSとOracleのバージョンにより、SGAに割り当てることができるメモリ量は異なっているので注意してほしい。
総てのOracleインスタンスで使用可能なメモリの上限

HP-UX 11.0 
Oracle8i(32bit) 1.75GB
Oracle8i(64bit) 無制限
Oracle9i(64bit) 無制限
Solaris2.5.1
Oracle8i(32bit) 3.75GB
Oracle8i(64bit) 無制限
Oracle9i(32bit) 3.75GB
Oracle9i(64bit) 無制限
Linux(2.4.x)
Oracle8i(32bit) 1.75GB
Oracle9i(32bit) 1.75GB
Windows2000
Oracle8i(32bit) 2.0GB
Oracle9i(32bit) 2.0GB

データベースバッファキャッシュのメモリ量であるが、多ければパフォーマンスが向上する(ディスクへのI/O回避)のだが、あるサイズを超えると OS(ここではLinux)のページングおよびスワッピングが発生するので限度があるということに注意してほしい。ページングが発生しないように少し余裕を持たせた設定にしたい。
そして、共有プールであるが、
Oracle のメモリ管理について(8i-9i)

一部のドキュメントではデータバッファーと同じぐらいのサイズが望まれるが、実際異なる場合が多い。このパラメータが大きすぎると、バッファーしたSQLを管理する効率が悪くなる。通常ではshared_pool_sizeは300MB以内に維持したい。

とあるようにSGAのサイズからデータベースバッファキャッシュと同じ 768 MBくらいにすることも可能だが、当初の目安のようにパフォーマンスが落ちない程度に小さくするために、256MBとした。この値をもとにパフォーマンステストや監視を行って、最適なメモリパラメータに調整していく。
DB_BLOCK_BUFFERS、SHARED_POOL_SIZE は、Oracleのパラメータ・コンフィグである init.ora で変更することができる。
一番初めに述べたように、「どんなシステムでも必ず有効な手段という物はない」ので、この値も参考値であり、どのシステムでもこうすればいいわけでは当然ないので、注意してほしい。
SGAの共有プールとデータベースバッファキャッシュが基本的なメモリチューニングであるが、同時に I/Oチューニングを行うとさらにパフォーマンスが向上するケースが多い。それが、全表スキャンのパフォーマンスを向上させるI/Oチューニングである。
そのパラメータが、DB_FILE_MULTIBLOCK_READ_COUNT である。
全件走査の性能調整(DB_FILE_MULTIBLOCK_READ_COUNT)

DB_FILE_MULTIBLOCK_READ_COUNTは、全表走査時に一回のI/Oで読み込むブロック数を指定します。指定サイズは大きければそれだけ早くなることになります。しかしながら、一定サイズ以降は頭打ちになります。頭打ちになるサイズは、

DB_FILE_MULTIBLOCK_READ_COUNT = OSの最大I/Oサイズ / DB_BLOCK_SIZE

となります。通常OSの最大I/Oサイズは1MBであるため、ブロックサイズが8KBとすると128となります。

この値を大きくしすぎると、オプティマイザが、indexスキャンより全表スキャンを選択する可能性が高まるらしく、実行計画に影響があると指摘する資料もあり、デフォルトでは、8 であるが、大きくしても 32 から 64 程度で抑えておいた方がよいかと思われる。
DB_FILE_MULTIBLOCK_READ_COUNT も init.ora で変更することができる。
init.ora はOracleインスタンスを再起動しないと反映しない。
WindowsにおけるOracleインスタンスの起動・停止方法
これ以外にも、ソートエリアサイズ( SORT_AREA_SIZE )などチューニングできるパラメータはあるが、これだけはやっておきたいという基本的なパラメータチューニングは以上である。
パラメータチューニングに限らず、OracleをDBMSとして利用する場合は、対象バージョンのドキュメントをしっかり読んでおくことをお勧めする。Oracle 8i に関して目を通しておいた方がよいドキュメントを以下にあげておく。すべてOracle社の正式ドキュメントである。
Oracle Japan – Oracle8i R8.1.7 ドキュメント
Oracle8i リファレンス・マニュアル
Oracle8i パフォーマンスのための設計およびチューニング
Oracle Japan – パフォーマンス&スケーラビリティ:アーカイブ
Oracle8/8i パフォーマンスチューニング I
Oracle8/8i パフォーマンスチューニング II
Oracle8 簡単チューニング
Oracle8 Performance Tuning
Oracle 8 の分も含まれているが、Oracle 8 と 8i はメジャーバージョンが同じであり、共通する部分が多いので参考としたい。
以下は、MySQLとPostgresqlのパラメータチューニングについて書いた過去の記事である。関係ないかもしれないが、同じDBMSのパラメータチューニングということでリンクをはっておく。
MySQLのパラメータチューニング
PostgreSQL 8系のパラメータチューニング

スポンサーリンク
レクタングル(大)広告
  • このエントリーをはてなブックマークに追加
スポンサーリンク
レクタングル(大)広告

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です