Performance Checklist for new Application

Performance Checklist for new Application
=========================================

For a new Application, build a list of tasks that—if performed correctly—will
increase the chance of optimal performance in production and—if there is a
problem—enable rapid debugging of the application:

1. When you create the control file for the production database, allow for growth by
setting MAXINSTANCES, MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, and
MAXLOGHISTORY to values higher than what you anticipate for the rollout. This
results in more disk space usage and bigger control files, but saves time later
should these need extension in an emergency.

2. Set block size to the value used to develop the application. Export the schema
statistics from the development/test environment to the production database if the
testing was done on representative data volumes and the current SQL execution
plans are correct.

3. Set the minimal number of initialization parameters. Ideally, most other
parameters should be left at default. If there is more tuning to perform, this shows
up when the system is under load.

4. Be prepared to manage block contention by setting storage options of database
objects. Tables and indexes that experience high INSERT/UPDATE/DELETE rates
should be created with automatic segment space management. To avoid
contention of rollback segments, automatic undo management should be used.

5. All SQL statements should be verified to be optimal and their resource usage
understood.

6. Validate that middleware and programs that connect to the database are efficient
in their connection management and do not logon/logoff repeatedly.

7. Validate that the SQL statements use cursors efficiently. Each SQL statement
should be parsed once and then executed multiple times. The most common
reason this does not happen is because bind variables are not used properly and
WHERE clause predicates are sent as string literals. If the precompilers are used to
develop the application, then make sure that the parameters MAXOPENCURSORS,
HOLD_CURSOR, and RELEASE_CURSOR have been reset from the default values
prior to precompiling the application.

8. Validate that all schema objects have been correctly migrated from the
development environment to the production database. This includes tables,
indexes, sequences, triggers, packages, procedures, functions, Java objects,
synonyms, grants, and views. Ensure that any modifications made in testing are
made to the production system.

9. As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.

10. Start anticipating the first bottleneck (there will always be one) and follow the
Oracle performance method to make performance improvement.