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

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

Copyright © 2021, Oracle and/or its affiliates | 3 12cR1新機能

一覧 • 適応問合せ最適化 • ヒストグラムの拡張 • 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」(セッションごとに統計情報の収集の必要あり)

Copyright © 2021, Oracle and/or its affiliates | 21 12cR2新機能

一覧 • 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

Copyright © 2021, Oracle and/or its affiliates | 31 18c新機能

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

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

Copyright © 2021, Oracle and/or its affiliates | 34 19c新機能

一覧 • ヒント・レポート • 実行計画の比較 • リアルタイム統計収集 • 高頻度の統計情報取得 • パーティション操作時のオンライン統計収集 • その他 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

自動SPM展開アドバイザのタスクを1時間ごとに実行可能となった その他 Copyright © 2021, Oracle and/or its affiliates | 49

Thank you 日本オラクル株式会社 50 Copyright © 2021, Oracle and/or its affiliates |

No content