Re: Why CBO choose wrong way?

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_at_naturaljoin.nl> wrote:
>
> From: info [mailto:info_at_naturaljoin.nl]
> Sent: Saturday, May 28, 2005 10:18
> To: 'zhai_jingmin_at_yahoo.com'; 'oracle-l_at_freelists.org'
> 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

--
http://www.freelists.org/webpage/oracle-l