Wait objects: IO

If you had many db file * reads and find some entries with segment type = table in the Top_N IO objects, these may need some|more|better|other indices. Use Statspack or Oracle Enterprise Manager Diagnostics Pack to find out more.

How to find out about missing indices?

If you know all applications accessing your DB instance well, you'll just have to analyze their statements execution plans and look for full table scans. If you find any, analyze the statement itself regarding its WHERE and GROUP BY clauses. Make sure that all columns mentioned there are member of a common index (i.e. all affected columns of the same table must be in one index, since only one index per table and statement will be used for the query). Run an ANALYZE TABLE...ESTIMATE STATISTICS for the tables in question plus their indices.

If you want to avoid to much handwork, I recommend you to install Oracles StatsPack. Run hourly snapshots for a couple of days, and then use the fts_plans.sh provided by OSPRep (the companion program of OraRep, you'll find it at the IzzySoft website for free download). This script will take the statistics and write all statements that caused full table scans together with their most recent execution plans into a separate HTML file. For these statements, follow the steps above.

What else can be done?

Things that may help to avoid some of the db file * read wait events are:

  • Tune the SQL statements used by your applications and users (most important! See above for this.)
  • Re-Analyze the schema to help the optimizer with accurate data e.g. with dbms_stats
  • Stripe objects over multiple disk volumes
  • Pin frequently used objects
  • Increase the buffer caches