Things To Check For Performance issue on Exadata system

Objective: To check on the performance on Exadata.

Applies to Engineered Systems X2, X3 and other Database machines.

Diagnostic Steps:

1. Check on the date and time of issue.  Check before and after events if any around that time.

2. Database instance alert log from all the nodes.

3. AWR (Automatic Workload Repository) reports collected for 30 - 60 minutes statistics from all the nodes around the time of the event.

4. Get Hanganalyze dump, collect the trace when the Performance event or any blocking or bottle neck issues occur.  Trace should be collected when the event occurs.  Collecting hanganalyze trace after the Performance issue occurs will not record any significant information.

Collect the hanganalyze trace during the time you are seeing the block or hang or 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.

For 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 {} \;

Please note 'YY.MM.DD' is the actual day of the oswatcher logs.

For Example: 13.01.03 if the Performance event occurs on January 3, 2013.

6. Identify the Machine where it occurs Exadata quarter rack, a half rack, or a full rack system.  Check for any recent changes at Hardware and Software level.

For example: Exadata Full rack X2-2 system (or) X2-8 system with any recent changes to Database, Patch, Patch bundle, OS patch, Network changes etc.

- What RDBMS version are the Compute Nodes running?
- What Exadata Bundle Patch are the Compute Nodes running?
- What software version are the Storage Cells running? (This can be obtained by running "imageinfo" on the cells)?