BestPerf: Performance & Best Practices

The Peoplesoft Payroll application is I/O latency sensitive. That is the performance of this workload hinges on the reducing the response time of physical I/Os, primarily single block reads. This can be easily seen by from the 'Top 5 Timed Foreground Events' section of the Oracle AWR Report:

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
db file sequential read	6,183,399	18,104	3	68.80	User I/O
DB CPU		7,762		29.50
enq: TX - index contention	81	164	2028	0.62	Concurrency
read by other session	230,755	103	0	0.39	User I/O
cursor: pin S wait on X	2,241	42	19	0.16	Concurrency

This represents a test run of the Pay Confirmation step where the database is configured on a fibrechannel cached storage array. 'db file sequential read' is a counter of single block reads executed by Oracle foreground processes. The 3ms average response time is respectable for a disk-based I/O subsystem but the sheer number of read requests (Waits) makes it account for 68.8% of the processing time in Oracle. To improve the performance of this workload we must reduce the number and/or the response time of physical reads.  This can be managed by increasing the size of the Oracle buffer cache in the SGA to eliminate reads. Unless you have enough memory to cache the entire database you will still have to perform physical reads and this is where the sub-millisecond performance of the F5100 Flash Array becomes a factor.

50/50 read/write microbenchmark tests have shown that each FMod in a F5100 is capable of about 10,600 IO/sec for small block reads and 426,000 IO/sec for a half-capacity F5100, which was the configuration used in the Peoplesoft Benchmark tests. I/O response time will be impacted as IO/sec increases. The Peoplesoft Payroll application averages 3100 reads/sec and 2400 writes/sec to datafiles containing tables, indexes. This easily fits within the F5100's capabilities to deliver the fastest I/O response times.

The database was reconfigured on a half-capacity F5100 Flash Array. Solaris Volume Manager (SVM) was used to consolidate the FMOD's into a 32-way metadevice and an 8-way metadevice. The 32-way metadevice was used for the tablespaces containing tables and indexes. The 8-way metadevice was used to contain the undo tablespace. The Oracle Redo Log files were assigned to an SVM 6-way metadevice created on the J4200 Storage Array. This layout resulted in less than 1ms I/O response times when accessing tables and indexes, more often than not on the order of 400-500 microseconds. Undo tablespaces I/O response times averaged 2.5ms. Redo Log I/O response times on the J4200 storage array averaged 4ms.

Here is the 'Top 5 Timed Foreground Events' section of the Oracle AWR Report after reconfiguring the database on the F5100:

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU		7,042		60.72
db file sequential read	5,784,794	4,373	1	37.71	User I/O
SQL*Net message to client	38,581,058	58	0	0.50	Network
read by other session	135,957	48	0	0.42	User I/O
enq: TX - index contention	129	31	238	0.27	Concurrency

This snapshot from Oracle Enterprise Manager shows minimal latency on small block reads:

The time Oracle spent executing single block physical reads decreased by 39%. A few simple guidelines were followed in laying out the database on the F5100:

- Only allocate files that perform I/O's on a 4k boundary alignment on the F5100. For Oracle this means Redo Logs should not be allocated on the F5100. In this case they were allocated on the attached J4200 Storage Array.
- Follow SAME (Stripe and Mirror Everywhere) guidelines.
- Segregate 4k aligned write intensive files. In this case the Undo tablespace was moved to its own set of FMods.

This resulted in a 14% processing time improvement of the Peoplesoft Payroll application as compared to a highly optimized configuration of the database on fibrechannel cached storage arrays.