RE: histograms
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 13 Sep 2006 10:45:32 -0400
In 10g or at least R2 didn't Oracle modify the parse process so that the
SQL statement is 'normalized' prior to figuring the hash so that two
verse three spaces between a column name or case of a column name will
no longer be seen as two different queries but will be seen as the same
SQL statement?
-- Mark D Powell --
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, September 13, 2006 9:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: histograms
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."
Other related posts:
