RE: Capacity Planner from OEM VS Statspack
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Feb 2004 13:54:50 -0600
"VMS" is the key.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
nelson.petersen@xxxxxxxxxxxxxxx
Sent: Friday, February 06, 2004 1:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
For 10046 tracing, I create a logon trigger for the user that I want to
trace.
(You can create a logon trigger for all users if you want.)
I set it up to store SID/SERIAL#/OSUSER/sysdate /trace-level plus a few
other tidbits.
It requires a table to insert into, obviously, but it allows me to go
back
in time
and figure out which trace file goes with which OSUSER.
>From the trace file:
(sid.serial#) sysdate
(*** SESSION ID:(161.918) 2003-05-09 11:49:14.874)
I've experienced the file lock issue too. The user in question
sometimes
has to completely
log out of the instance; even though it looks like the session being
traced
has exited.
We're on Oracle 8.1.7.1 on VMS.
Nelson
-----Original Message-----
From: babette.turnerunderwood@xxxxxxxxxxxxxxx
[mailto:babette.turnerunderwood@xxxxxxxxxxxxxxx]
Sent: Friday, February 06, 2004 1:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
I had given up on trace files a while ago...
I could NEVER format the trace files that went to SYSOUT=20
Finally in 8.1.7.4 we were about to write trace files to DASD
I have not had any luck using tkprof to format these files though.
Also, after creating the tracefile, the trace file is not always =
readable.
For some reason, the oracle service / database keeps am exclusive lock =
on
the file and I cannot view it. Obvioulsy I cannot shutdown the
database=20
just to release the lock.
Maybe I will try trace files again...=20
Will have to record time and date of trace, what I was doing and check =
hours (days?) later to see if file lock has been released :-(
- Babette
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of =
Jared.Still@xxxxxxxxxxx
Sent: 2004-02-05 3:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
Babette,
Have you done a 10046 trace on this?
Jared
<babette.turnerunderwood@xxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/05/2004 11:47 AM
Please respond to oracle-l
=20
To: <oracle-l@xxxxxxxxxxxxx>
cc:=20
Subject: RE: Capacity Planner from OEM VS Statspack
It is worse than that .....
EVERYONE has noticed that at times the performance is abysmally slow.
BUT according to all of the mainframe reporting information=3D20
Everything is fine... No swapping, no paging, no disk bottleneck, no =3D
memory problems, no CPU problems.....
For instance, full tablescan (no indexes) to update a NULL column to =3D
NULL
on 1 Million rows. Can take up to three times as long at times.
BUT System people insist there is nothing being pushed at the system =3D
level.
The CPU is not maxed out, the disks have no bottlenecks or contention =
=3D
and there are no memory problems at this time.
There are only three things, CPU, DISK, Network.
There has to be something wrong with at least one of them to be getting
=
=3D
the weird sporadic performance that we get.
It is hard to get overall picture of health of machine in MF =3D
environment.
We have Logical machines (LPARs) on a single physical box.
I know that I/O on other LPARs can affect our I/O, but we are told that
=
=3D
there are no problems according to the system records.
The statspack information is a bonus. We have SOMETHING that we can say
=
=3D
"explain this". Still waiting on explanation for a few weeks now...
Babette
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
Sent: 2004-02-03 3:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
> I agree with Ian.... Sometimes Statspack is VERY useful..
>=3D3D20
> In our case the Statspack reports shows ave read times of=3D3D20
> 1-10ms. However we occasionally see read times of 300-700 ms.
>=3D3D20
> We are currently investigating what is on the slower disks,=3D3D20
> What systems are sharing them, and whether oracle is=3D3D3D20=3D3D20
> chaining I/O requests and giving false stats or if there=3D3D20
> really is a =3D3D3D problem. (Hey, on OS/390 mainframe system =
we=3D3D20
> don't get iostat / sar / vmstat / =3D3D3D
> top)
>=3D3D20
> This top-down approach doesn't address any SPECIFIC=3D3D20
> performance proble. BUT ... if we didn't have Statspack=3D3D20
> running periodically, we might have =3D3D3D missed this.
>=3D3D20
> - Babette
I think the interesting question here is 'If you had missed this, would
anyone care?' and its corollary 'now you have caught it, does anyone =
=3D3D
care?'.
Now I admit that I have a biased view in that all anyone ever seems to
complain to me about is 'Screen X is running slow' or 'we can't complete
=
=3D
=3D3D
our
management reports overnight' or 'I'm not a dba so your presentation on
managing databases that I chose to attend was irrelevant' - oops sorry =
=3D
=3D3D
not
that last one. Almost never does anyone whinge that 'the system is =3D3D
slow', or
at least when they do they have a specific example in mind. As a result
=
=3D
=3D3D
I am
definitely biased towards a view that systems don't experience problems
=
=3D
=3D3D
-
processes do. I *suspect* that even where the *system* is slow then =
=3D3D
actually
it will be fewer than 5 processes that are killing it, but have no =3D3D
proof.=3D3D20
Niall
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
