To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-
Is the performance problem constant or does it occur at certain times of the day ?
CONSTANT - Gather an AWR or statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient). If you have an historic report which covers the same time of day and period when the performance was OK then take that too.
CERTAIN TIMES - Gather an AWR or statspack report for a period of time which covers the problem existing (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).
ADDITIONALLY gather an AWR or statspack report for a similar period of time when the problem does not occur for comparison. 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:- As much as possible statspacks reports should be minimum 10 minutes, maximum 30 minutes. Longer periods can distort the infomation and reports should be re-gathered using a shorter time period. With AWR a 1hr report is OK.
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
Does the database ''actually'' hang or just ''appear'' to hang?
(ie do sessions never complete their tasks (HANG or SPIN?) or do they it eventually finish (SLOW) )
HANG - Take some systemstates as well as a statspack report
SPIN? - No Response from the Server, Does it Hang or Spin?
SLOW - Gather 10046 for a selection of slow sessions.
Is the CPU usage high for one or more sessions when things run slowly ?
YES - Take some errorstacks from the suspect CPU process.
Is the performance problem constant or does it occur at certain times of the day ?
CONSTANT - Gather an AWR or statspack report for a period of time when the problem occurs (a 1 hour report is usually sufficient). If you have an historic report which covers the same time of day and period when the performance was OK then take that too.
CERTAIN TIMES - Gather an AWR or statspack report for a period of time which covers the problem existing (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).
ADDITIONALLY gather an AWR or statspack report for a similar period of time when the problem does not occur for comparison. 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:- As much as possible statspacks reports should be minimum 10 minutes, maximum 30 minutes. Longer periods can distort the infomation and reports should be re-gathered using a shorter time period. With AWR a 1hr report is OK.
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
Does the database ''actually'' hang or just ''appear'' to hang?
(ie do sessions never complete their tasks (HANG or SPIN?) or do they it eventually finish (SLOW) )
HANG - Take some systemstates as well as a statspack report
SPIN? - No Response from the Server, Does it Hang or Spin?
SLOW - Gather 10046 for a selection of slow sessions.
Is the CPU usage high for one or more sessions when things run slowly ?
YES - Take some errorstacks from the suspect CPU process.
No comments:
Post a Comment