RE: Capacity Planner from OEM VS Statspack
- From: "Guang Mei" <gmei@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Feb 2004 14:08:05 -0500
Statspack is just one of the tools which I think (based on my experience) is
useful for performance monitoring and tuning. We have a weekly build program
that took about 80 hours (with multihread programs running) to finish, it's
been running the same code for 2+ years. For the past year, I implmented
Statspack and took snapshot hourly, I identified the top sqls in each hour
and their individual programs. By studing the program I improved some of
their performacen dramatically (by changing program logic and re-writing
sqls). Together with dbms_profiler and 10046 trace, I found many places that
code could be improved (big and small). I reduced the weekly build program
down to 60 hours while the data has grown 100%. And I am still in the middle
of optimization efforts.
Guang
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Andy Rivenes
Sent: Monday, February 02, 2004 1:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Capacity Planner from OEM VS Statspack
However, what you're really collecting is "workload" information. It's only
useful, as Cary stated, for capacity planning, or what you stated, for
trends. You're not going to solve a "performance" problem with this level
of information.
Andy Rivenes
arivenes@xxxxxxxx
At 10:00 AM 2/2/2004 -0800, MacGregor, Ian A. wrote:
>I have to disagree here. Sometimes one needs to take a top-down approach
>to tuning. Measuring things such as CPU usage and I/O counts can be
>useful. For instance here's a report for a very lightly loaded system
>based on statspack data.
>
>
>DATABASE BEGIN_TIME END_TIME Physical Reads
>Physical Writes CPU SECONDS ELAPSED SECONDS
>---------- -------------------- -------------------- --------------
>--------------- ----------- ---------------
>ORAP 12-JAN-2004:00:00:03
>12-JAN-2004:01:00:05 14725 7697 224.005
>3602
>ORAP 12-JAN-2004:01:00:05
>12-JAN-2004:02:00:02 6271 2125 5.03
>3597
>ORAP 12-JAN-2004:02:00:02
>12-JAN-2004:03:00:04 66066 1043 6.105
>3602
>ORAP 12-JAN-2004:03:00:04
>12-JAN-2004:04:00:02 1496 1125 3.68
>3598
>ORAP 12-JAN-2004:04:00:02
>12-JAN-2004:05:00:04 1716 1462 3.995
>3602
>ORAP 12-JAN-2004:05:00:04
>12-JAN-2004:06:00:01 961 1721 3.71
>3597
>ORAP 12-JAN-2004:06:00:01
>12-JAN-2004:07:00:03 3779 1032 3.985
>3602
>ORAP 12-JAN-2004:07:00:03
>12-JAN-2004:08:00:06 16436 2026 5.84
>3603
>ORAP 12-JAN-2004:08:00:06
>12-JAN-2004:09:00:03 231051 2634 14.43
>3597
>ORAP 12-JAN-2004:09:00:03
>12-JAN-2004:10:00:05 137762 1245 15.605
>3602
>ORAP 12-JAN-2004:10:00:05
>12-JAN-2004:11:00:03 183870 1163 15.21
>3598
>ORAP 12-JAN-2004:11:00:03
>12-JAN-2004:12:00:05 143757 1166 12.975
>3602
>ORAP 12-JAN-2004:12:00:05
>12-JAN-2004:13:00:02 20349 1088 4.705
>3597
>ORAP 12-JAN-2004:13:00:02
>12-JAN-2004:14:00:04 195781 1827 13.355
>3602
>ORAP 12-JAN-2004:14:00:04
>12-JAN-2004:15:00:02 26901 1538 9.53
>3598
>ORAP 12-JAN-2004:15:00:02
>12-JAN-2004:16:00:04 43434 1039 8.205
>3602
>ORAP 12-JAN-2004:16:00:04
>12-JAN-2004:17:00:01 61892 1747 8.485
>3597
>ORAP 12-JAN-2004:17:00:01
>12-JAN-2004:18:00:04 36268 819 5.99
>3603
>ORAP 12-JAN-2004:18:00:04
>12-JAN-2004:19:00:01 87842 1302 8.4
>3597
>ORAP 12-JAN-2004:19:00:01
>12-JAN-2004:20:00:03 1256 653 4.625
>3602
>ORAP 12-JAN-2004:20:00:03
>12-JAN-2004:21:00:01 197415 1086 99.765
>3598
>ORAP 12-JAN-2004:21:00:01
>12-JAN-2004:22:00:03 556 1386 3.235
>3602
>ORAP 12-JAN-2004:22:00:03
>12-JAN-2004:23:00:06 1074 697 3.22
>3603
>ORAP 12-JAN-2004:23:00:06
>13-JAN-2004:00:00:03 14856 927 4.495
>3597
>ORAP 13-JAN-2004:00:00:03
>
>
>
>If I have a report which deviates from this, then I can start
>looking more closely. The statspack information is gathered every ten
>minutes. I can then propely trace the statements which are candidate
>problems. I can also get more information. If the CPU count goes up is
>it due to an increase of logical I/Os. How much of the physical I/O is
>direct and to what tablespaces etc.
>
>There are many problems with statspack and the virtual views upon which it
>is based. Counters resetting are going negative, the frequency of the
>updates etc. I have made elaborate systems, chucked them out of
>frustration, changed tactics and tried again.
>
>At one time I was totally against such systems because they were not
>perfect, actually far from it. But I came to realize that though
>statspack is inadequate, 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:
