在使用ASH不能满足，需要从AWR，即HIST系列表估算每个用户的cpu消耗，只能进行大概估算
- 先计算各用户使用的cpu time
- 计算出各用户占比
- 将用户cpu time 与osstat的cpu 使用率相乘
with cpu_usage as (select snap_id,BUSY_TIME/(IDLE_TIME+BUSY_TIME+IOWAIT_TIME+RSRC_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_ID=46109 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_ID=b.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_ID=b.snap_id and a.snap_id=c.snap_id
