Oracle Database Technology Night #64 Automatic SQL Plan Management は使えるのか

Oracle Database 19cから提供されたAutoSPMについて、どうのように動作して、どのようなときに有効かを、これまでのSPMも含めて解説します。最後に、SwingBenchを使用した検証も紹介します。

Transcript

1. Automatic SQL Plan Managemet (AutoSPM) は使えるのか Oracle Database TechNight #64 津島・出口 日本オラクル株式会社 クラウド・エンジニアリング統括 COE本部 2023年2月22日
2. Safe harbor statement 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、 情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以 下の事項は、マテリアルやコード、機能を提供することを確約するものではないため、購買 決定を行う際の判断材料になさらないで下さい。 オラクル製品に関して記載されている機能の開発、リリース、時期及び価格については、弊 社の裁量により決定され、変更される可能性があります。 Copyright © 2023, Oracle and/or its affiliates 2
3. 1. SQL計画管理とは 2. 自動SQL計画管理 3. 自動SQLチューニング・セット 4. SPMを使用したアップグレード 5. 自動SPM動作検証（ADW編） Agenda Copyright © 2023, Oracle and/or its affiliates 3
4. SQL計画管理（SPM : SQL Plan Management） とは Copyright © 2023, Oracle and/or its affiliates 4
5. SQL文の実行計画が決まる要因 コストベース・オプティマイザは、統計情報などを使用して各SQL文に最適な実行計画を選択する • オプティマイザのバージョンによって生成される実行計画が変化する可能性がある（オプティマイザ機能の違いにより） • 例えば、Query Transformation (問合せ変換) はバージョン・アップごとに機能が追加される SQL計画管理 (SPM) とは Copyright © 2023, Oracle and/or its affiliates 5 Query Transformation より効率的に処理できるように、問合せテキストを書き 換える Plan Generator 各SQLに異なるアクセスパスや結合タイプを使用した 複数プランが生成され、各プランのコスト計算から最小 コストのプランを使用する Cost Estimator コストは操作の実行に使用される CPU時間とディスクI/O数の見積もり Cost-based Optimizer SQLテキスト オブジェクト構造 初期化パラメータ 統計情報 データの実態
6. SQL計画管理 (SPM) とは Copyright © 2023, Oracle and/or its affiliates 6 SQLの実行計画 • 予測不可能な変更が起こりうる • 速くなれば問題ないが遅くなる場合もある SPM以前 (11gより前) のソリューション • 実行計画の変更を回避することがパフォーマンス低下を防ぐ唯一の方法 • 統計情報をロックして変更を防止する • ストアドアウトラインによる実行計画の凍結 • 実行計画をEvolve (改良) させる仕組みがない！ なぜ実行計画の管理が必要なのか 将来はパフォーマンスの問題になるリスクがある SPMによるソリューション • オプティマイザは「実行計画」を自動管理する • 既知の承認済み実行計画のみを使用 • 新しい実行計画は検証される • 今後は同等またはより良い実行計画のみが使用される
7. SQL計画管理 (SPM) とは Copyright © 2023, Oracle and/or its affiliates 7 データの変化により • コストベース・オプティマイザだからオプティマイザ統計が 変化するば実行計画も変化する • オプティマイザ統計が正確でないと非効率な実行計画に バージョン・アップやパラメータの変更により • オプティマイザ新機能 (問合せ変換など) が動作する • オプティマイザ統計が正確でないと新機能 (テストしてい なかった機能) が誤動作 • 問合せ変換もコストベースで行う データの変動に伴う性能劣化 • データの変動を考慮して、最適な実行計画が使用さ れるよう実行計画が自動で変化する • コスト計算が正しくないため最適でない実行計画に データの変動 性能(レスポンス) 実行計画A の誤ったコスト 実行計画B 実行計画A なぜ実行計画は劣化するのか オプティマイザ統計は正確にならない場合もある • 統計収集のサンプルサイズが小さい • 拡張統計は自動的には作成されない • 複数表の統計 (結合カーディナリティなど) はない
8. 主要コンポーネント SQL計画管理 (SPM) とは Copyright © 2023, Oracle and/or its affiliates 8 3つの主要コンポーネント • SQL計画ベースライン取得 (自動、手動) • SQL計画ベースライン選択 (ベースラインによる計画の安定化) • SQL計画ベースライン展開/改良 (Evolve) 2つの初期化パラメータで制御される • optimizer_capture_sql_plan_baselines • 繰返し可能SQL文に対するSQL計画ベースラインの自動取得を制御 (デフォルトはFALSE) • optimizer_use_sql_plan_baselines • オプティマイザによる既存のSQL計画ベースラインの使用を制御 (デフォルトはTRUE) • SQL計画ベースラインが設定されないと何もしない 選択 計画履歴 計画ベースライン 取得 展開 新しい 計画
9. SQL Management Base (SMB) SMBはデータディクショナリ内の論理的なリポジトリで、SYSAUX表領域に配置される • SQL Plan History (SQL計画履歴) • SQL文に対して時間をかけて生成された一連の実行計画 • SQL計画ベースラインと未承認プランの両方が含まれる (未承認SQL計画ベースライン と呼ばれることもある) • SQL Plan Baselines (SQL計画ベースライン) • オプティマイザがSQL文に使用することを許可している実行計画 (実行計画が最適に実 行されることを検証したもの) • 1つのSQL文は複数の承認済みSQL計画ベースラインを持つことができる • SQL Statement Log (SQL文ログ) • 自動取得で繰返し可能SQL文を識別するために使用される一連の問合せシグネチャ • SQL計画ベースラインとSQL計画履歴の確認 • DBA_SQL_PLAN_BASELINESビュー • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクション SQL計画管理のストレージ・アーキテクチャ 9 Copyright © 2023, Oracle and/or its affiliates
10. シグネチャ SQL Management Base Copyright © 2023, Oracle and/or its affiliates 10 SQL文を識別するために使用する • 正規化されたSQLテキスト（大文字小文字を区別せず、空白を削除）から生成される一意のSQL識別子 • 以下はどちらも同じシグネチャになる • SQL_IDとの違い（SQL_IDが異なってもシグネチャが同じ場合も） • V$SQLAREA/V$SQLのEXACT_MATCHING_SIGNATURE • cursor_sharing=forceだとバインド変数に置き換わるのでFORCE_MATCHING_SIGNATUREと同じ値になる SQL> SELECT sql_id, sql_text, exact_matching_signature, force_matching_signature 2 FROM v$sqlarea WHERE UPPER(sql_text) like '%DUMMY%' ; SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE ------------- ------------------------------------ ------------------------ ------------------------ b8fj5dkrqzkrq select 1 from dual where DUMMY= ‘A’ 13015969835749972382 13154199455204052618 6vum4z2c1rpua select 1 from dual where dummy= ‘A' 13015969835749972382 13154199455204052618 18k1ys5nhrrbk select 1 from dual where DUMMY='B' 1525540498770831959 13154199455204052618 gfrsz0vuczzag select 1 from dual where DUMMY='A' 13015969835749972382 13154199455204052618 SELECT a.data, b.data FROM TAB1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1
11. スペース使用量の調整方法と確認 DBMS_SPM.CONFIGUREプロシージャを使用してSMBの保存期間や占有の割合が変更可能 • 未使用のベースラインの保存期間 (plan_retention_weeksパラメータ : デフォルト53週間) • SYSAUXのスペース割合 (space_budget_percentパラメータ : デフォルト10%) • 超えるとアラート・ログに以下の警告が生成される (ハード・リミットではないので超えてもデータは削除されない) • SPM:SMB space usage (99215979) exceeds 10.000000% of SYSAUX size (1018594954). • 以下のように SYSAUX表領域の保存期間や占有の割合が変更できる SQL Management Base Copyright © 2023, Oracle and/or its affiliates 11 SQL> Exec dbms_spm.configure ('plan_retention_weeks', 27); SQL> Exec dbms_spm.configure ('space_budget_percent', 20); SQL> Select Parameter_Name, Parameter_Value 2 From Dba_Sql_Management_Config 3 Where Parameter_Name IN ('PLAN_RETENTION_WEEKS', 'SPACE_BUDGET_PERCENT'); PARAMETER_NAME PARAMETER_VALUE ------------------------- ----------------------- PLAN_RETENTION_WEEKS 27 SPACE_BUDGET_PERCENT 20 SPACE_BUDGET_PERCENTを超えた時の動作 スペース監視の制限となるが、スペースのハード・リ ミットではない。 Oracleは、この制限を超えてもSMB からデータを削除しない。 SYSAUX領域の占有の割合を超える場合は、予想 外の容量消費の増加について毎週アラートを生成 するだけです。
12. スペース使用量の調整方法と確認 削除を手動で実行する • SQL計画ベースラインの特定の実行計画を削除 • 既存のSQL計画ベースラインを削除する (Bug 29201325 : 19cで修正) • ORA-600 [QSMODELETEHEAP2:9]Occurs When Executing DBMS_SPM_INTERNAL.AUTO_PURGE_SQL_PLAN_BASELINE; (Doc ID 2864765.1) • SYSAUX表領域の削減 (割り当て済みのエクステントは解放されない) • 削除したデータのエクステントを解放するには以下のノートを参考にしてください • Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (Doc ID 1499542.1) • SQL計画ベースラインが保存されているSMBのスペース使用量は以下で確認できる SQL Management Base Copyright © 2023, Oracle and/or its affiliates 12 ret := sys.dbms_spm_internal.auto_purge_sql_plan_baseline ; ret := dbms_spm.drop_sql_plan_baseline(<SQLハンドル>,<プラン名>); SQL> Select Occupant_Name, Round(space_usage_kbytes/1024) "Space (M)", Schema_Name 2 From v$sysaux_occupants 3 Where Occupant_Name = 'SQL_MANAGEMENT_BASE'; OCCUPANT_NAME Space (M) SCHEMA_NAME ------------------------ ---------- --------------- SQL_MANAGEMENT_BASE 61 SYS
13. • DBA_SQL_PLAN_BASELINESビュー • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクション SQL計画ベースラインとSQL計画履歴の確認 Copyright © 2023, Oracle and/or its affiliates 13 SQL> SELECT sql_handle, plan_name, enabled, accepted, origin 2 FROM dba_sql_plan_baselines WHERE sql_text like 'select /* test */%’; SQL_HANDLE PLAN_NAME ENA ACC ORIGIN -------------------- ------------------------------ --- --- ---------------------- SQL_4188f0ec9e7ce004 SQL_PLAN_4327hxkg7ts0410ffffec YES YES EVOLVE-LOAD-FROM-STS SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_4188f0ec9e7ce004’, 2 Plan_name=>'SQL_PLAN_4327hxkg7ts0410ffffec')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_4188f0ec9e7ce004 SQL text: select /* test5 */ /*+ NO_RESULT_CACHE */ ... -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_4327hxkg7ts0410ffffec Plan id: 4186555243 Enabled: YES Fixed: NO Accepted: YES Origin: EVOLVE-LOAD-FROM-STS Plan rows: From Auto SQL Tuning Set -------------------------------------------------------------------------------- Plan hash value: 4186555243 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | ... これで内部的に 実行計画の比較を行う SQL_HANDLEは文字列形式の 一意のSQL識別子 PLAN_NAMEは文字列形式の 一意のプラン識別子
14. 自動計画取得 繰返し可能SQL (SQL文ログにシグネチャが存在するSQL) 1. SQL文が初めてハード解析され計画が生成される 2. ログを確認してシグネチャがあるか (2回目以降の実行か) を確認する (単発実行のSQLは対象にしないため) 3. 存在しない場合にシグネチャをSQL文ログに追加して実行する 4. 存在する場合に計画履歴を作成しSQL計画ベースラインとして使用 する SQL計画ベースライン取得 Copyright © 2023, Oracle and/or its affiliates 14 SQL計画ベースラインの自動作成 • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES を TRUE (デフォルトはFALSE) にすると、繰返し可能SQL文に 対して、SQL計画ベースラインが自動的に作成される • 以下が SPM の構成情報 SQL> Select Parameter_Name, Parameter_Value 2 From Dba_Sql_Management_Config; PARAMETER_NAME PARAMETER_VALUE ----------------------------------- ---------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 9 rows selected. HJ HJ GB 解析 Statement log 実行 Plan history HJ GB Plan baseline HJ 実行 2回目の実行
15. 自動計画取得 SQL計画ベースライン取得 Copyright © 2023, Oracle and/or its affiliates 15 自動取得はすべての繰返し可能な問合せに適用される • 多くの重要でない問合せを含みSYSAUX表領域を無駄にする 12.2では、必要なものだけを取得する • 特定のアプリケーションとスキーマ • 必要なSQL文をフィルタリング • SQL Text • Parsing Schema • Module • Action • 特定のアプリケーション (SQL文) だけを対象にできるが、 性能問題になるSQLすべてを対象にできる訳ではない 自動取得のためのフィルタ設定 • 「SCOTT」の解析スキーマを除外する • 現在のフィルタ設定はDBA_SQL_MANAGEMENT_CONFIGに格納されている (11ページ参照) 実行計画 実行 12.2 （必要なものだけ） SQL計画ベースライン dbms_spm.configure('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SCOTT', FALSE);
16. 手動計画取得 SQL計画ベースライン取得 Copyright © 2023, Oracle and/or its affiliates 16 特定のSQL (問題が発生したSQL) のみに計画ベースラインが設定できる • DBMS_SPM パッケージを使用して SQL 文に対して実行計画をロードする 以下のソースから手動でロードできるので、いろいろな用途で使用可能 • SQL Tuning Set • カーソル・キャッシュ • AWR リポジトリ (12.2から) • ステージング表を使用したアンパック • 既存のストアド・アウトライン DBA_SQL_PLAN_BASELINES ビューのORIGIN列から取得されたソース を確認できる SQL> Select Origin, Count(*) 2 From Dba_Sql_Plan_Baselines 3 Group by Origin 4 Order by 2; ORIGIN COUNT(*) ----------------------------- ---------- EVOLVE-AUTO-INDEX-LOAD 1 AUTO-CAPTURE 19 EVOLVE-LOAD-FROM-STS 69 AWR
17. ステージング表から • SQL計画ベースラインを別のシステムで取得することが可能 • テーブル経由でエクスポートされ (統計情報と同様) ローカルにインポー トされる • プランはテーブルから「unpacked」されSPMにロードされる ストアド・アウトラインから • 以前作成したストアドアウトライン をSQL計画ベースラインに移行する 手動計画取得（実行例） SQL計画ベースライン取得 Copyright © 2023, Oracle and/or its affiliates 17 カーソル・キャッシュから • カーソル・キャッシュからSPMにベースライン・プランとして計画をロードする • フィルタの指定が可能 (SQL_ID, module, schema, action) SQL Tuning Set (STS) から • STSの (重要な) SQL文のプランの詳細を取得する • これらのプランをベースライン・プランとしてSPMにロード dbms_spm.load_plans_from_cursor_cache( attribute_name => 'SQL_TEXT’, attribute_value => 'select%p.prod_name%'); dbms_spm.load_plans_from_sqlset( sqlset_name => 'MY_STS’, basic_filter => 'sql_text like ''select%p.prod_name%''' ); dbms_spm.unpack_stgtab_baseline( table_name => 'MY_STGTAB’, table_owner => 'SH’, sql_text => 'select%p.prod_name%'); dbms_spm.migrate_stored_outline( attribute_name =>'OUTLINE_NAME’, attribute_value =>’MYOL’, FIXED=>'NO');
18. SQL文が実行されたとき SQL計画ベースライン選択 Copyright © 2023, Oracle and/or its affiliates 18 オプティマイザは最適なコストベースの実行計画を生成するが、 OPTIMIZER_USE_SQL_PLAN_BASELINEがTRUEだと、 この計画で実行する前に、次の動作を行う • オプティマイザはSQL計画ベースラインが存在するかを確認する • ベースラインが存在しない場合は新たに生成された計画で自動計 画取得の確認を行う • ベースラインが存在する場合は新たに生成された計画がベース ラインにあるかを確認する (このとき固定計画があると優先され る) • 新たな計画がベースラインにある (PLAN_IDが一致した) 場合は この計画で実行する • ない場合は既知のベースライン計画で実行して、この計画を計画 履歴に保存する • ベースライン内に再現可能な計画がない場合は新たに生成さ れた計画を使用し、この計画を未承認計画としてSQL計画履 歴に保存する つまり、SQL計画ベースラインが設定されると、新しい実行計画は 計画履歴に追加されるようになる
19. 選択されたベースラインの確認 SQL計画ベースライン選択 Copyright © 2023, Oracle and/or its affiliates 19 SQL> SELECT sum(tab1.col1),sum(tab2.col1),count(*) FROM tab1,tab2 WHERE tab1.col2=tab2.col2 AND tab2.col2 = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 4186555243 ------------------------------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 | SORT AGGREGATE | | 1 | | 2 | NESTED LOOPS | | 181K| | 3 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | |* 4 | INDEX UNIQUE SCAN | IX1_TAB2 | 1 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 181K| |* 6 | INDEX RANGE SCAN | IX1_TAB1 | 181K| ------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("TAB2"."COL2"=1) 6 - access("TAB1"."COL2"=1) Note ----- - SQL plan baseline "SQL_PLAN_4327hxkg7ts0410ffffec" used for this statement PLAN_NAME
20. SQL計画ベースライン展開/改良 Copyright © 2023, Oracle and/or its affiliates 20 オプティマイザが新しい計画を検証して既存の SQL 計画ベースラインに追加するためのプロセスで、次のステップで構成される • SQL計画ベースラインに固定計画 (FIXED=YES) があると新たな計画をSQL計画ベースラインに追加しない 展開プロセスの結果はデータ・ディクショナリに記録され、DBMS_SPM.REPORT_EVOLVE_TASK関数を使用して参照可能 (確認例は 23ページ) Plan history Plan baseline NL NL GB HJ HJ GB 未承認の計画 (計画履歴) をチェック 既存の計画より優れ ているかのチェック （テスト実行） 1 2 Plan history HJ HJ GB Plan baseline NL NL GB 推奨された (元の計画より性能 が優れている) 計画は計画ベース ラインに追加する 3 L 元の計画よりも性能が良くない 計画は計画履歴に残り、未承認 とマークされ last_verified が 更新される NL NL GB 4
21. • タスク・パラメータの確認 SQL計画ベースライン展開/改良 Copyright © 2023, Oracle and/or its affiliates 21 自動タスク（12cから） • 代替計画は自動的に検証され、より良いと判断され た場合、手動で操作することなく導入することができる • メンテナンス・ウィンドウで実行される • タスク名は SYS_AUTO_SPM_EVOLVE_TASK • 承認を回避することもできる（デフォルトはTRUE） • 19cから高頻度自動タスクを提供 • Exadata、Autonomous Database (ADB) のみ • デフォルト60分間隔（ADBはデフォルト有効） • タスク名は SYS_AUTO_SPM_EVOLVE_TASK • 詳細は後で説明 Evolveタスク BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ACCEPT_PLANS', value => FALSE); END; / SQL> SELECT parameter_name, parameter_value 2 FROM dba_advisor_parameters 3 WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK’ 4 AND parameter_value != 'UNUSED’ 5 ORDER BY parameter_name; PARAMETER_NAME PARAMETER_VALUE ------------------------- --------------- ACCEPT_PLANS TRUE ALTERNATE_PLAN_LIMIT UNLIMITED DAYS_TO_EXPIRE UNLIMITED DEFAULT_EXECUTION_TYPE SPM EVOLVE EXECUTION_DAYS_TO_EXPIRE 30 TIME_LIMIT 3600
22. 手動展開アドバイザ・タスク（12cから） • 展開タスクの作成 • DBMS_SPM.CREATE_EVOLVE_TASKファンクション • 展開アドバイザ・タスクの実行 • DBMS_SPM.EXECUTE_EVOLVE_TASKファンクション • 展開レポートの生成 • DBMS_SPM.REPORT_EVOLVE_TASKファンクション • 推奨された実行計画の承認 • DBMS_SPM.ACCEPT_SQL_PLAN_BASELINEプロシージャ • 推奨されたすべての実行計画の承認 • DBMS_SPM.IMPLEMENT_EVOLVE_TASKファンクション SQL計画ベースライン展開/改良 Copyright © 2023, Oracle and/or its affiliates 22 手動タスク • DBAが手動で個々のSQL計画履歴を検証し承認する （自動的に承認されない） • 以下の手順で行う 1. 展開タスクの作成 2. 展開アドバイザ・タスクの実行 3. 展開レポートを生成して確認 4. 推奨された実行計画の承認 Evolveタスク
23. Evolveプロセスの実行例 (手動タスク) SQL計画ベースライン展開/改良 Copyright © 2023, Oracle and/or its affiliates 23 タスクの作成、アドバイザ・タスクの実行、レポートの生成と確認 • 下記のsql_handle は dba_sql_plan_baselines ビューより確認する（13ページ参照） OUTPUT -------------------------------------------------------------------- GENERAL INFORMATION SECTION -------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : TASK_46085 Task Owner : SYS Execution Name : EXEC_51306 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 11/28/2022 11:13:11 Finished : 11/28/2022 11:13:12 Last Updated : 11/28/2022 11:13:12 Global Time Limit : 2147483646 Per-Plan Time Limit : UNUSED Number of Errors : 0 ---------------------------------------------- <省略> SQL> SET SERVEROUTPUT ON 2 DECLARE 3 l_return VARCHAR2(32767); 4 BEGIN 4 l_return := DBMS_SPM.create_evolve_task( 5 sql_handle => 'SQL_63c20538b262be85’); 6 DBMS_OUTPUT.put_line('Task Name: ' || l_return); 7 END; 8 / Task Name: TASK_46085 SQL> DECLARE 2 l_return VARCHAR2(32767); 3 BEGIN 4 l_return := DBMS_SPM.execute_evolve_task(task_name=>'TASK_46085’); 5 DBMS_OUTPUT.put_line('Execution Name: ' || l_return); 6 END; 7 / Execution Name: EXEC_51306 SQL> SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100 2 Select DBMS_SPM.report_evolve_task(task_name => 'TASK_46085’, 3 execution_name => 'EXEC_51306') AS output 4 From dual;
24. 自動計画取得から展開タスクまでの流れ SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 24 実行されたSQLにベースラインが存在しない場合 (optimizer_capture_sql_plan_baselines=TRUE) • このSQLが繰返し可能SQLになると、 この計画がSQL計画ベースラインになり、この計画で実行する SQL計画履歴 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 25 | | 2 | NESTED LOOPS | | 25 | |* 3 | TABLE ACCESS FULL | TAB1 | 1 | |* 4 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 25 | ------------------------------------------------------------- SQL計画ベースライン ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 25 | | 2 | NESTED LOOPS | | 25 | |* 3 | TABLE ACCESS FULL | TAB1 | 1 | |* 4 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 25 | ------------------------------------------------------------- 実行
25. 実行されたSQLにSQL計画ベースラインが存在する場合 (optimizer_use_sql_plan_baselines=TRUE) • 新しい実行計画が生成されると、 承認されるまでSQL計画履歴に格納される • SQL計画ベースラインの実行計画で実行される 自動計画取得から展開タスクまでの流れ SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 25 SQL計画履歴 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 ----------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 25 | |* 5 | TABLE ACCESS FULL | TAB1 | 1 | | 8 | TABLE ACCESS FULL | TAB2 | 25 | ----------------------------------------------------------- SQL計画ベースライン ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 25 | | 2 | NESTED LOOPS | | 25 | |* 3 | TABLE ACCESS FULL | TAB1 | 1 | |* 4 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 25 | ------------------------------------------------------------- 実行 ----------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 25 | |* 5 | TABLE ACCESS FULL | TAB1 | 1 | | 8 | TABLE ACCESS FULL | TAB2 | 25 | -----------------------------------------------------------
26. 自動計画取得から展開タスクまでの流れ SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 26 展開タスクが実行された場合 • SQL計画履歴の実行計画が検証され、優れていれば承認されて SQL計画ベースラインに追加される • そうでなければ計画履歴にそのままに • 展開タスクでの検証 • 「Elapsed Time」と「Buffer Gets」などを検証する • 50%高速化する必要がある (1.5倍性能アップ) • SQLチューニング・アドバイザ と同じテスト・フレームワークを使用 SQL計画履歴 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 SQL計画ベースライン ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 25 | | 2 | NESTED LOOPS | | 25 | |* 3 | TABLE ACCESS FULL | TAB1 | 1 | |* 4 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 25 | ------------------------------------------------------------- ----------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 25 | |* 5 | TABLE ACCESS FULL | TAB1 | 1 | | 8 | TABLE ACCESS FULL | TAB2 | 25 | -----------------------------------------------------------
27. 自動計画取得から展開タスクまでの流れ) SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 27 実行されたSQLに複数のベースラインが存在する場合 (optimizer_use_sql_plan_baselines=TRUE) • オプティマイザで生成された実行計画が存在する (その実行 計画で実行する) • オプティマイザで生成された実行計画が存在しない (その実行 計画が計画履歴に格納され、コストが最も低いベースラインの 実行計画で実行する) SQL計画履歴 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 ----------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 25 | |* 5 | TABLE ACCESS FULL | TAB1 | 1 | | 8 | TABLE ACCESS FULL | TAB2 | 25 | ----------------------------------------------------------- SQL計画ベースライン ------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 25 | | 2 | NESTED LOOPS | | 25 | |* 3 | TABLE ACCESS FULL | TAB1 | 1 | |* 4 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 25 | ------------------------------------------------------------- ----------------------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | HASH JOIN | | 25 | |* 5 | TABLE ACCESS FULL | TAB1 | 1 | | 8 | TABLE ACCESS FULL | TAB2 | 25 | ----------------------------------------------------------- 実行
28. その他のパフォーマンス機能との関係 SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 28 適応計画（最終計画の決定は実行時まで先延ばしする） • SQL計画ベースラインは適応計画の最終計画で格納されているので、選択で適応計画になることはない • 新しい計画に適応性があるとデフォルト計画に適応マークを付けて計画履歴に格納する (実行前なので最終計画にできない) • 適応マークされた計画を展開するときは最終計画を求めて検証される SQLプロファイル（実行計画を最適にするための補足情報） • SQLプロファイルが存在するSQL文は３つのコンポーネントの見積りコストに影響を受けるがSPMの動作は同じ Adaptive Cursor Sharing（バインド変数の値によって実行計画を決定） • SPMは計画を制限させるが、ACSはバインド値によって計画を変化させるという相反する機能 • SQL計画ベースラインにACSの候補となる実行計画 (バインド値に適切な計画) がすべて含まれていないと選択されない • SQL計画ベースラインが一つ(自動計画取得の最初の計画など)のSQL文はACSで生成された計画は計画履歴に格納されるだけ • Evolveタスクでバインド値を意識した検証はしないので、ACSのメリットが得られる計画をすべて手動でロードする必要がある
29. 18cまでの問題点 SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 29 SQL計画ベースラインの取得が効果的に行うことができない • 手動は難しい、自動は必要ないものも取得される • 12.2では、自動取得のフィルター機能で必要なものだけを取得するようになった • ただし、性能まで判断できない • フィルターの設定が難しい • できれば問題が発生したSQLを自動で取得したい 19cでは、自動SQL計画管理により、実行計画管理のすべてのステップを自動化 • 次の章ではこの自動SQL計画管理について紹介する
30. 自動SQL計画管理 （自動SPM） Copyright © 2023, Oracle and/or its affiliates 30
31. SQLパフォーマンスの低下を自動的に検出して修復できるように19c から追加 • Exadata/ExaCS/ExaCC/ADB 上だけで使用可能 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 31 自動SQL計画管理は展開アドバイザ・タスクとして以下の 動作を行う 1. 大量のシステム・リソースを消費する SQL文を識別するために AWR (遅いSQLが格納されている) と自動SQLチューニング・セット (ASTS) を使用する (過去のパフォーマンス情報を使用して、パ フォーマンス低下の可能性があるかどうかを確認する) 2. 特定されたSQL文の代替実行計画となるものを探す (見つかった 計画はすべて取得され、SQL計画履歴に保存される) 3. SQL展開アドバイザのテストで、取得された計画が実行され、どの 計画がベストか判断される 4. 既存の計画よりパフォーマンスが良いことが判明した計画は、SQL 計画ベースラインに追加される 5. 問合せの劣化を回避するために、SQL計画ベースラインが計画を 制限する 1と2は自動SPMより追加され た動作となります
32. 自動SPMの制御 2つのパラメータで制御される • ALTERNATE_PLAN_BASELINE (ロードする代替実行計画を決定) • 非ADBのデフォルトは ‘EXISTING’（ベースラインが存在する SQL に対してのみロードする） • ADBのデフォルトは‘AUTO’（自動 SPM モードとして動作） • ALTERNATE_PLAN_SOURCE (代替実行計画の検索するソースを決定) • デフォルトは ‘AUTO’（自動的にソースが選択される） • 対象のソースは以下になる • CURSOR_CACHE • AUTOMATIC_WORKLOAD_REPOSITORY • SQL_TUNING_SET (すべてSTSが対象) 自動SPM は SQL計画ベースラインを取得して承認するため、自動計画取得の代わりとして使用できる 自動SPM を効果的に利用するには高頻度タスクを有効にする必要がある 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 32
33. 自動SPMの確認 (ADBのデフォルト) • ALTERNATE_PLAN_SOURCEはSQL_TUNING_SETになっている • 高頻度自動SPM 展開アドバイザ・タスクを有効に設定する方法 ※高頻度タスクを有効にすると自動SPM も有効になる • 高頻度自動SPM 展開アドバイザ・タスクを無効に設定する方法 ※高頻度タスクを無効にすると自動SPM も無効になる 自動SPM 及び高頻度自動SPM 展開アドバイザ・タスク機能は Exadata/ADBのみの機能 有効化 自動SPM を有効に設定する方法 'ALTERNATE_PLAN_BASELI 自動SPM を無効に設定する方法 (Exadataのデフォルト) 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 33 SQL> BEGIN 2 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK’, 4 parameter => 'ALTER_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 / SQL> BEGIN 2 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK’ , 4 parameter => 'ALTERNATE_PLAN_BASELINE’, 5 value => 'EXISTING’); 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 / SQL> Exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF'); SQL> Exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON'); SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" 2 FROM DBA_ADVISOR_PARAMETERS 3 WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK’ 4 AND PARAMETER_NAME IN (ALTERNATE_PLAN_SORCE,ALTERNAME_BASELINE); PARAMETER_NAME VALUE ------------------------------------- ----------------------- ALTERNATE_PLAN_SOURCE SQL_TUNING_SET ALTERNATE_PLAN_BASELINE AUTO
34. タスク状態の確認 自動SPM (高頻度自動SPM展開アドバイザ・タスク) の状態は 以下のSQL文で確認できる • ADBの場合は、高頻度自動SPM 展開アドバイザ・タスクは デフォルト有効になっており、実行間隔は1時間(3600秒)おき に実行されている • Exadataの場合は、高頻度自動SPM展開アドバイザ・タスク はデフォルト無効になっている 実行間隔の変更 • 高頻度自動SPM 展開アドバイザ・タスクの実行間隔は 1時間(3600秒)から変更できない 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 34 SQL> Select Dbid, Task_Name, Enabled, Interval, Status, 2. to_char(last_schedule_time,'DD-MON-YY hh24:mi') LATEST 2 From Dba_Autotask_Schedule_Control 3 Where Dbid = sys_context('userenv','con_dbid’) 4 and Task_Name = 'Auto SPM Task'; DBID TASK_NAME ENABL INTERVAL STATUS LATEST ---------- -------------- ----- -------- --------- ---------------- 3375532816 Auto SPM Task TRUE 3600 SUCCEEDED 13-NOV-22 10:50
35. タスクの実行状況確認 展開アドバイザの実行状況は以下の SQLで確認できる • 標準自動タスクと高頻度自動タスクはタスク名が同じになるので実行名で判断する • 標準タスクの実行名が EXEC_<number> という形式に対し、高頻度の実行名は SYS_SPM_<timestamp> という形式になる • EXECUTION_NAME 列より展開アドバイザ・タスクは 1 時間おきに実行されていることを確認できる 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 35 SQL> Select Task_Name, Execution_Name, Status, to_char(EXECUTION_END,'yy/mm/dd hh24:mi:ss’) 2 From Dba_Advisor_Executions 3 Where Task_Name LIKE '%SPM%’ 4 and (Execution_Name LIKE 'SYS_SPM%' OR Execution_Name LIKE 'EXEC_%’) 5 Order by Execution_End; TASK_NAME EXECUTION_NAME STATUS TO_CHAR(EXECUTION ---------------------------------------- ---------------------------------------- ---------- ----------------- ... SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2022-12-05/08:09:39 COMPLETED 22/12/05 08:09:42 SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2022-12-05/08:12:41 COMPLETED 22/12/05 08:12:49
36. タスクの実行内容確認 SQL計画ベースラインを以下の SQL で確認できる • 自動SPMタスクの SYS_AUTO_SPM_EVOLVE_TASK で処理されたものは EVOLVE-LOAD-FROM-xxx になる • ACCepted 列は YES になっている SQL は新しい実行計画が採用されている DBMS_SPM.REPORT_AUTO_EVOLVE_TASK ファンクションにより高頻度自動SPM展開アドバイザ・タスクの具体的な実行内容が 表示可能（次ページ参照） 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 36 SQL> Select Sql_Handle, Plan_Name, Sql_Text, Enabled, Accepted, Origin From Dba_Sql_Plan_Baselines Where Origin LIKE 'EVOLVE-LOAD-FROM%'; SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC ORIGIN ------------------------- ----------------------------------- ---------------------------------------- --- --- ----------------------------- SQL_e84b4779342a6dd0 SQL_PLAN_fhku7g4u2nvfh89ff1357 select /* test10 */ /*+ NO_RESULT_CACHE YES YES EVOLVE-LOAD-FROM-STS */ sum(tab3.col1), sum(tab4.col1), count (*) from tab3, tab4 where tab3.col2 = ta b4.col2 and tab4.col2 = 1 SQL_09fa7d7250b92f6a SQL_PLAN_0mymxf98bkbvaa695e320 select /* testsql */ /*+ NO_RESULT_CACHE YES YES EVOLVE-LOAD-FROM-STS */ sum(taba.col1), sum(tabb.col1), coun t(*) from taba, tabb where taba.col2 = t abb.col2 and tabb.col2 = 1 SQL_d32ef06ce6002012 SQL_PLAN_d6brhdmm0080k7f692e5d SELECT /*+ CONNECT_BY_FILTERING */ s.pri YES YES EVOLVE-LOAD-FROM-STS vilege# FROM sys.sysauth$ s CONNE<省略>
37. 自動Evolveレポート 以下の SQL より詳細Evolveレポートが出力される • 高頻度タスクのEXECUTION_NAMEは DBA_ADVISOR_EXECUTIONSビューから取得 右図は詳細Evolveレポートの一部となり、以下の項目から具体的 なevolveレポートが確認可能 • 「DETAILS SECTION」 具体的なSQLの情報が表示される • 「FINDINGS SECTION」 具体的な見つかった問題点などが紹介される • 「Recommendation」 劣化のSQL実行計画に対して具体的な対応方法が紹介され る 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 37 SQL> Select dbms_spm.report_auto_evolve_task( 2 execution_name => 'SYS_SPM_2022-12-05/08:12:41') 3 From dual;
38. タスクの実行時間 自動SPMタスクの過去の実行性能の概要を以下のように取得して確認できる • 最大実行時間、最小実行時間、平均実行時間 • CDB 及び PDB の配下に自動 SPM が存在しているため個別に確認する必要がある この例はPDB上の自動SPMタスクの情報になる • それぞれの値は次のようになる • 最大実行時間は13秒 • 最小実行時間は0秒(四捨五入) • 平均実行時間は5.35秒 • 平均CPU時間は4.18秒 タスクの実行時間を短くしたい場合は DBMS_SPM.SET_EVOLVE_TASK_PARAMETER プロシージャで以下のパラメータを調整してください • TIME_LIMIT（デフォルト3600秒） • ALTERNATE_PLAN_LIMIT（デフォルトUNLIMITED） 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 38 SQL> With dur As 2 ( 3 Select (To_Date('1','J')+Run_Duration-to_Date('1','J'))* 86400 Duration_Sec, 4 (To_Date('1','J')+CPU_Used-to_Date('1','J'))* 86400 CPU_Used_Sec 5 From DBA_Scheduler_Job_Run_Details 6 where job_name = 'ORA$_ATSK_AUTOSPMT’ 7 ) 8 Select Min(Duration_Sec) 自動SPM_Min_Time_Sec, 9 Max(Duration_Sec) 自動SPM_Max_Time_Sec, 10 Avg(Duration_Sec) 自動SPM_Average_Time_Sec, 11 Avg(CPU_Used_Sec) 自動SPM_Average_CPU_Sec 12 From dur; 自動SPM_MIN_TIME_SEC 自動SPM_MAX_TIME_SEC 自動SPM_AVERAGE_TIME_SEC 自動SPM_AVERAGE_CPU_SEC -------------------- ------------------- ------------------------ ----------------------- 0 13 5.35221421 4.18434604
39. スペース使用量 自動 SPM もそれぞれの SQL の情報をSYSAUX表領域上の SMB に作成して実行計画の管理を行う • SMBは SYSAUXの割合としてSPACE_BUDGET_PERCENTパラメータ (デフォルトは10%) で制限されている • Autonomous Database の場合はこの比率が 50 までしか変更できない 目安として、合計193のベースラインの場合は、約61MBのSMB専用の SYSAUX 表領域のスペースが使用されている 自動SQL計画管理 Copyright © 2023, Oracle and/or its affiliates 39 SQL> Select nvl(origin, 'TOTAL =============>>') as origin, count(*) 2 From dba_sql_plan_baselines 3 Group by rollup(origin) 4 Order by 2; ORIGIN COUNT(*) ----------------------------- ---------- EVOLVE-AUTO-INDEX-LOAD 3 AUTO-CAPTURE 76 EVOLVE-LOAD-FROM-STS 114 TOTAL =============>> 193 SQL> Select Occupant_Name, 2 Round(space_usage_kbytes/1024) "Space (M)", 3 Schema_Name 4 From v$sysaux_occupants 5 Where Occupant_Name = 'SQL_MANAGEMENT_BASE'; OCCUPANT_NAME Space (M) SCHEMA_NAME ------------------------ ---------- --------------- SQL_MANAGEMENT_BASE 61 SYS
40. 自動SQLチューニング・セット （自動STS） Copyright © 2023, Oracle and/or its affiliates 40
41. データベースで実行された複数の SQL の下記のような情報を含むデータベース・オブジェクト ◼ SQL 文のセット ◼ 関連する実行コンテキスト（スキーマ、アプリケーション・モジュール名、バインド値など） ◼ 関連する基本実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など） ◼ 各 SQL 文の関連実行計画と行ソース統計(オプション) STS だけでも利用可能だが、下記のような Oracle Database の高度な機能と組み合わせて利用することもある ◼ データベース上でのテストを実行する機能 ⇒ SQL Performance Analyzer ◼ チューニングのアドバイスを生成する機能 ⇒ SQL Tuning Advisor / SQL Access Advisor ◼ 実行計画を固定化するための機能 ⇒ SQL Plan Management STS はあるデータベースで記録したものを別のデータベースに移動させることにも使用可能 SQLチューニング・セット (STS) とは Copyright © 2023, Oracle and/or its affiliates 41 カーソル・キャッシュ AWR SQLトレース 他のSTS STS SPA (SQL Performance Analyzer) SQL Tuning Advisor SQL Access Advisor SQL Plan Management
42. システムが管理するSQLチューニングセット • STSを利用するにはDBAにより手動設定する必要があり、メンテナンスの手間もかかる（それを解決するためにOracle Database 19c RU 19.7 より提供された） • 19cの自動インデックス (Automatic Indexing) をサポートするために作成され、19.7でインフラストラクチャ・コンポーネントとして 公開された • Enterprise Edition ではすべてのプラットフォームで使用可能（追加のライセンスは必要ない） • 自動バックグラウンド・タスクで 15 分おきに実行される（SQL文の保存時間は 53 週間） • この値は変更できない • SQLのワークロードを収集し、SYS_AUTO_STS という SQL チューニング・セットに格納 • 時間の経過とともに、システム上で見られるほとんどのクエリを取り込むようになる (INSERT INTO VALUES などは含まない) • 再利用しないSQL文の取得には制限がある (アドホック・クエリやバインド変数の代わりにリテラルを使用する文など) • ASTS は STSと同じように SYSAUX の表領域に格納 • ASTS は Exadata および ADB での自動 SQL 計画管理 の主要コンポーネント (ADB以外はデフォルト無効) • 自動インデックスや自動SPM は ASTS に依存する為、利用する場合は ASTSタスクを無効にしない • 自動パーティションでも ASTS を使用するので、利用する場合は ASTSタスクを無効にしない 自動SQLチューニング・セット (ASTS) とは Copyright © 2023, Oracle and/or its affiliates 42
43. 有効化と確認方法 ASTSの有効かどうかの状態を確認する ASTSを有効にする ASTSを無効にする (RU19.8以降ADB以外はデフォルト無効) 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 43 SQL> Select Task_name, Enabled 2 From Dba_autotask_schedule_control 3 Where Task_name = 'Auto STS Capture Task'; TASK_NAME ENABL ------------------------------------- ----- Auto STS Capture Task TRUE(有効) SQL> begin 2 dbms_auto_task_admin.enable( 3 client_name => 'Auto STS Capture Task’, 4 operation => NULL, 5 window_name => NULL); 6 end; 7 / SQL> begin 2 dbms_auto_task_admin.disable( 3 client_name => 'Auto STS Capture Task’, 4 operation => NULL, 5 window_name => NULL); 6 end; 7 / ASTSタスクは定期的に実行される • 19cの実行間隔は、以下のように取得できる • 21cでは以下の方法で確認する SQL> Select Task_Name, Interval, Max_Run_Time 2 From DBA_AutoTask_Schedule_Control 3 Where Task_Name = 'Auto STS Capture Task' 4 And dbid = sys_context('userenv','con_dbid') ; TASK_NAME INTERVAL MAX_RUN_TIME ---------------------------------------- ---------- ------------ Auto STS Capture Task 900 900 SQL> Select Task_Name, Interval, Max_Run_Time 2 From Dba_Autotask_Settings 3 Where Task_Name = 'Auto STS Capture Task'; TASK_NAME INTERVAL MAX_RUN_TIME ---------------------------------------- ---------- ------------ Auto STS Capture Task 900 900
44. タスクの実行時間 ASTS タスクの実行性能は以下のように取得できる（最大時間、最小時間、平均時間） • CDB 及び PDB の配下に各自のASTSが存在しているため、個別に確認する必要がある この例では、PDB上で右図のSQLを実行して、自動 STS タスクの過去の実行時間を確認している • それぞれの値は次のようになる • 過去最長実行時間は20秒 • 過去最短実行時間は0秒(四捨五入) • 過去平均実行時間は3.58秒 • 過去平均CPU 時間は2.76秒 通常のシステムにおいて、ASTS のリソース使用量はごくわずかで、ほとんどの場合無視できるレベル • 通常、大規模な ASTS (シングル スレッド) のキャプチャ・タスクの実行時間は 30 ～ 60 秒 になる 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 44 SQL> With dur As 2 ( 3 Select (To_Date('1','J')+Run_Duration-to_Date('1','J'))* 86400 Duration_Sec, 4 (To_Date('1','J')+CPU_Used-to_Date('1','J'))* 86400 CPU_Used_Sec 5 From DBA_Scheduler_Job_Run_Details 6 Where job_name = 'ORA$_ATSK_AUTOSTS’ 7 ) 8 Select Min(Duration_Sec) ASTS_Min_Time_Sec, 9 Max(Duration_Sec) ASTS_Max_Time_Sec, 10 Avg(Duration_Sec) ASTS_Average_Time_Sec, 11 Avg(CPU_Used_Sec) ASTS_Average_CPU_Sec 12 From dur; ASTS_MIN_TIME_SEC ASTS_MAX_TIME_SEC ASTS_AVERAGE_TIME_SEC ASTS_AVERAGE_CPU_SEC ----------------- ----------------- --------------------- -------------------- 0 20 3.58722527 2.76270604
45. 消費容量 すべてのSTSが消費する容量は、右図のように取得できる • SQL文と実行計画のライン数の増加によってサイズが 大きくなる • 通常、ASTSは合計で 1.5GB未満 (実行計画のサイズと SQL文の数はさまざまである) • クエリの数が非常に多いシステムでは、SQL文が短くな り、計画が小さくなる可能性があるため、オーバーヘッド が 10KB/SQL文未満になる場合が多い 右図の確認SQLを実行すると、過去収集された自動STS の全体的なサイズ (59.5MB) が確認できる • WRI$_SQLSET_PLAN_LINES と WRH$_SQLTEXT で大部分を占める • この例では84.67%(50.38/59.5*100)になっている • SQL文や実行計画が複雑であればより割合が増える 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 45 SQL> Select Table_Name, 2 Round(Sum(size_b)/1024/1024, 3) Table_Size_MB, 3 Round(Max(Total_Size_B)/1024/1024, 3) Total_Size_MB 4 From (Select Table_Name, Size_B, Sum(Size_B) Over() Total_Size_B 5 From (Select Segment_Name as table_Name, Bytes Size_B 6 From DBA_Segments 7 Where Segment_Name Not Like '%WORKSPA%’ 8 And Owner = 'SYS’ 9 And (segment_Name Like 'WRI%SQLSET%’ 10 Or Segment_Name Like 'WRH$_SQLTEXT’) 11 Union All 12 Select t.Table_Name, Bytes Size_B 13 From DBA_Segments s, 14 (Select Table_Name, Segment_Name 15 From DBA_Lobs 16 Where Table_Name In ('WRI$_SQLSET_PLAN_LINES’, 17 'WRH$_SQLTEXT’) 18 And Owner = 'SYS') t 19 Where s.Segment_Name = t.Segment_Name)) 20 Group By Table_Name 21 Order By Table_Size_MB Desc; TABLE_NAME TABLE_SIZE_MB TOTAL_SIZE_MB ------------------------------ ------------- ------------- WRI$_SQLSET_PLAN_LINES 46.188 59.5 WRH$_SQLTEXT 4.188 59.5 <省略> • 以下の例では94.58%(319.38/337.69*100) TABLE_NAME TABLE_SIZE_MB TOTAL_SIZE_MB ------------------------- ------------- ------------- WRH$_SQLTEXT 183.19 337.69 WRI$_SQLSET_PLAN_LINES 136.19 337.69
46. SQLの平均消費サイズ SQLの平均消費サイズを確認する • 次のクエリで SQL チューニングセットごとのSQL数を確認する • 例: 前ページで確認できたTOTAL_SIZE_MBをCOUN数で割ると、一つSQLの平均サイズはおおよそ 76.06KB (59.5*1024/801) となる ASTS がキャプチャした各スキーマの SQL数はどのぐらいあるかを把握するには、以下のSQLで確認できる 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 46 SQL> Select Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS') SQLSets, Count(*) Count 2 From DBA_SQLSet_Statements 3 Group By Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS'); SQLSETS COUNT -------- ----------- ASTS 801 SQL> Select Parsing_Schema_Name, Count(*) 2 From Table(dbms_sqltune.select_sqlset('SYS_AUTO_STS')) 3 Group by Parsing_Schema_Name 4 Order by 2; PARSING_SCHEMA_NAME COUNT(*) ---------------------------- ---------- LBACSYS 1 DBSNMP 1 C##OGGADMIN 2 ORDSYS 6 SYS 1255 左図のように、例となるSYS_AUTO_STSには主にSYSユーザ で実行されたSQLがキャプチャされていることが分かった
47. SQLの削除 dbms_sqltune や dbms_sqlset のパッケージを利用して SYS_AUTO_STS の中身を修正することが可能 • ASTS の使用容量が大きくなってSYSAUX表領域を圧迫する場合、以下のプロシージャで SYS_AUTO_STS から SQL文を削除できる • このプロシージャでSYSAUX表領域に作成されているSTS用のテーブルや索引のデータは削除されるが、 割り当て済みのエクステントは解放されないので、SYSAUX表領域の使用容量は削減されない • 使用容量まで削減したい場合は“TRUNCATE TABLE”または“ALTER TABLE MOVE”と索引のRebuildを行う必要がある • TRUNCATE TABLEはすべてのデータが失われるので、部分的に削除したい場合にはMOVEとRebuildを行う • 主にスペースを消費しているのは WRH$_SQLTEXT と WRI$_SQLSET_PLAN_LINES になる • 他の機能でも使用されているので注意（WRH$_SQLTEXTはAWRでも使用されている） • 詳細は「WRH$_SQLTEXTとWRI$_SQLSET_PLAN_LINESのエクステント解放」を参照 • basic_filter を使用して目的のSQLのサブセットをSTSから選択することもできる (実行例は次ページ参照） • SQLSET_ROW型に含まれる項目をフィルタリング可能 (where句として使用) 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 47 SQL> Exec dbms_sqltune.delete_sqlset(sqlset_name => 'SYS_AUTO_STS', sqlset_owner=>'SYS'); ASTSは過去の最適な実行計画のために取得しているものなので、自動SPMのためにはできるだけ削除しない方が良い
48. SQLの削除（実行例） 「PARSING_SCHEMA_NAME」をフィルターで使用して、 SOEスキーマ以外のSQL文をすべて削除する実行例 • まずは、ASTSより取得した各スキーマのSQLの数を確認する • 次に、SOE スキーマ以外のスキーマのSQL文を削除する • 最後、ASTS に残ったスキーマを確認する 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 48 SQL> Begin 2 dbms_sqlset.delete_sqlset( 3 sqlset_name =>'SYS_AUTO_STS’, 4 basic_filter=>'PARSING_SCHEMA_NAME in (''SYS’’, 5 ''LBACSYS’’, 6 ''ORDSYS’’, 7 ''DBSNMP’’, 8 ''GSMADMIN_INTERNAL’’, 9 ''C##OGGADMIN’’, 10 ''MDSYS’’, 11 ''DVSYS'')’); 12 End; 13 / SQL> Select Sqlset_Name, Parsing_Schema_Name, Count(*) 2 From Dba_Sqlset_Statements 3 Group by Sqlset_Name, Parsing_Schema_Name 4 Order by 3; SQLSET_NAME PARSING_SCHEMA_NAME COUNT(*) ---------------- ------------------------------ ---------- SYS_AUTO_STS SOE 50 1 rows selected. SQL> Select Sqlset_Name, Parsing_Schema_Name, Count(*) 2 From Dba_Sqlset_Statements 3 Group by Sqlset_Name, Parsing_Schema_Name 4 Order by 3; SQLSET_NAME PARSING_SCHEMA_NAME COUNT(*) -------------------- ------------------------------------- ---------- SYS_AUTO_STS DVSYS 2 SYS_AUTO_STS MDSYS 2 SYS_AUTO_STS C##OGGADMIN 2 SYS_AUTO_STS GSMADMIN_INTERNAL 5 SYS_AUTO_STS DBSNMP 6 SYS_AUTO_STS ORDSYS 6 SYS_AUTO_STS LBACSYS 9 SYS_AUTO_STS SOE 50 SYS_AUTO_STS SYS 1977 9 rows selected.
49. SQLの削除 (WRI$_SQLSET_PLAN_LINESとWRH$_SQLTEXTのエクステント解放) WRI$_SQLSET_PLAN_LINESにはCLOB列とLONG列が存在するので”ALTER TABLE MOVE”できない • WRH$_SQLTEXTとWRI$_SQLSET_PLAN_LINESに対するCLOB列とLONG列の確認 • LONG列を含む表をMOVEすると「ORA-00997: LONGデータ型は使用できません。」になるので、 WRI$_SQLSET_PLAN_LINESは以下のノートを参考に行う必要がある • How to Move LOB Data to Another Tablespace When the Table Also Contains a LONG Column (Doc ID 453186.1) • How to clear SYSAUX space consumption by WRI$_SQLSET_PLAN_LINES (Doc ID 2857648.1) • WRH$_SQLTEXTについては以下のノートを参考にしてください • High Storage Consumption for LOBs in SYSAUX Tablespace (Doc ID 396502.1) 自動SQLチューニング・セット Copyright © 2023, Oracle and/or its affiliates 49 SQL> SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM dba_tab_columns 2 WHERE DATA_TYPE IN ('LONG','LONG RAW', 'CLOB’) 3 AND table_name IN (SELECT table_name FROM dba_tables 4 WHERE tablespace_name='SYSAUX’ 5 AND table_name in ('WRI$_SQLSET_PLAN_LINES','WRH$_SQLTEXT')); TABLE_NAME COLUMN_NAME DATA_TYPE ------------------------------ -------------------- ---------- WRI$_SQLSET_PLAN_LINES OTHER_XML CLOB WRH$_SQLTEXT SQL_TEX CLOB WRI$_SQLSET_PLAN_LINES OTHER LONG
50. SPMを使用したアップグレード Copyright © 2023, Oracle and/or its affiliates 50
51. アップグレード後に性能劣化しないようにしたい • 自動SPMを使用しない場合 • 自動SPMを使用する場合 SPMを使用したアップグレード Copyright © 2023, Oracle and/or its affiliates 51
52. 自動SPMを使用しないアップグレード Copyright © 2023, Oracle and/or its affiliates 52 12cから取得した SQL計画ベースラインを有効にして、 19cのクエリ実行計画のパフォーマンス低下を修復する 12cデータベースでSQL計画ベースラインを生成する • SQLチューニング・セットで取得することも可能 12cデータベースを19cにアップグレードする • 又は19cデータベースを作成して12cベースラインのステージング 表をエクスポート/インポートする アプリケーションは12cの実行計画を使用し、SPMはEvolveのため に19cの新しい実行計画を取得する (19cが優れているときは使用 される) • optimizer_capture_sql_plan_baselines = FALSE • optimizer_use_sql_plan_baselines = TRUE • optimizer_features_enable = 19.X.X.X
53. 18c以前からのアップグレード • SQLチューニング・セットを生成する • 19c以降にアップグレードして自動SPMを使用する • 性能劣化したSQLだけをSQL計画ベースラインに設定される 19c以降からのアップグレード • 自動STSを有効にする • アップグレードして自動SPMを使用する • 性能劣化したSQLだけをSQL計画ベースラインに設定される 自動SPMを使用したアップグレード Copyright © 2023, Oracle and/or its affiliates 53
54. まとめ Copyright © 2023, Oracle and/or its affiliates 54 • 自動SPMは実行計画の変化によるパフォーマンス低下を自動的に防いでくれる非常に便利な機能になるので、ADB はデフォルト有効であるがExadataでも使用を検討してみてください • 自動STSは過去の最適な実行計画を取得可能にするものなので、できるだけ削除しないでください • 自動STSのSYSAUX表領域の消費量は少し気になるがAWRよりは大分少ない。心配なときには、紹介したSQLで 事前確認してから使用してください • 自動STSは自動インデックスのために作成されたものであるが、今後は様々な自動機能で使用されていくため、これか らも機能拡張がされていくと思われる
55. 自動SPM動作検証-ADW編 Copyright © 2023, Oracle and/or its affiliates 55
56. 56 Copyright © 2023, Oracle and/or its affiliates 使用するデータ, テーブルの確認 • Swingbench (https://www.dominicgiles.com/swingbench/) • SOE：注文入力システムをモデルとしたワークロード用シナリオ • ORDER_ITEMS表(36,338,169件)とPRODUCT_INFORMATION表(1,000件)に対してクエリを実行 • 実行するSQLは自動SPMが動作しやすい（実行計画に差が出やすい）ものを選択
57. 57 Copyright © 2023, Oracle and/or its affiliates 統計情報の収集および関連設定 -- ORDER_ITEMS表とPRODUCT_INFORMATIONの統計情報を収集 SQL> Exec dbms_stats.gather_table_stats(NULL, ‘order_items', method_opt => 'for all columns size 254', no_invalidate => false); SQL> Exec dbms_stats.gather_table_stats(NULL, ‘product_information', method_opt => 'for all columns size 254', no_invalidate => false); -- 高頻度オプティマイザ統計収集をOFFにし、ヒストグラムが作成されないようにする（後でヒストグラムを削除し意図的に実行計画を変化させます） SQL> Exec dbms_stats.set_global_prefs('AUTO_TASK_STATUS', 'OFF'); -- 高頻度オプティマイザ統計収集状態の確認 SQL> Select dbid, task_name, enabled, status, to_char(last_schedule_time, 'DD-MON-YY hh24:mi') LATEST SQL> From dba_autotask_schedule_control SQL> Where dbid = sys_context('userenv', 'con_dbid') ; DBID TASK_NAME ENABL STATUS LATEST ---------- ---------------------------------------- ----- ---------- ------------------------ 2714076024 Advisor Framework Purge TRUE SUCCEEDED 23-JAN-23 00:22 2714076024 Auto STS Capture Task TRUE SUCCEEDED 23-JAN-23 14:45 2714076024 Auto Statistics Management Task FALSE SUCCEEDED 21-JAN-23 03:21 2714076024 Auto SPM Task TRUE SUCCEEDED 23-JAN-23 14:29
58. 58 Copyright © 2023, Oracle and/or its affiliates 検証用SQLの実行および実行計画の確認 SQL> set timing on SQL> Select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, sum(o.quantity), count(*) SQL> From order_items o, product_information p SQL> Where o.product_id = p.product_id and p.product_id = 1 SQL> Group By p.product_name, p.supplier_id; PRODUCT_NAME SUPPLIER_ID SUM(O.QUANTITY) COUNT(*) -------------------------- -------------- --------------- --------- 3Nhuma2OVK3RXJpbO84TJSYl91 340256 0 134 Elapsed: 00:00:00.07 -- 検証用SQLの実行計画を確認 SQL> set autotrace traceonly explain SQL> Select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, sum(o.quantity), count(*) SQL> From order_items o, product_information p SQL> Where o.product_id = p.product_id and p.product_id = 1 SQL> Group By p.product_name, p.supplier_id; ※実行結果のSUMが0になっていますが、自動SPMが動作する選択 率にするための条件設定をしています
59. 59 Copyright © 2023, Oracle and/or its affiliates 検証用SQLの実行および実行計画の確認 Execution Plan --------------------------- Plan hash value: 4007241392 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 3777 (1)| | 1 | HASH GROUP BY | | 1 | 39 | 3777 (1)| | 2 | NESTED LOOPS | | 4293 | 163K| 3777 (1)| | 3 | TABLE ACCESS BY INDEX ROWID | PRODUCT_INFORMATION | 1 | 32 | 1 (0)| | 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK| 1 | | 1 (0)| | 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORDER_ITEMS | 4293 | 30051 | 3776 (1)| | 6 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 4293 | | 14 (0)| ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 – access(“P”.“PRODUCT_ID”=1) 6 – access(“O”.“PRODUCT_ID”=1) Note ---- - automatic DOP: Computed Degree of Parallelism is 1
60. 60 Copyright © 2023, Oracle and/or its affiliates 自動STSより取得されたことを確認 SQL> set autotrace off SQL> Select sql_text, executions, plan_hash_value SQL> From dba_sqlset_statements SQL> Where sql_text like 'select /* testsql */%' SQL> and sqlset_name = 'SYS_AUTO_STS'; SQL_TEXT EXECUTIONS PLAN_HASH_VALUE ------------------------------------------------------------------------------ ---------- --------------- select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, 1 4007241392 sum(o.quantity), count(*) from order_items o, product_information p where o.product_id = p.product_id and p.product_id = 1 group by p.product_name, p.supplier_id;
61. 61 Copyright © 2023, Oracle and/or its affiliates SQL> Exec dbms_stats.delete_column_stats(user, ‘order_items', ‘product_id', col_stat_type => 'histogram'); SQL> Select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, sum(o.quantity), count(*) SQL> From order_items o, product_information p SQL> Where o.product_id = p.product_id and p.product_id = 1 SQL> Group By p.product_name, p.supplier_id; PRODUCT_NAME SUPPLIER_ID SUM(O.QUANTITY) COUNT(*) -------------------------- -------------- --------------- --------- 3Nhuma2OVK3RXJpbO84TJSYl91 340256 0 134 Elapsed: 00:00:00.07 劣化した実行計画を採用させるため、統計情報を削除
62. 62 Copyright © 2023, Oracle and/or its affiliates 劣化した実行計画の採用を確認 SQL> set autotrace traceonly SQL> Select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, sum(o.quantity), count(*) SQL> From order_items o, product_information p SQL> Where o.product_id = p.product_id and p.product_id = 1 SQL> Group By p.product_name, p.supplier_id;
63. 63 Copyright © 2023, Oracle and/or its affiliates 劣化した実行計画の採用を確認 Execution Plan --------------------------- Plan hash value: 2559952882 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 7918 (6)| | 1 | HASH GROUP BY | | 1 | 39 | 7918 (6)| | 2 | NESTED LOOPS | | 35943 | 1368K| 7918 (6)| | 3 | TABLE ACCESS BY INDEX ROWID | PRODUCT_INFORMATION | 1 | 32 | 1 (0)| | 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK| 1 | | 1 (0)| | 5 | PARTITION HASH ALL | | 35943 | 245K| 7917 (6)| | 6 | TABLE ACCESS STORAGE FULL | ORDER_ITEMS | 35943 | 245K| 7917 (6)| ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 – access(“P”.“PRODUCT_ID”=1) 6 – access(“O”.“PRODUCT_ID”=1) filter(“O”.“PRODUCT_ID”=1) Note ---- - automatic DOP: Computed Degree of Parallelism is 1 INDEX SCANからFULL SCAN になっている
64. 64 Copyright © 2023, Oracle and/or its affiliates 自動STSより劣化した実行計画が取得されたことを確認 SQL> set autotrace off SQL> Select sql_text, executions, plan_hash_value SQL> From dba_sqlset_statements SQL> Where sql_text like 'select /* testsql */%' SQL> and sqlset_name = 'SYS_AUTO_STS'; SQL_TEXT EXECUTIONS PLAN_HASH_VALUE ------------------------------------------------------------------------------ ---------- --------------- select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, 1 4007241392 sum(o.quantity), count(*) from order_items o, product_information p where o.product_id = p.product_id and p.product_id = 1 group by p.product_name, p.supplier_id; select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, 1 2559952882 sum(o.quantity), count(*) from order_items o, product_information p where o.product_id = p.product_id and p.product_id = 1 group by p.product_name, p.supplier_id; 新しい実行計画が取得されている
65. 65 Copyright © 2023, Oracle and/or its affiliates SPMベースラインが設定されたことを確認 上記ACCept列はYESとなっているため、該当ベースラインが使用されたことが分かります。 下記のように実行計画が使用されたことも確認できます。 ...<高頻度自動Evolveアドバイザ待ち>... SQL> Select sql_handle, plan_name, enabled, accepted, origin, cpu_time, buffer_gets SQL> From dba_sql_plan_baselines SQL> Where sql_text like 'select /* testsql */%'; SQL_HANDLE PLAN_NAME ENA ACC ORIGIN CPU_TIME BUFFER_GETS -------------------- ------------------------------ --- --- -------------------- --------- ----------- SQL_c2b3fe4a798b0966 SQL_PLAN_c5czy99wsq2b6f057ff6a YES YES EVOLVE-LOAD-FROM-STS 7699 146 SQL> set autotrace traceonly explain SQL> Select /* testsql */ /*+ NO_RESULT_CACHE */ p.product_name, p.supplier_id, sum(o.quantity), count(*) SQL> From order_items o, product_information p SQL> Where o.product_id = p.product_id and p.product_id = 1 SQL> Group By p.product_name, p.supplier_id;
66. 66 Copyright © 2023, Oracle and/or its affiliates SPMベースラインが設定されたことを確認 Execution Plan --------------------------- Plan hash value: 4007241392 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 31559 (1)| | 1 | HASH GROUP BY | | 1 | 39 | 31559 (1)| | 2 | NESTED LOOPS | | 35943 | 1368K| 31559 (1)| | 3 | TABLE ACCESS BY INDEX ROWID | PRODUCT_INFORMATION | 1 | 32 | 1 (0)| | 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK| 1 | | 1 (0)| | 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORDER_ITEMS | 35943 | 245K| 31598 (1)| | 6 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 35943 | | 103 (0)| ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 – access(“P”.“PRODUCT_ID”=1) 6 – access(“O”.“PRODUCT_ID”=1) Note ---- - SQL plan baseline “SQL_PLAN_c5czy99wsq2b6f057ff6a” used for this statement
67. 67 Copyright © 2023, Oracle and/or its affiliates 自動Evolveタスク・レポートを確認 -- 最後に実行された自動EvolveタスクのEXECUTION_NAMEを確認 SQL> Select task_name, execution_name, status, to_char(execution_end,'yy/mm/dd hh24:mi:ss') SQL> From dba_advisor_executions SQL> Where task_name like '%SPM%' SQL> and (exection like 'SYS_SPM%' or execution_name like 'EXEC_%') SQL> Order by execution_end; TASK_NAME EXECUTION_NAME STATUS TO_CHAR(EXECUTION ------------------------------ ---------------------------------------- ----------- ----------------- ... SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-01-23/13:29:07 COMPLETED 23/01/23 13:29:07 SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-01-23/14:29:33 COMPLETED 23/01/23 14:29:34 SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-01-23/15:30:01 COMPLETED 23/01/23 15:30:01 SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-01-24/00:34:03 COMPLETED 23/01/24 00:34:04 SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-01-24/01:34:31 COMPLETED 23/01/24 01:34:33
68. 68 Copyright © 2023, Oracle and/or its affiliates 自動Evolveタスク・レポートを確認 SQL> DBMS_SPM.REPORT_AUTO_EVOLVE_TASK(EXECUTION_NAME=>‘SYS_SPM_2023-01-24/01:34:31‘) ------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ---------------------------------------------------------------------------------------- Task Information: --------------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve task Execution Name : SYS_SPM_2023-01-24/01:34:31 ・ ・ ・ FINDINGS SECTION ---------------------------------------------------------------------------------------- Findings (2): ---------------------------- 1. The plan was verified in 1.13300 seconds. It passed the benefit criterion because its verified performance was 4351.41205 times better than that of the baseline plan. 2. The plan was automatically accepted.