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
|