Slide 1

Transcription

1 Oracle Direct Seminar 実践パフォーマンスチューニングオプティマイザ活用編 日本オラクル株式会社

2 Agenda オプティマイザとは コストベース オプティマイザでの運用管理 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 2

3 SQL の実行計画とは? SQL を実行するには 多数のステップが必要 データベースからデータを物理的に取り出すステップ ユーザーが発行する文に返すデータ行の準備ステップ 実行計画 : 文を実行するために Oracle が使用するステップの組み合せ Oracle Database メモリ ディスク 取り出し 結果 3

4 実行計画の例 従業員表 (EMP 表 ) と部門表 (DEPT 表 ) から 各部門ごとの従業員リストを作成する SQL の実行計画 SQL> set autotrace on SQL> select d.dname,e.empno,e.ename,e.job from emp e,dept d where e.deptno=d.deptno; 結合方法 Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=392) 1 0 HASH JOIN (Cost=5 Card=14 Bytes=392) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=238) 従業員表 (EMP) へのアクセス方法 部署表 (DEPT) へのアクセス方法 4

5 データの状況により最適な実行計画は違う 同じ表と索引でも データの状況 ( 返すべきデータが表に存在する割合など ) によっては 全表走査 / 索引走査した方が速い パターン A 全表走査 (FULL SCAN) パターン B 索引走査 (INDEX SCAN) 表 索引 表 Row1 Row2 Row3 Row4... 全表走査で実行した方が速い時もある hit hit hit hit 索引 Key1 索引 Key2 索引 Key3 索引 Key4... Row1 Row2 Row3 Row4... 索引走査は Single Block Read であり 1Block ずつの読み込み処理全表走査は Multi Block Read であり db_file_multiblock_read_count パラメータ個ずつの一括読み込み 6

6 オプティマイザの役割 問合せの結果を生成する最も効率的な方法 ( 物理的なアクセス手順 ) を決定し実行計画を作成 - 索引を利用するか - 全表スキャンを実行するか - どの結合方法を選択するか - どの順番で結合するか etc. 2 種類のオプティマイザ RBO: 使用可能なアクセスパスを順序づけるランキングに基づいて実行計画を作成 CBO: 統計情報に基づきコストを見積もり 最もコストの低い実行計画を作成 7

7 RBO のルールのアクセスパス選択動作例 例 ) SELECT empno FROM scott.emp WHERE ename = 'BLAKE' AND sal > 2000; EMP 表の索引名 索引種類 対象列 PK_EMP 主キー empno IND_ENAME 非一意索引 ename IND_SAL 非一意索引 sal Emp 表へのすべてのアクセスパスの洗い出し パス 1:( 索引検索 )IND_ENAME を使用した単一列索引検索パス 2:( 索引検索 )IND_SAL を使用した範囲検索パス 3:( 全表スキャン ) 索引を使用しないアクセスパス アクセスパスのランク付け 8

8 RBO のルールのアクセスパス選択動作例 (2) アクセスパスのランク付け パス 1:IND_ENAME を使用した単一列索引検索 < ランク 9> パス 2:IND_SAL を使用した範囲検索 < ランク 11> パス 3: 全表スキャン < ランク 15> 一番ランクの高い < ランク 9> を採用 IND_ENAME を使用した単一列索引検索の採用 1 ROWID による単一行 2 クラスタ結合による単一行 3 一意 / 主キーをもつハッシュ クラスタ キーによる単一行 4 一意 / 主キーによる単一行 5 クラスタ結合 6 ハッシュ クラスタ キー 7 索引付きのクラスタ キー 8 複合索引 9 単一列索引 10 索引列の境界付きの範囲検索 11 索引列の境界なしの範囲検索 12 ソート / マージ結合 13 索引付きの列の MAX または MIN 14 索引付きの列の ORDER BY 15 全表スキャン 実行計画の決定 9

9 RBO の問題点 SQL 文の内容 ( 構文 ) だけで 実行計画が決まる データの内容は見ていない OLTP タイプの処理を最適化するように設計されている データの中身により RBO よりも高速なパスが存在する 表の数が多くなると 最適な SQL の作成コストが増加 直感的でアドホックな検索は向かない 10

10 RBO の問題点 検索対象が多い場合も インデックススキャンをしてしまう ランク9 索引走査 (INDEX SCAN) ランク15 索引表表 索引 Key1 索引 Key2 索引 Key3 索引 Key4... Row1 Row2 Row3 Row4... Row1 Row2 Row3 Row4... 全表走査 (FULL SCAN) 本来は全表走査で実行すべき! 索引走査は Single Block Read であり 1Block ずつの読み込み処理全表走査は Multi Block Read であり db_file_multiblock_read_count パラメータ個ずつの一括読み込み 11

11 コストベース オプティマイザのメリット ルールベース オプティマイザ コストベース オプティマイザ レスポンス レスポンス 実行計画 A 性能劣化が顕著になり SQL チューニングが必要 初期性能 データの変動に伴う性能劣化 初期性能 データの変動に伴う性能劣化 実行計画 B データの変動 データの変動 データの変動に伴い 性能が劣化する可能性が存在する データの変動を考慮して 最適な実行計画が使用されるよう実行計画が自動で変化する 12

12 2 つのオプティマイザ比較 ルールベース オプティマイザ (RBO) コストベース オプティマイザ (CBO) 概要 使用可能なアクセスパスを順序づけるランキングに基づいて実行計画を作成 統計情報に基づきコストを見積もり 最もコストの低い実行計画を作成 特徴 新機能には対応しない OLTP 向き データの偏りや量は無視して実行計画を作成 新機能を使える OLTP DSS 共に有効 データの偏りや量に基づいて最適な実行計画を作成 備考 Oracle10g 以降はサポートされない 統計情報の取得が必要 13

13 Agenda オプティマイザとは コストベース オプティマイザでの運用管理 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 14

14 コストベース オプティマイザと統計情報 コストベース オプィテマイザは統計情報と初期化パラメータの情報を利用してアクセスコストを見積もり 実行計画を作成する 統計情報 : 表の行数 索引の大きさ など 初期化パラメータ :DB_FILE_MULTIBLOCK_READ_COUNT など 統計情報を適切に収集することが重要 15

15 CBO では統計情報の収集が必要 DBMS_STATS パッケージを利用 (Oracle 8.1 以降 ) 例 ) 表ごとの統計を収集 EXECUTE DBMS_STATS.GATHER_TABLE_STATS( SCOTT, EMP ); 例 ) スキーマ内のすべてのオブジェクトの統計を収集 EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS( SCOTT ); 例 ) データベース内のすべてのオブジェクトの統計を収集 EXECUTE DBMS_STATS.GATHER_DATABASE_STATS(); ANALYZE コマンド全行の統計情報を取得 ANALYZE TABLE 表名 COMPUTE STATISTICS; 16

16 コストベース オプティマイザの注意点 統計収集後 SQL 実行計画が変化し 性能が劣化した! 原因 統計情報が適切に取得されていない データが大幅に更新され 取得した統計情報が古くなっている 適切な精度 ( サンプルサイズ ) で収集されていない etc レスポンス 初期性能 性能劣化 2 データの変動に伴う性能劣化 実行計画 A 実行計画 B データの変動 17

17 自動オプティマイザ統計収集 10g 新機能 Oracle 10g からは オプティマイザが適切な実行計画を作成するのに必要な統計情報を自動で取得 統計情報をまだ収集していないオブジェクト 10% 以上データが更新されたオブジェクト 初期化パラメータ statistics_level が TYPICAL( デフォルト ) または ALL 統計情報は 平日の夜間と週末に自動収集 DBMS_STATS パッケージにおいて options に GATHER AUTO を指定するのと同様 18

18 11g からの自動オプティマイザ統計収集 自動メンテナンスタスク -GATHER_STATS_JOB の内容が変更 - 自動統計収集はデフォルトで有効 自動メンテナンスタスクが対応するデフォルトのウィンドウ : MONDAY_WINDOW から SUNDAY_WINDOW まで - 月曜日 ~ 金曜日それぞれの日に 22 時から翌 2 時までウィンドウがオープン 10g とちょっと変わりました - 土曜日 日曜日は AM6 時から 20 時間 ( 翌 2 時まで ) オープン 10g とちょっと変わりました 10g: 自動統計収集の無効化 スケジューラーから GATHER_STATS_JOB を DISABLE BEGIN DBMS_SCHEDULER.DISABLE('GATHER_ STATS_JOB'); END; / 11g: 自動統計収集の無効化自動メンテナンスタスクのうち オプティマイザ統計の収集をDISABLE BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE (client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / 19

19 自動オプティマイザ統計収集の設定変更 サーバー タブ > Oracle Scheduler 自動化メンテナンス タスク > 構成 統計の履歴の保存期間 サンプルサイズ 何 % 統計情報が失効したら 再度統計情報を収集するか 20

20 統計の収集タイミングの変更 サーバー タブ > Oracle Scheduler ウィンドウ 21

21 CBO で運用する際のリスク CBO で運用する限り避けられないリスク 統計収集後 SQL 実行計画が変化し 性能が劣化した! CBO で運用する上で最低限注意すべき事項 統計収集対象に 漏れ がないこと 列に対するヒストグラムも含め 必要なオブジェクトが必要なレベルで統計収集されていること 適切な精度 ( サンプルサイズ ) で収集されていること データの変動に応じた適切な頻度で再収集されていること システム状況の変動に応じてシステム統計が収集されていること 22

22 CBO で運用する際のリスク回避方法 :1 統計収集後 SQL 実行計画が変化し 性能が劣化する現象は起こり得ることとして運用を設計しておく 変化する前の統計情報に戻す 統計情報収集後に一部の SQL 性能が劣化したことが判明 現在の統計値をエクスポートして退避 適切な性能が出ていた過去の時点の統計情報をインポート エクスポートして退避した統計値を テスト環境にインポートして解析 問題分析中は新たに統計情報を収集しないようにする テスト環境での分析結果を受け 必要な対策を行った上で統計収集を再開 < 参考 > Oracle10g からは デフォルトで統計履歴が自動保存され 過去 31 日前までのどの時点にでも戻すことが可能 23

23 自動オプティマイザ統計収集のリストア サーバー タブ > 問合せオプティマイザ オプティマイザ統計の管理 > オプティマイザ統計のリストア オブジェクトの統計情報を新しく取得すると 前の統計情報は後にリストアするケースに備えて自動的に SYSAUX 表領域に保存される Oracle Enterprise Manager から簡単にリストア可能 デフォルトで 31 日間分保存 24

24 CBO で運用する際のリスク回避方法 :2 統計情報の変化に影響を受けないよう 実行計画を固定する 実行頻度が極めて高い ヒントを使用 その SQL の遅延がアプリケーション全体の性能に重大な影響を与える ストアド アウトラインを使用 (11g では非推奨 ) 運用中の実行計画変動のリスクを限りなくゼロにしたい 開発環境での実行計画を本番環境で確実に再現する必要がある Oracle のバージョンアップを考えているが バージョンアップ後も現在の実行計画を確実に保持したい 25

25 CBO で運用する際のリスク回避方法 3 SQL 計画管理 :SQL Plan Management(SPM) EE 実行計画が変わった場合は 検証をしてから適応させる SQL の実行計画を SQL Management Base で管理 使用する実行計画を Plan Baseline に指定 変更があった実行計画は Plan History に記録 常に Plan Baseline にある実行計画が使用される 実行 ( 運用時 ) プラン A SYSAUX 表領域 SQL Management Base Plan History Plan Baseline プランA プラン A 実行 ( システム変更後 ) プラン B プラン変更を検証し パフォーマンスが低下する場合は防止 27

26 SQL 計画管理 サーバー タブ > 問合せオプティマイザ SQL 計画管理 > SQL 計画ベースライン EE TRUE にすることで Baseline を取得 28

27 SQL 計画管理 サーバー タブ > 問合せオプティマイザ SQL 計画管理 > SQL 計画ベースライン EE ベースラインに保存した SQL の実行計画に新しい実行計画の候補があると新たに表示される 確定済 列の値は NO になっており まだこの実行計画は使用されていない 29

28 SQL 計画管理 EE 新しい実行計画は索引を使用する実行計画 元の実行計画は索引を使用しない実行計画 30

29 SQL 計画管理 サーバー タブ > 問合せオプティマイザ SQL 計画管理 > SQL 計画ベースライン EE 新しい実行計画を展開して 使用可能に 展開時に SQL 実行のコスト等を比較可能 31

30 SQL 計画管理 サーバー タブ > 問合せオプティマイザ SQL 計画管理 > SQL 計画ベースライン EE 新しい実行計画も 確定済 となり 使用可能に 1 つの SQL に使用可能な実行計画が複数ある場合は もっともコストが低いものが選択される 32

31 SQL 計画管理を活用したアップグレード ソリューション g のDB を起動 3 DB をシャットダウンし アップグレード アプリケーションを実行し 10g の SQL の実行計画でベースラインを取得 => 初期化パラメータ OPTIMIZER_FEATURES_ENABLE = 初期化パラメータ OPTIMIZER_FEATURES_ENABLE = ベースラインが有効化され アップグレード前の実行計画を使用して SQL を実行 4 データベースを再起動 ベースライン 初期化パラメータ OPTIMIZER_FEATURES_ENABLE = 初期化パラメータ OPTIMIZER_FEATURES_ENABLE =

32 新規の実行計画の通知 Diag Pack EE SQL 計画ベースラインを使用している SQL は 新規の実行計画が見つかっても自動的にはそれを使用しません Oracle Diagnostics Pack の機能を使用すると 新規の実行計画が見つかった場合に アラート通知を受けることが可能です メールでの通知も可能 34

33 アラート通知の設定方法 (1) ホーム タブ > 関連リンク ユーザー定義メトリック > 作成 Diag Pack EE 直近の 24 時間で ACCEPTED 列が NO になっているベースライン内のエントリ数を検索 35

34 アラート通知の設定方法 (2) ホーム タブ > 関連リンク ユーザー定義メトリック > 作成 Diag Pack EE 前のページの SQL の結果が 1 以上であれば アラートを生成 24 時間毎に監視 36

35 ストアドアウトラインから SPM へ移行 11gR2 よりストアドアウトラインから SQL ベースラインへ簡単に移行ができるようになりました ストアドアウトラインとはヒント句の集合であり 運用中に実行計画が変わることのリスクを出来る限り少なくするための機能 ( 今後のバージョンではサポートされなくなる予定 ) 11gR2 ではストアドアウトラインを SPM の SQL ベースラインへ移行が可能です DBMS_SPM.MIGRATE_STORED_OUTLINE の実行により移行 SPM へ移行することにより 新しく作成された実行計画のパフォーマンスを検証した後 割り当てが可能 複数の実行計画を利用可能にすることにより 一方が使用不可になったとしてもパフォーマンス劣化を防止できる 特定の SQL 文に複数の実行計画を管理できる 37

36 まとめ 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 38

37 パフォーマンス最適化のために コストベース オプティマイザと統計情報の理解 ルールベース オプティマイザは 10g から未サポート 自動オプティマイザ統計による適切な統計情報収集 SQL 実行計画の効果的な管理 SQL 計画管理を活用して 実行計画の変化に対応 11g へのアップグレードにも活用可能 39

38 あなたにいちばん近いオラクル Oracle Direct まずはお問合せください Oracle Direct 検索 システムの検討 構築から運用まで IT プロジェクト全般の相談窓口としてご支援いたします システム構成やライセンス / 購入方法などお気軽にお問い合わせ下さい Web 問い合わせフォーム フリーダイヤル 専用お問い合わせフォームにてご相談内容を承ります フォームの入力には Oracle Direct Seminar 申込時と同じログインが必要となります こちらから詳細確認のお電話を差し上げる場合がありますので ご登録されている連絡先が最新のものになっているか ご確認下さい 月曜 ~ 金曜 9:00~12:00 13:00~18:00 ( 祝日および年末年始除く ) 40

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