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

"Brian Peasland" <dba_at_remove_spam.peasland.com> schreef in bericht news:401ED37F.3C755AAC_at_remove_spam.peasland.com...
> > 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"

depends on the oracle version you're using there are some nice gui tools mostly above 7 ......

Ed Received on Mon Feb 02 2004 - 18:03:57 CST