首页 > 代码库 > 根据dba_hist_osstat统计CPU占用情况
根据dba_hist_osstat统计CPU占用情况
在11g里面，视图dba_hist_osstat用来记录OS级别的time时间指标。视图dba_hist_osstat_name显示了相关的指标名称。
SYS@134.32.114.1:1521/dzgddb> select * from DBA_HIST_OSSTAT_NAME; DBID STAT_ID STAT_NAME --------------- --------------- ---------------------------------------------------------------- 3352298469 0 NUM_CPUS 3352298469 1 IDLE_TIME 3352298469 2 BUSY_TIME 3352298469 3 USER_TIME 3352298469 4 SYS_TIME 3352298469 5 IOWAIT_TIME 3352298469 6 NICE_TIME 3352298469 14 RSRC_MGR_CPU_WAIT_TIME 3352298469 15 LOAD 3352298469 16 NUM_CPU_CORES 3352298469 17 NUM_CPU_SOCKETS 3352298469 1008 PHYSICAL_MEMORY_BYTES 3352298469 1009 VM_IN_BYTES 3352298469 1010 VM_OUT_BYTES 3352298469 2000 TCP_SEND_SIZE_MIN 3352298469 2001 TCP_SEND_SIZE_DEFAULT 3352298469 2002 TCP_SEND_SIZE_MAX 3352298469 2003 TCP_RECEIVE_SIZE_MIN 3352298469 2004 TCP_RECEIVE_SIZE_DEFAULT 3352298469 2005 TCP_RECEIVE_SIZE_MAX 3352298469 2006 GLOBAL_SEND_SIZE_MAX 3352298469 2007 GLOBAL_RECEIVE_SIZE_MAX 22 rows selected.
如上，nmu_cpu_cores是指cpu核心数，本例是32；num_cpus是指cpu核心线程数，本例是64；num_cpu_sockets是指cpu路数，也指cpu主板数，本例是4。
关键的计算公式是：
%User = USER_TIME/ (BUSY_TIME+IDLE_TIME)*100 %Sys = SYS_TIME/ (BUSY_TIME+IDLE_TIME)*100 %Idle = IDLE_TIME/ (BUSY_TIME+IDLE_TIME)*100
BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT
鉴于报表习惯，将关键指标%Idle换算为%CPU，则：
%CPU = BUSY_TIME/ (BUSY_TIME+IDLE_TIME)*100
顺带记录一下，LOAD指标记录的是snap_id起始点的OS Load值，对应AWR报告的Load Average End & Load Average Begin。
以下是最终的SQL：根据dba_hist_osstat统计CPU占用情况，顺带附上另外几个关键指标
SELECT sn.instance_number, sn.snap_id, to_char(sn.end_interval_time, ‘YYYY-MM-DD HH24:MI‘) AS snaptime, newread.value - oldread.value "physical reads", newwrite.value - oldwrite.value "physical writes", round((newdbtime.value - olddbtime.value) / 1000000 / 60, 2) "DB time(min)", round((newbusy.value - oldbusy.value) / ((newidle.value - oldidle.value) + (newbusy.value - oldbusy.value)) * 100, 2) "CPU(%)" FROM dba_hist_sysstat oldread, dba_hist_sysstat newread, dba_hist_sysstat oldwrite, dba_hist_sysstat newwrite, dba_hist_sys_time_model olddbtime, dba_hist_sys_time_model newdbtime, dba_hist_osstat oldidle, dba_hist_osstat newidle, dba_hist_osstat oldbusy, dba_hist_osstat newbusy, dba_hist_snapshot sn WHERE newread.stat_name = ‘physical reads‘ AND oldread.stat_name = ‘physical reads‘ AND newread.snap_id = sn.snap_id AND oldread.snap_id = sn.snap_id - 1 AND newread.instance_number = sn.instance_number AND oldread.instance_number = sn.instance_number AND newread.dbid = sn.dbid AND oldread.dbid = sn.dbid AND newwrite.stat_name = ‘physical writes‘ AND oldwrite.stat_name = ‘physical writes‘ AND newwrite.snap_id = sn.snap_id AND oldwrite.snap_id = sn.snap_id - 1 AND newwrite.instance_number = sn.instance_number AND oldwrite.instance_number = sn.instance_number AND newwrite.dbid = sn.dbid AND oldwrite.dbid = sn.dbid AND newdbtime.stat_name = ‘DB time‘ AND olddbtime.stat_name = ‘DB time‘ AND newdbtime.snap_id = sn.snap_id AND olddbtime.snap_id = sn.snap_id - 1 AND newdbtime.instance_number = sn.instance_number AND olddbtime.instance_number = sn.instance_number AND newdbtime.dbid = sn.dbid AND olddbtime.dbid = sn.dbid AND newidle.stat_name = ‘IDLE_TIME‘ AND oldidle.stat_name = ‘IDLE_TIME‘ AND newidle.snap_id = sn.snap_id AND oldidle.snap_id = sn.snap_id - 1 AND newidle.instance_number = sn.instance_number AND oldidle.instance_number = sn.instance_number AND newidle.dbid = sn.dbid AND oldidle.dbid = sn.dbid AND newbusy.stat_name = ‘BUSY_TIME‘ AND oldbusy.stat_name = ‘BUSY_TIME‘ AND newbusy.snap_id = sn.snap_id AND oldbusy.snap_id = sn.snap_id - 1 AND newbusy.instance_number = sn.instance_number AND oldbusy.instance_number = sn.instance_number AND newbusy.dbid = sn.dbid AND oldbusy.dbid = sn.dbid ORDER BY sn.instance_number, sn.snap_id;
根据dba_hist_osstat统计CPU占用情况
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。