Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Monitoring

Re: Performance Monitoring

From: joel garry <>
Date: Wed, 26 Sep 2007 13:54:08 -0700
Message-ID: <>

On Sep 26, 1:41 am, Stefan Wetter <> wrote:
> Hello NG!
> I'm pretty new to this theme. My task is, at the moment, to improve the
> performance of a database of one of our customers.
> I monitored the database-activity with the help of some tools and find
> pretty much DISK-IO (read). I think that should be lower. To the same
> time, the buffers are only filled half.
> I think i should find out, wich tables cause the physical reads (, to
> examine which process does need them to optimize the clients if necessary.
> If not, i would change the storage clauses of that tables to use another
> part of the buffers.)
> So, my question at this time is: how can i find out which tables (or
> maybe indexes) cause the disk-io?
> Thank you very much in advance!
> Stefan

  1. Read Concepts manual.
  2. Understand that most performance issues come from application issues. For example, if some silly SQL reads an entire table to get a few rows, you wil likely have a lot of unnecessary I/O that won't fill up the SGA.
  3. Read the Performance manual.
  4. Understand the optimizer. It can only use the information it is given. If the statistics it uses are wrong, non-existent, or skewed in a manner the optimizer doesn't know about, it can choose a silly plan for accessing the data. Sometimes a full table scan is not silly.
  5. Understand what plans are and how to use them to understand 4.
  6. Understand what statspack (or other tools such as Jerome mentioned) can tell you.
  7. Understand when, how and why to use tracing.
  8. Understand what waits are and how to evaluate them.
  9. Read and work through books and articles by Jonathan Lewis, Tom Kyte, and Cary Milsap. (And I still like Lawson's book for the basic how-to-dba implicit in your question, though it is out of date now).
  10. Understand why rules of thumb can be a bad idea for improving the database of customers.
  11. Understand that tools based strictly on Oracle can be a bit misleading from a systems standpoint, and systems tools can be misleading from Oracle's viewpoint. Simply knowing you have a lot of reads does not mean anything is wrong, after all, what is a database going to be used for? A proper tuning methodology will figure out what critical bottlenecks are, and what to do about them.
  12. Understand the basics. For example, if you have sequential writeintensive archive writing interfering with random reads and writes for undo and everything else, thrashing a SAN cache, you probably have a configuration problem. If you have multiple users accessing data, you need to understand how Oracle handles the issues involved.
  13. Create clear metrics for performance improvement.
  14. Read Concepts manual.
  15. Iterate.

Welcome to the group! See


-- is bogus.
Received on Wed Sep 26 2007 - 15:54:08 CDT

Original text of this message