PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

さみごみぶち
4 years ago
Views:

1 1

2 SQL Plan Management(SPM) 導入事例 ~ オラクルコンサルの現場から ~ 日本オラクル株式会社テクノロジーソリューションコンサルティング統括本部テクニカルアーキテクト本部 DB コアコンサルティング部プリンシパルコンサルタント鈴木健吾

3 以下の事項は弊社の一般的な製品の方向性に関する概要を説明するものですまた情報提供を唯一の目的とするものでありいかなる契約にも組み込むことはできません以下の事項はマテリアルやコード機能を提供することをコミットメント ( 確約 ) するものではないため購買決定を行う際の判断材料になさらないで下さいオラクル製品に関して記載されている機能の開発リリースおよび時期については弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります 3

4 Program Agenda SPM 概要プロジェクト事例 SPM 機能詳細最後に 4

5 本セミナーの内容に関する注意本資料に記載の機能詳細は製品仕様に関する正式な情報ではなくプロジェクトで調査をして把握した製品動作を記載していますそのため参考情報と考えてください実際に SPM を利用される際には技術検証は徹底してください DB のバージョンアップを行う際に必ずしも実行計画が変化して性能が低下するわけではありませんこのプロジェクトではお客様との打ち合わせを重ねて行くなかでアプリケーションの重要機能の性能低下が発生することを抑止したいというご要望に対してプロアクティブに対応するため SPM を利用するという選択をしています 5

6 SPM 概要 6

7 SPM とは SQL 文の実行計画を時間の経過とともに記録し評価を行う予防メカニズムデータベースの変更にかかわらず対応する SQL 文のパフォーマンスを維持承認された SQL 文の計画セット SQL 計画ベースラインを構築 SQL 計画ベースラインには承認された計画のみが含まれる計画履歴は時間の経過とともに SQL 文に対して生成された承認済および未承認の計画のセット SQL 計画ベースラインの改良フェーズでは新しい計画のパフォーマンスを評価しより優れたパフォーマンスの計画を SQL 計画ベースラインに組み込む 7

8 SPM のアーキテクチャ SYSAUX SQL 管理ベース計画履歴計画履歴繰返し実行される SQL 文 GB HJ HJ 計画ベースライン GB HJ HJ GB HJ HJ 計画ベースライン GB HJ HJ GB HJ HJ 計画履歴繰返し実行される SQL 文 GB HJ HJ GB HJ HJ 計画ベースライン GB HJ HJ 繰返し実行される SQL 文 8

9 プロジェクト事例 9

10 プロジェクト概要某社内基幹システム老朽化対応プロジェクト DB を含むミドルウェアのアップグレードを伴う現行システムの性能に関する問題点月次バッチ処理が長時間化 ( バッチウインドウを超過 ) DB 処理時間の 60% がディスク I/O の待機時間業務領域単位にディスク分散しており特定の領域のバッチ処理は特定のディスクに I/O が集中新システムの特徴 ASM を利用し全ディスクにデータを分散 ( 全体最適化 ) 業務データは全て EFD(SSD) を採用 10

11 環境概要現行システム Oracle E-Business Suite 11i(11.5.8) Oracle9i Database Release 2( ) on Solaris 新システム Oracle E-Business Suite 11i( ) Oracle Database 11g Release 2( ) on Linux x86(64bit) 11

12 SPM 利用の背景 SQL 文の性能低下による月次バッチ処理の遅延は避けなければならない Oracle Database のバージョンが変わるため同じ SQL 文でも実行計画は変化する可能性がある ( 多くの SQL は性能が改善すると想定されるがごく一部の SQL で性能が低下する可能性がある ) 新システムの I/O 性能は現行システムと比較して改善することが想定されるため同じ実行計画であれば性能改善が見込める全 SQL 文実行計画が変化する SQL 実行計画が同じ SQL 一部の SQL で性能が低下した影響により月次バッチが遅延することを避けなければならない性能低下 SQL 性能改善 SQL SQL の本数比率を図で表現しているわけではありません 12

13 SPM 利用の目的 SQL の実行性能を低下させない現行システムの重要 SQL の実行性能を低下させず新システムへ移行する SQL の実行性能を改善させるテスト期間中に SQL の性能低下を検出した場合 SPM の機能を使用して性能改善する本番稼働後に SQL の性能低下を検出した場合 SPM の機能を使用して性能改善する 13

14 SPM 利用範囲案 1 全ての SQL を管理対象とする案 2 管理対象とする SQL を選定する対象 SQL メリットデメリット備考重要業務 SQL 全 SQL 全 SQL 実行回数上位 SQL 全ての SQL の実行計画を現行システムと同一にすることでカットオーバ後の性能問題の発生を抑止できる重要業務の SQL や実行回数の多い SQL の実行計画を現行システムと同一にすることでカットオーバ後のクリティカルな性能問題の発生数を抑制できる現行システムで流れている全 SQL を取得する必要がありそのために現行システムの設定や運用方法を変更する必要がある SPM 管理対象外の SQL は性能問題が発生する可能性がある利用範囲を決定する設計フェーズで SPM で管理できる SQL の本数の妥当性が判断できなかった重要業務はこのフェーズで既に選定されていたプロジェクトでは案 2 を選択 14

15 SPM 初期登録 SQL の選定重要業務 SQL 実行性能が低下した場合の業務的な影響が大きな機能をアプリケーション側の観点から選定夜間バッチのクリティカルパスになっている機能やバッチウィンドウに必ず収めなければならない機能など実行回数上位 SQL SQL 実行回数が多いほど SQL 単体性能劣化の影響を大きく受けるため初期登録 SQL の本数と 9i と同じ実行計画で 11g へ SPM 登録できた SQL 本数の割合 ( 参考情報 ) 初期登録 SQL 実行計画一致割合重要業務 SQL 約 200 本約 80% 約 600 本約 90% 実行回数上位 SQL 約 400 本約 95% 15

16 プロジェクトの各フェーズにおける SPM 運用環境プロジェクト期間中本番稼動後新システム本番環境 ( ) 本番移行のタイミングで検証環境で作った SPM ベースラインを一括して本番環境へ移行するその後は性能改善した実行計画を SQL ハンドル単位に個別に移行する SPM PACK/UNPACK 初期流動期間 SPM ステージング表ステージング表ステージング表 SPM 新システム検証環境 ( ) 実行性能劣化防止アウトライン SPM 登録実行性能改善カーソルキャッシュ SPM 登録ヒントでチューニングしたプランを元の SQL として SPM 化 ( 元のプランは無効化 ) ダンプファイル性能劣化 SQL ヒントを付与ヒント SQL 現行システム検証環境 ( ) SQL アウトライン作成 TIPS 1. 最終的に登録した SPM ベースラインのサイズ 2. DBA_SQL_PLAN_BASELINES.DESCRIPTION 列の効果的な使い方ライセンスの関係で 9i 検証環境が残るのは初期流動期間まで 16

17 ( 参考 ) SPM ベースラインの編集 KROWN# [11g 新機能 ] ヒントを指定した際の実行計画を SQL 計画の管理にロードする方法作業内容元のプランの登録 ( ベース登録 ) ヒントを利用してプランを編集 SQL 実行 select * from tab2 where c2 > 999; select /*+ FULL (TAB2) */ * from tab2 where c2 > 999; カーソルの SQL_ID, PHV を確認 (V$SQL) カーソルのプランを確認 (dbms_xplan.display_curs or) カーソルから SPM 登録 SQL_ID PLAN_HASH_VALUE m7d74pkw543z Plan hash value: Id Operation Name SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID TAB2 * 2 INDEX RANGE SCAN ND var res number exec :res := dbms_spm.load_plans_from_cursor_cache( - sql_id => '3m7d74pkw543z', - plan_hash_value => ); SPM 登録 SQL_ID PLAN_HASH_VALUE j82gtnc0n4ps Plan hash value: Id Operation Name SELECT STATEMENT * 1 TABLE ACCESS FULL TAB 元の SQL_HANDLE にヒントで編集したプランを結び付けることで元の SQL のプランを変えることが可能 ( 元のプランを今後使わないように無効化 or 削除を行う ) var res number exec :res := dbms_spm.load_plans_from_cursor_cache( - sql_id => '1j82gtnc0n4ps', - plan_hash_value => , - sql_handle => SYS_SQL_ f95de3'); SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT CREATED SYS_SQL_ f95de3 SYS_SQL_PLAN_12f95de371befc37 MANUAL-LOAD YES YES NO YES 09/06/03 08:35:32 SYS_SQL_ f95de3 SYS_SQL_PLAN_12f95de3b157e6ce MANUAL-LOAD YES YES NO YES 09/06/03 08:39:06 SPM 登録 17

18 SPM 機能詳細 18

ハード解析で SPM を利用するかどうかの判断 SQL 実行青枠内のメッセージは event 10053 トレースへ出力されるメッセージを表します optimizer_use_sql_plan_baselines TRUE FALSE SPM ベースラインを利用せずハード解析 SPM 登録されている SQL と SIGNATURE と比較 SPM: current statement

19 ハード解析で SPM を利用するかどうかの判断 SQL 実行青枠内のメッセージは event トレースへ出力されるメッセージを表します optimizer_use_sql_plan_baselines TRUE FALSE SPM ベースラインを利用せずハード解析 SPM 登録されている SQL と SIGNATURE と比較 SPM: current statement disallowed SIGNATURE が一致しない SPM ベースラインを利用せずハード解析 SIGNATURE が一致する SPM: statement found in SMB SPM ベースラインを利用してハード解析 TIPS 1. SIGNATURE (EXACT_MATCHING_SIGNATURE) で判断? Oracle Database リファレンス 11g リリース 2(11.2) (B ) V$SQL.EXACT_MATCHING_SIGNATURE 正規化された SQL テキストに対して計算されたシグネチャ正規化には空白の削除と非リテラル文字列の大文字化が含まれる 19

20 プラン固定のための基礎情報実行された SQL に対する SPM ベースラインが登録されているかどうかを SIGNATURE で判断 SPM は実行計画をそのまま保持しているのではなく元の実行計画を再現するためのヒントの集合を保持元の実行計画と同じ実行計画を再現できたかどうかを PLAN_ID で比較 SIGNATURE PLAN_ID ヒントの集合概要正規化した SQL のハッシュ値 (SIGNATURE が一致する場合に該当 SPM ベースラインを利用 ) 元の実行計画と一致したかどうかを比較するため PLAN_ID を保持元の実行計画と一致したかどうかを比較するため PLAN_ID を保持確認方法 DBA_SQL_PLAN_BASELINES.SIGNATURE SQLOBJ$.SIGNATURE SQLOBJ$DATA.SIGNATURE SQLOBJ$.PLAN_ID SQLOBJ$DATA.PLAN_ID SQLOBJ$DATA.COMP_DATA 20

21 プラン固定のための基礎情報を確認 SQL> select spm.signature, spm.sql_handle, spm.plan_name, spm.enabled, spm.accepted, spm.reproduced, so.plan_id 2 from dba_sql_plan_baselines spm, sqlobj$ so 3 where spm.signature = and spm.signature = so.signature 5 and spm.plan_name = so.name; SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC REP PLAN_ID SQL_ f95de3 SQL_PLAN_2a1h62h9gkrg371befc37 YES YES YES SQL> select spm.signature, spm.sql_handle, spm.plan_name, sod.comp_data 2 from dba_sql_plan_baselines spm, sqlobj$ so, sqlobj$data sod 3 where spm.signature = and spm.plan_name = 'SQL_PLAN_2a1h62h9gkrg371befc37' 5 and spm.signature = so.signature 6 and spm.signature = sod.signature 7 and spm.plan_name = so.name 8 and so.plan_id = sod.plan_id; TIPS 1. ヒントにはデフォルト以外に設定されたオプティマイザ関連の初期化パラメータ値が含まれている? SIGNATURE SQL_HANDLE PLAN_NAME COMP_DATA SQL_ f95de3 SQL_PLAN_2a1h62h9gkrg371befc37 <outline_data><hint><![cdata[ignore_optim_embedded_hints]]></hint><hint><![cdata[optimizer_features_enable(' ')]]></hint><h int><![cdata[db_version(' ')]]></hint><hint><![cdata[opt_param('_replace_virtual_columns' 'false')]]></hint><hint><![cdata[opt_param('_optimizer_use_feedback' 'false')]]></hint><hint><![cdata[opt_param('_fix_control' "TAB2"@"SEL$1" ("TAB2"."C2"))]]></hint></outline_data> 21

22 ハード解析フェーズの流れ 1 通常の解析最適なコストのプランを生成青枠赤枠で別々の SQL に対する event トレースのメッセージを抜粋しています SPM: cost-based plan found in the plan baseline, planid = SPM: cost-based plan successfully matched, planid = PLAN_ID が一致しない SPM 登録されているプランと PLAN_ID と比較新しいプランを自動取得 PLAN_ID が一致する SPM 登録されているプランで SQL 実行 SPM: setup to add new plan to existing plan baseline, sig = , planid = SPM: planid's of plan baseline are: ラウンド 0 SPM ヒントセット + セッション OFE SPM: plan reproducibility round 0 (plan outline + session OFE) SPM: using qksan to reproduce accepted plan, planid = SPM 登録されているプランと PLAN_ID と比較 PLAN_ID が一致する SPM 登録されているプランで SQL 実行 PLAN_ID が一致しない 1 SPM: planid in plan baseline = , planid of reproduced plan = SPM: planid in plan baseline = , planid of reproduced plan =

23 ハード解析フェーズの流れ 2 1 ラウンド 1 SPM ヒントセット + ヒント OFE SPM: plan reproducibility round 1 (plan outline only) PLAN_ID が一致しない SPM 登録されているプランと PLAN_ID と比較 PLAN_ID が一致する SPM 登録されているプランで SQL 実行ラウンド 2 ヒント OFE のみ SPM: plan reproducibility round 2 (hinted OFE only) SPM 登録されているプランと PLAN_ID と比較 PLAN_ID が一致する SPM 登録されているプランで SQL 実行 PLAN_ID が一致しない SPM 登録されているプランは再現できない最もコストが低いプランで SQL 実行 SPM: planid in plan baseline = , planid of reproduced plan = SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planid =

24 SPM が利用できていることの確認 1 SQL> select EXACT_MATCHING_SIGNATURE, SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PLAN_BASELINE, substr(sql_text, 1, 100) SQL_TEXT 2 from v$sql 3 where EXACT_MATCHING_SIGNATURE = ; EXACT_MATCHING_SIGNATURE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PLAN_BASELINE SQL_TEXT m7d74pkw543z SQL_PLAN_2a1h62h9gkrg371befc37 select * from tab2 where c2 > 999 TIPS 1. カーソルの情報が V$SQL から消える 2. LAST_EXECUTED が更新されない SQL> set lines 120 SQL> col SQL_HANDLE for a20 SQL> col LAST_EXECUTED for a20 SQL> select spm.signature, spm.sql_handle, spm.plan_name, spm.enabled, spm.accepted, spm.reproduced, 2 to_char(spm.last_executed, 'YYYY/MM/DD HH24:MI:SS') LAST_EXECUTED 3 from dba_sql_plan_baselines spm 4 where spm.signature = and spm.plan_name = 'SQL_PLAN_2a1h62h9gkrg371befc37'; SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC REP LAST_EXECUTED SQL_ f95de3 SQL_PLAN_2a1h62h9gkrg371befc37 YES YES YES 2012/06/06 00:38:16 24

25 SPM が利用できていることの確認 2 SQL> select * from table(dbms_xplan.display_cursor('3m7d74pkw543z', 0)); PLAN_TABLE_OUTPUT SQL_ID 3m7d74pkw543z, child number select * from tab2 where c2 > 999 Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 12 (100) 1 TABLE ACCESS BY INDEX ROWID TAB (0) 00:00:01 * 2 INDEX RANGE SCAN IND (0) 00:00: Predicate Information (identified by operation id): access("c2">999) Note dynamic sampling used for this statement (level=2) - SQL plan baseline SQL_PLAN_2a1h62h9gkrg371befc37 used for this statement 24 rows selected. 25

26 最後に 26

27 所感および推奨バージョン SPM 利用の所感ヒントを使いこなすことにより SPM は SQL チューニングにおける強力な武器の一つとなる性能障害への対応速度が向上する SPM で実行計画を固定できない場合最終的にアプリケーションを修正することを検討する ( 残念ながら現在のバージョンでは SPM で固定できないことがあり得る ) SPM を利用する上での私個人の推奨バージョン Release Schedule of Current Database Releases (Doc ID ) 27

28 Appendix 28

29 A-1. 主要なビュー DBA_SQL_PLAN_BASELINES SPM 管理のための主要なディクショナリビュー ( 一部の列のみ抜粋 ) 列名説明 SIGNATURE 正規化した SQL のハッシュ値 (SIGNATURE が一致する場合に該当 SPM ベースラインを利用 ) SQL_HANDLE SQL_TEXT SPM で管理する SQL に対する名前 SQL 本文 PLAN_NAME 各プランに対する名前 (DBMS_SPM パッケージにて変更可能 ) ORIGIN MANUAL-LOAD / AUTO-CAPTURE / MANUAL-SQLTUNE / AUTO-SQLTUNE / STORED- OUTLINE (UNKNOWN) DESCRIPTION デフォルト NULL (DBMS_SPM パッケージにて 500 バイト以内で設定可能 ) CREATED SPM ベースラインの作成日 (PACK / UNPACK でコピーした場合は UNPACK 実行日 ) LAST_EXECUTED 前回このプランが利用された日 ( 前回の利用から 6.5 日間経過するまでは更新されない ) LAST_MODIFIED 何らかの属性が変更された日 (REPRODUCED 列の YES / NO が切り替わっただけでも日付は更新される ) REPRODUCED 通常 YES (NO の場合そのプランは有効に利用できていない ) 29

30 A-2. 主要なビュー DBA_SQL_PLAN_BASE_LINES - SIGNATURE - PLAN_NAME SQLOBJ$ - SIGNATURE - NAME - PLAN_ID SQLOBJ$DATA - SIGNATURE - PLAN_ID - COMP_DATA V$SQL - EXACT_MATCHING_SIGNATURE - SQL_PLAN_BASELINE 30

31 Oracle Database 11g R2: 新機能 Oracle Database 11gR2 の新機能を思う存分学ぶ! Database Oracle Database 11g Release 1 および Release 2 における変更管理の機能とその他の主な拡張機能について学習し演習を行います変更管理問題の診断とリカバリ高可用性の確保スケーラビリティおよびパフォーマンスの向上セキュリティの強化さらにデータベース管理者に関連するその他の領域を取り上げ新機能の利点と使用方法について詳しく説明しますコース内容対象者前提条件コース日程 5 日間 (2012 年 11 月現在 ) Oracle Database 11g Release 2のインストール Oracle Restartを使用したコンポーネントの管理 Oracle Grid Infrastructureのインストール Resource Managerの拡張機能の使用自動 SQLチューニングの設定と管理 SQL 計画管理の使用とSQL 計画ベースライン自動ストレージ管理 (ASM) の拡張機能の使用 Total Recallによる履歴データの作成保護および使用データポンプレガシーモードの使用 SQLパフォーマンスアナライザの使用データリカバリアドバイザの使用 Recovery Managerの新機能と拡張機能の使用データベースリプレイを使用したワークロードの記録およびリプレイ Enterprise Managerを使用したSQLコマンドの監視 DWHとパーティション機能の実装 Oracle Database 11g R2 を導入構築するテクニカルエンジニア Oracle9i Database または Oracle Database 10g の管理知識 ORACLE MASTER Gold Oracle9i Database または ORACLE MASTER Gold Oracle Database 10g をお持ちの方会場 : トレーニングキャンパス青山 2012 年 12 月 10 日 ( 月 )~14 日 ( 金 ) 銀座会場 2013 年 1 月 21 日 ( 月 )~25 日 ( 金 ) 受講料 (2012 年 11 月現在 ) 定価 363,825( 税込 ) Oracle PartnerNetwork 会員様はパートナー割引価格で受講いただけますお申込みお問合せオラクルユニバーシティ Tel:

32 32

33 33

PowerPoint Presentation

PowerPoint Presentation オラクルコンサルが語る! SQL 実行性能の安定化方式日本オラクル株式会社テクノロジーコンサルティング統括本部テクニカルアーキテクト本部 DB コアテクノロジー部プリンシパルコンサルタント鈴木健吾以下の事項は弊社の一般的な製品の方向性に関する概要を説明するものですまた情報提供を唯一の目的とするものでありいかなる契約にも組み込むことはできません以下の事項はマテリアルやコード機能を提供することをコミットメント