Re: performance measurment of an oracle db

M. Gedecke <spam_at_gedecke.de> wrote:
>hello ng,
>I want to measure the performance of an oracle db 8i or 9i. it is only about
>to see the actual performance. I think about a sql-select-statement which
>makes an complex select on standard databases. then i want to measure the
>time it needs to be performed. I will perform this statement once when there
>is no activity on the db at all to get a reference time. this time can then
>always be taken as reference value, so when I compare these two values i
>know the load on the db.
>which tables are useful for this purpose?
>
>
>set timing on
> select * from ????? where ???? like ???
>set timing off

You can't really test the performance of Oracle, it does very little on its own. SMON kicks off once in a while and pokes around a bit, occasionally your log files might switch, but beyond that it mostly just sits there waiting to be told what to do.

You can however test the performance of a system that includes Oracle and an application. Your proposed test is a simple select query; which is an application running on a database server, not a particularly complex application, it must be said.

What you need to do is decide _what_ you're testing, and why you're doing it. Testing how fast some rows come back from a select tells you nearly nothing, certainly not enough to extrapolate up to a larger application or compare two databases running the same application.

You can design a benchmark that tests the performance of a database system in a reasonably realistic manner that allows some extrapolation and comparison. For an idea of the sort of thing that makes an acceptable benchmark to compare databases have a look at this article on TPC-D:

<http://www.tpc.org/information/other/articles/TPCDart_0197.asp>

Note that even that in only even vaguely valid in the domain of DSS databases (also note the benchmark has been withdrawn). Actually, all it measures is the performance of the RDBMS server executing the TPC-D benchmark, but there's an assumption made that this is fairly closely correlated to the RDBMS server's performance when executing other DSS applications. The TPC would like you to make this assumption at least :-)

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/