Oracleのパフォーマンスの最適化

※ポイント※
     Enterprise Managerのパフォーマンス関連ページ、オプティマイザ統計の収集や索引などの再構築が対象となる。
     Enterprise Managerの画面だけでなく、どのような状態のときにどのような調整が必要であるかという概念と対処コマンドを覚えておく。

 オプティマイザ統計 

オプティマイザとは、SQLの実行を最適化する機能である。
OracleサーバがSQL文を受け取ると、それをどのように実行するかについて解析を行う。そのときにSQL文の実行計画を決定するのがオプティマイザである。

索引を使うかどうか、結合するのであればどの順序で結合するかなどさまざまなパターンが検討され、最もコストの低い実行計画が使用される。
この実行計画を決定する過程で使用されるのが「オプティマイザ統計」である。オプティマイザ統計には、次のような要素が含まれている。

* 行数
* セグメントが使用しているデータブロック数
* 列の最小値、最大値、固有数

オプティマイザ統計はデータディクショナリに格納されている。レコードが更新されてもオプティマイザ統計は変更されない。

オプティマイザ統計は、明示的に収集する必要がある。
Oracle Database 10gではデフォルトで1日に1度、22:00~6:00の間に収集されるようになっているが、
DBMS_STATSパッケージを使用して手動で収集することもできる。

SQL> SELECT COUNT(*) FROM emp1;
COUNT(*)

14

SQL> SELECT num_rows FROM user_tables WHERE table_name='EMP1';
NUM_ROWS

14

SQL> INSERT INTO emp1 SELECT * FROM emp1;
14行が作成されました。
SQL> COMMIT;
コミットが完了しました。

SQL> SELECT COUNT(*) FROM emp1;
COUNT(*)

28

SQL> SELECT num_rows FROM user_tables WHERE table_name='EMP1';
NUM_ROWS

14

上記のように、実際のレコード数が変化しても、オプティマイザ統計は変化していない。次のように明示的に収集する。

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP1')
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT num_rows FROM user_tables WHERE table_name='EMP1';
NUM_ROWS

28

 無効なオブジェクトの対処 

索引とPL/SQLプログラム構造体を使用する際は、オブジェクトのステータスが有効である必要がある。
メンテナンス操作によってはステータスが無効になる可能性があり、対処が必要である。


・索引
DMLによるデータ変更では、そのテーブルに作成された索引内の索引エントリも同時に変更される。
しかし、テーブルをALTER TABLE … MOVEコマンドにて移動した場合、索引内の索引エントリはメンテナンスされない。

テーブルが移動したことにより、テーブル内のレコードのROWIDは変更されていても、
索引内の索引エントリが示すROWIDは古いROWIDのままであるため、無効な状態(UNUSABLE)となる。
無効な索引は、手動で再構築することが必要である。

SQL> SELECT status FROM user_indexes
2 WHERE index_name = 'EMP1_ENAME_IDX';
STATUS

VALID

SQL> ALTER TABLE emp1 MOVE TABLESPACE example;
表が変更されました。
SQL> SELECT status FROM user_indexes
2 WHERE index_name = 'EMP1_ENAME_IDX';
STATUS

UNUSABLE

SQL> ALTER INDEX emp1_ename_idx REBUILD;
索引が変更されました。
SQL> SELECT status FROM user_indexes
2 WHERE index_name = 'EMP1_ENAME_IDX';
STATUS

VALID


・PL/SQLプログラム構造体  
PL/SQLプログラム構造体(プロシージャ、ファンクション、パッケージ、データベーストリガー)は、
そのプログラム内からアクセスしているテーブルなどのオブジェクトが変更されると、ステータスが無効(INVALID)になる。

次回の実行時に自動的にコンパイルされる仕様にはなっているが、コンパイルが失敗した場合、プログラムの実行がエラーとなってしまう。

例えば、プロシージャの中でUPDATEしているテーブルの列が削除された場合、対象となる列が存在しないことになり、コンパイルエラーになってしまう。
このようなエラーを防止するうえでも、可能な限り手動でコンパイルすることが望ましい。


設問内容
回答 解説

下記のコマンドを実行するために必要な条件は?
SQL> ALTER TABLE emp SHRINK SPACE;
.表が自動セグメント領域管理の表領域に格納されていること
.表の行管理が有効化されていること
ALTER TABLE … SHRINK SPACEコマンドは、セグメントを縮小するコマンドである。
セグメントの縮小においては、既存レコードを移動する「圧縮」、最高水位標(High Water Mark)を下げて
空きブロックを解放する「解放」の2段階で操作が行われる。
圧縮によるレコードの移動が行われるとレコードのROWIDが変化する可能性があることから、
行管理の有効化(ALTER TABLE … ENABLE ROW MOVEMENT)が必要である。
圧縮操作の結果は、ビットマップブロックと呼ばれるブロック内に格納される。
ビットマップブロックが用意されるのは、自動セグメント領域管理の表領域である。

DBA_TABLESビューにおいて、
オプティマイザ統計が正常に収集されたことを
確認するために使用できる列は?
.行数
.最新分析時刻
.平均レコードサイズ
自動オプティマイザ統計収集情報やDBMS_STATSパッケージを使用した
オプティマイザ統計情報は、データディクショナリに格納される。
表に関するオブジェクト統計はDBA_TABLESビューで確認できる。
オプティマイザ統計情報を収集することで格納される代表的な列には次のものがある。
NUM_ROWS レコード数
BLOCKS 使用ブロック数
AVG_ROW_LEN 平均レコードサイズ
LAST_ANALYZED 最後に統計情報が収集された日付

Database Controlのパフォーマンスタブにある「セッション:待機中と実行中」で
「User I/O」が非常に多くなっているという結果が表示されている。
この問題を解決するために調整する必要があるものは?
.RAIDシステムの使用
.Automatic Storage Management(ASM)の使用
User I/Oが多いということで、ディスクI/Oの競合に問題があると考えられる。
この場合、ディスク競合の解決のためには、複数のディスクにまたがるように領域を確保していくストライピングが有効である。

ハードウェアやOSの側で行うのであれば、RAID(Redundant Array of Independent Disks)が有効である。
RAID構成のタイプによっては、ストライピングとともに、ミラーリングによる高いデータの信頼性を実現できる。

Oracle Database 10gからは、ASM(Automatic Storage Management)が提供されている。
ASMを使用すれば、データベースファイルの格納のためのストライピングとミラーリングを提供できる。

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License