BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    interval  => 10);          -- Minutes. Current value retained if NULL.
END;

alter session set optimizer_adaptive_features =false;

alter session set optimizer_adaptive_plans   =true

drop table CHB_t1 purge

drop table CHB_t2 purge

 CREATE TABLE CHB_t1 (id PRIMARY KEY, n, pad) 
 AS 
 SELECT rownum, CASE WHEN rownum < = 10000 THEN rownum ELSE 666 END, lpad('*',100,'*') 
 FROM dual 
 CONNECT BY level <= 10150

  execute dbms_stats.gather_table_stats(user,'CHB_t1') 

  CREATE TABLE CHB_t2 (id PRIMARY KEY, n, pad) 
  AS 
  SELECT rownum, rownum, lpad('*',100,'*') 
  FROM dual 
  CONNECT BY level < = 10000

  CREATE TABLE t3 (id PRIMARY KEY, n, pad) 
  AS 
  SELECT rownum, rownum, lpad('*',100,'*') 
  FROM dual 
  CONNECT BY level < = 10000

  execute dbms_stats.gather_table_stats(user,'CHB_t2')

  CREATE OR REPLACE FUNCTION burn_cpu(p_wait IN INTEGER) RETURN INTEGER IS 
    l_begin NUMBER; 
  BEGIN 
    l_begin := dbms_utility.get_time(); 
    LOOP 
      EXIT WHEN l_begin+(p_wait*1200) < dbms_utility.get_time(); 
    END LOOP; 
    RETURN 1; 
  END burn_cpu; 

  / 

show parameter optimizer_adaptive  

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

set timing on

SELECT /*+optimizer_adaptive_plans_True */ count(CHB_t1.pad), count(CHB_t2.pad) 
 FROM CHB_t1, CHB_t2 
 WHERE CHB_t1.id = CHB_t2.id 
 AND CHB_t1.n = 666 
 AND burn_cpu(CHB_t1.id/CHB_t1.id) = 1

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

select a.snap_id,
       min(sample_time) mint,
       MAX(sample_time) Maxt,
       a.sql_id,
       a.SQL_ADAPTIVE_PLAN_RESOLVED,
       a.SQL_FULL_PLAN_HASH_VALUE,
       a.SQL_PLAN_HASH_VALUE
  from dba_hist_active_sess_history a
 where sql_id IN (select a.sql_id
                    FROM v$sql a
                   where sql_text like '%optimizer_adaptive_plans_True%'
                     and sql_text not like '%sql_text%')
 group by a.SNAP_ID,
          a.sql_id,
          a.SQL_ADAPTIVE_PLAN_RESOLVED,
          a.SQL_FULL_PLAN_HASH_VALUE,
          a.SQL_PLAN_HASH_VALUE
Order by  1  ,5        
          ;
          
select 
round(sum(a.ELAPSED_TIME_DELTA/1e6 )) sum_delta,
round(max(a.ELAPSED_TIME_total/1e6))  tot,
count(*) count_snaps
 from dba_hist_sqlstat a where sql_id in(
(select a.sql_id
                    FROM v$sql a
                   where sql_text like '%optimizer_adaptive_plans_True%'
                     and sql_text not like '%sql_text%')
)
group by sql_id           
;