Troubleshooting

Improving Oracle OLTP database performance with Dell Fluid Cache for DAS

2.1 The BIOS System Profile was changed to ‘Performance’. Rest of the BIOS options were

at default settings.

2.2 For details on VD configuration, refer to Table 4.

2.3 The Oracle ASM disk ownership and permission settings were configured in the

/etc/udev rules. For more details, refer to Appendix C.2.1. Udev settings for

baseline configuration.

2.4 The OS kernel parameters were tuned for performance testing. For more details, refer

to Appendix C.4.1. Kernel parameter settings.

2.5 Oracle’s OS Watcher (OSW) was installed to capture the performance metrics (CPU and

memory usage) of the host and the storage array (IOPS).

3. Oracle database setup: Oracle 11gR2 grid and database software for single node single

instance was installed

3.1 For details on configuration of ASM disks and ASM disk groups, refer to Table 7.

3.2 Oracle’s Automatic Shared Memory Management (ASMM) feature was enabled.

sga_target and sga_max_size were set to 8GB to stress the storage disks by

generating more physical I/Os than logical I/Os from the main memory

3.3 The database parameters were tuned for the performance test. For more details, refer

to Table 10.

4. Benchmarking tool and TPC-C database schema setup:

4.1 Dell Quest Benchmark Factory (BMF) was used to create a TPC-C test Oracle schema

data set of 450GB (300GB tables + 150GB Indexes) by using a benchmarking scale

factor of 4700 and was loaded into the DATA_DG

5. Database Stats collection: Database stats on the TPC-C schema was manually collected one

time in the beginning using the below SQL Query

SQL> exec dbms_stats.gather_schema_stats('QUEST', estimate_percent =>15);

where QUEST is the 450GB TPC-C schema created from BMF

6. Database schema backup:

6.1 The TPC-C schema was backed-up and exported using Oracle’s Data Pump on to two

local 300GB 15K SAS HDDs on the database server for later use.

6.2 In order to quickly restore the database to its original clean state after each of the

test iterations, Oracles flashback guaranteed restore point was created. For more

details, refer to the wiki article How to quickly restore to a clean database using

Oracle’s restore point.

7. Test load setup: BMF was used to generate the OLTP workload against the 450GB TPC-C

database schema.

7.1 OLTP workload was configured with a user load range of 500-5000 in steps of 100. The

max userload value was determined by doing sample tests to ensure that the highest

user load was able to stress the solution beyond the 2 seconds average response time

7.2 The sampling duration for each iteration was unchanged from the default value of

4mins.

7.3 The think time and the keying time latency setting for each of the TPC-C transaction

mix was set to 1/10

the default value.

8. Pre-test setup: