RE: Capacity Planner from OEM VS Statspack
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 02 Feb 2004 15:51:26 -0800
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@xxxxxxxx]
Sent: Monday, February 02, 2004 2:34 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx
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@xxxxxxxxxxxxxxxxx
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe'
in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
