How to Collect Standard Information for a Database Performance Problem

How to Collect Standard Information for a Database Performance Problem

(Doc ID 1595092.1)

Diagnostics Checklist

 With Diagnostic pack license  

   1) Generate and Check ADDM report, implement findings, re-test                      [  ]
   2) Gather Diagnostics
     a) AWR report covering problem period                                             [  ]
     b) AWR report covering good period of similar load and duration for comparison    [  ]
     c) AWR Compare Periods report comparing the good and bad periods                  [  ]
     d) Collect an ASH report for the same period                                      [  ] 
   3) Collect OSWatcher data                                                           [  ]
   4) Collect Alert log and traces covering the duration of the problem                [  ]
    a) Check the Alert log for the period when the issue occurred                      [  ]
    b) Find any trace files referenced in the problem period                           [  ]


 Without Diagnostic pack license 

   1) Gather a statspack report at the time of the issue                               [  ]
   2) Gather a statspack report covering good period for comparison                    [  ]
   3) Collect OSWatcher data                                                           [  ]
   4) Collect Alert log and traces covering the duration of the problem                [  ]
    a) Check the Alert log for the period when the issue occurred                      [  ]
    b) Find any trace files referenced in the problem period                           [  ]

 Collate and upload the diagnostic information

 5) Collate and upload the diagnostic information                                      [  ]

Diagnostics for Database Performance Issues

When collecting data for performance issues, ensure that information from different sources is collected over the same time periods and durations to facilitate comparison.

With Diagnostic Pack License

If you have the diagnostic pack license:

1) Generate and Check ADDM report, implement findings, re-test

Collect an ADDM (Automatic Database Diagnostic Monitor) report covering a short period while the problem is occurring (a 1 hour snapshot duration (or less) is preferable). Gather the ADDM report through Oracle Enterprise Manager or in SQL*Plus using the ADDM report generation script: $ORACLE_HOME/rdbms/admin/addmrpt.sql :

# cd $ORACLE_HOME/rdbms/admin/
# sqlplus SCOTT/TIGER
SQL> START addmrpt.sql

For more information see Document 1680075.1 "How to Generate and Check an ADDM report" for instructions on how to do this.
Once you have Collected an ADDM Report, reviewed its findings and implemented any recommendations, re-check the performance. If you are still encountering problems, proceed to the next step.

Gather Diagnostics AWR reports

Collect AWR reports covering 2 periods. These periods should be of the same duration (a 1 hour snapshot duration (or less) is preferable) and should cover:

The same problem period as with the ADDM report above
(If possible) Another period where the problem was not being seen, but the load was similar (for comparison)

2a) AWR report for the problem period

To gather an AWR report, use the AWR report generation script: $ORACLE_HOME/rdbms/admin/awrrpt.sql :

# cd $ORACLE_HOME/rdbms/admin/
# sqlplus SCOTT/TIGER
SQL> START awrrpt.sql

The report will prompt for HTML or TXT (choose HTML).

For more details, refer to the following article:

Document 1903158.1 How to Collect Standard Diagnostic Information Using AWR Reports for Performance Issues

2b) AWR report for another period

If possible, gather a report from another period with similar load where problems are not seem. For example, if you had problems at 2pm today but things were fine yesterday, collect a report from yesterday at 2pm. Use the procedure above to collect the report.

2c) AWR Compare Periods report comparing the 'good' and 'bad' periods

If possible, gather a Workload Repository Compare Periods report to compare the good and bad periods selected above. This can help quickly identify differences so that efforts can be focused on likely problem areas. To collect the report use the awrddrpt.sql from the $ORACLE_HOME/rdbms/admin directory supplying the Begin and End Snapshot Ids for both periods:

# cd $ORACLE_HOME/rdbms/admin/
# sqlplus SCOTT/TIGER
SQL> START awrddrpt.sql

2d) ASH report

Collect an ASH report for the same problem period. ASH reports provide:

More accurate information as to the start and end time of contention
The peak times for specific wait events
How the pattern of wait events develops over time

The ASH report should be of the same duration (a 1 hour snapshot duration (or less) is preferable) and should cover the same problem period as with the ADDM report above.

To gather an ASH report use the ASH report generation script: $ORACLE_HOME/rdbms/admin/ashrpt.sql :

# cd $ORACLE_HOME/rdbms/admin/
# sqlplus SCOTT/TIGER
SQL> START ashrpt.sql

The report will prompt for HTML or TXT (choose HTML).

For more details on gathering ASH reports, refer to the following article:

Document 1903145.1 How to Collect Standard Diagnostic Information Using ASH Reports for Performance Issues

3) Collect OSWatcher (osw) data

If you have OSWatcher installed and it was collecting data over the period of the problem, then "tar up" the OSWatcher archive data and upload the entire archive directory to the SR. The location of OSWatcher data can be found by looking in /tmp/osw.hb. If not then omit this step. See Document 301137.1 for details about OSWatcher.

4) Collect Alert log and traces covering the duration of the problem

Sometimes, performance issues will be accompanied with messages, warnings errors or trace files recorded in the database alert log.

4a) Check the Alert log for the period when the issue occurred

The alert log of a database is a chronological log of messages and may include messages, warnings, errors and references to trace files that may provide valuable information. To find the directory in which the alert log resides, use the following select. From SQL*Plus execute:

select value from v$diag_info where name ='Diag Trace';

This output generated is similar to:

VALUE
-----------------------------------------
/dbadmin/diag/rdbms/v12102/trace

The alert.log is named alert_SID.log. In an example where the SID is "v120102" and the directory the alert log resided in was "/dbadmin/diag/rdbms/v12102/trace", the full path of the alert log would be:

/dbadmin/diag/rdbms/v12102/trace/alert_v12102.log

Check the alert log for any messages recorded during the problem period. For example the following are messages associated with an ORA-12751 error occurring during a period of poor performance:

...
Tue May 08 18:40:18 2015
Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation
...

...
Wed Jun 13 23:14:43 2012
minact-scn: useg scan erroring out with error e:12751
Wed Jun 13 23:19:49 2012
minact-scn: useg scan erroring out with error e:12751
...

4b) Find any trace files referenced in the problem period

Check the alert log entries and identify any trace files that may be referenced. For example, the trace file for the ORA-12751 error mentioned earlier is referenced in the alert log as follows:

...
Tue May 08 18:40:18 2015
Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation
...

In this case, the trace file is called "oracle_mmon_1234567.trc" and is located in the following directory: "/oracle/diag/rdbms/oracle/trace"

Without Diagnostic Pack License

If you do not have a diagnostic pack license then you need to install and gather a statspack report at the time of the issue. To install statspack, see:

Document 1931103.1 How to Collect Standard Diagnostic Information Using Statspack Reports for Performance Issues

The Statspack report is similar to AWR, but AWR is much more comprehensive and contains significant additional information not available in Statspack.

1) Gather a statspack report at the time of the issue

Assuming that you had statspack installed at the time of the incident and staspack snapshots were being collected, you can gather a statspack report using the following command:

SQL> connect perfstat/perfstat
SQL> start ?/rdbms/admin/spreport

You will be prompted for the beginning snapshot Id, the ending snapshot Id and the name of the report text file to be created

2) Gather a statspack report for another period

3) Collect OSWatcher (osw) data

4) Collect Alert log and traces covering duration of the problem

Sometimes, performance issues will be accompanied with messages, warnings errors or trace files recorded in the database alert log.

4a) Check the Alert log for the period when the issue occurred

select value from v$diag_info where name ='Diag Trace';

This output generated is similar to:

VALUE
-----------------------------------------
/dbadmin/diag/rdbms/v12102/trace

/dbadmin/diag/rdbms/v12102/trace/alert_v12102.log

Check the alert log for any messages recorded during the problem period. For example the following are messages associated with an ORA-12751 error occurring during a period of poor performance:

...
Tue May 08 18:40:18 2015
Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation
...

...
Wed Jun 13 23:14:43 2012
minact-scn: useg scan erroring out with error e:12751
Wed Jun 13 23:19:49 2012
minact-scn: useg scan erroring out with error e:12751
...

4b) Find any trace files referenced in the problem period

Check the alert log entries and identify any trace files that may be referenced. For example, the trace file for the ORA-12751 error mentioned earlier is referenced in the alert log as follows:

...
Tue May 08 18:40:18 2015
Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation
...

In this case, the trace file is called "oracle_mmon_1234567.trc" and is located in the following directory: "/oracle/diag/rdbms/oracle/trace"

Next Steps

5) Collate and upload the diagnostic information

If the database performance issue you are encountering is accompanied by any "hang" symptoms, then you are strongly advised to also collect "hang" diagnostics as outlined in the database hang SRDC which is a superset of the performance SRDC:

Document 1594905.1 SRDC - How to Collect Standard Information for a Database Hang

If you have generated and checked the ADDM report and the findings have been implemented, then you can find more guidance regarding troubleshooting Database Performance issues in the following document:

Document 1362329.1 How To Investigate Slow Database Performance Issues

If you have been unable to resolve your issue, please collate the information and upload the files to support along with a clear explanation of the issue. The list of files to upload will be:

With Diagnostic pack license	Without Diagnostic pack license
ADDM Report (Text) 3 AWR reports (HTML): AWR report covering problem period AWR report covering good period AWR Compare Periods report (good/bad) ASH (HTML) report for the same period	2 Statspack reports (Text): Statspack report covering problem period Statspack report covering good period
OSWatcher data
Alert log covering problem period along with any trace files referenced in that period

In most cases the supplied information will be sufficient to progress the issue towards a solution but, in some cases, further follow up information may be required.

===========================================================================

REFERENCES

NOTE:1644732.1 - SRDC - How to Collect Standard Information for an Issue Where a SQL Plan Management (SPM) Baseline is Not Used
NOTE:1595650.1 - SRDC - How to Collect Standard Information for an Issue Where 'latch: cache buffers chains' Waits Are the Primary Waiters on the Database
NOTE:149121.1 - Gathering a StatsPack snapshot
NOTE:1594386.1 - SRDC - How to Collect Standard Information for a SQL Performance Problem
NOTE:1594905.1 - SRDC - How to Collect Standard Information for a Database Hang
NOTE:1595614.1 - SRDC - How to Collect Standard Information for an "ORA-00060 Deadlock Detected" Problem
NOTE:1595636.1 - SRDC - How to Collect Standard Information for an Issue Where 'enq: TX - ...' Type Waits Are the Primary Waiters on the Database
NOTE:149113.1 - Installing and Configuring StatsPack Package
NOTE:1490798.1 - AWR Reporting - Licensing Requirements Clarification
NOTE:250655.1 - How to use the Automatic Database Diagnostic Monitor
NOTE:1595672.1 - SRDC - How to Collect Standard Information for an Issue Where 'log file sync' Waits Are the Primary Waiters on the Database

Oracle DBA Information at one place

Search This Blog

How to Collect Standard Information for a Database Performance Problem

REFERENCES

Comments

Post a Comment