hi, there is a script to find out cpu load from awr by one instance ( in example number two ) :
select start_time,round (100*"'LOAD'"/"'NUM_CPU_CORES'") AS LOAD from
(
select os.INSTANCE_NUMBER,stat_name,sum(os.value) as load,
min(to_date(to_char(s.begin_interval_time,'DD.MM.YYYY hh24.mi.ss'))) as START_TIME,max(to_date(to_char(s.end_interval_time,'DD.MM.YYYY hh24.mi.ss'))) end_time
from DBA_HIST_OSSTAT os join
DBA_HIST_SNAPSHOT s on s.snap_id= os.SNAP_ID
where os.stat_name in ('LOAD','NUM_CPU_CORES','INSTANCE_NUMBER')
group by os.stat_name, (trunc(to_date(to_char(s.begin_interval_time,'DD.MM.YYYY hh24.mi.ss')),'HH24')),os.INSTANCE_NUMBER
)
pivot( max(LOAD) for stat_name in ('LOAD','NUM_CPU_CORES') )
where instance_number=2
order by instance_number,start_time asc ;
ps: usefull to make graphs linke this:
1 комментарий
select 100*(round ( count (*)/sum(count(*)) over(partition by dbid ),2 )) as pct ,nvl(wait_class,’CPU’) wait_class,dbid from dba_hist_active_sess_history group by wait_class,dbid order by dbid,1 desc;
select 100*(round ( count (*)/sum(count(*)) over( partition by dbid ),2 )) as pct ,nvl(event,’CPU’) event,dbid from dba_hist_active_sess_history where wait_class like ‘%I/O%’ group by event,dbid order by dbid, 1 desc;