/*
declare
v_num number;
begin
v_num := dbms_workload_repository.create_snapshot();
dbms_output.put_line(v_num);
end;
*/
--call dbms_workload_repository.add_colored_sql('fx9z8jxxvu5a7')
select trunc(sn.begin_interval_time,'mi') begin_interval_time,
       st.plan_hash_value phv,
       st.OPTIMIZER_MODE opt_m,
       coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE) profile,
       sum(st.executions_delta) as execs,
       round(avg(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as ela_per_exec_sec,
       round(avg(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta))/1e6,4)as CPU_per_exec_sec,
       max(round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))) as px_per_exec,
       round(avg((st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as rows_per_exec,
       round(avg((st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta)))) as fetches_per_exec
from   dba_hist_sqlstat st, dba_hist_snapshot sn, (select distinct sq.sql_id,sq.PLAN_HASH_VALUE,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE from gv$sql sq) sq
where  st.sql_id like '66m74s6yq786u%'
and    st.snap_id = sn.snap_id
and    sq.SQL_ID(+) = st.SQL_ID and sq.PLAN_HASH_VALUE(+) = st.PLAN_HASH_VALUE
and    st.instance_number = sn.instance_number
and    st.executions_delta > 0
and    sn.begin_interval_time between sysdate - 10 and sysdate
group by trunc(sn.begin_interval_time,'mi') , st.plan_hash_value, coalesce(st.sql_profile,sq.SQL_PLAN_BASELINE,sq.SQL_PATCH,sq.SQL_PROFILE),st.OPTIMIZER_MODE
order by 1 desc;

call dbms_workload_repository.add_colored_sql('&v_sql_id');