導入・構築事例 サポートサービス  パフォーマンストラブルの解消 [アプリケーション処理時間の正常化]

Oracleソリューションサービス 専用サイト TOP導入・構築事例 > サポートサービス パフォーマンストラブルの解消[アプリケーション処理時間の正常化]

SQLトレースの調査結果をもとに該当事例や類似案件を調べ、確認作業を行いました。

パフォーマンストラブルの解消[アプリケーション処理時間の正常化]

事象
あるアプリケーションで 普段は数秒の処理時間であるが、突然10分かかるようになった。
事象
1つめの事象は、あるアプリケーションで、 普段は数秒で終わる処理が、突然10分もかかるようになりました。データやテーブルの定義は変えていません。何が原因で処理時間が長くなったのか教えてください。という問合せをいただきました。
はじめにいただいた問合せ内容だけでは原因が特定できないので、状況確認をおこない、情報の提供を依頼しました。
ページトップへ
お客様の状況確認
  • アプリケーションについて
    C言語やCOBOL
  • SQL*PLUS単体では?
    現象が発生し、特定の表で再現(MERGE文*使用)
  • データ量は?
    特にデータ量が多いというわけでもなく、データ入力も多くない
    *MERGE SQL文 : ソース表からデータを検索し、条件によって対象の表にINSERTやUPDATEを1つのSQL文にて行うことが可能な文。
    <SQL文の例>
     merge into employee e using temp_emp t
     on (e.empno = t.empno)
     when matched then update set e.sal=e.sal+t.sal
     when not matched then insert (empno,ename,sal) values(empno,ename,sal);
ページトップへ
情報提供依頼
  • 問題となっている SQL 文
  • SQL トレース(取得済み)
  • 実行している SELECT文の実行計画
ページトップへ
SQLトレースの見方(1/3)
  • SQLトレースで出力する情報
    (a)解析、実行、フェッチの各フェーズを行った回数
    (b)CPU 時間、経過時間
    (c)physical reads,logical reads でそれぞれ読んだブロック数
    (d)処理した行数
    (e)ライブラリ・キャッシュ・ミスの回数
  • 出力するレベル
    <インスタンス・レベル>
    初期化パラメータ: sql_tarace = TRUE
    <セッション・レベル>
    SQL> alter session set timed_statistics= TRUE; (上記(b)の情報を必要とする場合には必ず実行して下さい)
    SQL> alter session set sql_trace = TRUE;
    SQL> select ... (処理を実行)
    SQL> alter session set sql_trace = FALSE;
    SQL> exit
  • 出力する場所
    初期化パラメータ:USER_DUMP_DEST(udump) で指定したバス
  • TKPROF ユーティリティを使用し、SQLトレース・ファイルを整形することによってチューニングのための分析を行いやすくします
SQLトレースで出力する情報の取得方法としては、インスタンス・レベルとセッション・レベルのいずれかで取得することができます。ただし、インスタンス・レベルで取得をしますと、データベースシステム全体のSQLをトレースの取得対象にしてしまい、パフォーマンスに影響を与えてしまいますので注意が必要です。
また、SQLトレースは、初期化パラメータuser_dump_destで指定したパスに出力されます。 ただし、出力されたままのSQLトレースは、分析が困難です。 そこで、分析をしやすくするために、tkprofというユティリティを使用し、SQLトレースファイルを整形していきます。
ページトップへ
SQLトレースの見方(2/3)
  • TKPROFとは
    TKPROFでSQLトレースをフォーマットし見やすくします。取得した SQL トレースを TKPROF コマンドで実行すると、SQL トレースがわかり易くフォーマットされます。
  • TKPROFの実行方法
    % tkprof <トレース・ファイル> <任意のファイル名>
    (例: % tkprof orcl_ora_1234.trc tkprof.txt)
    ※TKPROF コマンドにはたくさんのオプションがあります。
ページトップへ
SQLトレースの見方(3/3)
TKPROF(sql_trace)で出力される項目の概要
SQLトレースの見方(3/3)
tkprofで出力された内容は、Parse , Execute , Fetch のように、3つの段階に分けられて出力されます。そして、それぞれのCPU時間や経過時間、処理された行数などをを出力します。
ページトップへ
センタでの調査
SQLトレースの確認(tkprof生成後)
センタでの調査
お客様から情報提供いただいたSQLトレースを確認してみました。 カスタマサポートセンタでは、早期解決を目指すため、まずはじめにこのSQLトレースの調査結果をもとに該当する事例や類似案件がないか確認する作業を行いました。
ページトップへ
進捗状況
早期解決を目指すため、該当する事例がないか確認
進捗状況
該当する事例を検索するには、弊社システムや、オラクル社サポートページを利用し検索を行いました。
しかし、今回は該当する事例や類似案件は存在しませんでした。
ただし、調査過程において、SQLでマージ文を使用するとコストベースオプティマイザが採用されることが判明しました。
ページトップへ
オプティマイザとは
  • オプティマイザとは
    SQL文を実行するために、Oracleは、最適な「実行計画」(アクセス・パス)を決定する手段です。SQL文からアクセス・パスを決定するために、2つのオプティマイザが提供されています。
  • オプティマイザの種類
    -ルールベース・オプティマイザ(以降、RBO)
    あらかじめ決められたアクセス・パスのランクを使用して、候補となるアクセス・パスの中で、最も上位にランクされているものを選択します。ユーザはSQL文の構成を変更することによって、望む実行計画を選択させることができます。
    -コストベース・オプティマイザ(以降、CBO)
    CBOは、アクセス・パスのコストを見積もり、コストが最も低いものを選択します。ユーザはヒントを使用することによって、望む実行計画を選択させることができます。CBOを使用する場合、表のデータの特徴を把握するための統計情報を提供する必要があります。統計情報は、ANALYZE文やDBMS_STATSパッケージを使用して取得します。
  • ヒント
    CBOが生成した実行計画が最適なものでない場合、ヒントによって次のような内容に影響を与えることができます。
    - 最適化アプローチ
    - 表アクセスのアクセス・パス
    - 結合順序
    - 複合ビューのマージ(MERGE/NOMERGE)
ページトップへ
お客様への確認依頼
CBOの場合、「統計情報がずっと以前にとられていた、統計情報が未取得である」と最適な実行計画を決定できないことがある。問題となっているSQL文も最適な実行計画を決定できていない可能性がある。
お客様への確認依頼
ページトップへ
お客様からの結果報告
  • 統計情報取得日の確認結果
    SELECT文の「LAST_ANALYZED」列に出力がなかったので統計情報は未取得
  • 統計情報の取得
    弊社からの指示により、統計情報を取得(ANALYZE文 or DBMS_STATSパッケージ)した結果、レスポンスが 10分⇒1秒未満に大幅に改善
お客様から、統計情報の最終取得日である(「LAST_ANALYZED」列)に値が出力されなかったという結果をいただきました。つまり、出力されなかったということは、統計情報は未取得であった、となります。 そして、センタから指示したANALYZEやDBMS_STATSで統計情報を取得してみたところ、レスポンスが大幅に改善されたとのことでした。
ページトップへ
事象の原因
MERGE文により処理時間が長くなっていました。MERGE文は9iからの新機能であり、CBO が使用されます。
現象発生前までのデータ量の変化などにより、CBOの実行計画が不適切となり、パフォーマンスが劣化したと考えられます。
ページトップへ
今後の対策
定期的に統計情報を取得(CBOが最適な実行計画を選択)してください。
・大量(データ量については明確な数値はございません)のデータ更新
・データの特徴が変更になった場合

一定期間(日/週ごとに1ヵ月間など)、統計情報を記録し、統計情報が 大きく変化する間隔をみつけてください。
ページトップへ
チェックポイント
  • 問い合わせのチューニング
    問い合わせのパフォーマンスが悪い原因は様々です。目標とする応答時間と現在の応答時間のギャップを明確にした上で、一般的には次のような点から確認していきます。
    - オプティマイザの設定は適切か
    - 有効な索引が利用されているか
    - コストベース・オプティマイザ(CBO)の場合関連オブジェクトの ANALYZE は行われているか

    SQLトレースと tkprof ツールを活用して SQL の実行計画を確認し、何が問題になっているのかを突き止めてそれを解決していきます。
ページトップへ
(C)Hitachi solutions, Ltd. 2013. All rights reserved.