SYS_AUTO_SPM_EVOLVE_TASK

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;

col window_name for a20

select window_name,autotask_status from dba_autotask_window_clients;

exec dbms_auto_task_admin.enable(client_name=>'auto space advisor',operation=> NULL,window_name=>NULL);

col last_change for a40

select client_name,status,last_change from dba_autotask_client;

sql tuning advisor       ENABLED

auto optimizer stats collection      ENABLED

auto space advisor       ENABLED

select autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor

from dba_autotask_window_clients where window_name='MONDAY_WINDOW';

--https://zhuanlan.zhihu.com/p/658891291

select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS;

###AUTO_STATS_ADVISOR_TASK

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-advisor.html#GUID-D81A7708-FDA0-45BB-A6E2-103858B047AE

-- The task runs as part of the automatic optimizer statistics collection client.  

COL EXECUTION_NAME FORMAT a14

SELECT EXECUTION_NAME, EXECUTION_END, STATUS

FROM   DBA_ADVISOR_EXECUTIONS

WHERE  TASK_NAME = 'AUTO_STATS_ADVISOR_TASK'

ORDER BY 2;

<<<<< auto optimizer stats collection （子任务）

set line 300 pages 999

col client_name for a32

col window_name for a18

col window_start_time for a40

col window_duration for a28

col window_end_time for a40

select * from dba_autotask_client_history where window_start_time > sysdate -7

order by window_start_time ;

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-advisor.html#GUID-21F2295B-5DDE-42B6-9F5A-996C19EDF45B

SYS_AUTO_SPM_EVOLVE_TASK

//

No separate scheduler client exists for the Automatic SPM Evolve Advisor task.

One client controls both Automatic SQL Tuning Advisor and Automatic SPM Evolve Advisor. Thus, the same task enables or disables both.  

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-sql-plan-baselines.html#GUID-0D221783-C6C3-470E-8022-60ADF87E2473

https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/managing-sql-plan-baselines.html#GUID-A94CFA49-910A-4237-A7BB-39BFA94E227E

Enabling and Disabling the Automatic SPM Evolve Advisor Task

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-sql-plan-baselines.html#GUID-0D221783-C6C3-470E-8022-60ADF87E2473

SELECT TASK_NAME, EXECUTION_NAME, STATUS  

FROM   DBA_ADVISOR_EXECUTIONS  

WHERE  TASK_NAME LIKE '%SPM%';   <<<<sql tuning advisor  

SELECT TASK_NAME,count(*)  

FROM   DBA_ADVISOR_EXECUTIONS WHERE TASK_NAME NOT LIKE 'ADDM%' group by TASK_NAME;

select task_name, status from dba_advisor_tasks WHERE TASK_NAME NOT LIKE 'ADDM%';