查询Oracle的cpu使用情况,Oracle CPU使用情况曲线

Oracle CPU使用情况曲线

-- alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss:ff';

-- select min(SAMPLE_TIME) from GV$ACTIVE_SESSION_HISTORY;

-- DECODE (STATE,'WAITING', EVENT,'[CPU]:') event from v$session

-- 查看ASH Buffer大小

-- select total_size/1024/1024,awr_flush_emergency_count from v$ash_info;

-- DBA_HIST_ACTIVE_SESS_HISTORY

-- GV$ACTIVE_SESSION_HISTORY

-- GV$SESSION

-- CPU使用情况曲线

-- 动态视图 GV$ACTIVE_SESSION_HISTORY

-- 查询RAC，PDB=3的数据库的使用情况

set line 190 pages 0

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss:ff';

col SAMPLE_TIME for a16

set numw 6

select

to_char(SAMPLE_TIME,'yyyy-mm-dd hh24:mi') SAMPLE_TIME

,max(case when "Node 1">0 then "Node 1" else null end) node1_max

,max(case when "Node 2">0 then "Node 2" else null end) node2_max

,case when coalesce(count(case when "Node 1">0 then 1 else null end),0)=0 then 0

else round(sum("Node 1")/count(case when "Node 1">0 then 1 else null end),0) end node1_avg

,case when coalesce(count(case when "Node 2">0 then 1 else null end),0)=0 then 0

else round(sum("Node 2")/count(case when "Node 2">0 then 1 else null end),0) end node2_avg

,sum("Node 1") node1_total

,count(case when "Node 1">0 then 1 else null end) node1_num

,min(case when "Node 1">0 then "Node 1" else null end) node1_min

,sum("Node 2") node2_total

,count(case when "Node 2">0 then 1 else null end) node2_num

,min(case when "Node 2">0 then "Node 2" else null end) node2_min

from (

select SAMPLE_TIME,session_state,

count(case when inst_id=1 then 1 else null end) "Node 1",

count(case when inst_id=2 then 1 else null end) "Node 2"

from GV$ACTIVE_SESSION_HISTORY

where session_state='ON CPU'

and con_id=3

group by SAMPLE_TIME,session_state

) t group by to_char(SAMPLE_TIME,'yyyy-mm-dd hh24:mi')

order by 1

;

-- ASH历史信息 DBA_HIST_ACTIVE_SESS_HISTORY

col SAMPLE_TIME for a16

set numw 6

select

to_char(SAMPLE_TIME,'yyyy-mm-dd hh24:mi') SAMPLE_TIME

,sum("Node 1") node1_total

,count(case when "Node 1">0 then 1 else null end) node1_num

,min(case when "Node 1">0 then "Node 1" else null end) node1_min

,max(case when "Node 1">0 then "Node 1" else null end) node1_max

,round(sum("Node 1")/count(case when "Node 1">0 then 1 else null end),0) node1_avg

,sum("Node 2") node2_total

,count(case when "Node 2">0 then 1 else null end) node2_num

,min(case when "Node 2">0 then "Node 2" else null end) node2_min

,max(case when "Node 2">0 then "Node 2" else null end) node2_max

,round(sum("Node 2")/count(case when "Node 2">0 then 1 else null end),0) node2_avg

from (

select

SAMPLE_TIME,SESSION_STATE,

count(case when INSTANCE_NUMBER=1 then 1 else null end) "Node 1",

count(case when INSTANCE_NUMBER=2 then 1 else null end) "Node 2"

from containers(DBA_HIST_ACTIVE_SESS_HISTORY)

where SAMPLE_TIME between to_date('2020-06-30 08:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-06-30 18:00:00','yyyy-mm-dd hh24:mi:ss')

and con_id=3 and session_state='ON CPU'

group by SAMPLE_TIME,SESSION_STATE

) t group by to_char(SAMPLE_TIME,'yyyy-mm-dd hh24:mi')

order by 1

;