根据dba_hist_osstat统计CPU占用情况

.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)*<span style="color: #800080">100</span>
%Sys = SYS_TIME/ (BUSY_TIME+IDLE_TIME)*<span style="color: #800080">100</span>
%Idle = IDLE_TIME/ (BUSY_TIME+IDLE_TIME)*<span style="color: #800080">100<br>BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT<br></span>

鉴于报表习惯，将关键指标%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占用情况，顺带附上另外几个关键指标

<span style="color: #000000">SELECT sn.instance_number,
       sn.snap_id,
       to_char(sn.end_interval_time, </span><span style="color: #800000">‘</span><span style="color: #800000">YYYY-MM-DD HH24:MI</span><span style="color: #800000">‘</span><span style="color: #000000">) AS snaptime,
       newread.value </span>- oldread.value <span style="color: #800000">"</span><span style="color: #800000">physical reads</span><span style="color: #800000">"</span><span style="color: #000000">,
       newwrite.value </span>- oldwrite.value <span style="color: #800000">"</span><span style="color: #800000">physical writes</span><span style="color: #800000">"</span><span style="color: #000000">,
       round((newdbtime.value </span>- olddbtime.value) / <span style="color: #800080">1000000</span> / <span style="color: #800080">60</span>, <span style="color: #800080">2</span>) <span style="color: #800000">"</span><span style="color: #800000">DB time(min)</span><span style="color: #800000">"</span><span style="color: #000000">,
       round((newbusy.value </span>- oldbusy.value) /<span style="color: #000000">
             ((newidle.value </span>- oldidle.value) +<span style="color: #000000">
             (newbusy.value </span>- oldbusy.value)) * <span style="color: #800080">100</span><span style="color: #000000">,
             </span><span style="color: #800080">2</span>) <span style="color: #800000">"</span><span style="color: #800000">CPU(%)</span><span style="color: #800000">"</span><span style="color: #000000">
  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 </span>= <span style="color: #800000">‘</span><span style="color: #800000">physical reads</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND oldread.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">physical reads</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND newread.snap_id </span>=<span style="color: #000000"> sn.snap_id
   AND oldread.snap_id </span>= sn.snap_id - <span style="color: #800080">1</span><span style="color: #000000">
   AND newread.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND oldread.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND newread.dbid </span>=<span style="color: #000000"> sn.dbid
   AND oldread.dbid </span>=<span style="color: #000000"> sn.dbid
   AND newwrite.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">physical writes</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND oldwrite.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">physical writes</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND newwrite.snap_id </span>=<span style="color: #000000"> sn.snap_id
   AND oldwrite.snap_id </span>= sn.snap_id - <span style="color: #800080">1</span><span style="color: #000000">
   AND newwrite.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND oldwrite.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND newwrite.dbid </span>=<span style="color: #000000"> sn.dbid
   AND oldwrite.dbid </span>=<span style="color: #000000"> sn.dbid
   AND newdbtime.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">DB time</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND olddbtime.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">DB time</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND newdbtime.snap_id </span>=<span style="color: #000000"> sn.snap_id
   AND olddbtime.snap_id </span>= sn.snap_id - <span style="color: #800080">1</span><span style="color: #000000">
   AND newdbtime.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND olddbtime.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND newdbtime.dbid </span>=<span style="color: #000000"> sn.dbid
   AND olddbtime.dbid </span>=<span style="color: #000000"> sn.dbid
   AND newidle.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">IDLE_TIME</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND oldidle.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">IDLE_TIME</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND newidle.snap_id </span>=<span style="color: #000000"> sn.snap_id
   AND oldidle.snap_id </span>= sn.snap_id - <span style="color: #800080">1</span><span style="color: #000000">
   AND newidle.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND oldidle.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND newidle.dbid </span>=<span style="color: #000000"> sn.dbid
   AND oldidle.dbid </span>=<span style="color: #000000"> sn.dbid
   AND newbusy.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">BUSY_TIME</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND oldbusy.stat_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">BUSY_TIME</span><span style="color: #800000">‘</span><span style="color: #000000">
   AND newbusy.snap_id </span>=<span style="color: #000000"> sn.snap_id
   AND oldbusy.snap_id </span>= sn.snap_id - <span style="color: #800080">1</span><span style="color: #000000">
   AND newbusy.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND oldbusy.instance_number </span>=<span style="color: #000000"> sn.instance_number
   AND newbusy.dbid </span>=<span style="color: #000000"> sn.dbid
   AND oldbusy.dbid </span>=<span style="color: #000000"> sn.dbid
 ORDER BY sn.instance_number, sn.snap_id;</span>

 

根据dba_hist_osstat统计CPU占用情况

标签：11g   显示   oba   nic   报告   dba   value   习惯   情况