Don't Forget Optimizer Parameters!

One factor that dramatically affects the processing of Oracle queries is the setting of the OPTIMIZER_MODE initialization parameter. By default this parameter is set to ALL_ROWS which will attempt to optimize the SQL statements to return all rows form the queries in the minimum amount of time. ALL_ROWS will choose full scan options such as sort-merge-join and hash-join over paths that return initial rows the fastest such as a nested-loop. While ALL_ROWS may be a good setting for decision support or data warehouse databases in online transaction processing (OLTP) systems most queries will return very few rows, so one of the settings of the FIRST_ROWS_(n) value for the OPTIMIZER_MODE should be utilized. The (n) settings are 1, 10, 100 and 1000. In my testing using a TPC-C-OLTP type benchmark (using Benchmark Factory), varying the FIRST_ROWS setting from 100 down to 1 changed the results from 4500 up to 6900 tps, nearly a 53% improvement. From ALL_ROWS to a setting of FIRST_ROWS_1 the performance improved from 4200 to 6900 tps, a 64% improvement. This shows that OPTIMIZER_MODE must be set correctly to get the best performance for your system. In this case the TPC-C is a pure OLTP system so a FIRST_ROWS_1 Setting makes sense. The following graph shows the effects of switching the OPTIMIZER_MODE settings.



Figure : Effects of changing OPTIMIZER_MODE

All of these results were generated using a single RamSan630 for all database files. Utilizing a 24-10K rpm disk array the best result obtained was around 600 tps.