| 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: Capacity Planner from OEM VS Statspack
Andy, you have parroted my warnings about running the trace immediately vs. tracing it when the job normally runs. Well at least we agree on that. Let's take another look at the profile
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
Again. I know direct path I/O's are the major wait, and the actual trace will tell me the file in question. But even a 10046 trace doesn't tell me why it was happening. In this case it was being caused by another instance which was beating up the disk which held the file.
Oracle has methods of lowering a session's priority if it is CPU intensive. I'd like it to be able to do the same for I/O intensive operations as well.
Ian
-----Original Message-----
From: Andy Rivenes [mailto:arivenes_at_llnl.gov]
Sent: Monday, February 02, 2004 2:34 PM
To: oracle-l_at_freelists.org
Subject: RE: Capacity Planner from OEM VS Statspack
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
arivenes_at_llnl.gov
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
>ian_at_SLAC.Stanford.edu
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Feb 02 2004 - 17:51:26 CST
 |
 |