Linux server load average history

Introduction

Have you got an email in the morning saying that the database was slow during the night or particularly slow - say between 2:00 and 4:00 am ? How would you approach and find the cause of performance problems that have occurred in the past ?

AWR report from Database

Oracle introduced the AWR report in 10g and because it automatically collects performance statistics every one hour by default (called snaps) with a default 7 day retention, one can quickly obtain an AWR report for the specified duration and analyze the report.  

1) sqlplus '/ as sysdba'

2) @?/rdbms/admin/awrrpt.sql   

Note: (?  is a shortcut for the $ORACLE_HOME location)

sar report from operating system

If the database is hosted in Linux, one can also use the sar command to find the server load average upto a 10 minute granularity.  This is particularly useful if a root owned process or non-database processes (such as a long running gzip, gunzip, cp command etc) have caused the high load average in the server which in turn has slowed other processes in the server (including the database in question).

The below example shows a high server load average on Oct 11th.  The server saves the load average history for the last few days in /var/log/sa  (sa11 here refers to the 11th of the month).

cd /var/log/sa
sar -q -f sa11

sar command can be executed by oracle osuser 

One need not have the system administrator's privilege (root access)  for executing the sar command. This command can be executed by the oracle osuser directly.