Oracle dba_hist_osstat

Guru Oracle Tips by Mike Ault

Back in the days of STATSPACK we had to write customized vmstat and Windows utilities to measure server stress. Oracle does not run in a vacuum, and it is critical to monitor the server environment. For example, no amount of tuning will help a database with an I/O or network bottleneck, and the same principle applies to server internal resources like CPU and RAM resources.

Remember, OS stress events can be very fast (transient) and real-time monitors are generally useless. The real value comes from capturing Oracle OS statistics over-time, analyzing trends and Oracle trending & forecasting signature Analysis (especially wait event signatures), using several methods:

Query the dba_hist_osstat table - This is the most robust and flexible approach, and it required moderate DBA skills and understanding of v$ and AWR table structures. For beginners, you can get a free poster with the AWR table structures at this link.
The Ion tool - The Ion utility is a superb way to do external proactive Oracle server monitoring. It is almost as robust as scripting, and hides the complexity and removed the tedium from running huge AWR table queries.
Write your own persistent OS monitor - We like vmstat output - See details below. With these vmstat/iostat extensions (to put the output into Oracle tables) we could alert on excessive CPU enqueues, RAM paging and iostat bottlenecks.
OEM automatic alerts for OS statistics - For beginners, you can also create alerts based on Oracle OEM Alerts, using a screen GUI to specify the OS exceptions, such as the runqueue threshold.
OSWatcher - There is a 2006 release of Oracle OS Watcher utility, available only for MOSC members.

The Old-fashioned way of collecting Oracle Operating System Statistics

Prior to Oracle 10g, we had to write our own simple scripts to capture the output from a continuously-running vmstat process. We would create OS statistics tables and place the data inside Oracle, like this:

# run vmstat and direct the output into the Oracle table . . .
cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU
DLE_CPU
   do

      $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1<<EOF
      insert into perfstat.stats\$vmstat
                           values (
                             sysdate,
                             $SAMPLE_TIME,
                             '$SERVER_NAME',
                             $RUNQUE,
                             $PAGE_IN,
                             $PAGE_OUT,
                             $USER_CPU,
                             $SYSTEM_CPU,
                             $IDLE_CPU,
                             0
                                  );
      EXIT
EOF
   done

For Professionals - The guru approach

Today in Oracle 10g we now have the ability to get server information from the dba_hist_osstat table, and we no longer have to worry about OS differences in performance metrics. Here is a nice script against dba_hist_osstat.

select * from(
select
to_char(b.end_interval_time,'dd-mon-yyyy hh24:mi') as meas_date,
sum(case when a.stat_name = 'BUSY_TIME' then a.value else null end) - sum(case when c.stat_name = 'BUSY_TIME' then c.value else null end) "Busy Time",
sum(case when a.stat_name = 'IDLE_TIME' then a.value else null end) -sum(case when c.stat_name = 'IDLE_TIME' then c.value else null end) "Idle Time",
sum(case when a.stat_name = 'SYS_TIME' then a.value else null end)-sum(case when c.stat_name = 'SYS_TIME' then c.value else null end) "Sys Time",
sum(case when a.stat_name = 'USER_TIME' then a.value else null end)-sum(case when c.stat_name = 'USER_TIME' then c.value else null end) "User Time",
max(case when a.stat_name = 'LOAD' then a.value else null end) "Load",
max(case when a.stat_name = 'PHYSICAL_MEMORY_BYTES' then a.value else null end) "Physical Memory"
from dba_hist_osstat a,
dba_hist_snapshot b,
dba_hist_osstat c
where a.dbid=b.dbid and a.snap_id=b.snap_id
and a.snap_id-1=c.snap_id
and b.end_interval_time>sysdate-&&int
group by to_char(b.end_interval_time,'dd-mon-yyyy hh24:mi')
order by 1) b
where "Load">(select to_number(value) from v$parameter where name='cpu_count')
/

Here is a sample output from the dba_hist_ostat script:

Enter value for int: 7
Date: 02/23/06 Page: 1
Time: 02:59 PM OS Runqueue>CPU Count Statistics TCI
tcigt database

MEAS_DATE Busy Time Idle Time Sys Time User Time Load Physical Memory
-------------------- ------------ ------------ ------------ ------------ ------------ ---------------
16-feb-2006 22:00 3241548 3214521 79056 3162492 2.08984375 29484
17-feb-2006 22:00 3241341 3214548 80505 3160836 2.1796875 248092
21-feb-2006 22:00 3321540 3108834 88173 3233367 2.7392578125 23148
22-feb-2006 11:00 3386763 2949264 111771 3274992 2.2099609375 890876
22-feb-2006 22:00 3225879 3221982 79776 3146103 2.7197265625 949492

Enter value for pct_load: 60
Date: 02/23/06 Page: 1
Time: 02:59 PM OS >60 % CPU Statistics TCI
tcigt database

MEAS_DATE Busy Time Idle Time Sys Time User Time Load Physical Memory
-------------------- ------------ ------------ ------------ ------------ ------------ ---------------
22-feb-2006 18:00 4169925 2278611 107658 4062267 1.6494140625