Investigating Slow Oracle database performance

On many occasions you will face a scenario where the Oracle database performance is very slow. There are many reasons why the database performance can be slow. To investigate a slow performance problem, begin by deciding what diagnostics should be gathered. To do this, consider the following questions and take the appropriate action.

Database is Slow: Is the performance problem constant or does it occur at certain times of the day ?

* CONSTANT
o Gather an AWR or Statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient).
o If you have an historic report which covers the same time of day and period when the performance was OK then take that too.

* ONLY CERTAIN TIMES
o Gather an AWR or Statspack report for a period of time which covers the period when the problem exists (For instance, if you have a problem when something is run between 12 and 3 then make sure the report covers either that time or part of that time).
o Additionally, for comparison, gather an AWR or Statspack report for a similar period of time when the problem does not occur. Always ensure that you are making a fair comparison – for instance, the same time of day or the same workload and make sure the duration of the report is the same.

NOTE 1:- As much as possible statspack reports should be minimum 10 minutes, maximum 30 minutes. Longer periods can distort the information and reports should be re-gathered using a shorter time period. With AWR a 1hr report is OK, but for most performance issues a short 10-30 minute snapshot should be sufficient.

NOTE 2:- It is often prudent to use a matched ADDM report initially to give a pointer to the main issues. Reading the corresponding ADDM report as a first step to tuning can save a lot of time because it immediately points at the main user as compared to trying to understand what an AWR report is presenting.

NOTE 3:- If SQL performance is suspected as the cause of the slowness then collect an ASH report for the same period. If a specific SQL is suspected of slowness then run an ASH report just for that SQLID and also look at using SQLTXplain to diagnose issues with that statement.

Database is Slow: Does the problem affect one session, several sessions or all sessions ?

* ONE SESSION – Gather 10046 trace for the session.
* SEVERAL SESSIONS – Gather 10046 trace for one or two of the problem sessions
* ALL SESSIONS – Gather AWR or Statspack reports

Database Hangs: Does a particular Session ”appear” to hang or do several sessions or all sessions hang?

Please collect the following diagnostics according to the specific scenario:

When only one session appears to be ‘hung’

* Gather 10046 trace for the session.
* Get a few errorstacks for the session
* Gather an AWR (or Statspack) report for a period of time when the problem occurs (a 1 hour report is usually sufficient).

When more than one session appears to be ‘hung’

* Gather 10046 trace for one or two of the problem sessions
* Get a few errorstacks for one or two of the problem sessions
* Gather an AWR (or Statspack) report for a period of time when the problem occurs (a 1 hour report is usually sufficient).

When most of the sessions appears to be ‘hung’ treat this as Database Hang