10g optimizer_mode setting - all_rows vs first_rows_1
567263Sep 11 2007 — edited Sep 21 2007I would like to get someone's opinion on what might be the optimal setting for the optimizer_mode parameter in 10g. The majority of the time our application returns a relatively small number of records in our queries (normally 50k or less). In my testing in the past I have seen the all_rows setting perform rather well.
However lately I have run into several performance issues with clients of ours that tend to be resolved when I alter the optimizer_mode setting to first_rows_1. I have even used other first rows settings (ie first_rows_10, 100, 1000) and they have performed better, but not nearly as well as first_rows_1.
Can anyone provide some insight on this? It is much appreciated.