Oracle8簡単チューニング for Windows NT - OTN

Oracle8 簡単チューニング
for Windows NT
日本オラクル株式会社
目次
はじめに ................................................................................................................ 2
0.本書を使ったチューニングの進め方........................................................................... 3
1.WINDOWS NT を設定する................................................................................... 4
1.1.CPU とメモリーを節約する.............................................................................................4
1.2.システムキャッシュの使用を避ける..................................................................................8
W I N D O W S NT 設定のまとめ..................................................................................................9
2.ORACLE のパラメータを設定する......................................................................... 10
2.1.SHARED_POOL_SIZE を拡大する............................................................................10
2.2.DB_BLOCK_BUFFERS を拡大する ..........................................................................11
2.3.SORT_AREA_SIZE を拡大する..................................................................................14
2.4.DB_FILE_MULTIBLOCK_READ_COUNT を拡大する .........................................15
2.5.設定例 ...........................................................................................................................17
O R A C L E パラメータ設定のまとめ.........................................................................................18
3.設定した ORACLE パラメータを最適化する............................................................. 19
3.1.キャッシュヒット率を高くする......................................................................................19
3.2.メモリ使用量を確認する................................................................................................22
O R A C L E パラメータ最適化のまとめ.....................................................................................25
4.コストベースで最適化する..................................................................................... 26
4.1.ルールベースとコストベース..........................................................................................26
4.2.オプティマイザをコストベースに設定する......................................................................26
4.3.統計情報を収集する.......................................................................................................28
コストベース最適化のまとめ................................................................................................31
付録 SHARED_POOL_SIZE の計算方法.................................................................. 32
おわりに .............................................................................................................. 35
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
1
はじめに
本書は「Windows NT 版 Oracle のパフォーマンス・チューニングをしたいが、どこから
手を付けていいのか分からない」という方々のために「これだけは押さえるべき」といっ
たチューニングのポイントをまとめたものです。本書を取り掛かりに初歩的なチューニン
グに挑戦してください。
ただし、本書で解説するポイントは一般論であり、必ず Oracle のパフォーマンスを向上
させる保証はありません。チューニングにおいては、システムによって異なるボトルネッ
クを解消することが最も重要であり、本書で紹介するポイント以外にも考慮すべき項目は
たくさんあるからです。
本書では、できるだけ「難しい理論」より「簡単な実践方法」に重点を置いて解説して
います。本書では詳しく触れない点についてお知りになりたい方は、Oracle のマニュアル、
または、本文中に紹介します DMS の技術資料等を参考にすることをお勧めします。
なお、本書で使用したソフトウェアは以下の通りです。他のバージョンに於いては本書
の記述内容が異なる場合があります。
• Windows NT Server 4.0（SP4）
• Oracle8 Enterprise Edition R8.0.5 for Windows NT
ø Oracle、Oracle7、Oracle8 はオラクル社の登録商標です。
ø その他のすべての企業名と商品名は各社の登録商標または商標、製品名です。
ø なお、本文中では
、
は明記していません。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
2
0. 本書を使ったチューニングの進め方
本書の解説に従い、次のステップを踏んでチューニングを行ってください。
1. OS の設定
2. Oracle の設定
3. 設定の評価と最適化
4. コストベースの設定
各ステップで行うことは以下の通りです。
ステップ 1. OS の設定
初めに OS（本書では Windows NT）の設定を行います。Oracle のパフォーマンスを最
大限に発揮するように Windows NT の設定を変更します。
ステップ２. Oracle の設定
続いて Oracle の設定を行います。パフォーマンスを左右する重要な初期化パラメータの
設定を変更します。
ステップ 3. 設定の評価
Oracle 初期化パラメータの変更後、設定したパラメータの評価を行います。ステップ 2
で行った設定がパフォーマンスを向上させるものかを確認します。そして必要であればパ
ラメータ設定を更に最適化します。
このステップでは Oracle 初期化パラメータをより詳細にチューニングすることを目的と
しています。ステップ 2 での設定を暫定的に採用するのなら、このステップ 3 を省略する
ことは可能です。
ステップ 4. コストベース設定
Windows NT および Oracle 環境をチューニングした後、SQL 文の実行計画をコストベ
ースで最適化します。オプティマイザをコストベースに設定し、必要な統計情報を取得し
ます。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
3
1. Windows NT を設定する
この章では、Oracle を動かすプラットフォームとして Windows NT に設定すべきことを
説明します。
1.1. CPU とメモリーを節約する
OS を設定する上で最も大事なことは、「できるだけ多くのメモリと CPU を Oracle に
与える」ということです。具体的にすべき項目を以下に示します。
・Oracle に不要なプロセス（サービス）は停止する。
・スクリーンセーバーを使用する場合、「模様なし」にする。
・ドメインコントローラ、バックアップコントローラにはしない。
・フォアグランドプロセスの優先度を低くする。
Oracle にいらないプロセス（サービス）は停止する
「コントロールパネル」の「サービス」を起動して Oracle 稼動には必要のないサービス
を停止しておきます。
図 1.1 「サービス」画面
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
4
必要となる主な Oracle サービスは以下の 3 つです。
OracleServiceSID
OracleStartSID
OracleTNSListener80
OracleWebAssistant はデータベースからの問い合わせ情報を、指定された間隔で Web
ページに公表するサービスです。デフォルトでは OracleWebAssistant サービスが自動的
に開始されますが、この機能が必要なければ停止しておきます。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
5
スクリーンセーバーを使用する場合、「模様なし」にする。
「コントロールパネル」の「画面」を起動し、「スクリーンセーバー」タブを選びます。
スクリーンセーバーのメニューから「（なし）」または「模様なし」を選択します。
図 1.2 「スクリーンセーバー」設定画面
ドメインコントローラ、バックアップコントローラにはしない。
OS を設定する上では「できるだけ多くのメモリと CPU を Oracle に与える」ことが最
も重要であることは既に述べました。Oracle サーバーをドメインコントローラやバックア
ップコントローラ、またはファイルサーバとして同時に使用しないようにしてください。
パフォーマンス向上のため Oracle 専用サーバーとし、
できるだけ多くのリソースを Oracle
に割り当てるようにしてください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
6
フォアグランドプロセスの優先度を低くする。
Windows NT Workstation である場合、「コントロールパネル」の「システム」を起動
し、「パフォーマンス」タブを選びます。「アプリケーションの処理優先度」を最低に設
定します。こうすることでバックグラウンドで実行される Oracle に優先的に CPU が割り
当てられます。
図 1.3 「アプリケーションの処理優先度」設定画面
（注意）Windows NT Server である場合は、この設定を行ってもフォアグランドプロセ
スの優先度に変化はありません。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
7
1.2. システムキャッシュの使用を避ける
明示的に指定しないかぎり、Windows NT は自動的にシステムキャッシュを確保します。
本章では Oracle のキャッシュの動きと、その最適化について説明します。
Oracle のキャッシュ
Oracle 自身もキャッシュ機能を持ち、システムキャッシュをバイパスして、ファイル
への読み書きを行なっています。デフォルト設定のままで Oracle を使用していると、大
量データの読み書きが起こったときに、Windows NT は自動的にキャッシュメモリを大
量に確保してしまい、肝心の Oracle に割り当てられたメモリーがページアウトされてし
まう可能性があります。よって大量のシステムキャッシュを確保することは Oracle にと
って利点はなく、むしろ避けるべきです。
Windows NT Server システムキャッシュの最適化
Windows NT Server の場合、「コントロールパネル」の「ネットワーク」を開きます。
「サービス」タブを選び「サーバー」の「プロパティ」を確認します。「プロパティ」
中の「最適化」はデフォルトでは「ファイル共有のスループットを最小にする」になっ
ていますが、これを「ネットワークアプリケーションのスループットを最大にする」に
設定します。この設定をすることにより、システムキャッシュへ優先的にメモリが割り
当てられるのを防ぎます。
図 1.4 システムキャッシュ設定画面
（注意）Windows NT Workstation ではこの設定をすることはできません。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
8
Windows NT 設定のまとめ
CPU とメモリーを節約する
• Oracle に不要なプロセス（サービス）は停止する
• スクリーンセーバーを使用する場合、「模様なし」にする
• ドメインコントローラ、バックアップコントローラにはしない
• フォアグランドプロセス優先度を低くする
システム・キャッシュへのメモリ割り当て優先度を低くする
• 「ネットワークアプリケーションのスループットを最大にする」に設定する
（Windows NT Server の場合）
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
9
2. Oracle のパラメータを設定する
Oracle にはたくさんの init.ora パラメータがあります。本章では、その中でも重要な次
の 4 つのパラメータを設定します。
SHARED_POOL_SIZE
DB_BLOCK_BUFFERS
SORT_AREA_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
2.1. SHARED_POOL_SIZE を拡大する
このパラメータでは、共有プールのサイズをバイト単位で指定します。共有プールには、
共有 SQL と PL/SQL ブロック、パッケージ、プロシージャ、ファンクション、トリガー、
データディクショナリ情報等が格納されます。このパラメータはバッファキャッシュ
（DB_BLOCK_BUFFERS）をチューニングする前にチューニングしておきましょう。共
有プールのサイズ不足のほうが、バッファキャッシュのサイズ不足よりもはるかにパフォ
ーマンスを低下させる可能性が高いからです。
Oracle8 R8.0.5 for Windows NT における初期データベースの SHARED_POOL_SIZE
値は約 10MB ですが、この値を大きくすることでパフォーマンスの向上を期待できます。
このパラメータは本来システムに応じて設定するものですが、とりあえず暫定的に 15MB
程度に大きくしてください。ストアドプロシージャが多数ある場合、更に 5MB 程度追加し
てください。レプリケーション環境では、更にサイズを大きくした方が良いでしょう。
SHARED_POOL_SIZE を 15,000,000 バイトにする場合、INIT.ORA ファイルの現在の
設定をコメントアウトし、次の一行を加え、Oracle を再起動します。
SHARED_POOL_SIZE = 15000000
#SHARED_POOL_SIZE = 10000000
起動中 Oracle の SHARED_POOL_SIZE 設定値を確認するにはサーバーマネージャーか
ら次のコマンドを発行します。
SVRMGR> SHOW PARAMETERS SHARED_POOL_SIZE
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------shared_pool_size
文字列 15000000
設定したサイズの評価方法は第 3 章を参考にしてください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
10
2.2. DB_BLOCK_BUFFERS を拡大する
このパラメータでは、バッファキャッシュ内のバッファの数を指定します。一つのバッ
ファの大きさはデータベースブロックサイズであるため、次の式で求められる値がバッフ
ァキャッシュの合計サイズとなります。
バッファキャッシュの合計サイズ（バイト）＝ DB_BLOCK_BUFFERS × DB_BLOCK_SIZE
バッファキャッシュには、表、索引、クラスタ、ソートデータ、ディクショナリデータ、
ロールバックセグメントが格納されます。
Oracle8 R8.0.5 for Windows NT における初期データベースの DB_BLOCK_BUFFERS
値は 200 ですが、このブロックバッファ数を増やすほどにディスク I/O が減少し、パフォ
ーマンスの向上を期待できます。しかし実メモリに対して大きすぎるバッファキャッシュ
はページアウトやスワッピングを引き起こし、かえってパフォーマンスを悪化させます。
SHARED_POOL_SIZE パラメータを充分チューニングしたあと、実メモリサイズを考慮
して DB_BLOCK_BUFFERS を拡大すべきです。
（注意）全表検索とバッファキャッシュ
大きな表の全表検索処理においてすべてのデータをキャッシュするとどうなるでしょ
うか。複数ユーザーが共有で効率よく参照していた他の表のデータがバッファから追い
出されてしまい、かえってパフォーマンスに悪影響を与える可能性があります。このよ
うなことを避けるため、Oracle は大きな表の全表検索処理において全データをキャッシ
ュすることはありません。
ただし、CREATE TABLE 時または ALTER TABLE で CACHE キーワードを指定す
るか、全件検索を実行する SQL 文に CACHE ヒントを使用することで、以下の初期化パ
ラメータで指定する制限値（ブロック数）までバッファキャッシュを使用できるように
なります。
Oracle8
DB_BLOCK_BUFFERS
Oracle7
CACHE_SIZE_THRESHOLD
CACHE 指定方法についての詳細はマニュアルを参照してください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
11
DB_BLOCK_BUFFERS 計算方法
実メモリの大きさからバッファキャッシュに割り当てることのできるメモリのサイズを
計算し、DB_BLOCK_BUFFERS を決定する方法を以下に示します。
１. バッファキャッシュ以外に必要なメモリサイズを A とすると、A は以下の式により求
めることができます
A ＝ Windows NT 用
＋Oracle バックグラウンドプロセス用
＋各ユーザー用
＋SHARED_POOL_SIZE
２. それぞれに必要なメモリサイズの目安を以下に示します。
Windows NT
３２ MB
バックグラウンドプロセス
１０ MB
ユーザー
２×ｎ MB（ｎ：同時ユーザー数）
３. A を求めます。今回、同時ユーザー数は 20。SHARED_POOL_SIZE は 20MB としま
す。
A ＝ ３２ ＋ １０ ＋ ２×２０ ＋ ２０ ＝ １０２（MB）
4. 次の式によりバッファキャッシュに割り当てることのできるメモリのサイズを求めま
す。ここではステップ３の計算結果より A は 102MB、実メモリは 128MB とします。
バッファキャッシュに割り当てられるメモリのサイズ
＝ 実メモリのサイズ − A ＝ １２８ − １０２ ＝ １６（MB）
５. 次の式より DB_BLOCK_BUFFERS を求めます。ここではステップ４の結果より利用
可能メモリのサイズは 16MB ですがスワップが起きないよう余裕をみて 14MB とします。
また DB_BLOCK_SIZE は 2KB とします。
DB_BLOCK_BUFFERS
＝ 利用可能メモリのサイズ / DB_BLOCK_SIZE
＝ １４ MB / ２ KB ≒ ７０００
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
12
DB_BLOCK_BUFFERS を変更するには、初期化パラメータファイル（INIT.ORA）の
現在の設定をコメントアウトし、次のように新しい設定値の一行を加え、Oracle を再起動
します。
DB_BLOCK_BUFFERS = 7000
#DB_BLOCK_BUFFERS = 200
起動中 Oracle の DB_BLOCK_BUFFERS 設定値を確認するにはサーバーマネージャー
から次のコマンドを発行します。
SVRMGR> SHOW PARAMETERS DB_BLOCK_BUFFERS
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------db_block_buffers
整数値 7000
設定したサイズの評価方法は第 3 章を参考にしてください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
13
2.3. SORT_AREA_SIZE を拡大する
このパラメータでは、ユーザープロセスがソート処理のために利用できるメモリー（ソ
ートエリア）のサイズをバイト単位で指定します。ソートエリアはソートが発生するよう
な処理をユーザーが行ったときにメモリに割り当てられ、SORT_AREA_SIZE で指定した
サイズがその最大値となります。
意思決定支援システムまたはバッチ処理、大規模な CREATE INDEX 操作では、このパ
ラメータを拡大することでソートの効率が上がりパフォーマンスの向上を期待できます。
ただし、SORT_AREA_SIZE はユーザープロセス毎に割り当てられることに注意してくだ
さい。大きすぎる値は早く空きメモリを使い果たし、ページアウトやスワッピングを引き
起こす可能性があります。
Oracle8 R8.0.5 for Windows NT における SORT_AREA_SIZE のデフォルト値は 65,536
ですが、256KB、512KB、1MB、2MB としてテスト運用し、その適正値を判断してくだ
さい。設定したサイズの評価方法は第 3 章を参考にしてください。
SORT_AREA_SIZE を変更するには、初期化パラメータファイル（INIT.ORA）の現在
の設定をコメントアウトし、次のように新しい設定値の一行を加え、Oracle を再起動しま
す。ここでは SORT_AREA_SIZE を 256KB に指定します。
SORT_AREA_SIZE = 262144
#SORT_AREA_SIZE = 65536
データベースを再起動しなくても現行セッションだけに有効な変更であれば、次のよう
にして行うことができます。
SQL> ALTER SESSION SET SORT_AREA_SIZE=262144;
セッションが変更されました。
起動中 Oracle の SORT_AREA_SIZE 設定値を確認するにはサーバーマネージャーから
次のコマンドを発行します。
SVRMGR> SHOW PARAMETERS SORT_AREA_SIZE
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------sort_area_size
整数値 262144
（ 注 意 ） マ ル チ ス レ ッ ド サ ー バ ー （ MTS ） 使 用 時 に は SORT_AREA_SIZE は
SHARED_POOL_SIZE で確保したメモリ内にとられます。したがって MTS 使用時には
SHARED_POOL_SIZE を専用サーバー使用時よりも大きく設定する必要があります。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
14
2.4. DB_FILE_MULTIBLOCK_READ_COUNT を拡大する
このパラメータでは、全表検索（Full Table Scan）や Index Fast Full Scan において一
回の I/O 操作で読み取られるブロックの最大数を指定します。例えば Oracle のブロックサ
イズ（DB_BLOCK_SIZE）が 2KB で DB_FILE_MULTIBLOCK_READ_COUNT の値が
８の場合、一回の I/O のサイズは最大 16KB になります。
OLTP 系処理においてはこのパラメータ値は小さめで良く、デフォルトの 8 のままで良
いでしょう。しかし DSS 系処理ではこの値を大きくすることでパフォーマンスの向上を期
待できます。ただし設定できる最大値は次のような制限を受けます。
Oracle
一回の最大 I/O サイズ（DB_BLOCK_SIZE×DB_FILE_MULTIBLOCK_READ_COUNT）
Oracle7
６４ KB
Oracle8
１ MB
例えば、Oracle8 R8.0.5 for Windows NT においてブロックサイズ（DB_BLOCK_SIZE）
が 2KB である場合、DB_FILE_MULTIBLOCK_READ_COUNT の最大値は以下のように
求めます。
DB_FILE_MULTIBLOCK_READ_COUNT の最大値
＝ 一回の最大 I/O サイズ ／ DB_BLOCK_SIZE
＝ 1MB / 2KB ＝ 512
まずは DB_FILE_MULTIBLOCK_READ_COUNT を 16 程度に設定してテストしてみ
てください。DB_FILE_MULTIBLOCK_READ_COUNT を変更するには、初期化パラメ
ータファイル（init.ora）の現在の設定をコメントアウトし、次のように新しい設定値の一
行を加え、Oracle を再起動します。
DB_FILE_MULTIBLOCK_READ_COUNT = 16
#DB_FILE_MULTIBLOCK_READ_COUNT = 8
データベースを再起動しなくても現行セッションだけに有効な変更であれば、次のよう
にして行うことができます。
SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 16;
セッションが変更されました。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
15
起動中 Oracle の DB_FILE_MULTIBLOCK_READ_COUNT 設定値を確認するにはサ
ーバーマネージャーから次のコマンドを発行します。
SVRMGR> SHOW PARAMETERS DB_FILE_MULTIBLOCK_READ_COUNT
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------db_file_multiblock_read_count
整数値 16
ただし Oracle の I/O はエクステントをまたがって行うことはできないため、エクステン
トサイズが DB_BLOCK_SIZE×DB_FILE_MULTIBLOCK_READ_COUNT より小さい
場合は、多くの I/O が必要になることに注意してください。また索引検索での I/O はブロ
ック単位で実行されるので DB_FILE_MULTIBLOCK_READ_COUNT の値は特に関係あ
りません。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
16
2.5. 設定例
ここでは本章での説明をもとにした OLTP 系システムと DSS 系システムにおける
Oracle パラメータの設定例を紹介します。ただし、本節での設定例は一般的なものです。
それぞれのシステムに合わせて、より適切な設定に変更することをお勧めします。さらに
細かいパラメータ・チューニングを行うには、第 3 章を参考にしてください。
OLTP 系システム
SHARED_POOL_SIZE
15M
DB_BLOCK_BUFFERS
5,000∼10,000＋
DB_FILE_MULTIBLOCK_
READ_COUNT
場合は、5M 程度追加する。
ブロック・サイズ、実メモリのサイ
ズ等によって大きく異なる。
256K
SORT_AREA_SIZE
ストアドプロシージャが多数ある
まずはこれくらいに大きくする。
必要であれば更に大きくする。
OLTP 系システムではあまり大きく
8
しない。
DSS 系システム
SHARED_POOL_SIZE
20M
DB_BLOCK_BUFFERS
5,000∼10,000＋
ブロック・サイズ、実メモリのサイ
大量のデータのソートが発生する
可能性があるので大きくする。
DB_FILE_MULTIBLOCK_
READ_COUNT
ので大きめ。
ズ等によって大きく異なる。
1M∼2M
SORT_AREA_SIZE
ストアドプロシージャが多数ある
16
全表検索のため、まずはこれくらい
に大きくする。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
17
Oracle パラメータ設定のまとめ
Oracle 初期化パラメータを拡大する
• SHARED_POOL_SIZE
• DB_BLOCK_BUFFERS
• SORT_AREA_SIZE
• DB_FILE_MULTIBLOCK_READ_COUNT
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
18
3. 設定した Oracle パラメータを最適化する
第１章、第２章で変更したパラメータ等の設定は、本当にパフォーマンスを向上させる
ことができるのでしょうか。本章では変更したパラメータ等の設定を評価する方法につい
て解説します。必要であればパラメータの値を更に変更し設定を最適化します。
3.1. キャッシュヒット率を高くする
本節では次にあげるキャッシュ内のデータを参照した割合（キャッシュヒット率）を調
べるスクリプトを紹介します。キャッシュヒット率が低い場合は、どのパラメータの設定
を変更すべきか説明します。
•
ライブラリ・キャッシュ
•
ディクショナリ・キャッシュ
•
データベース・バッファ・キャッシュ
ライブラリ・キャッシュ・ヒット率を 90%以上にする
ライブラリ・キャッシュは共有プール内に割り当てられ、共有 SQL 領域や PL/SQL 領域
を含みます。実行する回数の多い SQL 文がライブラリ・キャッシュに残るように十分なメ
モリを確保するべきです。
以下に SHARED_POOL_SIZE とライブラリ・キャッシュ・ヒット率の一般的な関係を
示します。
SHARED_POOL_SIZE
大
小
ライブラリ・キャッシュ・ヒット率
高
低
次のスクリプトを実行し、V$LIBRARYCACHE 表に格納された統計を参照することで
ライブラリ・キャッシュ・ヒット率を求めることができます。
SQL> select sum(pins) "Executions",
2
sum(reloads)
"Misses",
3
round(100*(1-(sum(reloads)/sum(pins))),3) "Hit Ratio"
4
from v$librarycache
5
/
Executions
Misses Hit Ratio
---------- --------- --------403375
2356
99.416
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
19
ここでのキャッシュヒット率は 99.416%だとわかります。キャッシュヒット率が 90%を
下回る場合、SHARED_POOL_SIZE の値を大きくしてライブラリ・キャッシュに割り当
てられるメモリー・サイズを増やします。
ただし毎回異なる SQL 文を実行するような処理では SHARED_POOL_SIZE の値を大
きくしてもキャッシュヒット率の向上は期待できないことに注意してください。
ディクショナリ・キャッシュ・ヒット率を 90%以上にする
ディクショナリ・キャッシュは共有プール内に割り当てられ、データ・ディクショナリ
情報を保持しています。参照する回数の多いデータディクショナリ情報がディクショナ
リ・キャッシュに残るように十分なメモリを確保するべきです。
以下に SHARED_POOL_SIZE とディクショナリ・キャッシュ・ヒット率の一般的な関
係を示します。
SHARED_POOL_SIZE
大
小
ディクショナリ・キャッシュ・ヒット率
高
低
次のスクリプトを実行し、V$ROWCACHE 表に格納された統計を参照することでライブ
ラリ・キャッシュ・ヒット率を求めることができます。
SQL> select sum(gets) "Gets",
2
sum(getmisses)
3
round(100*(1-(sum(getmisses)/sum(gets))),3) "Data Dict Hit Ratio"
4
from v$rowcache
5
where gets <> 0
6
/
Gets
"Misses",
Misses Data Dict Hit Ratio
------- ------- ------------------1563275
5723
99.634
ここでのキャッシュヒット率は 99.634%だとわかります。キャッシュヒット率が 90%を
下回る場合、SHARED_POOL_SIZE の値を大きくしてディクショナリ・キャッシュに割
り当てられるメモリー・サイズを増やします。SHARED_POOL_SIZE の値を拡大する方
法は第 2.1 節を参照してください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
20
バッファ・キャッシュ・ヒット率を 90%以上にする
第 2.2 節で説明したデータベース・バッファ・キャッシュが効率的に使用されるように十
分なメモリを確保するべきです。
以下に DB_BLOCK_BUFFERS とバッファ・キャッシュ・ヒット率の一般的な関係を示
します。
DB_BLOCK_BUFFERS
大
小
バッファ・キャッシュ・ヒット率
高
低
次のスクリプトを実行し、V$SYSSTAT 表に格納された統計を参照することでバッフ
ァ・キャッシュ・ヒット率を求めることができます。
SQL> select (a.value+b.value) "Log_Reads",
2
c.value
"Phy_Reads",
3
round(((1 - (c.value / (a.value+b.value))) * 100),3) "Buffer Hit Ratio"
4
from v$sysstat a, v$sysstat b, v$sysstat c
5
where a.name = 'db block gets' and
6
b.name = 'consistent gets' and
7
c.name = 'physical reads'
8
/
Log_Reads Phy_Reads Buffer Hit Ratio
--------- --------- ---------------694785
29113
95.81
ここでのキャッシュヒット率は 95.81%だとわかります。キャッシュヒット率が 90%を下
回る場合、DB_BLOCK_BUFFERS の値を大きくしてバッファ・キャッシュに割り当てら
れるメモリー・サイズを増やします。このパラメータは共有プールサイズ
（ SHARED_POOL_SIZE ） を チ ュ ー ニ ン グ し た 後 に チ ュ ー ニ ン グ し ま し ょ う 。
DB_BLOCK_BUFFERS サイズの拡大方法は第 2.2 節を参照してください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
21
3.2. メモリ使用量を確認する
SHARED_POOL_SIZE や DB_BLOCK_BUFFERS の値を大きくすることでパフォーマ
ンスの向上を期待できることは既に説明しました。しかし必要以上に大きくすることはリ
ソースの無駄遣いですし、実メモリに対して大きすぎる値を設定すればページアウトやス
ワッピングを引き起こします。このときかえってパフォーマンスを悪化させてしまいます。
本節では Oracle のメモリ使用量を確認する方法を解説します。メモリが無駄に大きく割
り当てられていないか、また大きすぎてページアウトしていないか、などを調査します。
共有プール内の空き領域を確認する
次のスクリプトを実行して共有プール内の空き領域のサイズを調べることができます。
同時に共有プールに必要以上のメモリーが割り当てられていないか調べることができます。
空き領域のサイズ（Free Bytes）の割合（Perc_Free）は 30%以下にしたいところです。
十分に高いキャッシュヒット率を維持しているにも関わらず、Free Bytes が大きい時は
SHARED_POOL_SIZE を小さくすることを検討してください。
SQL> select s.name,
2
s.bytes "Free Bytes",
3
round((s.bytes/p.value)*100,3) "Perc_Free",
4
p.value/(1024*1024) "SHD_Pool_MB"
5
from
v$parameter p, v$sgastat s
6
where
s.name = 'free memory'
7
and
p.name = 'shared_pool_size'
8
/
NAME
Free Bytes Perc_Free SHD_Pool_MB
-------------------------- ---------- --------- ----------free memory
13267800
53.071
23.841858
SHARED_POOL_SIZE の設定を変更する方法は第 2 章を参考にしてください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
22
ページアウトしていないか確認する
Windows NT の管理ユーティリティを使用してページアウトが起きていないか確認する
ことができます。
1. 「スタート」→「プログラム」→「管理ツール（共通）」→「パフォーマンス モニタ」
を選択しパフォーマンスモニタを起動します。
2. 「編集」メニューから「グラフに追加」を選択します。
3. 「グラフに追加」ウィンドウの「オブジェクト」メニューから「Process」を選択します。
4. 「インスタンス」メニューから「oracle80」プロセスを選択します。
5. 「カウンタ」メニューから「Page Faults/sec」を選択し「追加」ボタンをクリックしま
す。
6. 「完了」ボタンをクリックし設定終了です。
図 3.1 「パフォーマンスモニタ」グラフ追加画面
この「Page Faults/sec」は Oracle プロセスが探していた内容が実メモリになく、ページ
ングファイルに読みに行った回数を示します。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
23
図 3.2 「パフォーマンスモニタ」による「Page Faults/sec」出力画面
同じ検索処理を繰り返すような場合でも、図 3.2 のように、「Page Faults/sec」が頻繁
に発生しているときはキャッシュに割り当てたメモリーが大き過ぎ、パフォーマンスに悪
影響を与えている可能性があります。物理メモリーを追加し、必要な量を補うか、
DB_BLOCK_BUFFERS、SHARED_POOL_SIZE または SORT_AREA_SIZE を小さくす
ることを検討してください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
24
Oracle パラメータ最適化のまとめ
キャッシュ・ヒット率を高くする（90% 以上）
• ライブラリ・キャッシュ・ヒット率
• ディクショナリ・キャッシュ・ヒット率
• バッファ・キャッシュ・ヒット率
メモリ使用量を確認する
• 共有プール内の空き領域に注意する
• ページアウトに注意する
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
25
4. コストベースで最適化する
本章では、コストベース・オプティマイザを効果的に使用し、データベースの状態を判
断して SQL 文の実行計画を最適化する方法を説明します。
4.1. ルールベースとコストベース
Oracle のオプティマイザが SQL 文の実行計画を最適化するアプローチには、ルールベー
スとコストベースがあります。ルールベース・オプティマイザは、使用可能なアクセスパ
スと、ある固定のアクセスパスに対する優先順位に基づいて実行計画を作成します。それ
に対しコストベース・オプティマイザは、使用可能なアクセスパスと、Oracle のデータデ
ィクショナリに格納された統計情報に基づいて実行計画を作成します。
データベースの統計情報に基づく実行計画はルールベースに比べパフォーマンスを向上
させる可能性が高いため、コストベース・オプティマイザを使用することをお勧めします。
コストベース・オプティマイザに必要なデータベースの統計情報は ANALYZE コマンドを
実行することで収集します。
4.2. オプティマイザをコストベースに設定する
使用するオプティマイザはデフォルトで CHOOSE に設定されています。このときオプ
ティマイザは、アクセスするオブジェクトに関する統計情報が存在する場合はコストベー
ス、存在しなければルールベースで実行計画を作成します。
起動中 Oracle で選択されているオプティマイザを確認するにはサーバーマネージャー
から次のコマンドを発行し、OPTIMIZER_MODE の設定値を確認します。
SVRMGR> SHOW PARAMETERS OPTIMIZER_MODE
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------optimizer_mode
文字列 CHOOSE
オプティマイザ・モードと設定値の関係は以下の通りです。本書ではそれぞれの設定値
についての解説は省略します。オプティマイザ・モードに関する詳細は Oracle のマニュア
ル等を参考にしてください。
ルールベース
RULE、CHOOSE（統計情報がないとき）
コストベース
FIRST_ROWS、ALL_ROWS、CHOOSE（統計情報があるとき）
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
26
オプティマイザ・モードを CHOOSE 以外に変更する必要があれば、初期化パラメータ
ファイル（init.ora）の OPTIMIZER_MODE に次のように新しい設定値の一行を加え、
Oracle を再起動します。ここでは OPTIMIZER_MODE を FIRST_ROWS に指定します。
OPTIMIZER_MODE = FIRST_ROWS
データベースを再起動しなくても現行セッションだけに有効な変更であれば、次のよう
にして行うことができます。
SQL> ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS;
セッションが変更されました。
また SQL 文毎に次のようにしてヒントを与えることもできます。
SELECT /*+ FIRST_ROWS */ EMPNO, ENAME FROM EMP;
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
27
4.3. 統計情報を収集する
コストベース・オプティマイザが最適な SQL 文の実行計画を作成できるよう、定期的に
データベースの統計情報を収集する必要があります。ここではその方法を説明します。
ANALYZE コマンド構文
表および索引の統計情報を収集するための ANALYZE コマンド構文を以下に示します。
• データ量の５パーセントを参照して統計情報を得る構文
ANALYZE TABLE 表名 ESTIMATE STATISTICS SAMPLE 5 PERCENT;
• すべてのデータを参照して統計情報を得る構文
ANALYZE TABLE 表名 COMPUTE STATISTICS;
• 収集した統計情報を削除する構文
ANALYZE TABLE 表名 DELETE STATISTICS;
COMPUTE STATISTICS 句ですべてのデータを参照する場合、表のデータ量によって
はたいへん時間がかかることがあります。実行時間を早めたいときは、
ESTIMATE STATISTICS
句を使用し 5%程度の統計情報を収集しておけば良いでしょう。
またデータに著しい偏りがあるようなときは次のようにしてヒストグラムを使用すると
良いでしょう。
• すべてのデータを参照して統計情報を取得し、INDEX のついているすべての列につい
てバケット数 10 のヒストグラムを作成する構文
ANALYZE TABLE 表名 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS SIZE 10;
例えば、次のようにしてユーザーSCOTT の EMP 表およびそれに付けられた索引の統計
情報を収集することができます。
SQL> connect scott/tiger
接続されました。
SQL> analyze table emp
2
estimate statistics sample 5 percent;
表が分析されました。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
28
ANALYZE スクリプト
すべての表の一つ一つについて ANALYZE コマンドを手動で発行するのは大変です。そ
こであるスキーマのすべての表に関して ANALYZE コマンドを発行できるスクリプト
（ANALYZE.SQL）を以下に紹介します。
/* analyze.sql */
set heading off
set feedback off
/* analyze 実行用のスクリプトを作成します */
spool c:¥orant¥script¥do_analyze.sql
select
'analyze table ' ￨￨ table_name ￨￨
' estimate statistics sample 5 percent;'
from user_tables where cluster_name is null;
spool off
set heading on
set feedback 6
/* analyze 実行用のスクリプトを実行します */
@c:¥orant¥script¥do_analyze
このスクリプトを使用し、例えば次のようにしてユーザーSCOTT のすべての表およびそ
れに付けられた索引の統計情報を収集することができます。
SQL> connect scott/tiger@dms805
接続されました。
SQL> @c:¥orant¥script¥analyze
analyze table DEPT estimate statistics sample 5 percent;
analyze table EMP estimate statistics sample 5 percent;
・・・・
analyze table SALGRADE estimate statistics sample 5 percent;
表が分析されました。
表が分析されました。
・・・・
表が分析されました。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
29
ANALYZE_SCHEMA プロシージャ
Oracle が提供する DBMS_UTILITY パッケージの ANALYZE_SCHEMA プロシージャ
を使用して特定スキーマ中のすべてのオブジェクトの統計情報を収集することができます。
ANALYZE_SCHEMA( schema VARCHAR2,
method VARCHAR2,
estimate_rows NUMBER DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL );
パラメータ
データ型
説明
schema
VARCHAR2
統計情報収集の対象となるスキーマ
method
VARCHAR2
分析方法（NULL または’ESTIMATE’）
’ESTIMATE’と指定する場合は estimate_rows か
estimate_percent のいずれかがゼロ以外でなくて
はいけない。
estimate_rows
NUMBER
推定の対象となる行数
estimate_percent
NUMBER
推定の対象となる行のパーセンテージ
estimate_rows を指定すると、このパラメータは無
視される。
このプロシージャを使用し、例えば次のようにしてユーザーSCOTT のすべての表および
それに付けられた索引の統計情報を収集することができます。
SQL> connect system/manager@dms805
接続されました。
SQL> execute dbms_utility.analyze_schema('SCOTT', 'ESTIMATE', null, 5);
PL/SQL プロシージャが正常に完了しました。
本節で紹介したスクリプトやプロシージャをバッチジョブに取り込むなどして、定期的
に実行し、最新の統計情報をコストベース・オプティマイザに与えるようにしてください。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
30
コストベース最適化のまとめ
ルールベースとコストベース
• コストベース・オプティマイザを使用する
オプティマイザをコストベースに設定する
• 初期化パラメータ：OPTIMIZER_MODE = CHOOSE
統計情報を収集する
• ANALYZE コマンドを実行する
• スクリプトを使用する
• ANALYZE_SCHEMA プロシージャを使用する
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
31
付録 SHARED_POOL_SIZE の計算方法
ここでは付録として共有プールに必要なサイズ（SHARED_POOL_SIZE）の目安を計算
する方法を説明します。計算式を以下に示します。
ユーザー毎カーソル・メモリ × 同時ユーザー数
＋ セッション情報メモリ（MTS 使用時）
＋ 共有 SQL 領域
＋ ライブラリ・キャッシュ内オブジェクトの共有メモリ
＋ Minimum 30% の空き領域
--------------------------------------------------------＝Minimum 共有プール・サイズ
次のようにして実際に共有プール・サイズを計算します。
1. ユーザー毎カーソル・メモリを求めます。ここではテスト環境におけるユーザーDMS
のメモリを求めています。
SQL> select 250 * value from v$sesstat s, v$statname n
2
where s.statistic# = n.statistic#
3
and n.name = 'opened cursors current'
4
and sid = ( select sid from v$process p, v$session s
5
6
where p.addr = s.paddr and s.username = 'DMS')
/
250*VALUE
--------250
結果より、ユーザーDMS は 250 バイトを必要としていると分かります。
本稼動システムであれば、使用のピーク時に次の SQL 文を用いてすべてのユーザー用
のメモリを求めることができます。
select sum(250 * users_opening) from v$sqlarea;
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
32
2. MTS を使用している場合、次のようにしてセッション情報メモリを求めます。MTS
を使用していない場合、このステップは省略します。ここでは、テスト環境においてユ
ーザーDMS のセッション情報メモリを求めています。
SQL> select value from v$sesstat s, v$statname n
2
where s.statistic# = n.statistic#
3
and n.name = 'session uga memory max'
4
and sid = ( select sid from v$process p, v$session s
5
6
where p.addr = s.paddr and s.username = 'DMS')
/
VALUE
--------19860
結果より、ユーザーDMS のセッション情報メモリは約 20KB だと分かります。
本稼動システムであれば、次の SQL 文を用いてすべてのユーザーのセッション情報メ
モリを求めることができます。
select sum(value) from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max';
3. 共有 SQL 領域を求めます。
SQL> select sum(sharable_mem) from v$sqlarea
2
/
SUM(SHARABLE_MEM)
----------------3752624
結果より、共有 SQL 領域は約 4MB だとわかります。
ただし、動的 SQL を使用しているときは、次の SQL 文の結果を更に加えます。
select sum(sharable_mem) from v$sqlarea where executions > 5;
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
33
4. ライブラリ・キャッシュ内オブジェクトの共有メモリを求めます。
SQL> select sum(sharable_mem) from v$db_object_cache
2
/
SUM(SHARABLE_MEM)
----------------5629763
結果より、ライブラリ・キャッシュ内オブジェクトの共有メモリは約 5.5MB だとわかり
ます。
5. 予備として 30%程度の空き領域を求めます。
ここでは同時実行ユーザーは 100 人、
MTS
は使用していないものとします。ステップ 1∼3 の結果より空き領域のサイズは次のよう
に計算できます。
（ 250×100 ＋ 4M ＋ 5.5M ）× 0.3 ＝ 約 3MB
6. ステップ１∼4 の結果より共有プール・サイズを求めます。
250 バイト × 100 ユーザー
＋ 4MB 共有 SQL 領域
＋ 5.5MB ライブラリ・キャッシュ内オブジェクトの共有メモリ
＋ 3MB 空き領域（30%）
---------------------------------＝ 約 13MB
以上より、この例では約 13MB の共有プールが必要となることがわかります。
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
34
おわりに
本書では、Windows NT 版 Oracle のパフォーマンス・チューニングのポイントを次に挙
げる項目に分けて解説しました。
• Windows NT の設定
• Oracle パラメータの設定
• 設定したパラメータの最適化
• コストベースでの最適化
ここで取り上げた項目以外にも、ロールバック・セグメントやテンポラリ・セグメント
の設定を見直すこともパフォーマンス向上につながる可能性があります。ロールバック・
セグメントやテンポラリ・セグメントの設定については Oracle のマニュアル、または DMS
の技術資料「領域管理∼トラブル防止のテクニック∼」等を参考にすることをお勧めしま
す。
同様に、本書ではあまり詳しく触れない点についてお知りになりたい方は、Oracle のマ
ニュアル、または、本文中に紹介した DMS の技術資料等を参考にしてください。
本書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることが
あります。この文章に関連して不都合が生じた場合も、米国オラクル社および日本オラク
ル株式会社は一切保証せず、特に責任は負いかねますのでご容赦ください。また許可なく、
改編、引用することを禁じます。
1999 年 6 月 初版
日本オラクル株式会社 テクノロジーセンター Design & Migration Services グループ
Copyright© ORACLE CORPORATION JAPAN 1999
Oracle8 簡単チューニング for Windows NT
Design & Migration Services
35