Re: Optimizer issue - cost of full table scans
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: brad_peek@xxxxxxxxx, Oracle-L List <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 11 Sep 2010 23:52:54 +0800
Two points
1. The SELECT constant FROM DUAL method means
that Oracle may not work out the cardinality
properly --- it assumes an "unknown value".
Jonathan Lewis commented on such a case at
http://jonathanlewis.wordpress.com/2010/09/07/cbo-surprise-3/
2. Setting OICA to 200 is a bad idea.
Hemant K Chitale
http://hemantoracledba.blogspot.com
At 03:06 AM Saturday, Brad Peek wrote:
Listers ? Is there a way to â??informâ?? the
optimizer of the true cost of the full
scans? I think there is, and I fully intend to
RTFM, but Iâ??m hoping for a quick answer?.
<snip>
Since I know the SELECT FROM DUAL on the last
line will look odd to some people, let me
explain that we use that syntax to trick the
optimizer into treating the literal as a bind
variable rather than checking the value against
the BATCH_ID column histogram. We found that
the optimizer would greatly underestimate the
cardinality if the value was not present when
the statistics were gathered last. At any
rate, I donâ??t think it is part of the current
nested loop issue since that syntax has not changed.
<snip>
Note that If I â??ALTER SESSION SET
OPTIMIZER_INDEX_COST_ADJ=200;â?? then it goes back to hash joins.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
