Oracle Performance Checklist

Oracle Tips by Burleson Consulting
Don Burleson

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 plan.sql), an spreport during peak times, and statspack_alert.sql output.

3 - Look for silver bullet fixes:
http://www.dba-oracle.com/art_dbazine_911.htm

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.

If you do Oracle tuning, you will need Mike Ault collection of 400+ scripts. Mike has priced his collection of 465 scripts at $39.95, less than a dime per script. You can download them immediately at this link:

http://www.rampant-books.com/download_adv_mon_tuning.htm

Regards,

    Don Burleson
    www.dba-oracle.com
    www.remote-dba.net

��