STATISTICSオプションを指定したSQLコマンドANALYZEを使用することによって、表・索引・クラスタの物理記憶特性を収集・保存することができる。データディクショナリに保存された統計データは、各種のビューを通じて参照することができる。なお、分析を行うためには、そのオブジェクトを所有しているか、あるいはANALYZE ANYシステム権限が必要である。
SQLコマンドANALYZEの統計情報収集のための書式は以下の通りである。
ANALYZE [オブジェクト種類] [オブジェクト名] COMPUTE STATISTICS;
この構文は、指定したオブジェクト全体を走査し、正確な統計を作成する。
ANALYZE [オブジェクト種類] [オブジェクト名] ESTIMATE STATISTICS
{SAMPLE ([数値] ROWS | [数値] PERCENT)}
この構文は、指定したオブジェクトの一部をサンプリングし、、統計見積もりを作成する。SAMPLEで使用するサンプルの量を件数または百分率で指定できるが、デフォルト値は1064行である。なお、サンプルの量を全体の半分以上に指定した場合は、サンプリングではなく全件走査が行われる。
また、同等の作業を行うプロシージャも定義されている。
なお、統計分析を行うことにより、該当オブジェクトを参照する共有SQL文はメモリからフラッシュされる。
ANALYZEコマンドによって収集できる情報は以下のようなものである。
| 種別 | 表 | 索引 | クラスタ |
|---|---|---|---|
| 必ず正確な情報 | 使用ブロック 未使用ブロック 列の最大値 列の最小値 |
索引レベル 最小キー値 最大キー値 |
(なし) |
| 見積もり情報 | 行数 利用可能空き領域 連鎖行数 平均行長 列あたりユニーク値数 |
リーフ・ブロック数 ユニークキー数 キー当りの平均リーフ・ブロック数 キー当りの平均データ・ブロック数 クラスタ化係数 |
平均クラスタ・キー連鎖長 |
| 使用するビュー | (USER|ALL|DBA)_TABLES, (USER|ALL|DBA)_TAB_COLUMNS |
(USER|ALL|DBA)_INDEXES | - |
SGAは、特殊な理由が無い場合、常に主メモリー内に保持されることが保証されねばならない。SGAと個々の内部構造への割り当てメモリ量は、Server Manager文SHOW SGAにて確認できる。
ライブラリ・キャッシュは、SQL文の実行のための解析情報を保存・共有するものである。この利用統計は、動的パフォーマンス表v$librarycacheから得ることができる。
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses" FROM v$librarycache;
キャッシュミスの率が1%を超える場合、共有プールサイズにより大きなメモリを割り当てるようにするか(SHARED_POOL_SIZEの値を大きくする)、同一のSQL文を書く(可能なときはバインド変数を使う)ことで、キャッシュミスを減らすようにする。
ライブラリ・キャッシュミスが無い場合、初期化パラメータCURSOR_SPACE_FOR_TIMEをTRUEに指定することにより、アプリケーションが使用中の解析情報をキャッシュアウトしなくなるので、そのための時間がわずかながら節約できる。
データ・ディクショナリ・キャッシュは、データ・キャッシュのインデックスの役割を果たす。この利用統計は、動的パフォーマンス表v$rowcacheから得ることができる。
SELECT SUM(gets) "Gets", SUM(getmisses) "Cache Misses" FROM v$rowcache;
キャッシュミスの率が10%〜15%を超える場合、共有プールサイズにより大きなメモリを割り当てるようにする。
マルチスレッド・サーバ構成では、セッションごとのSQL領域やソート領域は共有プール内に格納される。この利用統計は、動的パフォーマンス表v$sesstatから得ることができる。
SELECT name, SUM(value) || 'bytes' "total memory for"
FROM v$sesstat, v$statname
WHERE name IN ('session memory','max session memory')
AND v$sesstat.statistic# = v$statname.statistic#
GROUP BY name;
ここで得られたメモリ量は、共有プールのサイズを見積もるときの目安となる。
バッファ・キャッシュは、メモリ上に持たれるデータベースの最新イメージである。この利用統計は、動的パフォーマンス表v$sysstatから得ることができる。
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets','consistent gets','physical reads');
この問い合わせから得られる結果を元にして、バッファヒット率を以下のように計算する。
ヒット率 = 1 - ([physical reads] / ([db block gets] + [consistent gets]))
ヒット率が70%を下回るほど小さい場合には、初期化パラメータDB_BLOCK_BUFFERSの値を増やす。
また、ORACLEにはキャッシュ・バッファの利用率を見積もるための仮想表sys.x$kcbrbhが用意されている。これは、キャッシュに追加するバッファ数と、それによって得られるキャッシュ・ヒット数を表示するものである。これを有効にするためには、DB_BLOCK_LRU_EXTENDED_STATISTICSの値を追加を考える最大のバッファ数に設定する。また、初期化パラメータDB_BLOCK_LRU_STATISTICSをTRUEとし、仮想表sys.x$kcbcbhを用いることで、バッファ割り当てを減らした場合の影響を調べることもできる。なお、これらの表には、sysユーザしかアクセスできない。
ORACLEによるディスク・アクティビティは、動的パフォーマンス表v$filestatから得ることができる。
SELECT name, phyrds, phywrts FROM v$filestat fs, v$datafile df WHERE df.file# = fs.file#
この問い合わせを何度か行い、その時間間隔も求めて、時間当りのI/O発生率を求める。このI/O発生率が大きい場合(たとえばUNIXならディスク当り40/sec以上)場合は、I/Oの分散を検討する必要がある。
I/Oの分散方法には以下のようなものがある。
使用中のテーブルに別のデータファイルのエクステントを割り当てるためには、以下のようなコマンドを使用する。
ALTER TABLE [テーブル名]
ALLOCATE EXTENT ( SIZE [サイズ] DATAFILE [ファイル名]);
発行するSQL文が、実際にどれだけのCPU時間や物理・論理読み込みを行ったかということは、SQLトレース機能を用いて判別できる。この機能を使用するには、以下の手順を実行する：
また、SQL文がどのように実行されるかということは、EXPLAIN PLAN文で分析する。
VARCHARデータ型などにより行の長さが不定の場合、UPDATE文が行の長さを変えることにより、行が元のデータブロックに保持できなくなることがある。この場合、その行は別のデータブロックに保存されることになり、それを「行の移行」という。また、行の長さが利用可能なブロックに収まらない場合、その 行は複数のブロックに分割して保存されることになる。これを「行の連鎖」という。
行連鎖・行移行は、LIST CHAINED ROWSオプションを使用したANALYZE文で調査できる。その利用方法は以下の通り。
行連鎖・行移行が大量にある場合には、以下のプロセスを実行してそれらを取り除く。