set lines 256

column client_name format a35
column task_name format a30

column last_try_date format a20
column last_good_date format a20
column next_try_date format a20

alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss';

select 
	client_name, task_name, status, 
	to_char(last_try_date,'yyyy-mm-dd hh24:mi:ss') as last_try_date,
	to_char(last_good_date,'yyyy-mm-dd hh24:mi:ss') as last_good_date,
	to_char(next_try_date,'yyyy-mm-dd hh24:mi:ss') as next_try_date
from dba_autotask_task;



SQL> show parameter optimizer%baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE




set lines 200
set pages 1000
col parameter_name for a35
col parameter_value for a30
col last_modified for a30
col modified_by for a30

select * from  dba_sql_management_config where parameter_name like 'AUTO_SPM_EVOLVE_TASK%';



exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK','ON');
exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK','OFF');

The list of tunable parameters with DBMS_SPM.CONFIGURE:

col description FOR a40 word_wrapped
SET pages 1000

select parameter_name, parameter_value, description
     from   dba_advisor_parameters
     where  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
     and    parameter_value != 'UNUSED';
 


set lines 256

col DBID noprint
col TASK_ID noprint
col TASK_NAME noprint

select *
     from   dba_autotask_schedule_control
     where  dbid = sys_context('userenv','con_dbid')
     and    task_name = 'Auto SPM Task';

-- last task details
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 256 LINESIZE 256

SELECT DBMS_SPM.report_auto_evolve_task
FROM   dual;


CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users;

DECLARE
i NUMBER;
nbrows NUMBER;
BEGIN
  i:=1;
  nbrows:=50000;
  LOOP
    EXIT WHEN i>nbrows;
    IF (i=1) THEN
      INSERT INTO test1 VALUES(1,RPAD('A',49,'A'));
    ELSE
      INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
    END IF;
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/

CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users;

 
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2');


ALTER SYSTEM flush shared_pool;

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM test1 WHERE id=1;

SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
     FROM v$sql
     WHERE sql_id='4q7zcj8kp9q2r';


EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');


SELECT
       plan_hash_value,
       cpu_time,
       buffer_gets,
       disk_reads,
       direct_writes,
       rows_processed,
       fetches,
       executions,
       optimizer_cost,
       TO_CHAR(plan_timestamp,'dd-mon-yyyy hh24:mi:ss') AS plan_timestamp
     FROM dba_sqlset_statements
     WHERE sqlset_name='SYS_AUTO_STS'
     AND sql_id='4q7zcj8kp9q2r'
     ORDER BY plan_timestamp DESC;


select * from SYS.WRI$_ADV_EXECUTIONS where exec_type='SPM EVOLVE' order by exec_start desc;

select * from SYS.WRI$_ADV_EXECUTIONS where exec_type='SPM EVOLVE' 
where exec_start between date'2025-05-25 09:00:00' and date'2025-05-25 19:00:00'
order by exec_start desc;
     


select 
   sql_id
  ,plan_hash_value
  ,LAST_MODIFIED
from(
select 
  dbms_sql_translator.sql_id(sql_text) sql_id,
  (select to_number(regexp_replace(plan_table_output,'^[^0-9]*')) 
  from table(dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name)) 
  where plan_table_output like 'Plan hash value: %') plan_hash_value,
  bl.*
from dba_sql_plan_baselines bl 
)
;