Re: Cursor_sharing - Optimizer

From: <Rajesh.Rao_at_jpmchase.com>
Date: Thu, 31 Mar 2005 14:06:15 -0500
Message-ID: <OFFB545787.A797BCBC-ON85256FD5.00687510@jpmchase.com>

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_at_jlcomp.d        To:       <Rajesh.Rao_at_jpmchase.com>, <Oracle-L_at_freelists.org>                           
                      emon.co.uk>               cc:                                                                                     
                      Sent by:                  Subject:  Re: Cursor_sharing - Optimizer - Histograms                                   
                      oracle-l-bounce_at_fr                                                                                                
                      eelists.org                                                                                                       
                                                                                                                                        
                                                                                                                                        
                      03/31/2005 11:13                                                                                                  
                      AM                                                                                                                
                      Please respond to                                                                                                 
                      jonathan

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.
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_at_jpmchase.com> To: <Oracle-L_at_freelists.org> Sent: Thursday, March 31, 2005 4:59 PM Subject: Cursor_sharing - Optimizer - Histograms

My questions (if my inferences are right) are :

The optimizer for some reason believes that the unique key values are skewed, whereas for the primary key the values are not skewed. Why?
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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 31 2005 - 14:12:16 CST