Database Performance Issue - Diagnosis

The performance issue on Oracle Database.

Information that needs to be gather or where to start:

1. The date and time of issue - What incidents occurred before Performance issue.  i.e., any changes that made at Database parameter level, configuration level, Patch, OS level changes, OS patch etc.

2. Look for Database instance alert log from all the nodes (RAC/Cluster environment) or standalone database (single instance).

3. AWR (Automatic Workload Repository) reports collected for 30 - 60 minute statistics from all the nodes covering the issue period.

4. Hanganalyze dump:

Collect the hanganalyze trace during the time you are seeing the blocking/hang/performance issue.

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
exit

5. OS Watcher data from all the nodes or standalone database (single instance).

For Example: The particular day of oswatcher logs:

# cd /opt/oracle.oswatcher/osw/archive
# find . -name '*YY.MM.DD*' -print -exec zip /tmp/Exa_`hostname`-12.zip {} \;

<-- where the YY.MM.DD is the actual day of the oswatcher logs to get. Example: 13.01.03

Thanks.