Performance Tuning - Question 092

What are two main OLTP requirements? (Choose two)

A. Use bind variables rather than literals in your SQL code.

B. Analyze your tables regularly to refresh optimizer statistics.

C. Create multiple small rollback segments as opposed to a few big ones.

D. Create indexes on all columns that are regularly used in query predicates.

E. Set up appropriate default storage parameter values for dynamic (implicit) space allocation.

Answer: C, E

Explanation:

Due to high level of DML activity on the OLTP system you need to use more small rollback segments rather than few large rollback segments. To avoid the performance load of dynamic space allocation, you need to allocate space explicitly so tables, clusters and indexes.

Incorrect Answers

A: This one is not main requirement of the OLTP system.

B: Table and index statistics should be gathered regularly if the CBO is used because of data volumes tend to change quickly in OLTP systems. But this requirement cannot be considered as main OLTP requirement.

D: It is not recommended to create indexes on ALL columns that are regularly used in query predicates because of multiple INSERT and UPDATE operations will cause overload on the database trying to rebuild all indexes after each transaction.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 187-188

Chapter 3: SQL Application Tuning and Design