天津市网站制作公司,利津网站定制,京东网上商城创立时间,外贸平台网站在使用ASH不能满足#xff0c;需要从AWR#xff0c;即HIST系列表估算每个用户的cpu消耗#xff0c;只能进行大概估算
先计算各用户使用的cpu time计算出各用户占比将用户cpu time 与osstat的cpu 使用率相乘
with cpu_usage as (select snap_id,BUSY_TIME/(IDLE_TIMEBUSY…在使用ASH不能满足需要从AWR即HIST系列表估算每个用户的cpu消耗只能进行大概估算
先计算各用户使用的cpu time计算出各用户占比将用户cpu time 与osstat的cpu 使用率相乘
with cpu_usage as (select snap_id,BUSY_TIME/(IDLE_TIMEBUSY_TIMEIOWAIT_TIMERSRC_MGR_CPU_WAIT_TIME) precent from(
select oss.snap_id,
SUM(decode(oss.stat_name,IDLE_TIME, value)) IDLE_TIME,
SUM(decode(oss.stat_name,BUSY_TIME, value)) BUSY_TIME,
sum(decode(oss.stat_name,IOWAIT_TIME, value)) IOWAIT_TIME,
SUM(decode(oss.stat_name,RSRC_MGR_CPU_WAIT_TIME, value)) RSRC_MGR_CPU_WAIT_TIME
from dba_hist_osstat oss
where
-- oss.SNAP_ID46109 and
oss.stat_name in (
IDLE_TIME
,BUSY_TIME
,IOWAIT_TIME
,RSRC_MGR_CPU_WAIT_TIME
)
group by oss.snap_id ) ) ,
snap_user_cpu as (
select username, snap_id, sum(TM_DELTA_CPU_TIME) CPU_TIME from (
select b.USERNAME,a.SNAP_ID,a.DBID,a.INSTANCE_NUMBER,a.TM_DELTA_TIME,a.TM_DELTA_CPU_TIME,a.TM_DELTA_DB_TIME
from Dba_Hist_Active_Sess_History a ,DBA_USERS b
where a.USER_IDb.USER_ID
and a.SNAP_ID? )
group by username,snap_id),
snap_total_cpu as
(select snap_id ,sum(CPU_TIME) total_cpu
from snap_user_cpu
group by snap_id)
select username,a.snap_id,CPU_TIME ,CPU_TIME/total_cpu, CPU_TIME/total_cpu*100*c.precent
from snap_user_cpu a ,snap_total_cpu b,cpu_usage c
where a.SNAP_IDb.snap_id and a.snap_idc.snap_id
