Re: Cursor_sharing - Optimizer - Histograms
- From: Rajesh.Rao@xxxxxxxxxxxx
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Thu, 31 Mar 2005 14:06:15 -0500
Thank to Wolfgang, Jonathan and Steve for the replies.
Meanwhile, I had opened a TAR with oracle support on this, and I am now
working on making a test case. If reproducible, it will be filed as a new
bug under the latest patchset, I am told.
Regards
Raj
"Jonathan Lewis"
<jonathan@xxxxxxxx To:
<Rajesh.Rao@xxxxxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
emon.co.uk> cc:
Sent by: Subject: Re: Cursor_sharing -
Optimizer - Histograms
oracle-l-bounce@fr
eelists.org
03/31/2005 11:13
AM
Please respond to
jonathan
1) There are many data distributions that could be
"skewed" without displaying the typical "very
popular value" syndrome that is so loved of the
manuals and sound-bite guides. For example,
if you have 1,000 rows in the range 1 - 2000,
then a gap, then 1,000 rows in the range 1,000,000
to 2,000,000 that data is skewed and Oracle may
choose to create a histogram on it.
2) If there is a histogram on any column in the
WHERE clause, and you have cursor_sharing
set to similar, then Oracle re-optimizes. That's
just the way it is (at present). Your observation
that the this is a query on a column with a unique
index in place (is there a unique constraint as
well - might this make a difference) does suggest
that there had been an oversight in the code path
that might be fixed on a future release.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005
----- Original Message -----
From: <Rajesh.Rao@xxxxxxxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, March 31, 2005 4:59 PM
Subject: Cursor_sharing - Optimizer - Histograms
My questions (if my inferences are right) are :
1. The optimizer for some reason believes that the unique key values are
skewed, whereas for the primary key the values are not skewed. Why?
2. The presence of an unique index should tell the optimizer that only one
row should be returned when queried using the unique key predicate? So, why
does the optimizer infer that the plan could change, after peeking at the
bind variables?
Regards
Raj
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
