【実践】 パフォーマンス・チューニング～オプティマイザ活用術～

Transcript

1. 【実践】 パフォーマンス・チューニング 〜オプティマイザ活⽤術〜 稲葉 哲⼤ ⽇本オラクル株式会社 2024年5⽉
2. 1. SQL実⾏計画と問合わせオプティマイザ 2. SQL計画管理 3. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 2
3. Copyright © 2024, Oracle and/or its affiliates 3 本セミナーでは、以下のようなポイントをご理解いただくことをゴールとしています。 • SQL実⾏計画と問合せオプティマイザ • オプティマイザ統計とその収集⽅法 • SQL計画管理 ターゲットとなる層 • SQL実⾏計画と問合せオプティマイザの基本について学びたい⽅ • オプティマイザ統計の運⽤について学びたい⽅ 本セッションでは以下の内容については取り扱いません • カーソル共有とそれにともなう拡張機能(適応カーソル共有など) • 動的統計など、オプティマイザ統計情報が不⼗分である表への振る舞い • 適応計画など、SQL実⾏中に実⾏統計を補正する振る舞い 本セッションのゴール 参考資料: Oracle Database 12c〜19c統計情報および実⾏計画に関する新機能 Oracle Database Technology Night - オラクル・コンサルが語る︕-SQL性能を最⼤限に引き出す DB 12c クエリー・オプティマイザ新機能活⽤ と 統計情報運⽤の戦略
4. 1. SQL実⾏計画と問合わせオプティマイザ • SQL実⾏計画 • 問合わせオプティマイザ • コストベース・オプティマイザとルールベース・オプティマイザ • オプティマイザ統計 • オプティマイザ統計の収集(⾃動オプティマイザ統計) 2. SQL計画管理 3. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 4
5. 2種類のパフォーマンス・チューニング インスタンス・チューニングの検討事項 • データベースの初期設計でボトルネックを回避 • メモリの割り当て、I/O要件、オペレーティング・システムのチューニングを考慮 パフォーマンスの原理 • システム構成に応じた適切なリソース割り当て • ボトルネックを特定し、適切な変更をおこない、影響を低減または排除 ベースラインの重要性 • 実証されたパフォーマンス・ベースラインを使⽤して問題を⽐較 • アプリケーション、データベース、オペレーティング・システム、ディスクI/O、ネットワー クの統計を収集 症状および問題点 • パフォーマンスの低下の症状を正しく識別し、根本的な問題を理解 • 物理I/Oの遅さ、ラッチの競合、CPUの過剰使⽤などの問題点を考慮 チューニングを実施する時において • プロアクティブな監視による気付きとボトルネックの解消 • ボトルネックの解消には、アプリケーションの変更、Oracleの変更、ハードウェア構 成の変更などが含まれる SQLチューニングは、効率的なSQL⽂の作成が不可⽋であり、データベー スSQL処理エンジンに関する理解が重要 • ⼤量トランザクション・システムでは⾮効率なSQL⽂が使われないよう注意 • オンライン・トランザクション処理アプリケーションは索引の適切な使⽤が重要 • 意思決定⽀援システムでは多くの場合、全表スキャンが⾏われる 表のほとんどの⾏にアクセスする必要があるためであり、選択性が重視されない → スター・スキーマによる構造の簡素化とデータ取得のパフォーマンス向上 問合せオプティマイザおよび実⾏計画 • 問合せオプティマイザは、SQL⽂の実⾏計画を決定する際に、システム上で収 集された統計情報を活⽤する • これにより、最適な実⾏計画が選択され、処理時間が最⼩限に抑えられる Oracle Databaseのパフォーマンス・チューニング Copyright © 2024, Oracle and/or its affiliates 5 インスタンス・チューニング SQLチューニング マニュアル: データベース・パフォーマンス・チューニング・ガイド パフォーマンス・チューニングの概要 第10回「パフォーマンス・チューニング⼊⾨」より抜粋
6. ⼊⼒と出⼒の対応関係の定義 SQL (Structured Query Language) Copyright © 2024, Oracle and/or its affiliates 6 SELECT ename, dname, job, empno, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY ename 出⼒ 対応関係の定義 ⼊⼒ 論理データ構造 インターフェース
7. ⼊⼒と出⼒の対応関係の定義 SQL (Structured Query Language) Copyright © 2024, Oracle and/or its affiliates 7 SELECT ename, dname, job, empno, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY ename 何を取り出すかの定義であって、どうやって 導出するかのアルゴリズムは記述されていない 出⼒ 対応関係の定義 ⼊⼒ 論理データ構造 インターフェース
8. インターフェースとアルゴリズム実装の分離 SQLの結果集合を導出するアルゴリズムは⾃動⽣成される Copyright © 2024, Oracle and/or its affiliates 8 出⼒ 対応関係の定義 SQL実⾏計画 SQL実⾏計画探索 アルゴリズム 全体最適化問題 値の分布統計 物理データ構造 ハードウェア・リソース ⼊⼒ 論理データ構造 インターフェース
9. 出⼒ ⼊⼒ 論理データ構造 対応関係の定義 インターフェース インターフェースとアルゴリズム実装の分離 SQLの結果集合を導出するアルゴリズムは⾃動⽣成される Copyright © 2024, Oracle and/or its affiliates 9 SQL実⾏計画 SQL実⾏計画探索 アルゴリズム 全体最適化問題 値の分布統計 問合わせオプティマイザ 物理データ構造 ハードウェア・リソース オプティマイザ統計 SQL実⾏計画
10. SQL⽂の解析、最適化、⾏ソース⽣成および実⾏ SQL実⾏の内部動作 Copyright © 2024, Oracle and/or its affiliates 10 構⽂チェック セマンティクス・チェック 共有プール・チェック オプティマイザ ⾏ソースの⽣成 SQLの実⾏ データベースに関する情報を 提供する読取り専⽤の表の集合 SQL⽂ ハードパース ソフトパース 解析 共有プール ライブラリ・キャッシュ 共有SQL領域 SGA 構⽂チェック SQL⽂の構⽂上の妥当性のチェック。 SELECT * FORM employees; ORA-00923: FROM keyword not found where expected セマンティクス・チェック SQL⽂内のオブジェクトおよび列が存在するかどうかなど、⽂の意味が 有効かどうかを判断。 SELECT * FROM employee; ORA-00942: table or view does not exist 共有プール・チェック 共有SQL領域を検索して、既存の解析済のSQL⽂に同じハッシュ値 があるかどうかを確認。 • ある場合: ソフト解析(ソフトパース) • ない場合: ハード解析(ハードパース) ⾏ソースの⽣成 オプティマイザから最適なSQL実⾏計画を受け取り、SQLエンジンで 実⾏される反復実⾏計画を作成。 反復実⾏計画は⾏セットをどのオブジェクトからどのように取得するかと いう⾏ソースをツリー状に構成したもの。 ・・・ C1 C2 C3 SELECT * FORM employees; ORA-00923: FROM keyword not found where expected SELECT * FROM employee; ORA-00942: table or view does not exist
11. SQL実⾏計画とは SQL⽂を実⾏するためにデータベースが実⾏する⼀連の操作。 • SQL⽂の実⾏に必要な詳細なステップを⽰す • 各ステップは、⾏を⽣成したり消費したりするデータベース操作のセットとして表される • 各ステップで戻される⾏のセットを、⾏ソースという • 表⽰は⼀般的に表形式で表⽰されるが、実際の計画はツリー型 • 操作の順序と実装は、オプティマイザによって決定される SQL実⾏計画 Copyright © 2024, Oracle and/or its affiliates 11 SQL実⾏計画の表形式 SQL実⾏計画の実⾏ツリー HASH JOIN HASH JOIN TABLE ACCESS FULL jobs TABLE ACCESSS BY INDEX ROWID employees --------------------------------------------------------------------------- | Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time| --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 3 |189 |7(15)|00:00:01 | |*1| HASH JOIN | | 3 |189 |7(15)|00:00:01 | |*2| HASH JOIN | | 3 |141 |5(20)|00:00:01 | | 3| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 |2 (0)|00:00:01 | |*4| INDEX RANGE SCAN | EMP_NAME_IX | 3 | |1 (0)|00:00:01 | | 5| TABLE ACCESS FULL | JOBS |19 |513 |2 (0)|00:00:01 | | 6| TABLE ACCESS FULL | DEPARTMENTS |27 |432 |2 (0)|00:00:01 | --------------------------------------------------------------------------- TABLE ACCESSS FULL departments INDEX RANGE SCAN emp_name_ix 1 2 6 3 5 4
12. --------------------------------------------------------------------------- | Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time| --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 3 |189 |7(15)|00:00:01 | |*1| HASH JOIN | | 3 |189 |7(15)|00:00:01 | |*2| HASH JOIN | | 3 |141 |5(20)|00:00:01 | | 3| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 |2 (0)|00:00:01 | |*4| INDEX RANGE SCAN | EMP_NAME_IX | 3 | |1 (0)|00:00:01 | | 5| TABLE ACCESS FULL | JOBS |19 |513 |2 (0)|00:00:01 | | 6| TABLE ACCESS FULL | DEPARTMENTS |27 |432 |2 (0)|00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 2 - access("E"."JOB_ID"="J"."JOB_ID") 4 - access("E"."LAST_NAME" LIKE 'A%') filter("E"."LAST_NAME" LIKE 'A%') SQL実⾏計画のコンテンツ SQL実⾏計画は、⾏ソースの組み合わせ(⾏ソース・ツリー)で構成される。 ⾏ソース・ツリーには以下の情報が含まれる。 • SQL⽂で参照されている表の結合順序 • SQL⽂に記述された各表へのアクセス・パス • 索引レンジ・スキャン、全表スキャン など • SQL⽂の結合操作に影響される表の結合⽅法 • ネステッド・ループ結合、ハッシュ結合 など • フィルタ、ソート、集計などのデータ操作 SQL実⾏計画 Copyright © 2024, Oracle and/or its affiliates 12 結合⽅法 カーディナリティ アクセス・パス /結合順序 SQLチューニング・ガイド: 6.1 実⾏計画の概要 / 8 オプティマイザのアクセス・パス コスト フィルタなどの データ操作 各ステップごとのコストも表⽰される
13. データの状況に適したSQL実⾏計画を選択することが必要 全表スキャン(FULL SCAN) 索引スキャン(INDEX SCAN) 索引スキャン(INDEX SCAN) 効率が悪いケース データの状況により最適なSQL実⾏計画は違う Copyright © 2024, Oracle and/or its affiliates 13 同じ表と索引でも、データの状況(返すべきデータが表に存在する割合など)によって最適なSQL実⾏計画が異なる。 ユースケースによるが、多くの⾏にアクセスするほど、全表スキャンの⽅が⾼速になることが多い。 Row1 Row2 Row3 Row4 ・ ・ ・ 表 Row 1 Row 2 Row 3 Row 4 ・・ Row n ・・ 索引 表 Row 1 Row 2 Row 3 Row 4 ・・ Row n ・・ 索引 表
14. DBMSにおける最適なSQL実⾏計画の⽴案 SQL⽂が必要なデータにアクセスするための最も効率的な⽅法を判断する組込みデータベース・ソフトウェア。 • SQL⽂は、アルゴリズムを書かずにデータ抽出の条件のみを書けば良いという特徴がある • オプティマイザは、考えられる全ての計画の候補からコストが最⼩になるSQL実⾏計画(アルゴリズム)を選択する 問合わせオプティマイザ(オプティマイザ) Copyright © 2024, Oracle and/or its affiliates 14 SQL⽂(ヒント句) オブジェクト構造 初期化パラメータ 統計情報 実⾏環境 問合せ オプティマイザ レスポンス 実⾏計画 SQLチューニング・ガイド: 4 問合せオプティマイザの概念 / 19.2.1 オプティマイザの初期化パラメータについて ※以降のスライドでは、問合わせオプティマイザを "オプティマイザ" と記載します。
15. ルールベース・オプティマイザからコストベース・オプティマイザへ RBO (Rule-based Optimizer) CBO (Cost-based Optimizer) 概要 使⽤可能なアクセスパスをルール(順序づけるランキングと FROM句/WHERE句の順番)に基づいてSQL実⾏計画 を作成(OLTP向き)。 統計情報から算出したコスト(最も低い⾒積りコスト)に基 づいてSQL実⾏計画を作成(OLTP、DSS共に有効)。 メリット • アプリケーション開発者にとってRBOの考え⽅は理解し やすい • SQL実⾏計画の変動がほとんど起こらない • データの変動に追随できる • 機能強化の恩恵を受けられる • データ偏りや量に基づいてSQL実⾏計画を作成できる デメリット • データの変動に追随できない • データの偏りや量は考慮されない • 機能強化の恩恵を全く受けられない • Oracle Database 10g~ 未サポート • 統計情報の取得が必要 • 統計情報の再収集によって性能が変化するリスクがあ る オプティマイザの進化 Copyright © 2024, Oracle and/or its affiliates 15 以降のスライドでは、コストベース・オプティマイザを前提に説明します。
16. データ変動に追随するにはコストベース・オプティマイザ ルールベース・オプティマイザ コストベース・オプティマイザ ルールベース・オプティマイザとコストベース・オプティマイザ Copyright © 2024, Oracle and/or its affiliates 16 性能劣化が顕著になり SQLチューニングが必要 データの変動に伴い、 性能が劣化する可能性が存在する。 データの変動 実⾏時間 データの変動 に伴う性能劣化 リリース時 数年後 データの変動 実⾏時間 リリース時 数年後 データの変動を考慮して、 最適なSQL実⾏計画が⾃動的に使⽤される。 SQL 実⾏計画1 SQL 実⾏計画2 SQL 実⾏計画1 最適なSQL実⾏計画が使⽤される 使⽤されるSQL実⾏計画 使⽤されないSQL実⾏計画 SQL 実⾏計画3
17. 統計情報からコストを⾒積もり、最適なSQL実⾏計画を⽣成 考えられる全てのSQL実⾏計画の候補から、コストが最⼩になる計画を選択する。 • 複数のアクセス⽅法(全表スキャンや索引スキャンなど)、結合⽅法(ネステッド・ループ結合やハッシュ結合など)、 結合順序および可能な変換を調べることで、SQL⽂にとって最適なSQL実⾏計画を判断する • 計画の各ステップに数値コストを割り当てた後、計画全体のコスト⾒積もりを作成し、最もコストの低いSQL実⾏計画を選択する コストベース・オプティマイザ Copyright © 2024, Oracle and/or its affiliates 17 オプティマイザ スキーマ定義、 統計情報 など SQLチューニング・ガイド: 4.1.2 コストベースの最適化 問合せトランスフォーマ(Query Transformer) SQL⽂をより効率的に処理できる意味的に同じSQL⽂に 変換する。 プラン・ジェネレータ(Plan Generator) 各SQL⽂に異なるアクセス・パスや結合⽅法を使⽤した複 数のSQL実⾏計画を作成する。 その中から、最もコストの低いSQL実⾏計画を選択する。 エスティメータ(Estimator) 統計情報に基づき、各SQL実⾏計画の全体コストを ⾒積もる。 解析 オプティマイザ ⾏ソースの⽣成 SQLの実⾏ SQL⽂
18. オプティマイザの3つのコンポーネント 問合せトランスフォーマ • SQL⽂をより効率的に処理できる意味的に同じSQL⽂に変換 • 例: OR拡張、ビューのマージ、など プラン・ジェネレータ • 各SQL⽂に異なるアクセス・パスや結合⽅法を使⽤した 複数のSQL実⾏計画を作成 • 各プランのコスト計算から最⼩コストの計画を使⽤する エスティメータ(詳細は次ページ) プラン・ジェネレータで⽣成された各SQL実⾏計画の全体コストを判断する 以下の3つの計測を使⽤してコストを決定 • 選択性 • カーディナリティ • コスト(システムリソース、初期データ・セット、データ分布、アクセス構造) コストベース・オプティマイザ Copyright © 2024, Oracle and/or its affiliates 18 結合⽅法 • ハッシュ結合 • ネステッド・ループ結合 • ソート・マージ結合 オプティマイザ 結合順序 departments #0 employees #1 departments #1 employees #0 アクセス・パス • 索引スキャン • 全表スキャン プラン・ジェネレータ ハッシュ結合 departments #0, employees #1 最もコストの低いSQL実⾏計画 SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id; プラン・ジェネレータの動作例 (SQLチューニング・ガイド: 5 問合せ変換)
19. 選択性 エスティメータによる3つの計測 コストベース・オプティマイザ Copyright © 2024, Oracle and/or its affiliates 19 述語(条件)でフィルタされた⾏数の割合。 • どれだけの⾏数がフィルタを通過するかを⽰す • 0.0に近づくほど選択性が⾼くなる • 述語の例 • 従業員150⼈中Smithさんが1⼈の 場合は、選択性は1/150 = 0.006 SQL実⾏計画で、操作によって戻される予想⾏ 数または実際の⾏数。 • オプティマイザが結合のコストの判断をする際 に重要になる • カーディナリティの⾒積もり例 last_name = 'Smith' Employees表 ⾏数100、department_id列の個別値10 → 100/10=10 カーディナリティ10 SQL実⾏計画 エスティメータ 3 合計コスト 統計情報 選択性 カーディナリティ コスト カーディナリティ SQL実⾏計画のリソース使⽤量のコストの⾒積 もり。 • システム・リソース • I/O、CPU、メモリなど • 返される⾏数の⾒積もり(カーディナリティ) • 初期データ・セットのサイズ • データの分布 • アクセス構造 コスト
20. データベースおよびデータベース内のオブジェクト(表や列、索引 など)を特徴づける情報 オプティマイザは統計情報を使⽤して各SQL⽂に最適なSQL実⾏計画を選択する。 統計情報はデータ・ディクショナリに保存され、以下のような情報で構成される。 オプティマイザ統計 Copyright © 2024, Oracle and/or its affiliates 20 オプティマイザ SQL実⾏計画 索引統計 表統計 列統計 性別 部署ID 男 1 ⼥ 2 ⾏ 数 列数とサイズ レベル数 表統計 • ⾏数 • ブロック数 • ⾏の平均の⻑さ, etc. 列統計 • 列内の個別値(NDV数) • 列内のNULL数、 • データ配分(ヒストグラム) • 拡張統計, etc. 索引統計 • リーフ・ブロック数 • レベル数 • 索引クラスタ化係数, etc. システム統計 • I/Oパフォーマンスと使⽤率 • CPUパフォーマンスと使⽤率, etc. リーフ・ブロック数 統計情報 値の分布 (性別) 値の分布 (部署ID)
21. ヒストグラムとは 表における、列のデータ分布に関する詳細な情報を提供する統計情報(列統計)。 • デフォルトでは、オプティマイザは、列内の個別値全体で⾏のデータ配分が均⼀であると想定する • 列のデータが均⼀でない場合は、ヒストグラムを利⽤することでオプティマイザが正確なカーディナリティ予測を⽣成できるようになる オプティマイザ統計 Copyright © 2024, Oracle and/or its affiliates 21 DEPTNO 10⼈ ⼈数 1 2 3 4 ･･･ ･･･ 98 99 100 ヒストグラムがない場合のオプティマイザの予測 全体の1% DEPTNO 500⼈ ⼈数 1 2 3 4 ･･･ ･･･ 98 99 100 実際のデータの分布(部⾨1に50%配属) 全体の50% 10⼈ 従業員表のデータ分布の例 SELECT * FROM emp WHERE deptno = 1;
22. 2 3 4 ･･･ ･･･ 98 99 100 DEPTNO 500⼈ ⼈数 10⼈ DEPTNO 10⼈ ⼈数 2 3 4 ･･･ ･･･ 98 99 100 実際のデータの分布(部⾨1に50%配属) ヒストグラムとは 表における、列のデータ分布に関する詳細な情報を提供する統計情報(列統計)。 • デフォルトでは、オプティマイザは、列内の個別値全体で⾏のデータ配分が均⼀であると想定する • 列のデータが均⼀でない場合は、ヒストグラムを利⽤することでオプティマイザが正確なカーディナリティ予測を⽣成できるようになる オプティマイザ統計 Copyright © 2024, Oracle and/or its affiliates 22 1 ヒストグラムがない場合のオプティマイザの予測 全体の1% 1 全体の50% 全体の1%のデータへの検索と判断 索引スキャン 全体の50%のデータへの検索と判断 全表スキャン 従業員表のデータ分布の例 SELECT * FROM emp WHERE deptno = 1;
23. ヒストグラムの種類と仕組み ヒストグラムでは、コインをバケツに分けるように値をバケットに分類し、データの分布を詳細に提供する。 ヒストグラムには、以下の3つの種類がある。 • 頻度ヒストグラム、および上位頻度ヒストグラム • ⾼さ調整済ヒストグラム(レガシー) • ハイブリッド・ヒストグラム 頻度ヒストグラム • 列の各個別値ごとに1つのバケットを⽤意して値を分類する • バケット数の最⼤値は254個であるため、個別値数が254以下の場合に使⽤可能 オプティマイザ統計 Copyright © 2024, Oracle and/or its affiliates 23 頻度ヒストグラムのイメージ バケット1 バケット99 バケット2 バケット100 ・・・ 100 100 100 100 100 99 99 99 99 99 2 2 2 2 2 1 1 1 1 1 ・・ 1 1 1 1 1 1 1 1 1 1 500個 1 1 1 1 1
24. コストベース・オプティマイザは、統計情報と初期化パラメータの情報を利⽤してコストを⾒積もり、SQL実⾏計画を選択 する。 コストベース・オプティマイザの注意点 統計情報の収集後、SQL実⾏計画が変化し、性能が劣化した。 原因 統計情報が適切に取得されていない • データが⼤幅に更新され、取得した統計情報が古くなっている • 適切な精度(サンプルサイズ)で収集されていない 統計情報を適切に収集することが重要 統計情報を適切に収集することが重要 オプティマイザ統計 Copyright © 2024, Oracle and/or its affiliates 24 データの変動 実⾏時間 SQL実⾏計画1 SQL実⾏計画2 性能劣化
25. メンテナンス・ウィンドウに統計情報の⾃動収集 • 統計情報を定期的に更新するために、統計情報を⾃動収集する仕組み • 事前に定義した収集時間帯(メンテナンス・ウィンドウ)に収集。デフォルトの時間帯は以下の通り • ⽉曜~⾦曜: 午後10時に開始して、午前2時に終了 • ⼟曜~⽇曜: 午前6時に開始して、20時間後に終了 • 統計情報が未取得や古いオブジェクトを優先して⾃動的に収集 • デフォルト: データが10%以上変更されていた場合に再取得 ⾃動オプティマイザ統計収集 Copyright © 2024, Oracle and/or its affiliates 25 業務時間帯 メンテナンス・ウィンドウ ⾃動統計収集の実⾏ 表A 収集済み 表A 更新量10%以上 失効 (要再取得) 表A 業務時間帯 表A 更新量10%以上 表A 更新量10%以上 メンテナンス・ウィンドウ ⾃動統計収集の実⾏ 表C 表C 更新量10%未満 表C 更新量10%未満 収集 統計情報 表 オブジェクト 統計情報 表 オブジェクト 失効 (要再取得) 収集 未収集 収集 収集しない SQLチューニング・ガイド: 26.6.1 事前定義のメンテナンス・ウィンドウ
26. 統計収集するデフォルト・パラメータ値の変更 • ⾃動統計収集ジョブでは、DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROCプロシージャが使⽤される • このプロシージャは、他のDBMS_STATS.GATHER_*_STATSプロシージャと同じデフォルト・パラメータ値を使⽤ • パラメータ値を変更する場合は、 DBMS_STATS.SET_*_PREFプロシージャを使⽤する 実⾏例 • SALES表で、古い表とみなすしきい値(STALE_PRECENT)を5%に変更する⽅法 • 設定されているプリファレンスを確認する⽅法 ⾃動オプティマイザ統計収集 Copyright © 2024, Oracle and/or its affiliates 26 exec DBMS_STATS.SET_TABLE_PREFS(user,'SALES','STALE_PERCENT','5') SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',user,'SALES’) stale_percent FROM DUAL; STALE_PERCENT ------------- 5 SQLチューニング・ガイド: 13.1.3 コマンドラインからの⾃動オプティマイザ統計収集の構成 Oracle Database PL/SQLパッケージおよびタイプ・リファレンス: 172 DBMS_STATS
27. ⾃動オプティマイザ統計収集以外の収集⽅法 • 適度な速度で変更されるデータベース・オブジェクトに対しては、ほとんどの場合は⾃動収集で⼗分 • しかし、⾃動収集が適切でないか使⽤できないケースもあるもあるため、⼿動で統計情報を取得することも可能 オプティマイザ統計の収集⽅法 Copyright © 2024, Oracle and/or its affiliates 27 DBMS_STATSを使⽤した⼿動統計収集 • 必要な(⼤量に変更があった)ときを判断してユーザが実⾏ • DBMS_STATSパッケージを使⽤する⽅法の例 • データベース、スキーマ、表などのレベルで収集可能 オンライン統計収集 • ダイレクト・パス・ロード処理の⼀環で統計⽅法を収集 • Create Table As Select (CTAS) • Insert As Select (IAS) • データがロードされた直後に統計を利⽤でき、データ全体 のスキャンの追加実⾏が不要 • オンライン統計では、ヒストグラムや索引統計は収集されな い点に注意 exec DBMS_STATS.GATHER_DATABASE_STATS(); exec DBMS_STATS.GATHER_SCHEMA_STATS('SH’); exec DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES'); CREATE TABLE … AS SELECT …; INSERT /*+ append */ INTO … SELECT …; その他のケースにおける統計情報の収集については、ドキュメントを参照してください。(SQLチューニング・ガイド: 13.3.2 オプティマイザ統計の⼿動収集のガイドライン)
28. 1. SQL実⾏計画と問合わせオプティマイザ 2. SQL計画管理 • SQL計画管理とは • ⾃動SQL計画管理 3. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 28 参考資料: Oracle Database TechNight #64: Automatic SQL Plan Management (AutoSPM)はつかえるのか
29. インターフェース 11g以降のSQL実⾏計画制御⽅法 SQL計画管理(SQL Plan Management: SPM) Copyright © 2024, Oracle and/or its affiliates 29 SQL実⾏計画 SQL実⾏計画探索 アルゴリズム 全体最適化問題 値の分布統計 物理データ構造 ハードウェア・リソース SQL Plan Management SQL実⾏計画が複数存在し ていることを記憶しており承認 した計画が実⾏される 集積パッチ適⽤で実⾏ 計画策定アルゴリズムが 変化しない(18c〜) 出⼒ 対応関係の定義 ⼊⼒ 論理データ構造
30. なぜSQL実⾏計画の管理が必要なのか SQL実⾏計画 • 最も速いと「推定された」SQL実⾏計画が採⽤される • 速くなれば問題ないが遅くなる場合もある SQL計画管理(11g以降)によるソリューション • オプティマイザは「SQL実⾏計画」を管理する • 既知の承認済みSQL実⾏計画のみを使⽤ • 新しいSQL実⾏計画は検証される • 今後は同等またはより良いSQL実⾏計画のみが使⽤される SQL計画管理(SQL Plan Management: SPM) Copyright © 2024, Oracle and/or its affiliates 30 SQL実⾏計画が 複数存在している
31. 機能概要 オプティマイザ⾃⾝がSQL実⾏計画を時間経過とともに履歴として記録し、評価する管理メカニズム。 複数のSQL実⾏計画からSQL計画ベースラインを構築し、その中から最適なSQL実⾏計画を選択。 ⇒ SQL実⾏計画の安定性を保証するための仕組み SPMの3つのコンポーネント 1. 取得(⾃動/⼿動) • SQL実⾏計画を取得し、SQL計画履歴として記録する • 既存のSQL実⾏計画からのSQL計画ベースラインを作成する 2. 選択(SQL実⾏時) • SQL計画ベースラインの承認済みの実⾏計画だけが SQLで使⽤されるようにし、新しいSQL実⾏計画を 未承認の計画としてSQL計画履歴に記録 3. 展開/改良 • 新しいSQL実⾏計画のパフォーマンスを評価し、より優れた パフォーマンスのSQL実⾏計画をSQL計画ベースライン に組み込む SQL計画管理(SQL Plan Management: SPM) Copyright © 2024, Oracle and/or its affiliates 31 選択 取得 展開 SQL計画履歴 SQL計画1 SQL計画ベースライン SQL計画2 SQL計画3 承認 新しい計画 SQL計画4 ※SQL計画管理はStandard Edition2においても⼀部機能が利⽤可能です。 データベース・ライセンス情報ユーザー・マニュアル: 1.3 Oracle Database製品で許可される機能、オプションおよびManagement Pack
32. 3つのコンポーネント ⾃動取得 • 初期化パラメータ optimizer_capture_sql_plan_baselin es (デフォルト FALSE)をTRUEに設定 • 繰り返し実⾏されるSQL⽂に対してSQL 計画ベースラインが⾃動的に作成される • 対象となるSQL⽂をフィルタリング可能 • SQL Text、Schema、Module、Action ⼿動取得 • 特定の問題の発⽣したSQL⽂のみにSQL 計画ベースラインを設定 • 様々なソースよりロード可能 • SQL Tuning Set、カーソル・キャッシュ、 AWRリポジトリ、ステージング表、 既存のストアド・アウトライン • DBMS_SPMパッケージを利⽤ • dbms_spm.load_plans_from_sqlset など SQL⽂実⾏時の動作 • 初期化パラメータ optimizer_use_sql_plan_baselinesが TRUE (デフォルトTRUE)の時に、 SQL計画ベースラインの存在を確認 未承認のSQL実⾏計画の「検証」と SQL計画ベースラインへの「追加」 ⾃動タスク • メンテナンス・ウィンドウで実⾏ SYS_AUTO_SPM_EVOLVE_TASK • ⾼頻度⾃動タスク(19c〜) • Exadata、Autonomous Database (ADB)、 RU19.22以降のEnterprise Editionのみ • デフォルト60分間隔(ADBはデフォルト有効) ⼿動タスク • データベース管理者が個々のSQL計画履 歴を検証し、承認する 1. 展開タスクの作成 2. 展開アドバイザ・タスクの実⾏ 3. 展開レポートを⽣成して確認 4. 推奨された実⾏計画の承認 SQL計画管理(SQL Plan Management: SPM) Copyright © 2024, Oracle and/or its affiliates 32 取得 選択 展開/改良 HASH DEPT EMP ベースライン はあるか︖ SQL⽂ 発⾏ SQL 実⾏計画 が作成 NO 計画は ベースラインに あるか︖ YES YES HASH DEPT EMP HASH DEPT EMP 実⾏ NO NL DEPT EMP HASH DEPT EMP 既知のベースライン 計画の実⾏ SQL計画履歴に追加 (未承認のSQL計画) 保留
33. 18cまでの問題点 SQL計画ベースラインの取得が効果的に⾏うことができない。 • ⼿動は難しい、⾃動は必要ないものも取得される • ⾃動取得のフィルター機能で必要なものだけを取得できるようになった(バージョン12.2〜) • 性能まで判断できない • フィルターの設定が難しい • できれば問題が発⽣したSQLを⾃動で取得したい 19cでは、⾃動SQL計画管理により、実⾏計画管理のすべてのステップを⾃動化。 • Exadata、Autonomous Database、RU19.22以降のEnterprise Editionで使⽤可能 SQL計画管理の課題 Copyright © 2024, Oracle and/or its affiliates 33
34. SQLパフォーマンスの低下を⾃動的に検出して修復 ⾃動SQL計画管理は展開アドバイザ・タスクとして以下の 動作を⾏う。 1. ⼤量のシステム・リソースを消費するSQL⽂を識別するためにAWR (遅いSQLが格納されている)と⾃動SQLチューニング・セット (ASTS)を使⽤。 過去のパフォーマンス情報を使⽤して、パフォーマンス低下の可能 性があるかどうかを確認。 2. 特定されたSQL⽂の代替実⾏計画となるものを探す (⾒つかった計画はすべて取得され、SQL計画履歴に保存される) 3. SQL展開アドバイザのテストで、取得された計画が実⾏され、 どの計画がベストか判断される 4. 既存の計画よりパフォーマンスが良いことが判明した計画は、 SQL計画ベースラインに追加される 5. 問合せの劣化を回避するために、SQL計画ベースラインが計画を 制限する ⾃動SQL計画管理 Copyright © 2024, Oracle and/or its affiliates 34 ※ 上記の1、2は⾃動SPMとしての動作 SQL計画履歴 SQL計画 SQL計画ベースライン SQL計画 SQL計画 SQL カーソル・ キャッシュ AWR スナップショット スナップショット スナップショット スナップショット ASTS AWR スナップショット スナップショット スナップショット スナップショット ASTS TOP SQL HASH DEPT EMP NL DEPT EMP ②改良タスクが 代替計画を特定 ①改良タスク AWR/ASTS検査 SQL計画履歴 SQL計画 SQL計画ベースライン SQL計画 SQL計画 ③改良タスクの テスト実⾏ SQL計画履歴 SQL計画 SQL計画ベースライン SQL計画 SQL計画 ④SQL計画 ベースラインへ追加 SQL計画 追加 ⑤SQL計画 ベースラインを利⽤した SQL⽂の実⾏ テスト実⾏ ※ASTS: システムが管理するSQLチューニング・セット RU 19.7より提供 遅いSQL OK NG
35. ⾃動SQL計画管理を利⽤するためには「⾼頻度⾃動SPM展開 アドバイザ・タスク」を有効にする • ⾼頻度⾃動SPM展開アドバイザ・タスクの設定として以下の パラメータをセット • ALTERNATE_PLAN_BASELINE (ロードする代替実⾏計画を決定) • ⾮ADBのデフォルトは'EXISTING' (ベースラインが存在するSQLに対してのみロードする) • ADBのデフォルトは'AUTO' (⾃動SPMモードとして動作) • ALTERNATE_PLAN_SOURCE (代替実⾏計画の検索するソースを決定) • デフォルトは'AUTO' (⾃動的にソースが選択される) • 対象のソースは以下になる • CURSOR_CACHE • AUTOMATIC_WORKLOAD_REPOSITORY • SQL_TUNING_SET (すべてSTSが対象) ※⾃動SPMはSQL計画ベースラインを取得して承認するため、⾃動計画取得を 設定せずに済む • 初期化パラメータoptimizer_capture_sql_plan_baselines = FALSEのまま ⾃動SQL計画管理の制御 Copyright © 2024, Oracle and/or its affiliates 35 SQL> BEGIN 2 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK’, 4 parameter => ' ALTERNATE_PLAN_BASELINE', 5 value => 'AUTO’); 6 7 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 8 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK’, 9 parameter => 'ALTERNATE_PLAN_SOURCE’, 10 value => 'AUTO’); 11 END; 12 / パラメータの設定 ⾼頻度⾃動SPM展開アドバイザを有効化 SQL> Exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON'); ⾼頻度⾃動SPM展開アドバイザを無効化 SQL> Exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
36. 1. SQL実⾏計画と問合わせオプティマイザ 2. SQL計画管理 3. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 36
37. • SQL実⾏計画: SQLを実⾏するためにデータベースが実⾏する⼀連の操作で、複数のステップで構成される • 表の結合順序やアクセス・パス(索引スキャンや全表スキャンなど)、表の結合⽅法(ネステッド・ループ結合やハッシュ結合など)、データ操作(フィルタ・ ソート・集計など)に関する情報が含まれる • オプティマイザ: 考えられる全てのSQL実⾏計画の候補から、統計情報を利⽤してコストを⾒積もり、最適なSQL実⾏計画を決定する • プラン・ジェネレータが複数のSQL実⾏計画を作成し、エスティメータが各計画の全体コストを算出する まとめ Copyright © 2024, Oracle and/or its affiliates 37 SQL実⾏計画とオプティマイザ オプティマイザ統計とその収集⽅法 SQL計画管理 • オプティマイザ統計: 表の⾏数や⾏の⻑さ、列の値の分布、索引のレベル数など、データベース内のオブジェクトを特徴づける情報 • オプティマイザが最適な実⾏計画を選択するためには、統計情報を適切に収集することが重要 • ⾃動オプティマイザ統計収集によって、メンテナンス・ウィンドウ内で古かったり、未取得の統計情報を定期的に取得できる • 複数の実⾏計画からSQL計画ベースラインを構築し、その中から最適な実⾏計画を選択することで、SQL実⾏計画の安定性を保証する仕組み • 19cからは、⾃動SQL計画管理により、SQL計画管理のすべてのステップを⾃動化