RE: histograms

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <sacrophyte@xxxxxxxxx>
  • Date: Wed, 13 Sep 2006 14:06:31 -0400

Because it is a bind variable, rule 3 should not pertain should it?

 

Select * from ssntable were ssn = :b3  ie 334321234, 555443333

 

Joel Patterson 
Database Administrator 
joel.patterson@xxxxxxxxxxx 
x72542 
904  727-2542 

________________________________

From: Charles Schultz [mailto:sacrophyte@xxxxxxxxx] 
Sent: Wednesday, September 13, 2006 10:23 AM
To: Patterson, Joel
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: histograms

 

I am dealing with something very similar to that myself right now. Can
you provide:

*       your Oracle version
*       Type of histogram or number of distinct values
*       value of cursor_sharing

 

Your clever developer seems to be on the right track - he would be even
more clever if he did not call himself clever. *grin*

 

Aside from speculating about what has happened, have you gathered any
evidence? Trace files, information from v$sql_plan, v$sql_shared_cursor?

 

The Tuning and Performance Guide gives a bunch of rules for sharing:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/memo
ry.htm#sthref549 

 

A capital letter would fit rule 3, but watch out for rule 5 (your SQL
may be massaged).

 

On 9/13/06, Joel.Patterson@xxxxxxxxxxx < Joel.Patterson@xxxxxxxxxxx
<mailto:Joel.Patterson@xxxxxxxxxxx> > wrote:

This is from a developer.  I'm just trying to help in a timely manner,
so any input or clarifications would be helpful.

 

JP

 

 

"I am strongly under the impression that the default behavior of 10g is
to use the bind variable values provided on the first execution attempt
to determine the execution plan, in the case where those values may
matter (e.g. in the presence of histograms).

 

So for one query in particular, I did something fairly clever - I
actually know (of the few possible values for the histogrammed column)
which are selective and which are not.  I then issue the query with a
subtle difference (capitalization of one letter) depending on whether a
selective value was chosen or not.  Thus, if a user runs the
non-selective version, Oracle should give them a different execution
plan.  By this cleverness, I should be guaranteed that the plan that is
used when a selective value is chosen, is the plan that was first
developed when a selective value was

first sent.   But that's what I'm complaining about below - the plan
it's

using is appropriate for a non-selective value, so it's as if

a) It didn't use the bound values in determining the plan

b) The histogram wasn't available when it determined the plan I've even
tried "alter system flush shared_pool" to force the regeneration of the
plan, and that doesn't seem to work.  That also seems to rule out

b) above, because I can run queries without bind variables that
definitely are sensitive to the histogram." 




-- 
Charles Schultz 

Other related posts: