Improving the performance of single instance Oracle on file systems, January 2008

15
In the Oracle 10g benchmark, the reduction in performance (without ODM) was still significant
though lower. There was a reduction in performance of about 68%
7
for the same workload of 80
clients.
The benchmarks used different OLTP applications modified for the two versions of Oracle, with slight
differences in the number of data files and database warehouse structure built. Therefore, the
performance difference of I/O configurations within a benchmark is more significant than comparing
results for each configuration between the Oracle 9i and 10g benchmarks.
To understand how closely an ODM configuration performs relative to a raw volume configuration,
Experiment 2 found that transactions decreased approximately 8% relative to raw volumes on VxVM,
or about 10% relative to raw volumes on LVM.
Measuring the transaction rate on the two volume managers, the experiments found an approximate
4% difference in performance, with LVM performing better due to lower %system CPU utilization.
Test results and production environments
In production environments, performance improvements might be gained through an iterative process
of measuring, analyzing, and resolving each bottleneck encountered.
For example, a bottleneck might be eliminated by increasing physical memory and then increasing
the I/O capability of the secondary storage configuration. This might be followed by other steps such
as partitioning data into more files, tuning SQL queries, or changing instance startup parameters (for
example, to increase the SGA size or reduce the frequency of checkpoints).
Actual performance can also depend on the workload pattern and server configuration. An 8-10%
performance difference was measured for ODM versus a raw volume configuration. In a production
environment, this difference may higher or lower depending on other factors, such as the percentage
of write I/Os. It is likely that other applications will be active, using CPU cycles that might also reduce
the transaction rate.
Nevertheless, the test results provide a clear view of how well the different subsystems behave in a
controlled lab environment. They isolate other factors that could mask the comparative performance of
individual I/O subsystems and provide insight into the benefits of using ODM.
Conclusions
Even with carefully designed server configuration, the lack of asynchronous I/O on a file system can
hinder performance. As measured in the benchmarks, ODM can significantly improve performance of
a file system based Oracle database installation.
For the highest level of performance, raw volumes provide the highest transaction rate. However, the
tradeoff is decreased manageability. Where the manageability of a file system is a key factor, ODM
provides a valuable bridge, and the decision in balancing performance versus manageability is much
less difficult.
7
For a workload of 80 clients, the TPM measured was 43,250 for VxFS/VxVM+ODM and 13,563 for VxFS/VxVM. This is a relative drop in
performance of approximately 68%.