Count CPU usage based on Dba_hist_osstat

In 11g, the view Dba_hist_osstat is used to record the time-of-day metrics at the OS level. The view dba_hist_osstat_name displays the relevant indicator names.

[Email protected]134.32.114.1:1521/dzgddb>Select* fromDba_hist_osstat_name; DBID stat_id Stat_name--------------- --------------- ----------------------------------------------------------------3352298469               0Num_cpus3352298469               1Idle_time3352298469               2Busy_time3352298469               3User_time3352298469               4Sys_time3352298469               5Iowait_time3352298469               6Nice_time3352298469               -Rsrc_mgr_cpu_wait_time3352298469               theLOAD3352298469               -Num_cpu_cores3352298469               -num_cpu_sockets3352298469            1008physical_memory_bytes3352298469            1009vm_in_bytes3352298469            1010vm_out_bytes3352298469             -Tcp_send_size_min3352298469            2001Tcp_send_size_default3352298469            2002Tcp_send_size_max3352298469            2003Tcp_receive_size_min3352298469            2004Tcp_receive_size_default3352298469            2005Tcp_receive_size_max3352298469            2006Global_send_size_max3352298469             -Global_receive_size_max ARows selected.

As above, nmu_cpu_cores refers to the number of CPU cores, this example is 32;num_cpus refers to the number of CPU core threads, this example is 64;num_cpu_sockets refers to the number of CPU, also refers to the CPU board, this example is 4.

The key calculation formula is:

%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

In view of the report habits, the key indicator%idle conversion to%CPU, then:

%CPU = busy_time/(busy_time+idle_time) *100

Incidentally, the load indicator records the OS load value of the snap_id starting point, corresponding to the AWR report's load Average End & Load Average Begin.

The following is the final sql: Counting CPU usage According to Dba_hist_osstat, incidentally attaching several other key indicators

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/ -,2)"DB Time (min)", round ((Newbusy.value-Oldbusy.value)/((Newidle.value-Oldidle.value) +(Newbusy.value-Oldbusy.value)) * -,             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-1and 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-1and 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-1and 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-1and 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-1and 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;

Count CPU usage based on Dba_hist_osstat