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@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

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

Other related posts: