Re: is it possible to query an oracle database to find out how hard it's working?

Thanks, Brian, for all the helpful information! You brought up many very important points.

I realize my example was *extremely* simplified, but hopefully not simplistic.

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<401ED37F.3C755AAC_at_remove_spam.peasland.com>... [...]
> > Is there an SQL query or PL/SQL code block that could be run to
> > determine some measure of how busy a database server is?
>
> I always look at such queries with skepticism. You could query V$SYSSTAT
> for various instance-level statistics, but there is danger in looking at
> these aggregate totals as well. And how do you define "busy"? Put a load
> on one server and that server may be busy. Put the same load on another
> server and that server may not be busy. Tons of factors at work here.

V$SYSSTAT may provide information that is useful enough, I'm just looking for some ballpark figures on database performance. Right now the only thing I can think of is actually running 'sar' or 'top' from PL/SQL or Java and parsing the result. The DBAs don't want a particular query to run if the database server box has less than 10% idle CPU time, becuase, empirically, this is when the database is "too busy".

V$SYSSTAT has a TON of information. Would you suggest any info in particular?

> > This would be
> > quite useful to predetermine if some expensive queries would be
> > possible before actually starting them.
>
> Probably the best way is to actually spend time tuning the queries in
> development before you run them in production. Find out how much work
> (I/O, etc) that query is doing before you move it to a production
> environment. As a hint, look at V$SQL for the number of rows processed,
> elapsed time, etc.

I wholeheartedly agree! I always explain and tune queries. Some queries are just expensive.

Thank you,
-Adam Received on Tue Feb 03 2004 - 13:27:48 CST