Autonomous DatabaseのSQL実行計画管理（SPM)を試してみた

Autonomous DatabaseではSPM(SQl Performance Management)が有効化されています。
SPMについては詳細は[津島博士のパフォーマンス講座第75回](https://blogs.oracle.com/otnjp/post/tsushima-hakushi-75)をご確認ください。
動作について確認してみました。

実行はADMINユーザで行います

# 1. 使用する表とSQLについて

サンプル・スキーマSSBのCUSTOMER表をADMINでCTASで作成した表を使うことにします。
```sql
create table customer as select * from ssb.customer;
```
SQLはこの2つです。後で検索しやすいようにコメントをいれています。

1. select /* spm_check */ c_name from customer where c_custkey=30000000;
2. select /* spm_check */ count(*) from customer where c_region='MIDDLE EAST' 

現在の実行計画を確認すると、索引を何も作成していないので全表スキャンです。
1の結果のみですが、2のSQLも同様です。

```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を複数回実行します。

```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では後者のパラメータはセッションレベルでのみ設定変更が可能です。

```sql
/* ベースラインへロードする */
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で確認したベースラインのプラン名であることがわかります。

```sql
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に設定します。片方のベースラインを固定化してみます。

```sql
/* ベースラインの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
+ 自動承認

```sql
/* 自動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は一意の値の列なので一意索引にします。
```sql
/* 索引作成 */
CREATE UNIQUE INDEX IND1 ON CUSTOMER (C_CUSTKEY);
CREATE INDEX IND2 ON CUSTOMER (C_REGION);
```
まず、ベースラインを作成していないSQLで索引が使われるか実行してみます。
条件が3000000ではなく、3000001です。

```sql
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
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回タスクが実行されます。間隔は変更不可です。

```sql
/* 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のベースラインで索引を使用する実行計画の評価がされていることがわかります。

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

```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: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
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展開アドバイザタスクの結果を見てみます
```sql
/* ベースラインの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展開アドバイザタスクが完了していることを確認後、最新のレポートを確認します。

```sql
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

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
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を試してみました。
固定されていれば固定した実行計画で、そうでなければよりよい実行計画がないか評価し、選択されていることが確認できました。