Oracle Database 12c～19c 統計情報および実行計画に関する新機能

Oracle Database 12c～19c 統計情報および実行計画に関する新機能日本オラクル株式会社 2021 年 5 月

Safe harbor statement 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することを確約するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリース、時期及び価格については、弊社の裁量により決定され、変更される可能性があります。 Copyright
© 2021, Oracle and/or its affiliates | 2

一覧 • 適応問合せ最適化 • ヒストグラムの拡張 • SPM展開アドバイザ • 同時統計収集 •
パーティションの統計情報の拡張 • DBMS_STATSパッケージのレポート作成モード • その他 12cR1新機能 Copyright © 2021, Oracle and/or its affiliates | 4

概要よりよい実行計画を選択できるように実行計画を調整するための補助的な機能の総称初期化パラメータoptimizer_adaptive_features で制御 (デフォルトTrue) • Falseにしても本機能のための統計情報の収集は実施適応問合せ最適化 Copyright ©
2021, Oracle and/or its affiliates | 5 適応問合せ最適化適用問合せ計画 (適応計画) 適応統計自動再最適化 SQL計画ディレクティブ動的統計 SQL実行時に結合やパラレル度の調整を図る計画統計と実行統計の乖離が激しい場合に、次回以降の実行時に実行統計を補助統計として利用 11gまでの動的サンプリング機能を改称。統計情報が存在する表に対しても動的な統計情報収集を実施カーディナリティ誤りをSYSAUX表領域に保存

適用問合せ計画適応問合せ最適化 Copyright © 2021, Oracle and/or its affiliates |
6 初回の実行計画の作成、実行 SQL実行中に統計コレクタが実行統計を確認実行統計が内部的な閾値を上回った場合、実行計画を修正 • 最初のステップ(一つ目の索引や表のスキャン)は普通に実行 • SQLを実行しながら実行統計を収集 • 計画統計と実行統計の乖離が発生する主要なケース • 統計情報が古い • 統計情報の取り方が甘い(ヒストグラムがない、等) • 実行計画を修正するのは以下のアクション • 結合方法(例：Nested Loop ⇒ Hash) • パラレル処理の分散方法 (例 : PX SEND RANDOM ⇒ PX SEND HASH)

自動再最適化適応問合せ最適化 Copyright © 2021, Oracle and/or its affiliates |
7 SQL実行時に計画統計と実行統計の大幅な乖離を検知カーソル内に実行統計の情報を記録該当のSQLを再実行適用問合せ計画との違い • 2回目の実行時に動作 • 適用問合せ計画は1回目に動作 • 実行計画を再作成 • 統計フィードバック(11gまでのカーディナリティ・フィードバック) を実施 • パフォーマンス・フィードバックを実施 • 自動パラレル度の最適化 • 結合方法やパラレル処理の分散方法以外の部分(例えば結合する表の順番など)も変更される可能性があるカーソル内の統計を使用して実行計画を再作成

SQL計画ディレクティブの概要適応問合せ最適化 Copyright © 2021, Oracle and/or its affiliates |
8 SQL実行時にカーディナリティ誤りを検知すると、作成されるオブジェクト • 本機能のみの停止は不可(適応問合せ最適化全体を停止する必要あり) 拡張統計の取得の際に用いられる拡張統計の収集が不十分な場合、動的統計を使用 SYSAUX表領域に保存 • 53週以内に使用されなかったディレクティブは削除される DBMS_SPDパッケージを使用して、ディレクティブ個別に無効化や削除が可能

動的統計の概要適応問合せ最適化 Copyright © 2021, Oracle and/or its affiliates |
9 11gまでは動的サンプリングと呼ばれていた機能 • 前提条件(後述の事項)を満たしている場合、SQL実行時に定められた数のランダムなブロックをインプットとして、補助的な統計情報を収集 • 本機能で収集された統計情報は、統計を収集したSQL以外のSQLでも利用され得る旧動的サンプリングとの違い • パラレル処理で実行される可能性がある • SQL計画ディレクティブが存在する場合に働く 11gまでの初期化パラメータoptimizer_dynamic_samplingおよびヒントdynamic_samplingも引き続き有効 • 値を0に設定すれば動的統計の停止が可能

SQL文実行時に統計が不十分な場合の動作の流れ適応問合せ最適化 Copyright © 2021, Oracle and/or its affiliates |
10 SQL文の実行(初回) 自動再最適化(statistics feedback)を実施カーソルが残っている場合ディレクティブにより動的統計の収集を実施・適用計画・実行時統計のメモリ上への保存・SQL 計画ディレクティブの作成カーソルがエージアウトなどで存在しない場合同じSQL文の実行統計収集 (dbms_stats) ・SQL 計画ディレクティブから収集データを追加・・・

概要ヒストグラムの拡張 Copyright © 2021, Oracle and/or its affiliates |
11 AUTO_SAMPLE_SIZE指定時にフルスキャンで頻度ヒストグラムを作成 • AUTO_SAMPLE_SIZEはデフォルトかつ推奨の指定 • 近似値アルゴリズムを使用することで、フルスキャンでも低い負荷で情報収集を実施 • 11gまではサンプルベースの頻度ヒストグラム ⇒ 値の分布次第では実行計画が狂う可能性バケット数が254から2048に拡張 • デフォルトは254のままヒストグラムの列サイズが32バイトから64バイトに拡張新しいタイプのヒストグラム(後述) • 上位頻度ヒストグラム • ハイブリッド・ヒストグラム

頻度ヒストグラムと高さ調整済ヒストグラム (参考) 11gまでのヒストグラムの種類 Copyright © 2021, Oracle and/or its affiliates
| 12 頻度ヒストグラム • NDV<=254(ないし指定したバケット数)の場合に作成される、シンプルに値ごとにバケットを持つヒストグラム高さ調整済ヒストグラム • NDV>254 (ないし指定したバケット数)の場合に作成されるヒストグラム • DBMS_STASでの統計情報収集時のESTIMATE_PERCENTパラメータがAUTO_SAMPLE_SIZE(デフォルト)以外の場合にも作成される • 「高さ調整」＝各バケットに格納されているデータ数が同じ • 頻度ヒストラムと異なり、複数の値のデータが格納される • 件数の多いデータがあると、Endpoint Valueが同じバケットが複数存在する場合がある ⇒一番最後以外の同じEndpoint Valueのバゲット削除されるため、254よりも少ないバケット数になる場合がある NDV(Number of Distinct Values) : 個別の値の数

上位頻度ヒストグラムヒストグラムの拡張 Copyright © 2021, Oracle and/or its affiliates |
13 AUTO_SAMPLE_SIZEを指定かつNDVがバケット数を超える場合に作成されるヒストグラム • AUTO_SAMPLE_SIZE以外をしている場合は11gまでの高さ調整済ヒストグラムを作成バケット数の上位の値のみで頻度ヒストグラムを作成バケット数の上位の値のデータが大部分(バケット数 < (1 – (1/バケット数)) * 100)を占める場合に作成されるそうでない場合はハイブリッド・ヒストグラムが作成される NDV(Number of Distinct Values) : 個別の値の数

ハイブリッド・ヒストグラムヒストグラムの拡張 Copyright © 2021, Oracle and/or its affiliates |
14 高さ調整済ヒストグラムと頻度ヒストグラムを組み合わせたヒストグラム内部的には以下の過程を経て作成される ① 頻度ヒストグラムを作成 ② 同一の値は同一のバケットに入るように調整 ③ エンドポイントの繰り返しカウントをヒストグラム内に保持 ① ② ③

ヒストグラムの選択ロジック Copyright © 2021, Oracle and/or its affiliates | 15
• 11gからのアップグレード • ESTIMATE_PERCENTパラメータが AUTO_SAMPLE_SIZE以外 NDVがバケット数以下バケット数の上位の値のデータがバケット数 < (1 – (1/バケット数)) * 100) Yes 高さ調整済みヒストグラム Yes 頻度ヒストグラム Yes 上位頻度ヒストグラムハイブリッド・ヒストグラム No No No

概要 SPM展開アドバイザ Copyright © 2021, Oracle and/or its affiliates |
16 自動メンテナンス・タスクSYS_AUTO_SPM_EVOLVE_TASKによる、計画ベースラインの自動メンテナンス Plan history Plan baseline NL NL GB HJ HJ GB ①未承認の計画をチェック（メンテナンス・ウィンドウ） ②既存の計画より良いかのチェック（テスト実行） Plan history HJ HJ GB Plan baseline NL NL GB ③元の計画よりパフォーマンスが高い計画は計画ベースラインに追加する（推奨する） L ④元の計画よりもパフォーマンスが良くない計画は計画履歴に残り、未承認とマークされlast_verified が更新される NL NL GB

同時統計収集 Copyright © 2021, Oracle and/or its affiliates | 17
複数の表やパーティションの統計情報を、ジョブを複数起動することで同時に収集することが可能に • デフォルトは無効 • 初期化パラメータJOB_QUEUE_PROCESSESの範囲内で並列に収集を実施 • 本機能を利用する場合は4以上の値を設定する必要あり • CPUリソースを使い切る可能性があり、それが問題になる場合はResource ManagerでCPU使用率を制限 DBMS_STATS.SET_GLOBAL_PERFSプロシージャで利用可否を設定 • 第1引数：「CONCURRENT」 • 第2引数：設定値 MANUAL AUTOMATIC ALL OFF(デフォルト) 手動収集の場合有効無効有効無効自動収集の場合無効有効有効無効

パーティションの統計情報の拡張 Copyright © 2021, Oracle and/or its affiliates | 18
シノプシス(増分統計)の拡張 • 表レベルのシノプシスの収集が可能に • EXCHANGE PARTITION時にシノプシスも交換されるようになった増分統計において、(サブ)パーティションの統計がロック/失効されていてもグローバル統計の更新が実施されるようになった

DBMS_STATSパッケージのレポート作成モード Copyright © 2021, Oracle and/or its affiliates | 19
REPORT_GATHER_ で始まる名称のファンクション • 統計情報収集対象オブジェクトをレポート(CLOB)に出力 • 実際の統計情報収集は実施しない REPORT_STATS_OPERATIONSファンクション • 指定期間内に実行された統計情報の操作(収集、削除等)をレポート(CLOB)に出力 REPORT_SINGLE_STATS_OPERATIONファンクション • 指定した操作IDの統計オペレーションをレポート(CLOB)に出力

その他 Copyright © 2021, Oracle and/or its affiliates | 20
ストアド・アウトラインの非推奨 • SPMの利用を推奨列統計の拡張 • サイズが32バイトから64バイトに拡張 • 列グループ統計(11gR2新機能)の自動作成グローバル一時表の統計情報の共有/セッション個別保持の選択が可能に • DBMS_STATS.SET_GLOBAL_PERFSプロシージャで利用可否を設定 • 第1引数：「GLOBAL_TEMP_TABLE_STATS」 • 第2引数：「SHARED」(すべてのセッションで統計情報を共有)もしくは「SESSION」(セッションごとに統計情報の収集の必要あり)

一覧 • Adaptive 機能のパラメーター変更 • Optimizer Statistics Advisor • 統計情報収集の強化
• その他 12cR2新機能 Copyright © 2021, Oracle and/or its affiliates | 22

概要 Adaptive機能のパラメーター変更 Copyright © 2021, Oracle and/or its affiliates |
23 optimizer_adaptive_features optimizer_adaptive_plans optimizer_adaptive_statistics 分割!! Adaptive機能を使用しないやや保守的な Adaptive アプローチ (デフォルト) 新規の複雑なクエリーにもベストパフォーマンス optimizer_adaptive_plans FALSE TRUE TRUE optimizer_adaptive_statistics FALSE FALSE TRUE 12cR1より本パラメータは廃止

概要統計情報の収集状況を調査し、問題点を提示するレポートを出力 • 推奨される解決策と、問題の解決を図るSQLスクリプトも出力可能 Optimizer Statistics Advisor Copyright © 2021,
Oracle and/or its affiliates | 24 Rules Findings Recommendations Actions

実施の流れ(DBMS_STATSパッケージを使用) Optimizer Statistics Advisor Copyright © 2021, Oracle and/or its
affiliates | 25 CREATE_ADVISOR_TASK CONFIGURE_ADVISOR_*_FILTER EXECUTE_ADVISOR_TASK REPORT_ADVISOR_TASK IMPLEMENT_ADVISOR_TASK SCRIPT_ADVISOR_TASK アドバイザ・タスクの作成タスク対象の絞り込みアドバイザ・タスクの実行アドバイザ・レポートの出力推奨事項の即時適用推奨事項のスクリプトの生成自動実行で収集された情報を使用する場合は不要

実行方法メンテナンスウィンドウ中のジョブでの実行がデフォルトで設定されている • 1日に1回実施 • 停止することも可能 • 手動の実行も可能 • DBMS_STATS.CREATE_ADVISOR_TASK
⇒ DBMS_STATS. EXECUTE_ADVISOR_TASK Optimizer Statistics Advisor Copyright © 2021, Oracle and/or its affiliates | 26 SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual; DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK') -------------------------------------------------------------------------------- FALSE

アドバイザー・ルール V$STATS_ADVISOR_RULESビューでアドバイス対象のルールの確認が可能以下の3つのルール・クラスが存在(カッコ内はルール番号) • SYSTEM(1～6) : システム設定関連グローバル・プリファレンスのデフォルト値の推奨、SQL計画ディレクティブの有効化、など • OPERATION(7～10)
: 統計情報収集関連不要な統計情報の収集(更新のない表への収集等)の指摘、デフォルト値の推奨、など • OBJECT(11～23) : オブジェクト関連 ANALYZE TABLEコマンドの非推奨、パラレル統計情報収集の推奨、など収集対象のオブジェクトやルールのフィルタリングが可能 • DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER : ルールのフィルタリング • DBMS_STATS. CONFIGURE_ADVISOR_OPR_FILTER : タスクの操作のフィルタリング • DBMS_STATS. CONFIGURE_ADVISOR_OBJ_FILTER : オブジェクトのフィルタリング Optimizer Statistics Advisor Copyright © 2021, Oracle and/or its affiliates | 27

レポートのサンプル Optimizer Statistics Advisor Copyright © 2021, Oracle and/or its
affiliates | 28 GENERAL INFORMATION ------------------------------------------------------------------------------- Task Name : AUTO_STATS_ADVISOR_TASK Execution Name : EXEC_136 Created : 09-05-16 02:52:34 Last Modified : 09-05-16 12:31:24 ------------------------------------------------------------------------------- SUMMARY ------------------------------------------------------------------------------- For execution EXEC_136 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor has 3 findings. The findings are related to the following rules: USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL. Please refer to the finding section for detailed information. ------------------------------------------------------------------------------- FINDINGS ------------------------------------------------------------------------------- Rule Name: UseDefaultParams Rule Description: Use Default Parameters in Statistics Collection Procedures Finding: There are 367 statistics operations using nondefault parameters. Recommendation: Use default parameters for statistics operations. Example: -- Gathering statistics for 'SH' schema using all default parameter values: BEGIN dbms_stats.gather_schema_stats('SH'); END; Rationale: Using default parameter values for statistics gathering operations is more efficient. -------------------------------------------------------------------------------

下記12cR2新機能への対応 • 外部表のパーティション化 • HDFS外部表外部表スキャン速度の統計情報収集 • DBA_TABSTATICTICSビュー、DBA_TAB_PENFING_STATSビューのSCANRATE列 DBMS_STATSでインメモリ列ストアの統計情報の取得が可能に •
収集対象はIMCU数(im_imcu_count)、ブロック数(im_block_count) DBA_TAB_STSTISTICS, DBA_IND_STATISTICS, DBA_TAB_MODIFICATIONSの情報が常に最新の情報を表示するようになった • 従来はDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOを使用して、メモリ上の情報をディスクに反映させる必要があった⇒上記ビューがメモリとディスクの両方から情報を取得統計情報収集の強化 Copyright © 2021, Oracle and/or its affiliates | 29

Active Data GuradのスタンバイでSQLチューニング・アドバイザの起動が可能に • データの書き込み先はスタンバイではなく、データベース・リンクを介してプライマリに対して実施 SQL計画ディレクティブで、ワークロードの述語の記述に基づいて、列グループ統計の作成を発生させることが可能に • DBMS_STATS.SET_*_PERFSプロシージャで利用可否を設定 •
第1引数：「AUTO_STAT_EXTENSIONS」 • 第2引数：「ON」(デフォルトは「OFF」) SPMにて実行計画取得のソースとしてAWRが追加その他 Copyright © 2021, Oracle and/or its affiliates | 30

一覧 • パフォーマンス・フィードバックの独立化 18c新機能 Copyright © 2021, Oracle and/or its
affiliates | 32

• パフォーマンス・フィードバックが初期パラメータOPTIMIZER_ADAPTIVE_STATISTICSから独立 • 本パラメータをFALSEにしてもパフォーマンス・フィードバックは稼働 18c新機パフォーマンス・フィードバックの独立化 Copyright © 2021, Oracle and/or
its affiliates | 33

一覧 • ヒント・レポート • 実行計画の比較 • リアルタイム統計収集 • 高頻度の統計情報取得 •
パーティション操作時のオンライン統計収集 • その他 19c新機能 Copyright © 2021, Oracle and/or its affiliates | 35

SQL> EXPLAIN PLAN FOR 2 SELECT /*+ INDEX(t2) FULL(@sel$2 t1)
*/ COUNT(*) FROM jobs t2 3 WHERE t2.job_id IN (SELECT /*+ FULL(t1) UNNEST */ job_id FROM employees t1); SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'HINT_REPORT')); 概要 • 実行計画の出力に、ヒントに関する情報が追加された • ヒントの使用有無、未使用の理由が明確にヒント・レポート Copyright © 2021, Oracle and/or its affiliates | 36 ヒント・レポート絡みの引数従来からの引数使用されないヒントのみ出力 HINT_REPORT_UNUSED TYPICAL 使用されるヒントのみ出力 HINT_REPORT_USED N/A 両方出力 HINT_REPORT ALL

出力例ヒント・レポート Copyright © 2021, Oracle and/or its affiliates |
37 Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 4 (U - Unused (1)) --------------------------------------------------------------------------- 4 - SEL$5DA710D3 / T1@SEL$2 U - FULL(t1) / hint overridden by another in parent query block ヒント未使用の理由 • 「E」 : ヒントの構文エラー (存在していないヒントを指定した場合など) • 「N」 : 未解決のヒント (存在していないテーブルを指定した場合など) • 「U」 : 最終的に使用されなかったヒント (ヒントが他のクエリーブロックと重複している場合など) • 実行計画の行番号に対応 • 0の場合は実行計画に出てこないヒント

DBMS_XPLAN.COMPARE_PLANSファンクション実行計画の比較 Copyright © 2021, Oracle and/or its affiliates |
38 REFERENCE_PLAN • 比較される実行計画 – ひとつだけ指定 COMPARE_PLAN_LIST • 比較する実行計画 – 複数指定 TYPE • TEXT, HTML, XMLのどれか LEVEL • BASIC, TYPICAL, ALLのどれか SECTION • SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALLのどれか DBMS_XPLAN.COMPARE_PLANS( reference_plan IN generic_plan_object, compare_plan_list IN plan_object_list, type IN VARCHAR2 := 'TEXT’, level IN VARCHAR2 := 'TYPICAL’, section IN VARCHAR2 := 'ALL’) RETURN CLOB;

実行例実行計画の比較 Copyright © 2021, Oracle and/or its affiliates |
39 ふたつのSQLの実行計画を比較して、結果を出力 select count(*) from products p, sales s where p.prod_id = s.prod_id and p.prod_min_price > 200; select count(*) from products p, sales s where p.prod_id = s.prod_id and s.quantity_sold = 43; SQL_ID: 10dqxjph6bwum SQL_ID: 0hxmvnfkasg6q VARIABLE v_rep CLOB BEGIN :v_rep := DBMS_XPLAN.COMPARE_PLANS( reference_plan => cursor_cache_object('0hxmvnfkasg6q', NULL), compare_plan_list => plan_object_list( cursor_cache_object('10dqxjph6bwum', NULL) ), type => 'TEXT', level => 'TYPICAL', section => 'ALL'); END; / SET PAGESIZE 50000 SET LONG 100000 LINESIZE 210 COLUMN report FORMAT a200 SELECT :v_rep REPORT FROM DUAL;

REFERENCE_PLAN, COMPARE_PLAN_LIST引数の指定対象実行計画の比較 Copyright © 2021, Oracle and/or its affiliates
| 40 計画ソース引数指定方法 PLAN TABLE plan_table_object(owner, plan_table_name, statement_id, plan_id) カーソル・キャッシュ cursor_cache_object(sql_id, child_number) AWR awr_object(sql_id, dbid, con_dbid, plan_hash_value) SQLチューニング・セット sqlset_object(sqlset_owner, sqlset_name, sql_id, plan_hash_value) SQL計画管理 spm_object(sql_handle, plan_name) SQLプロファイル sql_profile_object(profile_name) アドバイザ advisor_object(task_name, execution_name, sql_id, plan_id)

出力例実行計画の比較 Copyright © 2021, Oracle and/or its affiliates |
41 REPORT --------------------------------------------------------------------------------------------- COMPARE PLANS REPORT --------------------------------------------------------------------------------------------- Current user : SH Total number of plans : 2 Number of findings : 1 --------------------------------------------------------------------------------------------- COMPARISON DETAILS --------------------------------------------------------------------------------------------- Plan Number : 1 (Reference Plan) Plan Found : Yes Plan Source : Cursor Cache SQL ID : 0hxmvnfkasg6q Child Number : 0 Plan Database Version : 19.0.0.0 Parsing Schema : "SH" SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and s.quantity_sold = 43 Plan ----------------------------- Plan Hash Value : 4261227730 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 20676 | | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | NESTED LOOPS | | 1 | 11 | 20676 | 00:00:01 | | 3 | PARTITION RANGE ALL | | 1 | 7 | 20676 | 00:00:01 | | * 4 | TABLE ACCESS FULL | SALES | 1 | 7 | 20676 | 00:00:01 | | * 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 0 | | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 4 - filter("S"."QUANTITY_SOLD"=43) * 5 - access("P"."PROD_ID"="S"."PROD_ID") --------------------------------------------------------------------------------------------- Plan Number : 2 Plan Found : Yes Plan Source : Cursor Cache SQL ID : 10dqxjph6bwum Child Number : 0 Plan Database Version : 19.0.0.0 Parsing Schema : "SH" SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and p.prod_min_price > 200 Plan ----------------------------- Plan Hash Value : 585022814 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 318 | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | NESTED LOOPS | | 1837666 | 23889658 | 318 | 00:00:01 | | * 3 | TABLE ACCESS FULL | PRODUCTS | 9 | 81 | 3 | 00:00:01 | | 4 | PARTITION RANGE ALL | | 204187 | 816748 | 318 | 00:00:01 | | 5 | BITMAP CONVERSION COUNT | | 204187 | 816748 | 318 | 00:00:01 | | * 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - filter("P"."PROD_MIN_PRICE">200) * 6 - access("P"."PROD_ID"="S"."PROD_ID") Comparison Results (1): ----------------------------- 1. Query block SEL$1: Join order is different at position 1 (reference plan: "S"@"SEL$1", current plan: "P"@"SEL$1"). --------------------------------------------------------------------------------------------- NL結合の外部表はPRODUCTS NL結合の外部表はSALES Comparison Results (1): ----------------------------- 1. Query block SEL$1: Join order is different at position 1 (reference plan: "S"@"SEL$1", current plan: "P"@"SEL$1"). 結合順序が違う、と報告

概要 DMLの発行時に統計情報を収集するようになった • 通常の統計情報は別の領域に格納 • NO_GATHER_OPTIMIZER_STATISTICSヒントを入れることで収集の抑止が可能 • 19.10以降は初期化パラメータOPTIMIZER_REAL_TIME_STATISTICSで有効化/無効化が可能 ※デフォルトはFALSE(無効)、19.3～19.9からのアップグレードの場合、実行計画が変更になるSQLが発生する可能性あり
パフォーマンスに影響を与えないよう、最低限の情報を収集 • MIN、MAX、NUM_ROWSなど、すべてのデータを参照する必要がない情報のみリアルタイム統計収集 Copyright © 2021, Oracle and/or its affiliates | 42 Exadataのみ

概要統計が最新であることを知っている場合、問合せに統計を使用して回答することができる • 変換するとビュー名が VW_SQT_xxxx になる変換実施の可否は以下によって決まる • 集計の種類 •
MIN, MAX, COUNT, APPROXIMATE_COUNT_DISTINCT • 統計の正確さ（以下が不正確にする要因） • サンプリング統計、最後の統計収集後に発生したDML、統計のインポートやDBMS_STATS.SET_STATSの人工統計など • 問合せの種類（WHERE句の述語がないなど） • 値のグループに関する集計情報が統計にないため • 問合せの場所（問合せがトランザクション内にない） Statistics-Based Query Transformation Copyright © 2021, Oracle and/or its affiliates | 43 Exadataのみ SQL> SELECT MAX(amt), MAX(volume) FROM sales; ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | VIEW | VW_SQT_65BBF4BE | | 2 | SORT AGGREGATE | | | 3 | PX COORDINATOR | | | 4 | PX SEND QC (RANDOM) | :TQ10000 | | 5 | SORT AGGREGATE | | | 6 | PX BLOCK ITERATOR | | | 7 | TABLE ACCESS FULL | SALES | ----------------------------------------------------

実行例リアルタイム統計収集 Copyright © 2021, Oracle and/or its affiliates |
44 SQL> INSERT INTO sales2 SELECT FROM sales; ------------------------------------------------ |Id| Operation | Name | ------------------------------------------------ | 0| INSERT STATEMENT | | | 1| LOAD TABLE CONVENTIONAL | SALES2 | | 2| OPTIMIZER STATISTICS GATHERING | | | 3| PARTITION RANGE ALL | | | 4| TABLE ACCESS FULL | SALES | ------------------------------------------------ SQL> SELECT COUNT(*) FROM sales2 WHERE quantity_sold > 50; ----------------------------------- |Id| Operation | Name | ----------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | |*2| TABLE ACCESS FULL | SALES2 | ----------------------------------- Note ----- - dynamic statistics used: stats for conventional DML 【リアルタイム統計情報が収集された場合の例】【リアルタイム統計情報が利用された場合の例】 - dynamic statistics used: stats for conventional DML リアルタイム統計が使用されたことを通知

リアルタイム統計の存在の確認リアルタイム統計収集 Copyright © 2021, Oracle and/or its affiliates |
45 ALL/DBA/USER_TAB_STATISTICSビューとALL/DBA/USER_TAB_COL_STATISTICSビューのNOTES列に「STATS_ON_CONVENTIONAL_DML」と表示されていれば、リアルタイム統計が収集されている SQL> SELECT NVL(partition_name, 'GLOBAL') partition_name, num_rows, blocks, notes 2 FROM user_tab_statistics WHERE table_name = 'SALES2’ORDER BY 1, 4; PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ------------------------- GLOBAL 1837686 3315 STATS_ON_CONVENTIONAL_DML GLOBAL 918843 3315 SALES_2015 0 0 SALES_2016 0 0 …

リアルタイム統計のExport/Import リアルタイム統計収集 Copyright © 2021, Oracle and/or its affiliates |
46 以下のDBMS_STATSパッケージのサブプログラムの引数stat_categoryのキーワードとして、「REALTIME_STATS」を指定以下のプロシージャで指定可能 • EXPORT_TABLE_STATS • EXPORT_SCHEMA_STATS • IMPORT_TABLE_STATS • IMPORT_SCHEMA_STATS • DIFF_TABLE_STATS_IN_STATTAB • DIFF_TABLE_STATS_IN_HISTORY BEGIN DBMS_STATS.EXPORT_TABLE_STATS ( ownname => 'SH', tabname => 'SALES', stattab => 'OPT_STATS', stat_category => 'OBJECT_STATS,REALTIME_STATS' ); END; /

概要データが頻繁に更新される場合でも手動で統計取集する必要がない • 自動オプティマイザ統計収集だけでは古い統計によりパフォーマンス問題になる可能性があった標準の統計収集ジョブを補完（標準の統計収集ジョブ中は実行されない） • デフォルトでは、15分ごとに収集が行われる高頻度タスクは「軽量」で古い統計（10%を超える変更）のみを収集する以下のDBMS_STATS.SET_GLOBAL_PREFSパラメータでタスクの設定を行う •
AUTO_TASK_STATUS（デフォルトはOFF） • AUTO_TASK_MAX_RUN_TIME（デフォルトは3600秒） • AUTO_TASK_INTERVAL（デフォルトは900秒）高頻度の統計情報取得 Copyright © 2021, Oracle and/or its affiliates | 47

概要パーティションの交換(EXCHANGE PARTITION)の実施のタイミングで統計情報が収集される • 基本的な統計情報、シノプシス、ヒストグラムが維持される • グローバル統計は交換元の表と交換先の表の総計となるパーティションの移動(MOVE PARTITION)、結合(MERGE PARTITION,
COALESCE PARTITION)のタイミングで統計情報が収集される • グローバル統計とパーティション統計、増分統計を含むパーティション操作時のオンライン統計収集 Copyright © 2021, Oracle and/or its affiliates | 48

Oracle Database 12c～19c 統計情報および実行計画に関する新機能

Oracle Database 12c～19c 統計情報および実行計画に関する新機能

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript

Oracle Database 12c～19c統計情報および実行計画に関する新機能