Overview

Tuning parameters described in this book are initialization parameters set for each database at creation time. Depending on the system, these parameters can then be modified or not; possibly these modifications take effect only after the server has been shut down and restarted.

Here is a short list of the main configuration parameters.

• Log file size should be big enough to hold all updates between dumps.

• Buffer size should be as big as possible without spilling over to swap space.

• Block size (unit of transfer between disk and memory) bigger for scan-intensive applications. Should be smaller for online transaction processing.

• Log buffer size unit of transfer to the log. Should be big enough to allow group commit.

• Group commit size.

• Prefetching size should be large enough to make scanning efficient roughly the size of a track.

• Checkpoint interval a balance between warm start recovery and overhead.

• Degree of parallelism if too large, blocking can occur; if too small, there can be insufficient parallelism.

As an example, we present the configuration parameters used for TPC-C and TPC-H benchmarks with the three systems we are focusing on. ^[1]

E.1 Oracle

Here are the configuration parameters used for the Sun Starfire Enterprise 10000 with Oracle 9i (on Solaris 8) TPC-H benchmark published in April 2001. The initialization parameters are regrouped in a file, usually init.ora, that can be modified directly new parameters take effect when the server is restarted. Oracle Performance...