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

> The only measure (that I've heard) of how hard an Oracle database is
> working is the current CPU load on the box.

Database systems tend to be (but not always) more I/O bound than CPU bound. So looking at just the CPU load on the server is doing a disservice to the Oracle database and the work it has to do to stay up and running.  

> For instance, a DBA would tell me: "spiffy, the Oracle server has two
> 0% idle CPUs! We need to kill some sessions or tune queries."

This may be true, or it may not be.  

> I assume they're getting the information by running 'top' or 'sar'
> from a shell on spiffy.

One of the nice indicators on 'top' will be a line that will tell an aggregate on overall CPU consumption, overall I/O consumption. How much time is spent on kernel activites, and swapping activities. The line will look similar to the following:

CPU states: 75.6% idle, 10.8% user, 9.8% kernel, 3.8% iowait, 0.0% swap

However, there is a danger in looking at such aggregates and using it to determine how "busy" one piece of software on this system is.  

> 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.

> 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.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"