導入・構築事例 サポートサービス  パフォーマンストラブルの解消 [バージョンアップによる実行速度低下の改善]

Oracleソリューションサービス 専用サイト TOP導入・構築事例 > サポートサービス パフォーマンストラブルの解消[バージョンアップによる実行速度低下の改善]

「複合ビューのマージ機能」の影響を確認し、対策を施しました。

パフォーマンストラブルの解消[バージョンアップによる実行速度低下の改善]

事象
R8.1.6.3 ⇒ R9.2.0.6へバージョンアップをしたところ、実行が遅くなった SQLがみつかりました。以前のパフォーマンスにするにはどのようにすればいいでしょうか。
事象
はじめにいただいた問い合わせ内容だけでは、原因が特定できないので状況確認と情報の提供の依頼をしました。
ページトップへ
お客様の状況確認
現象を確認した表の処理件数
XX表:約11万件
ページトップへ
情報提供依頼
  • SQL*PLUSの実行計画
    移行前(8.1.6.3)と移行後(9.2.0.6)
  • 再現可能な該当表
    表(ビュー表、GROUP BY 使用)、索引の定義を提供
ページトップへ
実行計画(1/2)
<ユーザ SCOTT での実行例> (AUTOTRACE) *1…SET AUTOTRACE ONと指定した場合は、実行計画、統計情報およびSQL文の結果のすべてを表示します。SET AUTOTRACE ON EXPLAINと指定した場合は、実行計画とSQL文を表示します。
*2…SET AUTOTRACE OFF を実行するまでは、そのセッションで実行されるSQL 文の実行計画と実行時の統計情報が画面に表示されます。


SQL*PLUSから実行計画を確認する方法は、AUTOTRACEという方法を用いて確認を行います。AUTOTRACEは、SQL文に対する実行計画が自動的にSQL*PLUSの画面に出力されるため、トレースファイルの変換を必要とせず簡単に実行計画を確認します。
ページトップへ
実行計画(2/2)
実行計画の読み方
  • 整形したトレースにおいて
    -各ステップ(実行計画の各行)が行ソースを意味します。
    -木構造になっています。
    -同じ深さにインデントされたステップがあった場合は上位に表示されているステップが先に実行されます。
    -リーフ・ステップ(一番インデントの深いステップ)のうち、最上位に表示されたものが一番始めに実行されます。[topmost leaf]
    <例>実行計画が索引を使用せず、全表走査を選択した場合は次のとおりになります。
    SQL> set autotrace on explain
    SQL> select first_name from employees where first_name like 'John';
実行計画(2/2)
autotraceを使った、あるSQL文の実行計画の例です。 この場合、2行あるうちの下の行ソースの方がインデントが深いので、まず初めに実行されます。
また、この実行計画をみていきますと、TABLE ACCESS(FULL)つまり全表走査が選択されてることもわかります。
今回は、検索列に索引が設定されていますが、索引が使われなかったとうことが、この実行計画から確認できます。
ページトップへ
センタでの調査
お客様からいただいた実行計画の確認
センタでの調査
お客様からいただいた実行計画を確認すると、オプティマイザは移行前も移行後もHINT句を使い、RBOで実行させています。
しかし、結合アルゴリズムが異なっていることが確認できました。移行前では、SORT MERGE JOINの結合が使用されており、移行後では、NESTED LOOPSの結合が使用されていました。
ページトップへ
結合アルゴリズム(1/2)
  • 結合アルゴリズム 結合条件に基づいて2つの行ソースを結合するアルゴリズムです。 結合アルゴリズムは、ネステッドループ結合、ソート/マージ結合、ハッシュ結合の3つの種類があります。
  • ネステッドループ結合
    -行ソース1をスキャンします。
    -行が1行返されるたびに行ソース2と照合します。
    -行を結合して返します。
結合アルゴリズム(1/2)
ページトップへ
結合アルゴリズム(2/2)
  • ソート/マージ結合
    -行ソース1の行を結合列でソートします。
    -行ソース2の行を結合列でソートします。
    -行ソース1と2は同時にスキャンできません。
    -両方のソートが完了するとマージされます。
結合アルゴリズム(2/2)
ページトップへ
センタでの調査
お客様と同じ環境で同じSQLを流し、実行計画を確認
センタでの調査
カスタマサポートセンタでも、移行前では、SORT MERGE JOINの結合、移行後では、NESTED LOOPSの結合、と、お客様と同一の現象を確認することができました。
ページトップへ
センタでの調査
お客様と同じ環境で同じSQLを流し、実行計画を確認
センタでの調査
お客様と同じ結果が出たため、2つの調査方針を決定しました。
ページトップへ
進捗状況
R8.1.6.3環境の「複合ビューのマージ機能」を使用可能に変更
進捗状況
ビュー表における複合ビューのマージ機能が影響した可能性があることがわかりました。そこで、移行前R8.1.6.3の環境の複合ビューのマージ機能を使用不可に変更したところ、以降後R9.2.0.6と同じ実行計画を得ることができました。
ページトップへ
ビューマージ
  • ビューマージ
    ビューに対する問い合わせを行うと、ビュー定義時に指定した元表に対するSELECT文に 書き換えられます。そのSELECT文をユーザが実行した問合せに組込みます。これにより、潜在的なアクセス・パスを発見できたり結合操作を行うことができる可能性があります。
  • ビューマージの方法
    -ユーザが実行した問い合わせビューにビュー定義の問い合わせを組み込む方法
    -ビュー定義の問い合わせにユーザが実行した問い合わせを組み込む方法
  • ビュー定義に以下の構文を使用している場合はビューマージを行いません。
    -GROUP BY
    -全てのグループ関数
    -ROWNUMの参照
    -STARTWITH/CONNECT BY
    -全ての集合演算子(UNION,MINUS…)
    ※GROUP BY,DISTINCT のいずれかを使用したビューは「複合ビューのマージ機能」が使用可能になっている場合にのみ、問い合わせにビューをマージすることができます。
ビューマージは、ビューに対する問合せを元表に直接問合せる文に変換し、オリジナルの問合せに組み込みます。今回は、この仕組みが影響して実行計画を変えていました。
ページトップへ
お客様への確認依頼
移行前と同じパフォーマンスに戻すために考慮した点
  • 移行前(R8.1.6.3)は「複合ビューのマージ機能」が使用不可能になっている。
  • SQLはRBOで実行が行われているため、CBOのヒント(MERGE/NOMERGE)を使うと、パフォーマンスに影響を及ぼす可能性がある。
  • 移行後(R9.2.0.6)環境の「複合ビューのマージ機能」を使用不可能に変更 移行後(R9.2.0.6)環境に対し、カスタマサポートセンタの指示した設定を行っていただき、移行前のパフォーマンスに戻るか確認していただく。
ページトップへ
お客様からの結果報告
移行後(9.2.0.6)の実行計画が 移行前(8.1.6.3)の実行計画と同じになること、実行速度も改善されたことを確認
ページトップへ
事象の原因
実行計画の差分は、 R8.1.6.3とR9.2.0.6で、オプティマイザの動作の設定が違うために発生していました。今回は、「複合ビューのマージ機能」の使用可能/使用不可能の設定が影響していました。
ページトップへ
チェックポイント
  • アップグレード前の情報収集
    -初期化パラメータの設定値
    -オブジェクト定義とオプティマイザ統計
    <SQLによる確認例>
    SELECT owner, table_name, degree, instances, cache, num_rows
       FROM dba_tables;
    SELECT owner, index_name, index_type, uniqueness, compress,
         degree, instances, distinct_keys
       FROM dba_indexes;
    -実行計画
    -統計情報
  • チェックリスト
    -初期化パラメータがアップグレード前と変更されていないか
    -削除/作成/再構築されたオブジェクトがないか
    -使用しているオプティマイザがアップグレード前と変更されていないか
    -CBO が使用されている場合、アップグレード後に統計情報収集が行われているか
    -実行計画がアップグレード前と異なっていないか
  • 一般的な対処方法
    -RBO を使用している場合は CBOに変更
    -(ANALYZE または DBMS_STATS による)統計情報の再収集
    -値に偏りのある列のヒストグラム統計の収集
    -以下のパラメータ調整による新しいオプティマイザ機能の無効化
       optimizer_features_enable
       star_transformation_enabled
       optimizer_index_cost_adj
       optimizer_index_caching
    -ヒントの使用による実行計画の調整
    -問合せ文の変更
ページトップへ
(C)Hitachi solutions, Ltd. 2013. All rights reserved.