Like coding, tuning is an area about which hundreds of pages have been written. But I can distill this mass of material into a few good practices.
It is a good practice to perform proactive database monitoring. Proactive database monitoring means that you must, at a minimum:
- Monitor for space usage.
- Monitor waits.
- Monitor for execution times for standard queries/procedures.
- Monitor for statements that use excessive disk I/O.
- Monitor for statements that use excessive memory I/O.
- Monitor for excessive recursive SQL.
- Monitor latch and lock usage.
Ideal tools for monitoring include Quest Spotlight, Performance Analyzer, Toad, Oracle Enterprise Manager, Statspack and AWR.
All the monitoring in the world does you no good if no one takes the time to look at it and analyze it. But continually watching a screen is probably the most boring thing I can imagine. So it is a good practice to use an automated alert mechanism that uses thresholds to alert you by email or pages, before a problem is noticed by users.
It is also a good practice to build in alert mechanisms for security-related issues such as multiple unsuccessful login attempts, login attempts to secure accounts, password invalidations and attempted accesses to restricted tables or restricted types of access to specific tables. It is also good to receive some sort of alert if unauthorized tools are used to access the database. Oracle's auditing and login triggers can be utilized for this, as can InTrust from Quest Software.
I don't know how many projects I have been on where the wrong type of disk RAID was in use. It is a good practice to use the right RAID level for the right datafile type. Oracle recommends:
- Use RAID 1+0 whenever possible for data and indexes.
- Use RAID 0 for redo logs and archive logs.
- Use RAID 5 only if it is not possible to use RAID 1+0.
In Oracle9i and Oracle 10g it is a good practice to use multiple block sizes; this allows you to tailor the block size to a specific type of access. Place tables and indexes in tablespaces according to access. For single block read type OLTP access, use 8K block sizes. For full table scan access (such as with data warehouses) use 16-32K block sizes. Use 8-16K block sizes for index lookups. For indexes that are scanned or bitmap indexes, use 16-32K block sizes.
It is also a good practice to use multiple buffer pools properly. Use default, keep and recycle pools in 8, 8i, 9i and 10g. Use keep for lookup tables and indexes (frequently accessed small objects). Use recycle for objects whose data won't be reused multiple times (blob or large full table scan tables).
It is a good performance practice to use correct index types. Do not use bitmap indexes for frequent insert, update and delete (IUD) tables in any version. Do not use B*tree indexes for STAR schema facts in 9i and 10g. A word of caution: index-only tables (IOTs) rarely improve performance in any version. Only use reverse-key indexes in RAC environments where index header block contention is an issue (be careful, some third party tools create these by default).
As with PL/SQL, with any code it is a good practice to pre-tune all SQL statements. No matter how good your table and index layout is, if the SQL is not properly constructed to utilize it, your performance will suffer.
Return to Mike Ault's Oracle "good practices."
About the author
Mike Ault is an Oracle database specialist at Quest Software and a recognized Oracle expert with over 16 years' experience as an Oracle DBA and consultant in a variety of industries and companies. A prolific author, Mike has published over 20 Oracle-related books including Oracle Administration and Management, Oracle DBA OCP ExamCram and Oracle10g Grid and RAC. He is a regular contributor to trade publications including Oracle magazine, and frequently presents at major Oracle conferences such as IOUG.
