1
津島博士のパフォーマンス講座最新のチューニング手法 日本オラクル株式会社テクノロジー製品事業統括本部基盤技術部ディレクター津島浩樹
以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社 関連会社の米国及びその他の国における登録商標です 文中の社名 商品名等は各社の商標または登録商標である場合があります 3
Program Agenda チューニングとは Oracleのチューニング DB 時間ベースのチューニング ベースラインによる監視 実行計画 ( オプティマイザ ) 4
チューニングとは 5
チューニングとは 目標の性能にすること 効果的なリソース配分 / 競合の改善 インスタンス チューニング 無駄なリソース使用の排除 SQL チューニング ワークロードの均一化 アプリケーション運用のチューニング チューニング? まだ難しいと思っている方が多いのでは? 6
チューニングとは 情報収集することから悩む 何を分析するか分からないと何を収集するのかも分からない どのような情報を収集するのか 取得間隔は? 負荷的に大丈夫か どのように分析するのか 応答時間が遅いやスループットが低いからどうするか どのように改善するのか 何が原因か分からない 情報不足で再取得する必要になる 7
チューニングとは 簡単にするためには 明示的に収集する必要が無い 自動収集される スクリプトなどを作成する必要が無い 負荷が少ない 負荷を気にせずに常に収集できる 単純な考え方である 難しいと使えない ツールで簡単に行える 8
チューニングとは 簡単にするためには パフォーマンス監視も簡単に ベースラインの管理 性能ダウン時のベースラインとの比較 簡単に調査ができるように システム メトリックによるしきい値チェック アラートにより通知する このようなことが簡単にできると嬉しいですよね 9
Oracle のチューニング 10
Oracle のチューニング Oracle チューニング メソッド : 変遷 ( できるだけ簡単に ) Oracle6( 回数 / 割合 ) BSTAT/ESTAT SQL*Trace Oracle7( 待機イベントの導入 ) Oracle8i( 回数から時間へ : TIMED_STATISTICS) Statspack Oracle Database 10g(DB 時間チューニング ) AWR ASH ADDM 自動収集 Exadata(OSWatcher の提供 ) Exadata 以外は MOS(My Oracle Support) からダウンロード 11
Oracle のチューニング 二つのケースを使い分ける 定常的なパフォーマンス チューニング 全体的な負荷のスループットや応答時間を改善する AWR(Automatic Workload Repository) デフォルトは 60 分間隔 一時的なパフォーマンスの問題を診断 報告されたパフォーマンスの問題を確認して調査する ASH(Active Session History) 1 秒間隔 ( メモリ ) と 10 秒間隔 ( ディスク上 ) SQL を使用しないカーネルコールのため負荷が少ない 12
Oracle のチューニング 二つのケースを使い分ける ( ツール ) 定常的なパフォーマンス チューニング AWRレポート AWR 期間比較レポート ADDM(Automatic Database Diagnostic Monitor) を使用 一時的なパフォーマンスの問題を診断 ASHレポート EM(Enterprise Manager) パフォーマンス ページのリアルタイム画面 リアルタイム SQL 監視など DB 時間ベースのチューニングを使用 13
Oracle のチューニング ツール (ADDM) DB 時間メソッドの使用を埋め込んだエキスパート システム 症状の根本的な原因を特定 様々な範囲での診断 ホスト インスタンス SQL DB ブロック RAC(Oracle Database 11g から ) DB 時間への影響が大きいものから探す 柔軟な範囲の履歴を見つけることができる 診断結果に対してのフィルタリング DB 時間が削減されるようにアドバイスする 14
Oracle のチューニング ツール (EM リアルタイム画面 ) 一時的または部分的な対処 自動的ではない ( 画面による対話操作が必要 ) 大きい箇所をクリックする 問題点が視覚的に ( 偏りとして ) 表示される 根本的な原因とは別の症状について多少の専門知識が必要 これだけでは簡単に解決できない 問題点は特定できるが解決策は提示できない 現在動作中であればADDMの手動実行する 15
DB 時間ベースのチューニング 16
DB 時間ベースのチューニング 基本的な概念 (DB 時間とアクティブ セッション ) DB 時間 ( データベース時間 ) すべてのデータベース内処理に要したセッション ( フォアグランド ) の合計時間 (CPU 時間 I/O 時間 非アイドル待機時間が含む ) 応答時間 Active Session( アクティブ セッション ) 現在データベース内処理に時間を要している ( アイドルでない ) セッション %Activity( 平均アクティビティ ) 実経過時間とアクティブな時間の割合 一冊の本の本を閲覧するカートに入れる精算するレビューを読む SQL = データベースで使用した時間 時間 17
DB 時間ベースのチューニング 基本的な概念 ( 複数セッションのとき ) DB 時間 : すべてのセッションのDB 時間の合計 Average Active Sessions( 平均アクティブ セッション ) すべてのセッションの平均アクティブ数時間 t には2つのアクティブ セッションが存在する ユーザ 1 ユーザ 2 ユーザ 3 = データベースで使用した時間 t ユーザ n 時間 18
DB 時間ベースのチューンング基本的な概念 (DB 時間の可視化 ) 平均アクティブ セッション = 全データベース時間 実際の経過時間 ユーザ1 ユーザ2 ユーザ3 ユーザn t0 t1 4 3 2 1 経過時間 アクティブ セッションの経過 時間 19
DB 時間ベースのチューニング EM パフォーマンス ページ 待機クラスごとのアクティブ セッションの経過 カラー領域の合計 = DB 時間 20
DB 時間ベースのチューニング 基本的な概念 ( システム パフォーマンスと DB 時間 ) システム負荷の増加 ( 自分以外による ) 待機の増加 (=> DB 時間の増加 ) ホスト マシンが CPU ネック CPU 実行キュー時間の増加 (=> DB 時間の増加 ) I/O パフォーマンスの低下 I/O 時間の増加 (=> DB 時間の増加 ) 報告される待機時間 報告される待機時間 db file sequential read 実行キュー CPU 実行 db file sequential read 実行キュー CPU 実行 ユーザ 1 実待機 (I/O) 時間 実待機 (I/O) 時間 21
DB 時間ベースのチューニング 基本的な概念 (DB 時間はどこから ) 時間モデル統計 (V$SYS_TIME_MODEL, V$SES_TIME_MODEL) STAT_NAME= DB time システム メトリック (V$SYSMETRIC_HISTORY) METRIC_NAME= Database Time Per Second, CPU Usage Per Sec METRIC_NAME= Average Active Sessions (11gから ) SQL 統計 (V$SQL) ELAPSED_TIME, CPU_TIME XXX_WAIT_TIME(APPLICATION, CONCURRENCY, CLUSTER, USER_IO) ASH(V$ACTIVE_SESSION_HISTORY) 22
DB 時間ベースのチューニング ASH(Active Session History) システム全体のデータベース内処理の記録 すべてのアクティブ セッションを1 秒ごとに収集 フォアグランドとバックグランドを抽出 アクティブ フォアグランドをDB 時間とする メモリ内 (V$ACTIVE_SESSION_HISTORY) 抽出間隔 :1 秒 ディスク上 (DBA_HIST_ACTIVE_SESS_HISTORY) 抽出間隔 :10 秒 保存期間 : デフォルトでは8 日間 (10gでは7 日間 ) SGA v$active_session_history 60 分間隔バッファが一杯 MMON MMNL SYSAUX dba_hist_active_sess_history 23
DB 時間ベースのチューニング ASH( アクティブ セッションと DB 時間 ) ASH サンプル数 = DB 時間 ( 秒 ) アクティブ セッションの求め方 SELECT sample_time, COUNT(*) ActSess, sql_id FROM v$active_session_history WHERE session_type!= BACKGROUND AND sample_time between to_timestamp( 12-11-01 09:00:00, YY-MM-DD HH24:MI:SS ) and to_timestamp( 12-11-01 17:00:00, YY-MM-DD HH24:MI:SS ) AND wait_class = xxxxx GROUP BY sample_time, sql_id ORDER BY sample_time, sql_id ; 24
DB 時間ベースのチューニング ASH( アクティブ セッションと DB 時間 ) ASH を使用すると DB 時間ベースのチューニングが簡単に DB Time は曲線下の領域 t = 1 秒 SAMPLE_TIME ごとのアクティブ セッションの値 アクティブ セッション DB 時間 t0 時間 t1 25
DB 時間ベースのチューニング DB 時間メソッド ( 手順 ) 1. パフォーマンス問題を見つける 2. 問題の条件の確認 3. 目標の設定 4. データ収集 ( 操作は不要 ) 5. DB 時間の分布を調べる 最も改善の可能性があるものを特定 6. 最大の改善に対してシステムを変更する 7. 目標に対して評価する 目標が満たされていない場合はステップ 4 から繰り返す パフォーマンス チューニングは超過した DB 時間の削減 26
DB 時間ベースのチューニング DB 時間メソッド (DB 時間の分布を調べる ) DB 時間の不均一な分布 ( 偏り ) を見つける 範囲で最も改善する可能性があるもの システム範囲 リソースの限界 (DB 以外の問題かなど ) アプリケーション範囲 サービス モジュールとアクション (DBMS_APPLICATION_INFOパッケージで設定) SQLとRowSource リソース競合( ラッチなど ) セッション範囲 長時間実行中の SQL リソース競合 ( エンキューなど ) 27
DB 時間ベースのチューニング DB 時間メソッド ( 可能性のある解決策の特定 ) セッション競合の問題 (Concurrency など ) セッション数の削減 アプリケーションの修正 SQL の問題 (CPU, User I/O など ) SQL の変更 SQL チューニング アドバイザ ( 索引 SQL プロファイル ) 設計の問題 (Configuration など ) SQL アクセス アドバイザ ( 索引 物理的な配置 ) システムの問題 (System I/O, Network, Cluster, Commit など ) 初期化パラメータの変更 リソースの追加 28
DB 時間ベースのチューニング DB 時間メソッド ( システムの変更 ) 最初は最大の DB 時間から開始する 兆候ではなく根本的な原因の箇所から 解決条件と問題条件は一致させる SQL チューニングの前にオプティマイザ パラメータを微調整しない 1 度に 1 変更だけ行い それを繰り返す 同時変更のそれぞれが影響する それぞれの変更で結果の判断と確認をする 目標が達成すれば終了 29
DB 時間ベースのチューニング DB 時間メソッド ( まとめ ) ユーザに影響を与える一つだけ ( 時間 ) を調整する 単純にする ( 無駄な作業を減らす ) データ収集は必要ない 常にデータ収集を行っているので 再現する必要がない 多くのスコープで DB 時間の分析を可能にする 問題を解決するために適切なスコープが必要 パフォーマンス向上とは同じ処理を少ない DB 時間で行うこと 30
ベースラインによる監視 31
ベースラインによる監視 パフォーマンス監視とは パフォーマンスの状態を常に監視する 重大なパフォーマンス劣化を防ぐ 簡単でないと頻繁にチェックできない 最適な状態のパフォーマンス統計 ( ベースライン ) を退避しておく パフォーマンス劣化の原因を簡単に見つける ベースラインの管理と比較方法が簡単でないと Oracleデータベースで提供 T P S 時間 32
ベースラインによる監視 ベースラインの管理 AWR ベースライン管理 スナップショットの期間 ( 開始と終了 ) を名前を付けて保存することが可能 保存期間が過ぎても削除されない 固定ベースライン 指定した期間 ( 最適なパフォーマンスなど ) のベースラインを作成する 変動ベースライン 変動ウィンドウ ( デフォルト 8 日 ) 内の AWR( しきい値統計が 1 週間に 1 度計算される ) ベースライン テンプレート 将来のある期間に自動的にベースラインを作成する ( 名前を付けて AWR を取得する ) 33
ベースラインによる監視 ベースラインの管理 ベースライン テンプレート 単一ベースライン 単一の固定期間 例 今週末の土曜日 8:00から17:00までテストを行うから作成する 繰返しベースライン 一定期間の特定間隔で繰り返されるベースライン 例 2012 年 1 月から12 月まで毎週月曜日の午前 10 時から12 時まで作成する AWR ベースラインは 名前を付けて保管するだけです 34
ベースラインによる監視 ベースラインの管理 AWR ベースラインの作成 EM AWR ベースライン ページ DBMS_WORKLOAD_REPOSITORY パッケージ 35
ベースラインによる監視 問題点のチェック方法 AWR 期間比較レポート (10gR2 から ) 二つの期間のパフォーマンス比較をすることが可能 適応メトリックしきい値 (11g から ) 固定しきい値 以外の設定が可能 ワークロードが変化するシステムでも使用できる ベースラインからメトリックしきい値を設定する ベースラインの時間ごとにしきい値が設定される 設定が簡単に行える 36
ベースラインによる監視 AWR 期間比較レポート 比較するプログラムを独自で作成する必要がない 二つの期間を出力して比較できる 第 1 期間 ( 最適なベースライン ) と第 2 期間 ( 調査したいベースライン ) 第 1 期間 ( 最適なベースライン ) と第 2 期間 ( 調査したいスナップショットのペア ) レポート生成 EM 自動ワークロード リポジトリ ページ @$ORACLE_HOME/rdbms/admin/awrddrpt.sql @$ORACLE_HOME/rdbms/admin/awrgdrpt.sql(RAC 環境 ) 37
AWR 期間比較レポート 38
ベースラインによる監視 適応メトリックしきい値の設定 EM ベースライン メトリックしきい値 ページで クイック構成 変動ベースラインによるメトリックしきい値 ワークロード プロファイルによって設定 ( 設定されるメトリックが決まる ) 主としてOLTP( トランザクション処理の未 24 時間 ) 主としてデータ ウェアハウス ( 問合せとロード集中型 ) 代替 ( 日中はOLTP 夜間はバッチ) ベースラインの時間ごとにしきい値が設定される しきい値タイプ クリティカルのしきい値 警告のしきい値 39
ベースラインによる監視 適応メトリックしきい値のタイプ 最大パーセント ベースラインのデータの最大値に対する割合 (100%,120% までOK) ピーク ワークロード用 重大レベル ベースラインのデータを超える値に対する百分位数 高い :.95(100 回に5 回までOK) 非常に高い:.99 重度:.999 極度:.9999 40
ベースラインによる監視 適応メトリックしきい値の編集 メトリックの しきい値の編集 ページで行う ベースラインを指定してメトリックごとにしきい値を編集する 変動ベースラインまたは固定ベースライン 固定ベースラインはしきい値統計の計算が必要 指定したベースラインの時間ごとのしきい値を表示 しきい値タイプ クリティカルのしきい値 警告しきい値 しきい値のないメトリックについては 基本メトリック 又は 基本および追加メトリック を使用する 41
実行計画 ( オプティマイザ ) 42
実行計画 ( オプティマイザ ) CBO についての疑問点 最適な実行計画を作成してくれるのか SQL の書き方を気にしなくて良いのか ( 問合せの変換が動作します ) 実行計画を変化させたくない なぜ ( どのようなときに ) 変化するのか分かりますか オプティマイザ統計収集に時間が掛る あまり収集できない ( 効率的な収集を行っていますか ) オプティマイザ統計の運用をどうするのか 自動オプティマイザ統計収集を知っていますか 43
実行計画 ( オプティマイザ ) オプティマイザ機能 ( 問合せの変換 ) 論理的に等しく効率的な SQL に変換する ( 代表的なものとして ) 副問合せありに変換 ( 件数を削減してから結合する ) ビューのマージ ( ビューを副問合せとして処理せずに ) 述語のプッシュ ( 副問合せ結果件数を削減するため ) select * from (select * from tab1 where ) where c1 = x select * from (select * from tab1 where c1 = x and ) OR 拡張 (OR を UNION ALL に変換することで索引を使用する ) 実行計画は読めるようになりましょう 44
実行計画 ( オプティマイザ ) オプティマイザ機能 ( 実行計画に影響 ) オプティマイザ統計 大きく変化すれば ( サンプル サイズが少ないと効果が低下する場合がある ) 初期化パラメータ OPTIMIZER_MODE all_rows( デフォルト ), first_rows_n( より索引を使用させたい ), first_rows 統計情報が正確でない ( 行数が正しく見積もれない ) とき以外は変えない ダイナミック サンプリング 動的にオプティマイザ統計を取得する ( デフォルトは 2: 統計情報がないと行う ) カーディナリティ フィードバック 実際のカーディナリティが統計情報と異なっている場合に 45
実行計画 ( オプティマイザ ) オプティマイザ機能 ( 実行計画に影響 ) 子カーソル ( 同じ SQL で複数の実行計画ができる ) 同じ SQL でもベストな実行計画が異なるため バインド変数 (bind peek 優れたカーソル共有 CURSOR_SHARING) スキーマが異なるなど 共有メモリの消費が増える 子カーソル数の上限値に達すると無効化される 46
実行計画 ( オプティマイザ ) オプティマイザ統計管理 オプティマイザ統計を収集するとき 自動オプティマイザ統計収集 22:00から26:00( 土日は6:00から26:00) に自動実行する 独自の方式で収集している以外は使用する オプティマイザ統計の手動収集 必要な ( 大量に変更があった ) ときを判断して実行する 47
実行計画 ( オプティマイザ ) オプティマイザ統計管理 ( 効率化 ) オプティマイザ統計収集の効率を向上させる パラレル化 ( デフォルトはテーブルの DEGREE 句で行います ) AUTO_DEGREE を推奨 ( オブジェクト サイズによってパラレル度が調整される ) CONCURRENT( 複数オブジェクトの統計収集を並列実行 ) DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE'); インクリメンタル統計 ( デフォルトは false) AUTO_SAMPLE_SIZE で行う ( デフォルトで行う ) 48
実行計画 ( オプティマイザ ) オプティマイザ統計管理 ( 効率化 ) オプティマイザ統計を収集しないもの ( ダイナミック サンプリングを使用 ) 一時的な中間テーブルや揮発性の高いテーブル 統計をロックする 一時表 (Global Temporary Table) 自動オプティマイザ統計では収集されない 49
実行計画 ( オプティマイザ ) オプティマイザ統計管理 ( 効率化 ) オプティマイザ統計の他のタイプの収集 ディクショナリ 必ず自動オプティマイザ統計で収集する ( ディクショナリだけでも自動収集する ) DBMS_STATS.SET_GLOBAL_PREFS( AUTOSTATS_TARGET, ORACLE ); 固定オブジェクト 自動オプティマイザ統計では収集しない ( ダイナミックサンプリングもされない ) アプリケーションの変更やデータベース構成の変更の時に手動で再収集する DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; システム統計 (CPU 速度 I/O 速度 ) デフォルトでは自動的に初期化される ( 基本はこれで問題ない ) 50
実行計画 ( オプティマイザ ) オプティマイザ統計管理の効率化 (AUTO_SAMPLE_SIZE) どのように正確なオプティマイザ統計を収集するか Computeモード統計は正確な結果が得られますが時間が掛る サンプリングは速いが常に正確ではない AUTO_SAMPLE_SIZEはデータの偏りには効果的に動作しない AUTO_SAMPLE_SIZE の新しい画期的な実装 (11g から ) 行サンプリング アルゴリズムからハッシュ アルゴリズム サンプリングより高速 Computeモード統計と同等の精度 51
実行計画 ( オプティマイザ ) オプティマイザ統計管理の効率化 (ESTIMATE_PERCENT の比較 ) 1% 100% AUTO_SAMPLE_SIZE 実行時間 ( 比率 ) 797 秒 (1) 18,772 秒 (23.55) 1,908 秒 (2.39) L_ORDERKEY (distinct 値 ) 225,000,000 450,000,000 450,000,000 L_COMMENT (distinct 値 ) 7,248,885 177,499,684 181,122,127 TPC-H ベンチマーク (scale factor=300) の Lineitem テーブル (230G バイト ) Reference Best Practices for Gathering Optimizer Statistics 52
実行計画 ( オプティマイザ ) オプティマイザ統計管理の効率化 ( インクリメンタル統計 ) パーティション表の統計収集 1 パーティションに関する統計を収集することは ( 例えば バルク ロード後 ) グローバル表統計の収集のためにすべてのパーティションのフルスキャンが行われ 非常に時間とリソースを集中的に使用する インクリメンタル統計を使用 (11g から ) アクセスしたパーティションのみのオプティマイザ統計を収集 テーブル ( グローバル ) 統計はパーティション統計から構築される 指定方法 ( テーブルの統計プリファレンスで設定 ) dbms_stats.set_table_perfs( SH, SALES, INCREMENTAL, TRUE ); 53
実行計画 ( オプティマイザ ) インクリメンタル グローバル統計 Sales Table 2012/10/18 1. パーティション レベル統計は収集と要約の作成 2012/10/19 2012/10/20 2. パーティション要約を集約することでグローバル統計を生成する 2012/10/21 2012/10/22 2012/10/23 SYSAUX 表領域 54
実行計画 ( オプティマイザ ) インクリメンタル グローバル統計 ( 続き ) Sales Table 2012/10/18 3. 新しいパーティションをテーブルに追加してデータロード 2012/10/19 2012/10/20 2012/10/21 6. 既存と新しい一つのパーティション要約を集約することでグローバル統計を生成する 2012/10/22 2012/10/23 2012/10/24 4. 新しいパーティションのパーティション統計を収集 5. SYSAUX から他のパーティション要約を取得 SYSAUX 表領域 55
最新のチューニング手法 まとめ 自動収集されているAWR ASHを利用してDB 時間ベースで パフォーマンス監視はAWRベースライン管理で オプティマイザ統計収集は効果的に パラレルで短時間に サンプル サイズはAUTO_SAMPLE_SIZEを使用する パーティション表はインクリメンタル統計を使用する 無駄な統計は収集しない 56
Database Oracle Database 11g パフォーマンス チューニング コース内容 前提条件 コース日程 5 日間 (2012 年 11 月現在 ) Oracle Database 11g のパフォーマンス チューニング手法を習得! SQL チューニング アドバイザ SQL アクセス アドバイザ 自動ワークロード リポジトリや自動データベース診断モニタなどの Oracle Database 11g 自動チューニング機能の使い方 およびチューニング方法を習得します またこのコースでは Oracle Database のインスタンス コンポーネントのチューニングなど DBA に求められるチューニング タスクに焦点を当てています Enterprise Manager Database Control を使用する豊富な実機ワークショップを通じて 実践的なチューニング手法を習得します パフォーマンス チューニング概要 サービスを使用したアプリケーションの監視 AWR 関連ツールの使用 基本ツールを使用した監視 ベースライン Statspackの使用 自動ワークロード リポジトリ (AWR) の使用 共有プールのチューニング メトリックおよびアラートの使用 問題の認識 バッファ キャッシュのチューニング 変更の管理 問題のあるSQL 文の認識 PGAおよび一時領域 I/Oのチューニング オプティマイザの適用 自動メモリー管理 パフォーマンス チューニング : まとめ SQL 計画管理 ブロック領域使用量のチューニング Oracle Database 11g 管理ワークショップ I Oracle Database 11g 管理ワークショップ II コースを受講された方 / 相当の知識をお持ちの方 会場 : トレーニングキャンパス青山 2013 年 3 月 4 日 ( 月 )~8 日 ( 金 ) 会場 : トレーニングキャンパス大阪 2013 年 3 月 4 日 ( 月 )~8 日 ( 金 ) 三田 ( 芝浦 ) 会場 2012 年 11 月 26 日 ( 月 )~30 日 ( 金 ) 品川インターシティ会場 2012 年 12 月 17 日 ( 月 )~21 日 ( 金 ) 2013 年 2 月 25 日 ( 月 )~3 月 1 日 ( 金 ) 大手町会場 2012 年 12 月 10 日 ( 月 )~14 日 ( 金 ) 2013 年 2 月 25 日 ( 月 )~3 月 1 日 ( 金 ) 西新宿会場 2013 年 2 月 18 日 ( 月 )~22 日 ( 金 ) 銀座会場 2012 年 12 月 3 日 ( 月 )~7 日 ( 金 ) 2013 年 1 月 28 日 ( 月 )~2 月 1 日 ( 金 ) 2013 年 3 月 11 日 ( 月 )~15 日 ( 金 ) 受講料定価 363,825( 税込 ) Oracle PartnerNetwork 会員様は パートナー割引価格で受講いただけます お申込み お問合せ http://www.oracle.com/jp/education オラクルユニバーシティ Tel: 0120-155-092 57
58
59