RE: Why CBO choose wrong way?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <zhai_jingmin@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 28 May 2005 10:24:45 +0200
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?
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Zhai Jingmin
Sent: Saturday, May 28, 2005 04:45
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
