Re: Evaluating overall performance
Date: Fri, 9 Apr 2010 04:51:48 -0700 (PDT)
Message-ID: <e4fd5857-70e4-430b-8ab8-1a48a0b400d9_at_z11g2000yqz.googlegroups.com>
I think the questions in the OP are quite reasonable.
My 2 cents
A database is an engineering object, like a car, or bridge, or plane,
or ship, and as such it can be characterized reasonably well by a set
of numbers. For database such numbers would be:
- size of datafiles
- size of SGA
- redo log generation per second
- logical reads per second
- physical reads per second
- physical writes per second
- parses per second
- transactions per second
These numbers appear on the first page of Statspack report (except db size). Compare them for a few databases, this will give you good idea which database is read-intensive, update-intensive, etc. There are some known limitations, for example Oracle typically starts struggling if number of hard parses per second exceeds 100 or if there are more than 500 transactions per second.
The values in "Top 5 Wait Events" should be divided by duration of the report. For example a report may show CPU usage of 3457 seconds, by itself this number doesn't tell you much. However if you know that report is for 15 min interval then 3457 of CPU usage means that approximately 4 CPUs were used by the database.
Finally "Top SQL statements" section tells you whether activity in the database is dominated by frequently executed queries that run for less than 1 second or long-running queries or mixture of both.
This wouldn't tell you whether users are happy with performance or not. However with this data you can compare performance now and month ago, or performance during normal and problematic period.
Ultimately the only thing a database does is execution of SQL
statements (in broad sense - including things like SQL*Loader).
A statement may change its performance, for example as a result of
changes in table/index statistics.
However poor performance of SQL statement doesn't lead to increase in
transaction rate or rate of hard parses, those things are driven by
the application.
Received on Fri Apr 09 2010 - 06:51:48 CDT