パフォーマンス改善のために検討したいOracleのその他設定

はじめに

前回、前々回と初期化パラメータ、メモリチューニングについて記載しました。
今回は、Oracleのパフォーマンス改善のために、初期化パラメータ、メモリチューニング以外に検討したい内容について紹介したいと思います。

前提

Oracle12c R1以降のバージョンを前提にしています。
エディションは、Standard Edition2を想定しています。

AWRのスナップショットを停止する

AWRはパフォーマンス問題調査の際に、非常に有用なツールですが、Enterprise Edition(以下EE)でしか利用できません。
しかし、AWRを利用できないStandard Edition2(以下SE)環境でも、AWRのスナップショット取得処理は1時間毎(毎時0分)に実行されており、無駄にリソースが使われている(CPU、メモリ、SYSAUX表領域のサイズなど)状態になっています。

EE環境では有効にしておくべきもの(代わりにStatspackは使わない)ですが、SE環境では、AWRのスナップショットを停止しておく事をお勧めします。
停止するには、SYSユーザーで以下を実行します。

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);

※Oracleのバージョンが新しくなるにつれ、この他にもオプティマイザ関連や、SQLチューニング関連などでEEでしか利用できないタスクが追加になっていく可能性がある為、新機能を確認し、SE環境で不要なものがあれば、無効(停止)する事をお勧めします。

自動メンテナンスタスクの無効化

Oracleでは、以下3つのタスクがメンテナンスタスクとして実行されています。

1. 自動オプティマイザ統計収集
   データベース内に統計がないか、古い統計のみがあるすべてのスキーマ・オブジェクトに関するオプティマイザ統計を収集します。

2. 自動セグメント・アドバイザ
   再生可能な領域が存在しているセグメントを識別し、それらのセグメントの断片化を解消する方法について推奨事項を生成します。

3. 自動SQLチューニング・アドバイザ
   高負荷のSQL文のパフォーマンスを調査し、それらの文のチューニング方法について推奨事項を生成します。

1の自動オプティマイザ統計収集は有効にしておいた方がよいですが、2、3については無効にする事も検討するとよいと思います。
特に3についてはEE環境(+オプション)でしか利用できない為、SE環境では停止しておく事をお勧めします。

自動メンテナンスタスクの無効化

自動メンテナンスタスクを無効化するには、SYSユーザーで以下を実行します。

自動メンテナンスタスクの状態を確認

SQL> select client_name,status,window_group from dba_autotask_client;

CLIENT_NAME                      STATUS       WINDOW_GROUP
-------------------------------- ------------ --------------------
sql tuning advisor               ENABLED      ORA$AT_WGRP_SQ
auto optimizer stats collection  ENABLED      ORA$AT_WGRP_OS
auto space advisor               ENABLED      ORA$AT_WGRP_SA
SQL>

自動メンテナンスタスクの無効化(以下の例は自動SQLチューニング・アドバイザを無効化)

SQL> execute DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor',operation => NULL,window_name => NULL);

PL/SQLプロシージャが正常に完了しました。

SQL> select client_name,status,window_group from dba_autotask_client;
CLIENT_NAME                      STATUS       WINDOW_GROUP
-------------------------------- ------------ --------------------
sql tuning advisor               DISABLED     ORA$AT_WGRP_SQ
auto optimizer stats collection  ENABLED      ORA$AT_WGRP_OS
auto space advisor               ENABLED      ORA$AT_WGRP_SA

自動メンテナンスタスクのスケジュール変更

自動オプティマイザ統計収集のタスクは、デフォルトでは以下の時間から実行されるようになっています。

• 月～金：22時にタスクを開始し、最大4時間(2時まで)実行(それ以上かかる場合は中断)
• 土、日：6時にタスクを開始し、最大20時間(翌日2時まで)実行(それ以上かかる場合は中断)

上記時間帯にOSが停止(再起動)していたり、バックアップや他システムとのデータ連携ジョブなどが実行されていたりすると、統計情報が取得されなかったり、統計情報と実データとの乖離が発生したり、期間内に統計の取得が終わらなかったりします。
システム運用状況に合わせ、適時メンテナンスタスクの実行時間帯を変更しておく必要があります。

自動メンテナンスタスクのスケジュール変更

自動オプティマイザ統計収集タスクの実行スケジュールを変更するには、SYSユーザーで以下の対応を行います。

自動オプティマイザ統計収集タスクのウィンドウ・グループ「ORA$AT_WGRP_OS」に属すウィンドウメンバーを確認

SQL> select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- -------------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW

7行が選択されました。

自動オプティマイザ統計収集タスクのウィンドウ設定を確認

SQL> select window_name,repeat_interval,duration,enabled,comments from dba_scheduler_windows;
WINDOW_NAME               REPEAT_INTERVAL                                                        DURATION      ENABLED    COMMENTS
------------------------- ---------------------------------------------------------------------- ------------- ---------- -----------------------------------------
MONDAY_WINDOW             freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00 TRUE       Monday window for maintenance tasks
TUESDAY_WINDOW            freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00 TRUE       Tuesday window for maintenance tasks
WEDNESDAY_WINDOW          freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00 TRUE       Wednesday window for maintenance tasks
THURSDAY_WINDOW           freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00 TRUE       Thursday window for maintenance tasks
FRIDAY_WINDOW             freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00 TRUE       Friday window for maintenance tasks
SATURDAY_WINDOW           freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00 TRUE       Saturday window for maintenance tasks
SUNDAY_WINDOW             freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00 TRUE       Sunday window for maintenance tasks
WEEKNIGHT_WINDOW          freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00 FALSE      Weeknight window - for compatibility only
WEEKEND_WINDOW            freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00 FALSE      Weekend window - for compatibility only

9行が選択されました。

デフォルトでは、月～金は毎日22時からデータベース全体の統計情報取得が開始され、最大4時間実行(統計の取得が全て終わらなくても終了)。
土、日は、朝6時からデータベース全体の統計情報取得が開始され、最大20時間実行するようになっています。

運用上、このスケジュールだと問題がある場合は、統計情報取得開始時間や実行期間を変更します。

自動メンテナンスタスクのスケジュール変更

以下をSYSユーザーで実行すると、FRIDAY_WINDOWの開始時間を23時に、期間を3時間に変更できます。
※ウィンドウ毎に設定を変更する必要があります

BEGIN
	DBMS_SCHEDULER.DISABLE(name => 'FRIDAY_WINDOW');
	DBMS_SCHEDULER.SET_ATTRIBUTE(
		name => 'FRIDAY_WINDOW',
		attribute => 'DURATION',
		value => numtodsinterval(3, 'hour'));
	DBMS_SCHEDULER.SET_ATTRIBUTE(
		name => 'FRIDAY_WINDOW',
		attribute => 'REPEAT_INTERVAL',
		value => 'freq=daily;byday=FRI;byhour=23;byminute=0; bysecond=0');
	DBMS_SCHEDULER.ENABLE(name  => 'FRIDAY_WINDOW');
END;
/

統計情報を独自のタイミングで取得する

Oracleの統計情報は、上述の自動オプティマイザ統計収集タスクで、毎日自動的に取得されますが、不定期に実行されるジョブが統計収集タスク実行後に実行され、統計と実データとの乖離が発生すると、パフォーマンス悪化の要因になってしまいます。

また、ジョブの実行により大規模なデータ更新があると、ジョブの実行中にも統計情報と実データとの乖離が発生し、ジョブ自体のパフォーマンスも悪化する事があります。

そのような場合には、自動オプティマイザ統計収集のタイミング以外で、ジョブの実行中(30分おきなど)やジョブ実行後に統計情報の取得を行うようにすると、ジョブ実行中やジョブ実行後のシステムのパフォーマンス悪化を防ぐ事ができる場合があります。

独自のタイミングでの統計情報の取得は、データベース全体だと時間がかかる可能性があるので、システムに関係するスキーマ単位で行うのがよいと思います。

cronやOracleのスケジューラープロシージャなどから、システムが利用するスキーマで以下を実行する事で、統計情報の取得ができます。

SQL> execute dbms_stats.gather_schema_stats(ownname => null, cascade => true, options => 'GATHER AUTO', no_invalidate => false);

※no_invalidate => falseを指定すると、取得しなおした統計情報と関連があるSQLカーソルが無効化されます。
統計情報を取得しても、共有プールにキャッシュされているSQLカーソルが、古い統計情報を基にした実行計画で動作していると、統計情報を取得しなおしても、SQLのパフォーマンスが改善されない場合があるため、このパラメータはfalseにしておくとよいと思います。

KEEPバッファ・キャッシュにキャッシュする

あくまで暫定的な対処ではありますが、キャッシュからエージアウトしてしまうとパフォーマンス的に困るオブジェクトをKEEPバッファにキャッシュする事で、パフォーマンス悪化が防げる場合があります。

前提として、初期化パラメータ「DB_KEEP_CACHE_SIZE」で、KEEPバッファ・プールのサイズを指定しておき、メモリを確保します(キャッシュしたいオブジェクトが格納できるサイズにする必要があります)。

オブジェクトをKEEPバッファにキャッシュさせたい場合は、オブジェクトの設定で指定します。
キャッシュできるオブジェクトは表や索引などで、CREATE文、ALTER文で指定します。
以下に構文を記載します。

[CREATE | ALTER] TABLE [テーブル名] STORAGE(BUFFER_POOL KEEP);
[CREATE | ALTER] INDEX [索引名] STORAGE(BUFFER_POOL KEEP);

KEEPバッファに実際にキャッシュされるのは、対象オブジェクトがフルスキャンされたり、オブジェクトに関する統計情報が取得されたタイミングです。
また、キャッシュ対象オブジェクトのサイズが増大し、KEEPバッファに格納しきれなくなった場合は、バッファサイズを増やさないと別ブロックデータで上書きされる点に注意が必要です。

表、索引の断片化の解消

ある表に対して、データの追加、更新、削除が繰り返し実行されていくと、表や索引の断片化が発生し、全表スキャンや索引の効率が悪化していく可能性があります。
これに対する対処としては、表の圧縮、MOVE、expdp/impdp、索引の再構築、圧縮、再作成などがありますが、本番環境で断片化有無を確認したり、再構築などを行う事は、運用上難しい事があります。
ここでは、参考情報へのリンクを以下に記載するのみとします。

データベースファイルの設定調整

自動拡張設定

自動拡張をONにする事によるオーバーヘッドを気にする議論もありますが、過去私が関わった環境では、10年以上自動拡張ON(拡張時は100MB毎増加)で、数万名規模のお客様でも運用していましたが、特に影響はありませんでした(個人見解なので、他でもあてはまる保証はありません)。

なので、個人的には自動拡張をONにしても問題はないと考えますが、OFFにするのであれば、始めからデータファイルの最大サイズ(8KBブロックであれば、約32GB)で作成しておく方が、運用が効率よくなるため、検討してもよいのではないかと考えます。

配置場所

SSD > SAS > SATAのように、データファイルはなるべくI/O性能が高いストレージに配置するようにします。
AWS環境などでも、コストとの兼ね合いにはなりますが、gp2、io2といったIOPSの高いEBSボリューム上にデータファイルを配置するようにします。

RAIDを構成する場合はRAID1+0で構成されているLUNに配置するようにします。
※ただし、ソフトウェアRAIDは遅いのでお勧めしません。

オンラインREDOログのサイズ

オンラインREDOログファイルには、Oracleデータベース内で行われた変更が全て記録されています。
1つのロググループのログファイルがいっぱいになると、次のロググループにスイッチしていき、全てのロググループのファイルがいっぱいになると、古いものから上書きされていきます。

バッチ処理などで、大量の更新処理が行われると、ログスイッチが頻繁に発生し、ログスイッチ待ちにより、動作が遅くなる場合があります。
このため、数秒、数十秒毎にログスイッチが発生するといった状況になっている場合は、オンラインREDOログファイルのサイズを大きくし、必要に応じてロググループ数を増やす事を検討してください。

最近では、オンラインREDOログファイルのサイズを512MB、1024MB程度にしても、特に影響なく動作しますが、検証を行い、最適なオンラインREDOログファイルのサイズに調整する事をお勧めします。

OS、ストレージ設定

Windowsサーバーの設定

サーバーの役割を「アプリケーション・サーバー」とする事で、Oracle(Tomcatなども)の動作に最適なOS設定になります。
ただし、Windows Server 2016以降では、デフォルトで有効になっている為、最近のWinodwsでは気にする必要はありません。

その他、スクリーンセーバーはOFFにする、電源設定(Highパフォーマンス設定)、不要なサービスは止めておくなど、細かい点はありますが、Windowsの場合は、あまり設定を気にしなくてもよいかもしれません。

Linuxサーバーの設定

• カーネルパラメータのチューニング(特にHUGE PAGES設定)
• EXT4、XFSなどの書き込みバリア設定をOFFに ※検証が必要ですが、OFFにしないと更新(書き込み)処理が遅くなる場合があります
• ランレベルは3に→Oracleインストール時以外はGUIを起動しない
• 余計なサービス、プロセスは起動しない(インストールしない)
  など

ストレージ

• ハードウェアRAID(RAID1+0)で構成する
• RAID5や6は検索性能はあまり落ちませんが、更新が遅くなります(コスパはいい)
• ソフトウェアRAIDは避ける(遅いので)
• Oracleのデータファイル領域をRAID1にするのはお勧めしない
• SSDなどIOPS性能が高いディスク(できれば、ちゃんとしたストレージ)を使う
• ファイバチャネル、インフィニバンドなど、コンピュータとストレージ間の転送速度が高速なもので構成する(コストはかかりますが、性能差は大きい)

アプリ運用での暫定回避

特定のSQLが非常に遅く、これにより全体のパフォーマンスに影響を及ぼしてしまう場合がよくあります。
一例として、調査の結果、パフォーマンスの悪化の要因が、履歴情報を保管している表への全表スキャンであり、この表のレコード件数の影響が大きく、索引などでの対処も効果が無い、もしくは緊急での対応が必要だったとします。

このような場合、パフォーマンス悪化の要因になっている表の履歴データを(必要に応じて別表などにバックアップの上)削除可能である場合は、履歴データの削除および統計情報の再取得をする事で、(扱う集合が小さくできるので)パフォーマンスを一時的に改善できる場合があります。
あくまで暫定策であり、一時的に問題が解消している間に何らかの恒久対策を行う必要がありますが、緊急の場合には、このような暫定対策も検討してみてはと思います。

アプリケーションサーバーの設定調整

アプリケーションサーバーの設定で、直接データベースのパフォーマンスに影響があるのは、コネクションプールの接続設定になります。

1APサーバーあたり、どの程度のDBセッション数にしたらいいかは、1セッションで実行されるSQLでどの程度DBサーバーに負荷(特にメモリ消費)がかかるかにもよりますが、これは、アプリケーションの実装により変動するため、一概にこの位の数がよいという事は難しいです。

本番環境に近いデータを用意した環境で、負荷テストを実施したり、本番環境でのアクティブセッション数やパフォーマンス状況を確認するなどして、最適な数を決める事が重要です。

Webサーバー、APサーバーのスレッド数(通常は、Webのスレッド数 > APのスレッド数 > DBのコネクション数になる認識)やサーバースペックの兼ね合いにもよりますが、過去私が関わっていたシステムでは、1APサーバーあたりのコネクション数は、本番環境で40～50程度が一番効率がよく、それ以上増やすと、DBサーバーのメモリ使用率が高くなり、高負荷時のパフォーマンスが悪くなる傾向がありました。

何れにせよ、皆さんが関わっているシステムの特性を把握し、効率のよい設定を見つけ出していただければと思います。

おわりに

今回は、メモリチューニング、初期化パラメータの設定以外で、Oracleのパフォーマンス改善のために検討したい点を紹介しました。