| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: DB Slow down
One good use of STATSPACK is that it can provide the ³historical²
perspective that Mogens mentions, albeit at a high level. Taking snapshots
hourly and saving that data for a couple weeks enables one to ³look backward
in time² to see what was happening then versus what is happening now.
I have a useful SQL*Plus script called ³sp_systime_9i.sql² (http://www.EvDBT.com/tools.htm) that can be used to perform ³response-time analysis² according to Anjo¹s YAPP method over time, using snapshot data in STATSPACK. It won¹t necessarily pinpoint the answer the way 10046 tracing can, but it can certainly provide a useful long view. It has an obscure bug or two which I¹m trying to fix and post, so if you see spikes of really huge numbers related to ³CPU time², then ignore those for now...
Anyway, if ³CPU time² or the ³db file ... read² events appear dominant, then consider using the ³top_stmt4_9i.sql² and ³run_top_stmt4.sql² scripts to obtain a ³top-N SQL² report over the period of time after differences were noted. As everyone is aware, performance problems are most often due to application SQL (particularly when everyone swears that ³nothing has changed² :-) )...
Your story also illustrates good reasons to resist ³seniors² who advise blindly flipping switches (i.e. change this or that parameter, take db out of archivelog, etc), in favor of obtaining and following facts...
on 12/26/05 6:17 PM, Mogens Nørrgaard at mln_at_miracleas.dk wrote:
> Only one real idea or suggestion: If you don't know where the time was spent > before the problem started, you'll have a hard time now spotting the > difference. Trace, trace, trace. > > Mogens > > > Onkar N Tiwary wrote:
 |
 |