As a consultant, I follow a standard procedure when I come into a new
shop with a database that I have never seen before. My goal is
to quickly identify and correct performance problems. Here is a
summary of the things that I look at first:
1 - Install STATSPACK first, and get hourly snaps working.
2 - Get an SQL access report (or plan9i.sql), an spreport during
peak times, and statspack_alert.sql output.
-
partial schema statistics (using dbms_stats)
-
missing indexes
-
optimizer_index_cost_adj=(between 20-60 for OLTP systems)
-
optimizer_index_caching=xx (depending on RAM for index caching)
-
optimizer_mode=first_rows (for OLTP)
-
hash_area_size too small (too many nested loop joins)
4 - Fully utilize server RAM - On a dedicated Oracle server, use
all extra RAM for db_block_buffers (db_cache_size) less PGA's and
20% RAM reserve for OS.
5 - Get the bottlenecks - See STATSPACK top 5 wait events - OEM
performance pack reports - TOAD reports
6 - Look for Buffer Busy Waits resulting from table/index
freelist shortages
7 - See if large-table full-table scans can be removed with
well-placed indexes
8 - If tables are low volatility, seek an MV that can
pre-join/pre-aggregate common queries. Turn-on automatic query
rewrite
9 - Look for non-reentrant SQL - (literals values inside SQL from
v$sql) - If so, set cursor_sharing=force
10 - Monitor
over time - The ongoing STATSPACK reports should show any new
performance problems.
