Add new comment

Hi David,

I use sql developer report when plan has not changed.

Chart Type Bar - Vertical Stack Dual Y in sql developer using following sql:

SELECT *
FROM
(select to_char(trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24,'dd.mm.yyyy hh24:mi:ss') time,
sum(hss.executions_delta) executions,
round(sum(hss.cpu_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cpu_time,
round(sum(hss.iowait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) io_time,
round(sum(hss.clwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cl_time,
round(sum(hss.apwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) ap_time,
round(sum(hss.ccwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cc_time
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where hss.sql_id=:SQL_ID
and hss.snap_id=hs.snap_id
and hss.instance_number=hs.instance_number
and hs.begin_interval_time>=trunc(sysdate)-:DAYS_HISTORY+1
group by hss.instance_number, trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
order by trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
)
UNPIVOT (Time_S FOR STATE_TIME IN (cpu_time AS 'CPU_TIME', executions AS 'EXECS',io_time AS 'IO_TIME', cl_time AS 'CL_TIME', ap_time AS 'AP_TIME', cc_time as 'CC_TIME'))

Thanks,
Vishal