Allocate a tablespace�s physical data files in standard sizes. This will make swapping the data files a very easy task when you begin the tuning cycle for I/O balancing.

A database file is a database file. Place them all on a central directory for that disk.

Install from CD-ROM whenever possible. Purchase a CD-ROM if you don�t have one. It can save space during an Oracle install.

Set maxdatafiles to a very high number. Too many sites use the default and run out. The default is too low.

Redo logs are a single point of failure for the database: if you lose one, you may lose your entire database and have to restore a copy from a previous backup. To protect yourself, mirror your redo logs.

In the default initialization parameter file, �small� means the number of users and not database size.

Always choose central table support when installing Oracle Reports. Local table support is a DBA nightmare.

Each database should have a minimum of three control files. Place the control files on separate physical devices.

Note:If you set the initialization parameter to 0 while running Oracle7 with the distributed option, you will not be permitted to perform any distributed transactions, and the RECO process will not be started.

Tip: Inspect instance alert log files for error messages regarding online redo log groups. It is the easiest way to determine whether you have enough redo log files for your database. If Oracle cannot reuse a redo log because cleanup is not yet completed, you may need more redo logs.

Note: After adjusting DB_BLOCK_BUFFERS in the init.ora file, the database must be restarted for the new value to take effect.

Always create separate tablespaces for your tables and indexes and never put objects that are not part of the core Oracle system in the system tablespace.

Ensure the data tablespaces and index tablespaces reside on separate disk drives.

Know how your data is be accessed by end users.

Whenever possible, place objects that are most often referenced simultaneously and frequently on separate disks.

When your database contains large objects that will have users concurrently accessing different data elements, striping the objects over multiple disks would be helpful.

Create at least two user-defined rollback tablespaces on separate disks to hold your rollback segments.

Order the rollback segments in the initialization parameter file so that they toggle between multiple disks.

Create at least one tablespace whose exclusive use will be for temporary segments.

Put your redo logs on a disk that has a low incidence of reads and writes.

Use a proven analytical method to size your tables and indexes.

Assess the demands placed on the CPU by the users during busy time periods.

Assess the demands placed on the CPU by the users during not-so-busy time periods (evenings and weekends).

Assess the amount of idle time of user sessions.

Assess the ongoing CPU requirements of the Oracle engine.

Assess the balance between CPU time required to support user processes versus system services.

Make the shared pool large enough to eliminate unnecessary repassing.

The shared pool should be large enough to achieve an 80 percent or greater hit ratio on the data dictionary cache. Since the caches are always empty when the database starts up, measure after the database has been primed.

Activate the checkpoint process if you have heavy online transaction processing. Too few checkpoints can cause system degradation. When in doubt, enable it.

If the redo logs are large, set the checkpoint interval to occur at evenly spaced lengths throughout the logging process. This will reduce the startup time of the system after the crash.

If performance is your concern, set LOG_CHECKPOINT_INTERVAL to 0 (the default). Set its values to a size greater than the physical redo log file. Then checkpoints will occur only when the redo log file fills up -- we like to see that happen once every two hours.

Remember, Oracle is one of many processes that must all live, share, and breathe all available resources. In addition, the SGA should never take over 50 percent to the available memory.

Set the DB_BLOCK_BUFFFERS as high as possible for your operating system environment in order to hold as much data in memory as possible. But don�t induce excessive operating system paging and swapping.

Tuning is about trade-offs. As the DBA, you must constantly struggle with all the available resources and decide what the equitable split is for your situation.

Only use the DB_BLOCK_LUR_STATISTICS to determine if more or less DB_BLOCK_BUFFERS should be used, and not during normal operations, because of the negative impact it has on performance.

Overestimate the number of DML_LOCKS that will be needed, because if you run out, its a show stopper. Better too many than not enough.

Size the LOG_BUFFER properly to reduce I/O to the redo logs.

If the objective is not to limit the number of users on the system, overestimate the number of concurrent processes.

Always name your rollback segments in the initialization parameter file. Rollback segments names in this file are private.

Always place your rollback segments in their own tablespace.

Always create a special rollback segment designed to handle your large transactions (commonly referred to as the �update from hell�).

Be generous with the OPEN_CURSORS parameter, especially in an Oracle Forms application environment. This will be a show stopper for your application if it is set too low in the in init.ora file.

Size your SORT_AREA_SIZE to fit the need of the users. This is a big user memory and also a big help with performance.

RELOAD represents entries in the library cache that were parsed more than once. You should strive for the goal of zero RELOADs. The solution is to increase the init.ora file SHARED_POOL_SIZE parameter.

GETHITRATIO and PINHITRATIO should always be greater than 80 percent. If you fall below this mark, you should increase the value of SHARED_POOL_SIZE in the init.ora file.

Your data cache should have a hit ratio of greater than 80 percent.

All resource waits are bad and should be avoided. If you see �undo� rollback segment waits, then increase the number of private rollback segments your database contains.

If you see data block waits, you need to increase your freeness parameter on heavily inserted tables. You must re-create the table to change an existing freeness setting. When in doubt, we recommend setting freeness to 2 on tables you suspect may become insert bottlenecks.

If you are on an operating system that allows multiple DBWR process, such as UNIX, then increase the number of database writers you have.

When using UTLBstat/UTLEstat remember they will only give you the Oracle I/O perspective. Be very careful you don�t overlook operating system files that may be on the same device.

I/O is a major bottleneck for performance. You should always distribute your I/O as evenly as possible.

If you determine you have redo allocation latch contention, make the init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE smaller; this will cause Oracle to use more redo copy latches.

If you have redo copy latch contention, make the init.ora parameter LOG_SIMULTANEOUS_COPIES larger. Another alternative is to tell Oracle to prebuild redo entry information before requesting the latch. You do this my setting a value for the LOG_ENTRY_PREBUILD_THRESHOLD entry.

If you are detecting rollback segment contention, add more rollback segments. When in doubt, add more.

Run UTLBstat/UTLEstat often with TIME_STATISTICS = TRUE in the init.ora file and examine the output and make changes to the init.ora file as deemed necessary.

Chaining should be avoided at all costs, because it doubles the amount of I/Os needed to accomplish retrieving the data.

Avoid chaining by using an appropriate pctfree parameter with table creation commands.

Remove as many chained or migrated rows of data as you can after identifying them with the analyze command.

Always put redo log files on a separate disk from your datafiles.

The lowest number specified by any of these three items (O/S restrictions, DB_FILES, or maxdatafiles) is the one that affects the other two.

Common show stoppers:
Concurrent open file limit needs adjusting. database down an extra 2 hours after backup. database coming down to activate new values. database down to rebuild control files. need to recreate a database delayed. excessive disk I/O to satisfy requests for block IDs to create new rows. table needs more space and more extents cannot be allocated. unpredictable and runaway amounts of space being eaten up by tables. runaway space consumption on indexes pointing to tables undergoing high delete activity. block contention -- transaction space limited in data blocks. wait time for previous transactions to terminate. applications behaving in unusual ways all of a sudden. archive process unable to complete redo log copy. DBA forgot to take database out of restricted mode and nobody can log on. applications raising Oracle errors due to too few DML_LOCKS.

Be aware of limits that may be placed on the number of open files by your operating system. Work with your hardware personnel to ensure operating system limits are not exceeded by Oracle�s requirements.

Set the maxdatafiles parameter in the create database command to a number that will permit you to double the number of database files your instance uses.

When maxdatafiles needs to be increased, use alter database backup controlfile to trace.

Learn how to create a database and practice before you are forced to do so during a real-life emergency.

If the number of waits for free blocks is greater than 1 percent of the total number of requests, consider increasing the freeness parameter on your insert and update intensive tables.

Have a sufficient number of freeness entries for a table to accommodate the number of concurrent processes that will be inserting data into that table.

Monitor expansion of tables in your database and plan for table reorganization before you run out of extents.

Learn how to re-create your database using the create database command. If and when you want to change the block size, re-creating your database is only way to do it.

If you use Oracle on a platform that permits it, set your Oracle block size to 4K or even 8K.

Set the pctincrease to 0 when creating tables. Look at existing tables and reset this value to 0.

When you require additional space for a table larger than the next extent, use the alter table allocate extent rather than adjusting the storage parameters for the table.

When the deleted row space in an index is over 20 percent of the space being used in the index, you should drop and re-create the index to reclaim the unused space.

Set initrans high enough to at least accommodate the expected number of concurrent transactions a table experiences.

Set maxtrans to the value of initrans +2.

When raising initrans and maxtrans for a table, raise it for the data and index components at the same time. After all, inserting a row into a table also requires creation of an additional index entry.

Monitor the available space on the directory that contains your archived redo logs. If its utilization climbs over 90 percent, it may require immediate attention.

Dedicate enough space in your archive logs destination to hold at least two full days of archived redo logs.

Archived redo logs should be backed up every day and deleted afterwards. Do not rely on manual purging of the archived redo log destination.

Take your database out of restricted access daily before all your users start logging on.

There must be a character-by-character match between the statement being examined and the one already in the shared pool.

The objects being reference in the new statement are exactly the same as those objects in a statement that has passed the comparison in the previous rule.

If bind variables are referenced, they must have the same name in both the new and existing statements.

Know your data. Figure out the columns with good selectivity. Selectivity is the percent of rows in a table that have the same value. Columns with low selectivity are good candidates for indexing.

The only candidates for indexing are columns that are mentioned after WHERE and AND in SQL statements.

Even if a columns contents contain a wide range of values, do not index if it is only ever referenced in a function such as string manipulation, or conversion.

When looking at and using the cost-based optimizer, ensure that your tables are analyzed to gather important column statistics that aid in the selection of indexes to created.

When choosing candidate columns for composite indexes, look at those columns that are used in where and and together during your application. if they are retrieved in when and and separately as well as together, two single column indexes may be better.

A composite index will only be used to satisfy a query when the leftmost column in that composite index is mentioned in WHERE or AND.

Run export and import with large buffer sizes. Pre-create tables before an import.

Use the Oracle utilities export and import to help protect against data loss. As a DBA, you can spend more of your time on a database performance-tuning exercise when you have reliable, tested backup systems in place.

Do online backups on all or part of your database when running in ARCHIVELOG mode. Do the backup one tablespace at a time to reduce overhead. This will minimize the impact on your online systems.

Ensure that your control file is part of the online backup. Copy it to the same destination as your archived redo logs at the END of your tablespace backup.

Run your database in ARCHIVELOG mode. This allows protection against a wide range of problems with hardware and user error. It helps minimize instance downtime.

Make an operating system text copy of your control file using the alter database backup controlfile to trace statement. Incorporate this into your backup routine.

A recovery procedure is only goof if TESTED. Run a number of mock recovery situations when first putting recovery mechanisms into place. The documentation explains recovery and what-to-do when very well but there could be some surprises when recovering form a real-life crash.

Inspect your database trace files daily using an automated process. Oracle writes these files for your information --- use them.

Examine database free space as part of your tuning process, using an automated process.

Ensure that users are not using the SYSTEM tablespace as a work area for sorts that use disk space as well as memory.

Monitor the processes on your machine for orphans that should be stopped. You may need the assistance of the system administrator for this.

If you are using the multi-threaded server option watch out for runaway dispatcher processes. Then can consume massive amounts of CPU time.

If your operating system supports both two-task and single-task architecture, investigate using Oracle programs in single-task.

Use truncate table and truncate cluster when appropriate. It is much faster and needs much fewer resources to complete.

To tune the performance of large SQL transactions, use set transaction use rollback segment.

Issue frequent commit statements in your programs to free up resources.

Use explicit cursors in all your Pl/SQL blocks.

Investigate using indexed clusters to speed up access to tables commonly joined together on a standard set of matching columns.

Use documented formulae for calculating space parameters for indexed clusters based on row characteristics, volume of data, and the average number of rows per table per cluster index value.

If the approximate conditions have been met, use hash clusters to enhance the performance of applications.