Database Support Blog

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

  • Oracle Database
2015.12.15

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

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

この記事は 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に合わせた定期的な情報収集/管理を自動化できます。

AWR/STATSPACK

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

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スナップショットのレベルと収集される情報
基本統計 アドバイス情報 SQL統計情報 SQL詳細情報 セグメント
詳細情報
ラッチ詳細情報
Level 0
Level 5
Level 6
Level 7
Level 10



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

V$SESSION/V$PROCESS

サーバリソース監視によりデータベースのプロセスが負荷を上げている状況が確認できた場合、そのプロセスがどのセッションに紐付き、何をしているのかを確認するには動的パフォーマンスビューの情報を確認します。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年もよくあるトラブルを未然に防ぐ/再発させない方法を紹介していくことで、データベースを管理される方のお役に立てればと思っています。

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

執筆者情報

大野 高志

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

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

関連している記事

  • Oracle Database
2016.09.21

再現ケースを簡単に作成!SQLテスト・ケース・ビルダーの使用方法

再現ケースに必要な情報を一括で容易に取得できる「SQLテスト・ケース・ビルダー」の使用方法を紹介します。

  • Oracle Database
2016.08.31

DataGuard構築後に使うSQLコマンドまとめ(逆引き)

DataGuard構築後に使うSQLコマンドを逆引きでまとめました。DataGuardを構築した方、これから構築する方は押さえておきましょう。トラブル発生時に焦らないように。

  • Oracle Database
2016.08.12

仮想列関連エラーORA-54032/ORA-54033の原因と対処方法

仮想列に関するエラー(ORA-54032、ORA-54033等)が発生した際の対処方法をご紹介します。Oracle Database 12cでは仮想列が自動で作成されるため、このようなエラーにつながるケースがあります。

カテゴリ一覧

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る