select  snapid,case db_stat_name
            when 'parse time elapsed' then
                'soft parse time'
            else db_stat_name
            end db_stat_name,
        case db_stat_name
            when 'sql execute elapsed time' then 
                time_secs - plsql_time
            when 'parse time elapsed'  then
                time_secs - hard_parse_time
            else time_secs
            end time_secs,
        case db_stat_name
            when 'sql execute elapsed time' then 
                round(100 * (time_secs - plsql_time) / db_time,2)
            when 'parse time elapsed'  then
                round(100 * (time_secs - hard_parse_time) / db_time,2)
            else round(100 * time_secs / db_time,2)
            end pct_time
from
(select b.snap_id snapid,stat_name db_stat_name, round(avg((b.value) / 1000000),3) time_secs from dba_hist_sys_time_model b,dba_hist_snapshot c where stat_name not in('DB time','background elapsed time','background cpu time','DB CPU') and  b.snap_id=c.snap_id and to_char(c.END_INTERVAL_TIME,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) db_time from dba_hist_sys_time_model b,dba_hist_snapshot c where stat_name = 'DB time' and b.snap_id=c.snap_id and to_char(c.END_INTERVAL_TIME,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) plsql_time from dba_hist_sys_time_model b,dba_hist_snapshot c where stat_name = 'PL/SQL execution elapsed time' and b.snap_id=c.snap_id and to_char(c.END_INTERVAL_TIME,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) hard_parse_time from dba_hist_sys_time_model b,dba_hist_snapshot c where stat_name = 'hard parse elapsed time' and b.snap_id=c.snap_id and to_char(END_INTERVAL_TIME,'DD')=&NoOfDays group by stat_name,b.snap_id)
order by 2 desc;