Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capacity Planner from OEM VS Statspack

RE: Capacity Planner from OEM VS Statspack

From: Andy Rivenes <>
Date: Mon, 02 Feb 2004 14:33:38 -0800
Message-Id: <>

I like what Mark and Donald have already said in their responses, and my additional response is yes, you can trace it and determine why the job is slow, or you can do what you describe below and probably fix it. Then again, as you say, you may wind up tracing it anyway. The other point that you make has to do with collection. Immediately tracing it may not be the best approach, since you're probably not duplicating the environment that the actual transaction is running in. Another approach might be to trace the process the next time it runs. Assuming that it still runs poorly, you should be able to determine exactly why. And, if it is indeed being caused by some external process, that should show up as well (perhaps a serialization issue or slow I/O, or ??).

Andy Rivenes

At 12:57 PM 2/2/2004 -0800, you wrote:
>What do you do when someone calls to say, "My job which usually takes 20
>minutes to run took over 2 hours last night?" Do you inquire about the
>composition of the job and immediately run a 10046 trace on it? The
>information from that trace may not represent what is happened the prevous
>night because the conditions such as the load on the database are
>different. However with statspack information I might see that the
>number of direct path reads and writes went up significantly during that
>period from their norm, and the waits also increased. Remember the
>information is collected every 10 minutes I can now get the plan
>information as well. Is there a hash join. Time to check the statistics.
>Perhaps the problem is not being caused by the program the person is
>calling about, but by another. Statspack can be helpful here as well. If
>I cannot figure it out I can still try tracing the program or set a login
>trigger to start the trace when the job runs that night. Of course
>Ideally I should have already collected a job profile ...
>Something like
>Call Duration Calls Duration/Call
>direct path write 95.28s 81.1% 5707 0.02s
>direct path read 21.47s 18.3% 7632 0.00s
>SQL*Net message from client 0.59s 0.5% 4 0.15s
>db file scattered read 0.17s 0.1% 652 0.00s
>SQL*Net message to client 0.00s 0.0% 4 0.00s
>db file sequential read 0.00s 0.0% 1 0.00s
>Total cpu time: 30.5 seconds
>And be ready to compare it with what the new trace delivers.
>Finally there are some jobs which manipulate data which are impossible to
>repeat because the data has changed. What do you run the 10046 trace on then?
>Statspack is certainly no 10046 trace, but it is not useless.
>Ian MacGregor
>Stanford Linear Accelerator Center

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Feb 02 2004 - 16:33:38 CST

Original text of this message