根据dba_hist_osstat统计CPU占用情况

2023-10-13 14:44:16

在11g里面，视图dba_hist_osstat用来记录OS级别的time时间指标。视图dba_hist_osstat_name显示了相关的指标名称。

SYS@134.32.114.1:/dzgddb> select * from DBA_HIST_OSSTAT_NAME;

           DBID         STAT_ID STAT_NAME

--------------- --------------- ----------------------------------------------------------------

                     NUM_CPUS

                     IDLE_TIME

                     BUSY_TIME

                     USER_TIME

                     SYS_TIME

                     IOWAIT_TIME

                     NICE_TIME

                    RSRC_MGR_CPU_WAIT_TIME

                    LOAD

                    NUM_CPU_CORES

                    NUM_CPU_SOCKETS

                  PHYSICAL_MEMORY_BYTES

                  VM_IN_BYTES

                  VM_OUT_BYTES

                  TCP_SEND_SIZE_MIN

                  TCP_SEND_SIZE_DEFAULT

                  TCP_SEND_SIZE_MAX

                  TCP_RECEIVE_SIZE_MIN

                  TCP_RECEIVE_SIZE_DEFAULT

                  TCP_RECEIVE_SIZE_MAX

                  GLOBAL_SEND_SIZE_MAX

                  GLOBAL_RECEIVE_SIZE_MAX

 rows selected.

如上，nmu_cpu_cores是指cpu核心数，本例是32；num_cpus是指cpu核心线程数，本例是64；num_cpu_sockets是指cpu路数，也指cpu主板数，本例是4。

关键的计算公式是：

%User = USER_TIME/ (BUSY_TIME+IDLE_TIME)*

%Sys = SYS_TIME/ (BUSY_TIME+IDLE_TIME)*

%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) /  / , ) "DB time(min)",

       round((newbusy.value - oldbusy.value) /

             ((newidle.value - oldidle.value) +

             (newbusy.value - oldbusy.value)) * ,

             ) "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 -

   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 -

   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 -

   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 -

   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 -

   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;

码农公寓

相关文章