Hi Lex, Interesting. I am dealing with a new version of a COTS package that does that in a login trigger. (sets FIRST_ROWS that is) This had me rather confused for a few minutes when I would get a good plan for some problem queries when logged in as myself, and a very poor plan for the same query when logged in as the application account. At that point I started looking for a trigger. I am now working on convincing them to drop the trigger and stick with the default 'CHOOSE', or at least change to FIRST_ROWS_n. Jared On 5/28/05, Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx> wrote: > > From: info [mailto:info@xxxxxxxxxxxxxx] > Sent: Saturday, May 28, 2005 10:18 > To: 'zhai_jingmin@xxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx' > Subject: RE: Why CBO choose wrong way? > the CBO thinks it saves an expensive(?) sort this way, to satisfy the > order by > clause. > why do you have optimizer_mode set to FIRST_ROWS? and even if you need it, > then > you should at least use the flavors with a trailing number. FIRST_ROWS is > only > maintained for backward compatibility, and is based on heuristics rather > than > statistics. and why do you have optimizer_index_cost_adj set to 30? > > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- //www.freelists.org/webpage/oracle-l