Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Friday, February 24, 2012

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.

No comments: