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/memory.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_at_crowley.com <Joel.Patterson_at_crowley.com> 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

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