パフォーマンスダウンを「再現待ち」にしないための準備

この記事は JPOUG Advent Calendar 2015 の15日目のエントリです。

アシストのサポートセンターには「パフォーマンスダウンが発生したので原因を教えてほしい」というお問い合わせをいただくことがありますが、原因特定をする上で調査に必要な情報が不足しているケースが多く、解決が非常に難しいお問い合わせの一つです。

調査のためにはサーバ、データベース、プロセスそれぞれの情報を普段から取得している必要があります。今回はパフォーマンスダウンが発生してしまった際に、原因特定の確率を上げ、再発を防ぐための網羅的な情報取得方法を紹介します。

「パフォーマンスダウンが発生した」というお問い合わせをいただいた際には、まず何が遅くなったのかを切り分けるところから始めます。大別すると次のような発生状況が考えられます。

・サーバレベルでパフォーマンスダウン（OSコマンドの応答に時間がかかる）
・データベースレベルでパフォーマンスダウン（あらゆるSQLの応答に時間がかかる）
・特定のSQLレベルでパフォーマンスダウン（他のSQLは問題ない）
・接続レベルでパフォーマンスダウン（データベースに繋がるまでに時間がかかる）

サーバやデータベースのレベルでパフォーマンスダウンが発生していた場合、サポートセンターにはサーバ再起動などで対処された後に原因特定のお問い合わせをいただくケースが大半を占めます。発生時の状況を伺い、その上で当時の状況が確認できる情報の提供を依頼しますが、実際に必要な情報が揃っていることは稀です。

情報が不足していると次回発生時に情報取得を行うことになるため、パフォーマンスダウンを再発させなければなりません。以下4つの情報を普段から取得いただくことで、再起動後でも原因特定に至る確率が上がります。

Oracle DatabaseもOS上で動くアプリケーションの一つです。パフォーマンスダウンの原因がサーバリソースの競合にあるケースは少なくありませんので、サーバリソースの監視が必要です。データベースのパフォーマンスダウンが発生したというお問い合わせの原因には、発生時間帯にシステムのバックアップやウイルス検知のソフトが動いており、負荷を上げていた（データベースは被害者だった）といったケースも多くあります。

Linux系OSであれば次のコマンドを定期的に実行し、それぞれログにリダイレクトします。この際、必ず時間もセットで取得します。vmstatのようなサーバ全体の情報だけでなく、OS上の各プロセスがCPUやメモリなどをどの程度使用しているのか、ps／topから確認できるようにします。

・date;vmstat
・date;ps -elf
・top

これらの情報は取得間隔が短ければ短いほど問題発生時間に近い情報を確認できるため、5分（可能であれば1分）以内の間隔で取得いただきたい情報です。取得負荷はほぼないと思いますが、psコマンドの出力量はご利用の環境に依存しますので、いきなり定期実行するのではなく、一度コマンドを実行いただき、負荷と出力量を考慮した上で許容可能な取得間隔と保持期間を検討します。

オラクル社からも OSWatcher Black Box というツールが提供されています。こちらを使用することでご利用のOSに合わせた定期的な情報収集／管理を自動化できます。

データベースレベルでのパフォーマンスダウンが発生した際には、発生前と比較してブロック読み込み量、トランザクション数、メモリの使用状況や待機イベントの傾向などに差異がないかを確認します。

Enterprise Edition ＋ Diagnostics Packのライセンスがあれば AWR を使用して確認できますが、AWRスナップショットのデフォルト保持期間は12cでは8日間です（DBA_HIST_WR_CONTROL.RETENTION列で確認できます）のでサポートにお問い合わせをいただいた時には情報がないということも良くあります。定期的にレポート出力しておくなどの対応をご検討ください。

Enterprise Edition ＋ Diagnostics Packのライセンスがない環境ではSTATSPACKを使用して確認します。STATSPACKはスナップショットのレベルごとに取得できる情報が異なります。デフォルトはLEVEL 5ですが、LEVEL 6で取得することでSTATSPACKスナップショットに含まれたSQLの実行計画を確認できます。

特定のSQLのパフォーマンスダウンが発生した際など、当時の実行計画を確認したいようなケースで重宝しますので、取得負荷が許容の範囲に収まるのであればLEVEL 6での定期取得を検討します。
※LEVEL 10は非常に負荷が高いため、通常の運用で取得することはありません。

STATSPACKスナップショットは自動でローテートされないため、定期的にメンテナンスを行わないと格納領域の肥大化、またはデータファイルの拡張エラーが発生します。STATSPACKをインストールする際には専用の表領域を作成／指定し、定期的なレポート出力と削除をご検討ください。

サーバリソース監視によりデータベースのプロセスが負荷を上げている状況が確認できた場合、そのプロセスがどのセッションに紐付き、何をしているのかを確認するには動的パフォーマンスビューの情報を確認します。V$SESSION／V$PROCESSの結果を定期的にログにリダイレクトすることで、パフォーマンスダウンの原因となっているセッション（プロセス）や実行しているSQLの情報、接続元のクライアントなどが確認できます。

STATSPACKをLEVEL 6で取得し、スナップショット内に確認したいSQLが含まれていれば、V$SESSION.SQL_HASH_VALUEからそのセッションが実行していたSQLの実行統計や実行計画を確認できるため、調査の確度が上がります。

サーバで取得したリソース情報と突き合わせて確認をしますので、次の例のように日付の情報を含めた上で、取得の間隔はサーバのリソース監視情報と同じかそれよりも短い間隔で取得します。また、カンマ区切りで出力させることで、調査の際にExcelでのグルーピングや、SQL*Loaderなどを使用したデータベースへのロードが容易になります。データベースにロードできれば使い慣れたSQLでWHERE句で条件を絞った確認ができるようになるためお奨めです。

SELECT 
    ''||TO_CHAR (SYSDATE ,'YY/MM/DD HH24:MI:SS')||','||SADDR||','||SID||'…<略>…,'||ECID||''
FROM
    V$SESSION
ORDER BY SID;
　
--出力例
15/12/03 21:22:11,000007FF115E14D8,1…<略>…,
15/12/03 21:22:11,000007FF115DE668,2…<略>…,
15/12/03 21:22:11,000007FF115DB7F8,3…<略>…,
15/12/03 21:22:11,000007FF115D8988,4…<略>…,

出力量が多い場合はWHERE句で「STATUS='ACTIVE'」でアクティブセッションを対象とする、あるいは「EVENT NOT LIKE 'SQL*Net message%'」のようにアイドルイベントを除外するなど条件を絞ることで対応します。

統計情報の取得はDBMS_STATSとANALYZEのどちらかで行えます。もしANALYZEで取得をされているのであれば DBMS_STATS パッケージで取得するよう変更します（ANALYZEは下位互換のために残されており、推奨はDBMS_STATSです）。DBMS_STATSで統計情報を取得した場合、デフォルトの設定では31日前までリストア可能なように、SYSAUX表領域に以前の統計情報の履歴が保持されます。

意図しないSQLのパフォーマンスダウンが発生し、実行計画の変化による影響が疑われる場合には、過去の統計情報にリストアすることで改善が見られるかの切り分けが早期解決に繋がる可能性があります。

--LAST_ANALYZED を確認
SQL> SELECT TO_CHAR(LAST_ANALYZED,’YYYY-MM-DD HH24:MI:SS') FROM DBA_TABLES
  2  WHERE OWNER='SCOTT' AND TABLE_NAME='EMP';　
　
LAST_ANALYZED
-------------------
2015-11-25 15:53:13
　
　
--統計情報の履歴を確認
SQL> SELECT OWNER, TABLE_NAME, STATS_UPDATE_TIME
  2  FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='EMP';　
　
OWNER      TABLE_NAME STATS_UPDATE_TIME
---------- ---------- ---------------------------------------------
SCOTT      EMP        15-11-17 16:50:00.228573 +09:00
SCOTT      EMP        15-11-17 17:17:03.531041 +09:00
SCOTT      EMP        15-11-25 15:53:13.373593 +09:00　
　
　
--パフォーマンスダウン発生前の統計に戻す（戻したい統計が使われていた時間を指定）
SQL> exec DBMS_STATS.RESTORE_TABLE_STATS(-
     ownname=>'SCOTT',tabname=>'EMP',as_of_timestamp=>'15-11-17 17:20:00.000000');　
　
PL/SQLプロシージャが正常に完了しました。　
　
　
--過去の統計に戻っていることを確認
SQL> SELECT TO_CHAR(LAST_ANALYZED,’YYYY-MM-DD HH24:MI:SS') FROM DBA_TABLES　
  2  WHERE OWNER='SCOTT' AND TABLE_NAME='EMP';　　
LAST_ANALYZED
-------------------
2015-11-17 17:17:03
 

なお、表の作成や更新処理が多く、 自動オプティマイザ統計収集 により多くの表で統計情報が収集される環境では、意図せずSYSAUXが肥大化するケースがあります。手動で統計情報の履歴を削除する、または、リストアする可能性を考慮して保持期間を31日から変更するなどの対応が必要ですので、ご利用環境のSYSAUX表領域が大きなサイズになっている場合には、一度サポートにお問い合わせください。

今回紹介した情報を取得いただくことで、パフォーマンスダウンが発生した際に原因特定に至る確率は上がります。トラブルは起きないことが一番ですが、起きてしまった時に原因追求ができるよう準備をしておくことが再発防止の観点で重要です。

実際のサポート対応では「データベースで全体的にパフォーマンスダウンが発生した。vmstatを定期的に取得している。該当時間帯ではCPU使用率が高かったようだ。」といった状況報告をいただくケースがあります。確かにvmstatからはパフォーマンスダウンが発生していた時間帯でCPU使用率が高い様子は確認できますが、どのプロセス／処理が負荷を上げていたのかまではわかりません。

そのため、プロセス単位の情報や該当時間帯のデータベースの稼働状況がわかる情報も取得していないと、該当時間帯に動作しているジョブなどからの推測と「再現時に備えた情報取得」を依頼する回答に留まります。

この「再現時に備えた情報取得」は、その環境に不利益が発生することを承知の上で「もう一度その問題を発生をさせてください」とお願いしていることと同じですので、個人的にサポートをする上で最も避けたい回答の一つです。

2015年のDatabase Support Blog（Oracle Database）は本記事が最後ですが、2016年もよくあるトラブルを未然に防ぐ／再発させない方法を紹介していくことで、データベースを管理される方のお役に立てればと思っています。

それでは、少し早いですが「メリークリスマス＆良いお年を！」

2021年 18日目
Oracle Databaseのサポート対応で依頼することの多いファイルとコマンド
https://www.ashisuto.co.jp/db_blog/article/jpoug-oracledb-logfiles-command.html

2020年 17日目
Oracle Databaseバージョンアップ後の性能劣化で試したい暫定対処
https://www.ashisuto.co.jp/db_blog/article/jpoug-vup-temp-solution.html

2019年 24日目
【Oracle Database】DMLリダイレクションで一歩進んだ Active Data Guard の使い方（19c新機能）
https://www.ashisuto.co.jp/db_blog/article/20191224_ADG_REDIRECT_DML.html

2019年 20日目
【Oracle Database Cloud】"超"現実的に考える！Oracle CloudへのDB移行ガイド
https://www.ashisuto.co.jp/db_blog/article/20191220_cloud.html

2017年 14日目
Oracle Database 12cR2へのアップグレード後に発生するORA-01017
https://www.ashisuto.co.jp/db_blog/article/201712-ora-01017.html

2016年 9日目
【Oracle Database】2016年にサポートにお問い合わせをいただいたORAエラー TOP5
https://www.ashisuto.co.jp/db_blog/article/2016_oerr_rank.html

執筆者情報

• 大野 高志

サービス事業部　サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されている調査のノウハウを社内外に伝える活動を行っている。