RE: Why CBO choose wrong way?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Sat, 28 May 2005 10:24:45 +0200
Message-Id: <20050528082446.1488B93CB8@user3.cybercity.dk>

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?

I would start tuning from the default CBO settings ...

hope this helps, kind regards,

Lex.

Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zhai Jingmin
Sent: Saturday, May 28, 2005 04:45
To: oracle-l_at_freelists.org
Subject: Why CBO choose wrong way?

Hi,list

My query is

    select a.* from peak_present a,present b     where a.present_id=b.id
    and a.peak_id=:1
    order by a.id;

The table a has 39M rows ,and table b is very small,I have a index on a.peak_id,all the statistics are current.The problem is the CBO choose following execution plan and the index on peak_id is not used .If I delete the 'order by' line,theCBO can use the index on peak_id column.Actually the peak_id column is almost unique.The query is very fast if it can use the index on peak_id column.Why the CBO order the table so early before filter out right rows? My optimizer* parameters list:

optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     30
optimizer_mode                       string      FIRST_ROWS

db_file_multi_block_read_count 16

--

http://www.freelists.org/webpage/oracle-l Received on Sat May 28 2005 - 04:29:40 CDT