Databases Reference
In-Depth Information
between (sysdate-&&days_ago)
and ((sysdate-&&days_ago)+(&&duration_min/1440));
SQL> WITH xtimes (xdate) AS
(SELECT to_date('&_bt') xdate FROM dual
UNION ALL
SELECT xdate+(&&interval_mins/1440) FROM xtimes
WHERE xdate+(&&interval_mins/1440) <= to_date('&_et')
)
select to_char(xdate,'DD-MON-RR HH24:MI:SS') samplestart,
(sum(decode(wait_class,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS'))) smpcnt,
(sum(decode(wait_class,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS')))/60/&&interval_mins aas,
nvl(wait_class,'*** IDLE *** ') tc,
decode(wait_class,null,0,dbtw) dbtw,
decode(wait_class,null,0,dbtw)/60/&&interval_mins aas_comp
from (
select s1.xdate,wait_class,10*count(*) dbtw,count(ash.smpcnt) smpcnt
from ( select sample_id, sample_time, session_state,
decode(session_state,'ON CPU','CPU + CPU Wait',wait_class) wait_class,
10*(count(sample_id) over (partition by sample_id)) smpcnt
from dba_hist_active_sess_history
where to_date(to_char(sample_time,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')
between to_date('&_bt') and to_date('&_et')) ash,
(select xdate from xtimes ) s1
where ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
group by s1.xdate,wait_class)
order by 1,dbtw desc
/
BeginTime DBTimeTotal AAS Component DBTPerMetric AASPerMetric
------------------ ----------- ------ -------------- ------------- ------------
08-NOv-12 01:11:46 20.00 .07 CPU + CPU Wait 20.00 .07
08-NOv-12 01:16:46 10.00 .03 System I/O 10.00 .03
08-NOv-12 01:21:46 .00 .00 *** IDLE *** .00 .00
08-NOv-12 01:26:46 20.00 .07 CPU + CPU Wait 20.00 .07
08-NOv-12 01:31:46 70.00 .23 CPU + CPU Wait 70.00 .23
08-NOv-12 01:36:46 10.00 .03 CPU + CPU Wait 10.00 .03
08-NOv-12 01:41:46 14100.00 47.00 User I/O 11790.00 39.30
CPU + CPU Wait 2210.00 7.37
System I/O 50.00 .17
Scheduler 20.00 .07
Concurrency 20.00 .07
Other 10.00 .03
08-NOv-12 01:46:46 26870.00 89.57 User I/O 24150.00 80.50
... Output omitted for brevity
Search WWH ::
Custom Search