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,
        case db_stat_name
            when 'parse time elapsed' then
                'soft parse time'
            else db_stat_name
            end db_stat_name
from
(select b.snap_id snapid,stat_name db_stat_name,
        round(avg((b.value) / 1000000),3) time_secs
    from stats$time_model_statname a,stats$sys_time_model b,stats$snapshot c
    where stat_name not in('DB time','background elapsed time','background cpu time','DB CPU') and a.stat_id=b.stat_id and b.snap_id=c.snap_id and to_char(c.snap_time,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) db_time
    from stats$time_model_statname a,stats$sys_time_model b,stats$snapshot c
    where stat_name = 'DB time' and a.stat_id=b.stat_id and b.snap_id=c.snap_id and to_char(c.snap_time,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) plsql_time
    from stats$time_model_statname a,stats$sys_time_model b,stats$snapshot c
    where stat_name = 'PL/SQL execution elapsed time' and a.stat_id=b.stat_id and b.snap_id=c.snap_id and to_char(c.snap_time,'DD')=&NoOfDays group by stat_name,b.snap_id),
(select b.snap_id,round(avg((b.value) / 1000000),3) hard_parse_time
    from stats$time_model_statname a,stats$sys_time_model b,stats$snapshot c
    where stat_name = 'hard parse elapsed time' and a.stat_id=b.stat_id and b.snap_id=c.snap_id and to_char(snap_time,'DD')=&NoOfDays group by stat_name,b.snap_id)
order by 2 desc;