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

Automatic SQL Plan Managemet (AutoSPM)
は使えるのか
Oracle Database TechNight #64
津島・出口
日本オラクル株式会社
クラウド・エンジニアリング統括
COE本部
2023年2月22日

View Slide

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

View Slide

1. SQL計画管理とは
2. 自動SQL計画管理
3. 自動SQLチューニング・セット
4. SPMを使用したアップグレード
5. 自動SPM動作検証（ADW編）
Agenda
3

View Slide

SQL計画管理（SPM :
SQL Plan Management）
とは
4

View Slide

SQL文の実行計画が決まる要因
コストベース・オプティマイザは、統計情報などを使用して各SQL文に最適な実行計画を選択する
• オプティマイザのバージョンによって生成される実行計画が変化する可能性がある（オプティマイザ機能の違いにより）
• 例えば、Query Transformation (問合せ変換) はバージョン・アップごとに機能が追加される
SQL計画管理 (SPM) とは
5
Query Transformation
より効率的に処理できるように、問合せテキストを書き
換える
Plan Generator
各SQLに異なるアクセスパスや結合タイプを使用した
複数プランが生成され、各プランのコスト計算から最小
コストのプランを使用する
Cost Estimator
コストは操作の実行に使用される
CPU時間とディスクI/O数の見積もり
Cost-based Optimizer SQLテキスト
オブジェクト構造
初期化パラメータ
統計情報データの実態

View Slide

6
SQLの実行計画
• 予測不可能な変更が起こりうる
• 速くなれば問題ないが遅くなる場合もある
SPM以前 (11gより前) のソリューション
• 実行計画の変更を回避することがパフォーマンス低下を防ぐ唯一の方法
• 統計情報をロックして変更を防止する
• ストアドアウトラインによる実行計画の凍結
• 実行計画をEvolve (改良) させる仕組みがない！
なぜ実行計画の管理が必要なのか
将来はパフォーマンスの問題になるリスクがある
SPMによるソリューション
• オプティマイザは「実行計画」を自動管理する
• 既知の承認済み実行計画のみを使用
• 新しい実行計画は検証される
• 今後は同等またはより良い実行計画のみが使用される

View Slide

7
データの変化により
• コストベース・オプティマイザだからオプティマイザ統計が
変化するば実行計画も変化する
• オプティマイザ統計が正確でないと非効率な実行計画に
バージョン・アップやパラメータの変更により
• オプティマイザ新機能 (問合せ変換など) が動作する
• オプティマイザ統計が正確でないと新機能 (テストしてい
なかった機能) が誤動作
• 問合せ変換もコストベースで行う
データの変動に伴う性能劣化
• データの変動を考慮して、最適な実行計画が使用さ
れるよう実行計画が自動で変化する
• コスト計算が正しくないため最適でない実行計画に
データの変動
性能(レスポンス)
実行計画A
の誤ったコスト
実行計画B
実行計画A
なぜ実行計画は劣化するのか
オプティマイザ統計は正確にならない場合もある
• 統計収集のサンプルサイズが小さい
• 拡張統計は自動的には作成されない
• 複数表の統計 (結合カーディナリティなど) はない

View Slide

主要コンポーネント
8
3つの主要コンポーネント
• SQL計画ベースライン取得 (自動、手動)
• SQL計画ベースライン選択 (ベースラインによる計画の安定化)
• SQL計画ベースライン展開/改良 (Evolve)
2つの初期化パラメータで制御される
• optimizer_capture_sql_plan_baselines
• 繰返し可能SQL文に対するSQL計画ベースラインの自動取得を制御 (デフォルトはFALSE)
• optimizer_use_sql_plan_baselines
• オプティマイザによる既存のSQL計画ベースラインの使用を制御 (デフォルトはTRUE)
• SQL計画ベースラインが設定されないと何もしない
選択計画履歴
計画ベースライン
取得
展開
新しい
計画

View Slide

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

View Slide

シグネチャ
SQL Management Base
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

View Slide

スペース使用量の調整方法と確認
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
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領域の占有の割合を超える場合は、予想
外の容量消費の増加について毎週アラートを生成
するだけです。

View Slide

スペース使用量の調整方法と確認
削除を手動で実行する
• 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
12
ret := sys.dbms_spm_internal.auto_purge_sql_plan_baseline ;
ret := dbms_spm.drop_sql_plan_baseline(,<プラン名>);
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

View Slide

• DBA_SQL_PLAN_BASELINESビュー
• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクション
SQL計画ベースラインとSQL計画履歴の確認
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は文字列形式の
一意のプラン識別子

View Slide

自動計画取得
繰返し可能SQL (SQL文ログにシグネチャが存在するSQL)
1. SQL文が初めてハード解析され計画が生成される
2. ログを確認してシグネチャがあるか (2回目以降の実行か) を確認する
(単発実行のSQLは対象にしないため)
3. 存在しない場合にシグネチャをSQL文ログに追加して実行する
4. 存在する場合に計画履歴を作成しSQL計画ベースラインとして使用
する
SQL計画ベースライン取得
14
SQL計画ベースラインの自動作成
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES を
TRUE (デフォルトはFALSE) にすると、繰返し可能SQL文に
対して、SQL計画ベースラインが自動的に作成される
• 以下が SPM の構成情報
SQL> Select Parameter_Name, Parameter_Value
2 From Dba_Sql_Management_Config;
----------------------------------- ----------------------
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回目の実行

View Slide

自動計画取得
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);

View Slide

手動計画取得
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

View Slide

ステージング表から
• SQL計画ベースラインを別のシステムで取得することが可能
• テーブル経由でエクスポートされ (統計情報と同様) ローカルにインポー
トされる
• プランはテーブルから「unpacked」されSPMにロードされる
ストアド・アウトラインから
• 以前作成したストアドアウトラインをSQL計画ベースラインに移行する
手動計画取得（実行例）
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');

View Slide

SQL文が実行されたとき
SQL計画ベースライン選択
18
オプティマイザは最適なコストベースの実行計画を生成するが、
OPTIMIZER_USE_SQL_PLAN_BASELINEがTRUEだと、
この計画で実行する前に、次の動作を行う
• オプティマイザはSQL計画ベースラインが存在するかを確認する
• ベースラインが存在しない場合は新たに生成された計画で自動計
画取得の確認を行う
• ベースラインが存在する場合は新たに生成された計画がベース
ラインにあるかを確認する (このとき固定計画があると優先され
る)
• 新たな計画がベースラインにある (PLAN_IDが一致した) 場合は
この計画で実行する
• ない場合は既知のベースライン計画で実行して、この計画を計画
履歴に保存する
• ベースライン内に再現可能な計画がない場合は新たに生成さ
れた計画を使用し、この計画を未承認計画としてSQL計画履
歴に保存する
つまり、SQL計画ベースラインが設定されると、新しい実行計画は
計画履歴に追加されるようになる

View Slide

選択されたベースラインの確認
SQL計画ベースライン選択
19
SQL> SELECT sum(tab1.col1),sum(tab2.col1),count(*) FROM tab1,tab2 WHERE tab1.col2=tab2.col2 AND tab2.col2 = 1;
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------
| 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

View Slide

SQL計画ベースライン展開/改良
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

View Slide

• タスク・パラメータの確認
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;
------------------------- ---------------
ACCEPT_PLANS TRUE
ALTERNATE_PLAN_LIMIT UNLIMITED
DAYS_TO_EXPIRE UNLIMITED
DEFAULT_EXECUTION_TYPE SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE 30
TIME_LIMIT 3600

View Slide

手動展開アドバイザ・タスク（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ファンクション
22
手動タスク
• DBAが手動で個々のSQL計画履歴を検証し承認する
（自動的に承認されない）
• 以下の手順で行う
1. 展開タスクの作成
2. 展開アドバイザ・タスクの実行
3. 展開レポートを生成して確認
4. 推奨された実行計画の承認
Evolveタスク

View Slide

Evolveプロセスの実行例 (手動タスク)
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;

View Slide

自動計画取得から展開タスクまでの流れ
SQL計画管理
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
-------------------------------------------------------------
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | 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 |
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
実行

View Slide

実行されたSQLにSQL計画ベースラインが存在する場合 (optimizer_use_sql_plan_baselines=TRUE)
• 新しい実行計画が生成されると、
承認されるまでSQL計画履歴に格納される
• SQL計画ベースラインの実行計画で実行される
SQL計画管理
25
SQL計画履歴
WHERE a.code = :1
-----------------------------------------------------------
-----------------------------------------------------------
|* 1 | HASH JOIN | | 25 |
| 8 | TABLE ACCESS FULL | TAB2 | 25 |
-----------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
実行
-----------------------------------------------------------
-----------------------------------------------------------
|* 1 | HASH JOIN | | 25 |
-----------------------------------------------------------

View Slide

SQL計画管理
26
展開タスクが実行された場合
• SQL計画履歴の実行計画が検証され、優れていれば承認されて
SQL計画ベースラインに追加される
• そうでなければ計画履歴にそのままに
• 展開タスクでの検証
• 「Elapsed Time」と「Buffer Gets」などを検証する
• 50%高速化する必要がある (1.5倍性能アップ)
• SQLチューニング・アドバイザと同じテスト・フレームワークを使用
SQL計画履歴
WHERE a.code = :1
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
|* 1 | HASH JOIN | | 25 |
-----------------------------------------------------------

View Slide

自動計画取得から展開タスクまでの流れ)
SQL計画管理
27
実行されたSQLに複数のベースラインが存在する場合 (optimizer_use_sql_plan_baselines=TRUE)
• オプティマイザで生成された実行計画が存在する (その実行
計画で実行する)
• オプティマイザで生成された実行計画が存在しない (その実行
計画が計画履歴に格納され、コストが最も低いベースラインの
実行計画で実行する)
SQL計画履歴
WHERE a.code = :1
-----------------------------------------------------------
-----------------------------------------------------------
|* 1 | HASH JOIN | | 25 |
-----------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
|* 1 | HASH JOIN | | 25 |
-----------------------------------------------------------
実行

View Slide

その他のパフォーマンス機能との関係
SQL計画管理
28
適応計画（最終計画の決定は実行時まで先延ばしする）
• SQL計画ベースラインは適応計画の最終計画で格納されているので、選択で適応計画になることはない
• 新しい計画に適応性があるとデフォルト計画に適応マークを付けて計画履歴に格納する (実行前なので最終計画にできない)
• 適応マークされた計画を展開するときは最終計画を求めて検証される
SQLプロファイル（実行計画を最適にするための補足情報）
• SQLプロファイルが存在するSQL文は３つのコンポーネントの見積りコストに影響を受けるがSPMの動作は同じ
Adaptive Cursor Sharing（バインド変数の値によって実行計画を決定）
• SPMは計画を制限させるが、ACSはバインド値によって計画を変化させるという相反する機能
• SQL計画ベースラインにACSの候補となる実行計画 (バインド値に適切な計画) がすべて含まれていないと選択されない
• SQL計画ベースラインが一つ(自動計画取得の最初の計画など)のSQL文はACSで生成された計画は計画履歴に格納されるだけ
• Evolveタスクでバインド値を意識した検証はしないので、ACSのメリットが得られる計画をすべて手動でロードする必要がある

View Slide

18cまでの問題点
SQL計画管理
29
SQL計画ベースラインの取得が効果的に行うことができない
• 手動は難しい、自動は必要ないものも取得される
• 12.2では、自動取得のフィルター機能で必要なものだけを取得するようになった
• ただし、性能まで判断できない
• フィルターの設定が難しい
• できれば問題が発生したSQLを自動で取得したい
19cでは、自動SQL計画管理により、実行計画管理のすべてのステップを自動化
• 次の章ではこの自動SQL計画管理について紹介する

View Slide

自動SQL計画管理
（自動SPM）
30

View Slide

SQLパフォーマンスの低下を自動的に検出して修復できるように19c から追加
• Exadata/ExaCS/ExaCC/ADB 上だけで使用可能
31
自動SQL計画管理は展開アドバイザ・タスクとして以下の
動作を行う
1. 大量のシステム・リソースを消費する SQL文を識別するために
AWR (遅いSQLが格納されている) と自動SQLチューニング・セット
(ASTS) を使用する (過去のパフォーマンス情報を使用して、パ
フォーマンス低下の可能性があるかどうかを確認する)
2. 特定されたSQL文の代替実行計画となるものを探す (見つかった
計画はすべて取得され、SQL計画履歴に保存される)
3. SQL展開アドバイザのテストで、取得された計画が実行され、どの
計画がベストか判断される
4. 既存の計画よりパフォーマンスが良いことが判明した計画は、SQL
計画ベースラインに追加される
5. 問合せの劣化を回避するために、SQL計画ベースラインが計画を
制限する
1と2は自動SPMより追加され
た動作となります

View Slide

自動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 を効果的に利用するには高頻度タスクを有効にする必要がある
32

View Slide

自動SPMの確認 (ADBのデフォルト)
• ALTERNATE_PLAN_SOURCEはSQL_TUNING_SETになっている
• 高頻度自動SPM 展開アドバイザ・タスクを有効に設定する方法
※高頻度タスクを有効にすると自動SPM も有効になる
• 高頻度自動SPM 展開アドバイザ・タスクを無効に設定する方法
※高頻度タスクを無効にすると自動SPM も無効になる
自動SPM 及び高頻度自動SPM 展開アドバイザ・タスク機能は
Exadata/ADBのみの機能
有効化
自動SPM を有効に設定する方法
'ALTERNATE_PLAN_BASELI
自動SPM を無効に設定する方法 (Exadataのデフォルト)
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
9 parameter => 'ALTERNATE_PLAN_SOURCE’,
11 END;
12 /
SQL> BEGIN
3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK’ ,
4 parameter => 'ALTERNATE_PLAN_BASELINE’,
5 value => 'EXISTING’);
6
9 parameter => 'ALTERNATE_PLAN_SOURCE’,
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

View Slide

タスク状態の確認
自動SPM (高頻度自動SPM展開アドバイザ・タスク) の状態は
以下のSQL文で確認できる
• ADBの場合は、高頻度自動SPM 展開アドバイザ・タスクは
デフォルト有効になっており、実行間隔は1時間(3600秒)おき
に実行されている
• Exadataの場合は、高頻度自動SPM展開アドバイザ・タスク
はデフォルト無効になっている
実行間隔の変更
• 高頻度自動SPM 展開アドバイザ・タスクの実行間隔は
1時間(3600秒)から変更できない
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

View Slide

タスクの実行状況確認
展開アドバイザの実行状況は以下の SQLで確認できる
• 標準自動タスクと高頻度自動タスクはタスク名が同じになるので実行名で判断する
• 標準タスクの実行名が EXEC_ という形式に対し、高頻度の実行名は SYS_SPM_ という形式になる
• EXECUTION_NAME 列より展開アドバイザ・タスクは 1 時間おきに実行されていることを確認できる
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

View Slide

タスクの実行内容確認
SQL計画ベースラインを以下の SQL で確認できる
• 自動SPMタスクの SYS_AUTO_SPM_EVOLVE_TASK で処理されたものは EVOLVE-LOAD-FROM-xxx になる
• ACCepted 列は YES になっている SQL は新しい実行計画が採用されている
DBMS_SPM.REPORT_AUTO_EVOLVE_TASK ファンクションにより高頻度自動SPM展開アドバイザ・タスクの具体的な実行内容が
表示可能（次ページ参照）
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<省略>

View Slide

自動Evolveレポート
以下の SQL より詳細Evolveレポートが出力される
• 高頻度タスクのEXECUTION_NAMEは
DBA_ADVISOR_EXECUTIONSビューから取得
右図は詳細Evolveレポートの一部となり、以下の項目から具体的
なevolveレポートが確認可能
• 「DETAILS SECTION」
具体的なSQLの情報が表示される
• 「FINDINGS SECTION」
具体的な見つかった問題点などが紹介される
• 「Recommendation」
劣化のSQL実行計画に対して具体的な対応方法が紹介され
る
37
SQL> Select dbms_spm.report_auto_evolve_task(
2 execution_name => 'SYS_SPM_2022-12-05/08:12:41')
3 From dual;

View Slide

タスクの実行時間
自動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）
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

View Slide

スペース使用量
自動 SPM もそれぞれの SQL の情報をSYSAUX表領域上の SMB に作成して実行計画の管理を行う
• SMBは SYSAUXの割合としてSPACE_BUDGET_PERCENTパラメータ (デフォルトは10%) で制限されている
• Autonomous Database の場合はこの比率が 50 までしか変更できない
目安として、合計193のベースラインの場合は、約61MBのSMB専用の SYSAUX 表領域のスペースが使用されている
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

View Slide

自動SQLチューニング・セット
（自動STS）
40

View Slide

データベースで実行された複数の SQL の下記のような情報を含むデータベース・オブジェクト
◼ SQL 文のセット
◼ 関連する実行コンテキスト（スキーマ、アプリケーション・モジュール名、バインド値など）
◼ 関連する基本実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など）
◼ 各 SQL 文の関連実行計画と行ソース統計(オプション)
STS だけでも利用可能だが、下記のような Oracle Database の高度な機能と組み合わせて利用することもある
◼ データベース上でのテストを実行する機能 ⇒ SQL Performance Analyzer
◼ チューニングのアドバイスを生成する機能 ⇒ SQL Tuning Advisor / SQL Access Advisor
◼ 実行計画を固定化するための機能 ⇒ SQL Plan Management
STS はあるデータベースで記録したものを別のデータベースに移動させることにも使用可能
SQLチューニング・セット (STS) とは
41
カーソル・キャッシュ
AWR
SQLトレース
他のSTS
STS
SPA (SQL Performance Analyzer)
SQL Tuning Advisor
SQL Access Advisor
SQL Plan Management

View Slide

システムが管理する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) とは
42

View Slide

有効化と確認方法
ASTSの有効かどうかの状態を確認する
ASTSを有効にする
ASTSを無効にする (RU19.8以降ADB以外はデフォルト無効)
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

View Slide

タスクの実行時間
ASTS タスクの実行性能は以下のように取得できる（最大時間、最小時間、平均時間）
• CDB 及び PDB の配下に各自のASTSが存在しているため、個別に確認する必要がある
この例では、PDB上で右図のSQLを実行して、自動 STS タスクの過去の実行時間を確認している
• それぞれの値は次のようになる
• 過去最長実行時間は20秒
• 過去最短実行時間は0秒(四捨五入)
• 過去平均実行時間は3.58秒
• 過去平均CPU 時間は2.76秒
通常のシステムにおいて、ASTS のリソース使用量はごくわずかで、ほとんどの場合無視できるレベル
• 通常、大規模な ASTS (シングルスレッド) のキャプチャ・タスクの実行時間は 30 ～ 60 秒になる
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

View Slide

消費容量
すべての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文や実行計画が複雑であればより割合が増える
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

View Slide

SQLの平均消費サイズ
SQLの平均消費サイズを確認する
• 次のクエリで SQL チューニングセットごとのSQL数を確認する
• 例: 前ページで確認できたTOTAL_SIZE_MBをCOUN数で割ると、一つSQLの平均サイズはおおよそ 76.06KB (59.5*1024/801) となる
ASTS がキャプチャした各スキーマの SQL数はどのぐらいあるかを把握するには、以下のSQLで確認できる
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がキャプチャされていることが分かった

View Slide

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句として使用)
47
SQL> Exec dbms_sqltune.delete_sqlset(sqlset_name => 'SYS_AUTO_STS', sqlset_owner=>'SYS');
ASTSは過去の最適な実行計画のために取得しているものなので、自動SPMのためにはできるだけ削除しない方が良い

View Slide

SQLの削除（実行例）
「PARSING_SCHEMA_NAME」をフィルターで使用して、
SOEスキーマ以外のSQL文をすべて削除する実行例
• まずは、ASTSより取得した各スキーマのSQLの数を確認する
• 次に、SOE スキーマ以外のスキーマのSQL文を削除する
• 最後、ASTS に残ったスキーマを確認する
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.

View Slide

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)
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

View Slide

SPMを使用したアップグレード
50

View Slide

アップグレード後に性能劣化しないようにしたい
• 自動SPMを使用しない場合
• 自動SPMを使用する場合
SPMを使用したアップグレード
51

View Slide

自動SPMを使用しないアップグレード
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

View Slide

18c以前からのアップグレード
• SQLチューニング・セットを生成する
• 19c以降にアップグレードして自動SPMを使用する
• 性能劣化したSQLだけをSQL計画ベースラインに設定される
19c以降からのアップグレード
• 自動STSを有効にする
• アップグレードして自動SPMを使用する
• 性能劣化したSQLだけをSQL計画ベースラインに設定される
自動SPMを使用したアップグレード
53

View Slide

まとめ
54
• 自動SPMは実行計画の変化によるパフォーマンス低下を自動的に防いでくれる非常に便利な機能になるので、ADB
はデフォルト有効であるがExadataでも使用を検討してみてください
• 自動STSは過去の最適な実行計画を取得可能にするものなので、できるだけ削除しないでください
• 自動STSのSYSAUX表領域の消費量は少し気になるがAWRよりは大分少ない。心配なときには、紹介したSQLで
事前確認してから使用してください
• 自動STSは自動インデックスのために作成されたものであるが、今後は様々な自動機能で使用されていくため、これか
らも機能拡張がされていくと思われる

View Slide

自動SPM動作検証-ADW編
55

View Slide

使用するデータ, テーブルの確認
• Swingbench (https://www.dominicgiles.com/swingbench/)
• SOE：注文入力システムをモデルとしたワークロード用シナリオ
• ORDER_ITEMS表(36,338,169件)とPRODUCT_INFORMATION表(1,000件)に対してクエリを実行
• 実行するSQLは自動SPMが動作しやすい（実行計画に差が出やすい）ものを選択

View Slide

統計情報の収集および関連設定
-- 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

View Slide

検証用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
※実行結果のSUMが0になっていますが、自動SPMが動作する選択
率にするための条件設定をしています

View Slide

検証用SQLの実行および実行計画の確認
Execution Plan
---------------------------
----------------------------------------------------------------------------------------------------
| 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)|
----------------------------------------------------------------------------------------------------
---------------------------------------------------
4 – access(“P”.“PRODUCT_ID”=1)
6 – access(“O”.“PRODUCT_ID”=1)
Note
----
- automatic DOP: Computed Degree of Parallelism is 1

View Slide

自動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;

View Slide

SQL> Exec dbms_stats.delete_column_stats(user, ‘order_items', ‘product_id', col_stat_type => 'histogram');
PRODUCT_NAME SUPPLIER_ID SUM(O.QUANTITY) COUNT(*)
-------------------------- -------------- --------------- ---------
3Nhuma2OVK3RXJpbO84TJSYl91 340256 0 134
Elapsed: 00:00:00.07
劣化した実行計画を採用させるため、統計情報を削除

View Slide

劣化した実行計画の採用を確認
SQL> set autotrace traceonly

View Slide

劣化した実行計画の採用を確認
Execution Plan
---------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 39 | 7918 (6)|
| 2 | NESTED LOOPS | | 35943 | 1368K| 7918 (6)|
| 5 | PARTITION HASH ALL | | 35943 | 245K| 7917 (6)|
| 6 | TABLE ACCESS STORAGE FULL | ORDER_ITEMS | 35943 | 245K| 7917 (6)|
----------------------------------------------------------------------------------------------
---------------------------------------------------
filter(“O”.“PRODUCT_ID”=1)
Note
----
- automatic DOP: Computed Degree of Parallelism is 1
INDEX SCANからFULL SCAN
になっている

View Slide

自動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
------------------------------------------------------------------------------ ---------- ---------------
新しい実行計画が取得されている

View Slide

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

View Slide

SPMベースラインが設定されたことを確認
Execution Plan
---------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 39 | 31559 (1)|
| 2 | NESTED LOOPS | | 35943 | 1368K| 31559 (1)|
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORDER_ITEMS | 35943 | 245K| 31598 (1)|
| 6 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 35943 | | 103 (0)|
-----------------------------------------------------------------------------------------------------
---------------------------------------------------
Note
----
- SQL plan baseline “SQL_PLAN_c5czy99wsq2b6f057ff6a” used for this statement

View Slide

自動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
------------------------------ ---------------------------------------- ----------- -----------------
...

View Slide

自動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.

View Slide

View Slide

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

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

oracle4engineer
PRO

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript

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

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

oracle4engineer PRO

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript

oracle4engineer
PRO