Autonomous DatabaseではSPM(SQl Performance Management)が有効化されています。
SPMについては詳細は津島博士のパフォーマンス講座第75回をご確認ください。
動作について確認してみました。
実行はADMINユーザで行います
1. 使用する表とSQLについて
サンプル・スキーマSSBのCUSTOMER表をADMINでCTASで作成した表を使うことにします。
create table customer as select * from ssb.customer;
SQLはこの2つです。後で検索しやすいようにコメントをいれています。
- select /* spm_check */ c_name from customer where c_custkey=30000000;
- select /* spm_check / count() from customer where c_region='MIDDLE EAST'
現在の実行計画を確認すると、索引を何も作成していないので全表スキャンです。
1の結果のみですが、2のSQLも同様です。
/* SQLを実行 */
SQL> select /* spm_check */ c_name from customer where c_custkey=30000000;
C_NAME
-------------------------
Customer#030000000
/* 実行計画の確認 */
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3m45v8vtbcxca, child number 0
-------------------------------------
select /* spm_check */ c_name from customer where c_custkey=30000000
Plan hash value: 2844954298
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 131K(100)| |
|* 1 | TABLE ACCESS STORAGE FULL| CUSTOMER | 5455 | 133K| 131K (1)| 00:00:06 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("C_CUSTKEY"=30000000)
filter("C_CUSTKEY"=30000000)
2. Auto SQL Tuning Set(ASTS)へのキャプチャ
Autonomous Databaseでは自動SQLチューニング・セット(Auto SQL Tuning Set/ASTS)が有効化されています。
ASTSのキャプチャタスクは15分に1回実行されます。キャプチャされるようにSQLを複数回実行します。
/* Auto STS Captureタスクの確認 */
select task_name, status,enabled,interval,last_schedule_time,systimestamp-last_schedule_time ago from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid') and task_name like '%STS%';
TASK_NAME STATUS ENABLED INTERVAL LAST_SCHEDULE_TIME AGO
--------------------- --------- ------- -------- ------------------------ -----------------
Auto STS Capture Task SUCCEEDED TRUE 900 2023-02-16T07:28:37.074Z +0 0:10:18.262262
/* ASTSにSQLが含まれているか確認 →ない */
select sql_text from dba_sqlset_statements where sql_text like '%spm_check%'
and sqlset_name = 'SYS_AUTO_STS';
no rows selected
/* SQLを各2回以上実行する */
select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
select /* spm_check */ c_name from customer where c_custkey=30000000;
/* Auto STS Captureタスクの確認 */
select task_name, status,enabled,interval,last_schedule_time,systimestamp-last_schedule_time ago from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid') and task_name like '%STS%';
TASK_NAME STATUS ENABLED INTERVAL LAST_SCHEDULE_TIME AGO
--------------------- ------- ------- -------- ------------------------ ---------------
Auto STS Capture Task RUNNING TRUE 900 2023-02-16T07:43:47.458Z +0 0:0:2.928063
/* 改めてASTSに含まれているか確認 →はいっている */
select sql_id,sql_text from dba_sqlset_statements where sql_text like '%spm_check%'
and sqlset_name = 'SYS_AUTO_STS';
SQL_ID SQL_TEXT
------------- -----------------------------------------------------------
3m45v8vtbcxca select /* spm_check */ c_name from customer where c_custkey=30000000
gducdyfnax47m select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
:
3.ベースラインへのロード
ベースラインのロードは自動、手動の二通り方法があります。今回は手動でASTSからSQLをフィルタしてロードします。
自動はSPM展開アドバイザタスクにより負荷が高いSQLがロードされる、または、初期化パラメータoptimizer_capture_sql_plan_baselinesをtrueに変更すると2回以上実行されたSQLは自動的にベースラインに登録されます。Autonomous Databaseでは後者のパラメータはセッションレベルでのみ設定変更が可能です。
/* ベースラインへロードする */
DECLARE
cnt integer;
BEGIN
cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name=>'SYS_AUTO_STS',
sqlset_owner=>'SYS',
basic_filter=> 'sql_text like ''select /* spm_check */%''');
END;
/
PL/SQLプロシージャが正常に完了しました。
/* ベースラインに含まれているか確認*/
select SQL_HANDLE,PLAN_NAME,ACCEPTED,CREATED,fixed,enabled,origin,last_modified,sql_text from dba_sql_plan_baselines
where CREATED >= sysdate -1 and sql_text like '%spm_check%' order by CREATED;
SQL_HANDLE PLAN_NAME ACCEPTED CREATED FIXED ENABLED ORIGIN LAST_MODIFIED SQL_TEXT
-------------------- ------------------------------ -------- ------------------------ ----- ------- -------------------- -------------------- ---------------------------------------------------------------------------
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp8f93f050 YES 2023-02-16T08:03:18.121Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:03:18Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx8a124eae YES 2023-02-16T08:03:18.162Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:03:18Z select /* spm_check */ c_name from customer where c_custkey=30000000
4.ベースラインが利用されているかを確認
SQLを実行して、ベースラインが利用されているかを確認します。Noteで使用されたベースラインのプラン名が表示されます。
3で確認したベースラインのプラン名であることがわかります。
select /* spm_check */ c_name from customer where c_custkey=30000000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
SQL> select /* spm_check */ c_name from customer where c_custkey=30000000;
C_NAME
-------------------------
Customer#030000000
/* 先のSQLの実行計画を確認、Noteにベースラインのプラン名が表示されている */
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3m45v8vtbcxca, child number 1
-------------------------------------
select /* spm_check */ c_name from customer where c_custkey=30000000
Plan hash value: 2844954298
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 131K(100)| |
|* 1 | TABLE ACCESS STORAGE FULL| CUSTOMER | 5455 | 133K| 131K (1)| 00:00:06 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("C_CUSTKEY"=30000000)
filter("C_CUSTKEY"=30000000)
Note
-----
- SQL plan baseline SQL_PLAN_1hpnjr3w0scwx8a124eae used for this statement
5.ベースラインを固定化する
新しい計画が追加され、評価の結果改善効果があると判断された場合は新しい計画がベースラインとなります。
既存のベースラインを固定させたい場合には、fixedパラメータをYESに設定します。片方のベースラインを固定化してみます。
/* ベースラインのfixedの変更 */
declare
cnt integer;
begin
cnt:=dbms_spm.alter_sql_plan_baseline(
plan_name=>'SQL_PLAN_1hpnjr3w0scwx8a124eae',
attribute_name=>'fixed',
attribute_value=>'YES');
end;
/
/* 対象のベースラインのFixed列がYESになっているかを確認 →なっている*/
select SQL_HANDLE,PLAN_NAME,ACCEPTED,CREATED,fixed,enabled,origin,last_modified,sql_text from dba_sql_plan_baselines
where CREATED >= sysdate -1 and sql_text like '%spm_check%' order by CREATED;
SQL_HANDLE PLAN_NAME ACCEPTED CREATED FIXED ENABLED ORIGIN LAST_MODIFIED SQL_TEXT
-------------------- ------------------------------ -------- ------------------------ ----- ------- -------------------- -------------------- ---------------------------------------------------------------------------
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp8f93f050 YES 2023-02-16T08:03:18.121Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:03:18Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx8a124eae YES 2023-02-16T08:03:18.162Z YES YES MANUAL-LOAD-FROM-STS 2023-02-16T08:11:00Z select /* spm_check */ c_name from customer where c_custkey=30000000
6.SPM展開アドバイザタスクの確認
SPM展開アドバイザタスクの設定を確認します。Autonomous Databaseでは以下がデフォルトで設定されています。
- 自動SPM
- 高頻度SPM
- 自動承認
/* 自動SPMの確認*/
select parameter_name,parameter_value from DBA_ADVISOR_PARAMETERS
where task_name='SYS_AUTO_SPM_EVOLVE_TASK';
PARAMETER_NAME PARAMETER_VALUE
------------------------ ---------------
APPLY_CAPTURED_COMPILENV UNUSED
LOCAL_TIME_LIMIT UNUSED
SQLSET_NAME UNUSED
SQLSET_OWNER UNUSED
ALTERNATE_PLAN_LIMIT UNLIMITED
ALTERNATE_PLAN_SOURCE SQL_TUNING_SET
ALTERNATE_PLAN_BASELINE AUTO
ACCEPT_PLANS TRUE
DAYS_TO_EXPIRE UNLIMITED
END_SNAPSHOT UNUSED
END_TIME UNUSED
INSTANCE UNUSED
JOURNALING INFORMATION
MODE COMPREHENSIVE
START_SNAPSHOT UNUSED
START_TIME UNUSED
TARGET_OBJECTS 1
TIME_LIMIT 3600
DEFAULT_EXECUTION_TYPE SPM EVOLVE
CON_DBID_MAPPING UNUSED
ORA_EM_PARAM1 UNUSED
ORA_EM_PARAM2 UNUSED
ORA_EM_PARAM3 UNUSED
ORA_EM_PARAM4 UNUSED
ORA_EM_PARAM5 UNUSED
ORA_EM_PARAM6 UNUSED
ORA_EM_PARAM7 UNUSED
ORA_EM_PARAM8 UNUSED
ORA_EM_PARAM9 UNUSED
ORA_EM_PARAM10 UNUSED
EXECUTION_DAYS_TO_EXPIRE 30
REMOTE_SOURCE UNUSED
/* 高頻度SPMの確認*/
select * from dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
-------------------------------- --------------- -------------------- -----------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
AUTO_CAPTURE_SQL_TEXT
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_SPM_EVOLVE_TASK ON 2023-02-15T05:49:28Z ADMIN
AUTO_SPM_EVOLVE_TASK_INTERVAL 3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
/*SPMタスクのスケジュール確認 */
select task_name,status,enabled,interval,last_schedule_time,systimestamp-last_schedule_time ago from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid') and task_name like '%SPM%';
TASK_NAME STATUS ENABLED INTERVAL LAST_SCHEDULE_TIME AGO
------------- --------- ------- -------- ------------------------ ----------------
Auto SPM Task SUCCEEDED TRUE 3600 2023-02-16T08:14:08.808Z +0 0:0:30.672084
7.索引を作成後にSQLを実行する
表に索引を作成します。C_CUSTKEYは一意の値の列なので一意索引にします。
/* 索引作成 */
CREATE UNIQUE INDEX IND1 ON CUSTOMER (C_CUSTKEY);
CREATE INDEX IND2 ON CUSTOMER (C_REGION);
まず、ベースラインを作成していないSQLで索引が使われるか実行してみます。
条件が3000000ではなく、3000001です。
SQL> select /* spm_check */ c_name from customer where c_custkey=3000001;
C_NAME
-------------------------
Customer#003000001
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 69r0bqgy9c306, child number 0
-------------------------------------
select /* spm_check */ c_name from customer where c_custkey=3000001
Plan hash value: 1451921675
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 25 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND1 | 1 | | 2 (0)| 00:00:01 |----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C_CUSTKEY"=3000001)
作成した一意索引が使われています。ではベースラインを作成したSQLで確認します。
SQL> select /* spm_check */ c_name from customer where c_custkey=30000000;
C_NAME
-------------------------
Customer#030000000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3m45v8vtbcxca, child number 2
-------------------------------------
select /* spm_check */ c_name from customer where c_custkey=30000000
Plan hash value: 2844954298
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 131K(100)| |
|* 1 | TABLE ACCESS STORAGE FULL| CUSTOMER | 5455 | 133K| 131K (1)| 00:00:06 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("C_CUSTKEY"=30000000)
filter("C_CUSTKEY"=30000000)
Note
-----
- SQL plan baseline SQL_PLAN_1hpnjr3w0scwx8a124eae used for this statement
索引は使われず、ベースラインが有効であることが確認できます。
8.SPM展開アドバイザタスク その1
高頻度自動SPM展開アドバイザタスクが有効化されているので、1時間に1回タスクが実行されます。間隔は変更不可です。
/* SPMタスクの履歴を確認 */
SELECT TASK_NAME, EXECUTION_NAME, STATUS FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME LIKE '%SPM%' AND (EXECUTION_NAME LIKE 'SYS_SPM%' OR EXECUTION_NAME LIKE 'EXEC_%') AND EXECUTION_START > CURRENT_DATE-1/4
ORDER BY EXECUTION_END;
TASK_NAME EXECUTION_NAME STATUS
------------------------ --------------------------- ---------
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/04:11:23 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK EXEC_23840 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/05:12:03 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/06:12:45 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/07:13:26 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/08:14:08 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/09:14:48 COMPLETED
最新の高頻度SPMのレポートはDBMS_SPM.report_auto_evolve_taskで確認します。
固定化していないSQLのベースラインで索引を使用する実行計画の評価がされていることがわかります。
/* 最新の高頻度SPM展開アドバイザタスクのレポート出力 */
SELECT DBMS_SPM.report_auto_evolve_task FROM dual;
REPORT_AUTO_EVOLVE_TASK --------------------------------------------------------------------------------------------------------------------------------------------------------------
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-02-16/09:14:48
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 02/16/2023 09:14:48
Finished : 02/16/2023 09:15:10
Last Updated : 02/16/2023 09:15:10
Global Time Limit : 3600
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 0
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 1668
Test Plan Name : SQL_PLAN_ahdy76qnss9xp1bdda1c9
Base Plan Name : SQL_PLAN_ahdy76qnss9xp8f93f050
SQL Handle : SQL_a837c735a98c27b5
Parsing Schema : ADMIN
Test Plan Creator : ADMIN
SQL Text : select /* spm_check */ count(*) from customer where
c_region='MIDDLE EAST'
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): 1.133074 5.277925
CPU Time (s): .316064 .630794
Buffer Gets: 482296 21364
Optimizer Cost: 131432 22028
Disk Reads: 482290 5435
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 1 1
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (1):
-----------------------------
1. 計画は22.01800秒で検証されました。検証されたパフォーマンスが、ベースライン計画のパフォーマンスに0.50106倍及ばなかったため、利点基準に達
しませんでした。
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 14145
Plan Hash Value : 2408837200
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 131432 | 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| * 2 | TABLE ACCESS STORAGE FULL | CUSTOMER | 6177273 | 80304549 | 131432 | 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - storage("C_REGION"='MIDDLE EAST')
* 2 - filter("C_REGION"='MIDDLE EAST')
Test Plan
-----------------------------
Plan Id : 14146
Plan Hash Value : 467509705
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 22028 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| * 2 | INDEX RANGE SCAN | IND2 | 6177273 | 80304549 | 22028 | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C_REGION"='MIDDLE EAST')
---------------------------------------------------------------------------------------------
SPM展開アドバイザタスクでは、索引を利用する新しい実行計画よりも現在のベースラインのほうがパフォーマンスが優れているという結果ですね。
ベースラインの情報を確認すると、新しいプランが追加（SQL_PLAN_ahdy76qnss9xp1bdda1c9)されていますが、承認はされていません（Accepted＝NO)。
/* ベースラインの情報を確認*/
select SQL_HANDLE,PLAN_NAME,ACCEPTED,CREATED,fixed,enabled,origin,last_modified,sql_text from dba_sql_plan_baselines
where CREATED >= sysdate -1 and sql_text like '%spm_check%' order by CREATED;
SQL_HANDLE PLAN_NAME ACCEPTED CREATED FIXED ENABLED ORIGIN LAST_MODIFIED SQL_TEXT
-------------------- ------------------------------ -------- ------------------------ ----- ------- -------------------- -------------------- ---------------------------------------------------------------------------
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp8f93f050 YES 2023-02-16T08:03:18.121Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:22:41Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx8a124eae YES 2023-02-16T08:03:18.162Z YES YES MANUAL-LOAD-FROM-STS 2023-02-16T08:11:00Z select /* spm_check */ c_name from customer where c_custkey=30000000
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp1bdda1c9 NO 2023-02-16T08:22:41.189Z NO YES AUTO-CAPTURE 2023-02-16T09:14:48Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
したがって、SQLを実行すると元の索引を使わないベースラインの実行計画がそのまま使われます。
SQL> select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST';
COUNT(*)
----------
6002115
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7krrmjxajq40u, child number 1
-------------------------------------
select /* spm_check */ count(*) from customer where c_region='MIDDLE
EAST'
Plan hash value: 882626266
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 131K(100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS STORAGE FULL| CUSTOMER | 6177K| 76M| 131K (1)| 00:00:06 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("C_REGION"='MIDDLE EAST')
filter("C_REGION"='MIDDLE EAST')
Note
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------- - SQL plan baseline SQL_PLAN_ahdy76qnss9xp8f93f050 used for this statement
9.SPM展開アドバイザタスク その2
固定したほうのベースラインは、固定しているため新しい実行計画は登録されませんでした。固定を外して、SPM展開アドバイザタスクの結果を見てみます
/* ベースラインのfixedの変更 */
declare
cnt integer;
begin
cnt:=dbms_spm.alter_sql_plan_baseline(
plan_name=>'SQL_PLAN_1hpnjr3w0scwx8a124eae',
attribute_name=>'fixed',
attribute_value=>'NO');
end;
/
/* fixed=NOになっていることを確認*/
select SQL_HANDLE,PLAN_NAME,ACCEPTED,CREATED,fixed,enabled,origin,last_modified,sql_text from dba_sql_plan_baselines
where CREATED >= sysdate -1 and sql_text like '%spm_check%' order by CREATED;
SQL_HANDLE PLAN_NAME ACCEPTED CREATED FIXED ENABLED ORIGIN LAST_MODIFIED SQL_TEXT
-------------------- ------------------------------ -------- ------------------------ ----- ------- -------------------- -------------------- ---------------------------------------------------------------------------
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp8f93f050 YES 2023-02-16T08:03:18.121Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:22:41Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx8a124eae YES 2023-02-16T08:03:18.162Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T09:28:19Z select /* spm_check */ c_name from customer where c_custkey=30000000
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp1bdda1c9 NO 2023-02-16T08:22:41.189Z NO YES AUTO-CAPTURE 2023-02-16T09:14:48Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
変更後のSPM展開アドバイザタスクが完了していることを確認後、最新のレポートを確認します。
TASK_NAME EXECUTION_NAME STATUS
------------------------ --------------------------- ---------
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/06:12:45 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/07:13:26 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/08:14:08 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/09:14:48 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/10:15:25 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2023-02-16/11:16:03 COMPLETED
/* 最新の高頻度SPM展開アドバイザタスクのレポート出力 */
SELECT DBMS_SPM.report_auto_evolve_task FROM dual;
REPORT_AUTO_EVOLVE_TASK
--------------------------------------------------------------------------------------------------------------------------------------------------------------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-02-16/11:16:03
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 02/16/2023 11:16:03
Finished : 02/16/2023 11:16:03
Last Updated : 02/16/2023 11:16:03
Global Time Limit : 3600
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 1670
Test Plan Name : SQL_PLAN_1hpnjr3w0scwx7efa90c3
Base Plan Name : SQL_PLAN_1hpnjr3w0scwx8a124eae
SQL Handle : SQL_185691b8f80c339d
Parsing Schema : ADMIN
Test Plan Creator : ADMIN
SQL Text : select /* spm_check */ c_name from customer where
c_custkey=30000000
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .032253 .000009
CPU Time (s): .01983 0
Buffer Gets: 482296 4
Optimizer Cost: 131164 3
Disk Reads: 482290 0
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. 計画は0.36000秒で検証されました。検証されたパフォーマンスが、ベースライン計画のパフォーマンスを120574.49575倍上回ったため、利点基準
に達しました。
2. 計画は自動的に承認されました。
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
object_id => 1670, task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 14147
Plan Hash Value : 2316455598
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5455 | 136375 | 131164 | 00:00:06 |
| * 1 | TABLE ACCESS STORAGE FULL | CUSTOMER | 5455 | 136375 | 131164 | 00:00:06 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - storage("C_CUSTKEY"=30000000)
* 1 - filter("C_CUSTKEY"=30000000)
Test Plan
-----------------------------
Plan Id : 14148
Plan Hash Value : 2130350275
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 25 | 3 | 00:00:01 |
| * 2 | INDEX UNIQUE SCAN | IND1 | 1 | | 2 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C_CUSTKEY"=30000000)
---------------------------------------------------------------------------------------------
固定ではなくなったため、索引を利用した新しい実行計画が検討され、検証の結果パフォーマンスが優れていると評価されました。
自動承認の設定のため、新しい実行計画がベースラインとして承認されます。ベースラインの情報を確認してみると、ACCEPTEDがYESです。
SQL_HANDLE PLAN_NAME ACCEPTED CREATED FIXED ENABLED ORIGIN LAST_MODIFIED SQL_TEXT
-------------------- ------------------------------ -------- ------------------------ ----- ------- -------------------- -------------------- ---------------------------------------------------------------------------
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp8f93f050 YES 2023-02-16T08:03:18.121Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T08:22:41Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx8a124eae YES 2023-02-16T08:03:18.162Z NO YES MANUAL-LOAD-FROM-STS 2023-02-16T09:28:19Z select /* spm_check */ c_name from customer where c_custkey=30000000
SQL_a837c735a98c27b5 SQL_PLAN_ahdy76qnss9xp1bdda1c9 NO 2023-02-16T08:22:41.189Z NO YES AUTO-CAPTURE 2023-02-16T09:14:48Z select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST'
SQL_185691b8f80c339d SQL_PLAN_1hpnjr3w0scwx7efa90c3 YES 2023-02-16T10:19:54.855Z NO YES AUTO-CAPTURE 2023-02-16T11:16:03Z select /* spm_check */ c_name from customer where c_custkey=30000000
SQLを実行してみると、索引を利用する実行計画が利用されています。
SQL> select /* spm_check */ c_name from customer where c_custkey=30000000;
Customer#030000000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL +note'));
SQL_ID 3m45v8vtbcxca, child number 1
-------------------------------------
select /* spm_check */ c_name from customer where c_custkey=30000000
Plan hash value: 1451921675
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 25 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND1 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C_CUSTKEY"=30000000)
Note
-----
- SQL plan baseline SQL_PLAN_1hpnjr3w0scwx7efa90c3 used for this statement
おわりに
ちょっと長くなってしまいましたが、Autonomous DatabaseでのSPMを試してみました。
固定されていれば固定した実行計画で、そうでなければよりよい実行計画がないか評価し、選択されていることが確認できました。