Oracle10g Performance Checklist (讀書筆記)

To assist in the rollout process, 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:[@more@]

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. See Chapter 4, "Configuring a Database for Performance" for information on parameter settings in an initial instance configuration.

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. See Chapter 4, "Configuring a Database for Performance" for information on undo and temporary segments.

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. For more information, see Chapter 3, "Performance Improvement Methods".