RE: Histogram worthwhile?

  • From: "Channa, Santhosh" <Santhosh.Channa@xxxxxxxxxxxx>
  • To: roberto_veiga@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 24 Feb 2009 08:42:25 -0600


I have faced the very similar issue recently and I found the issue is with 
"bind peeking only". I tested this by disabling bind peeking and things seems 
to be going smooth afterwards.
Here is the info in my blog:

Santhosh Channa

P Consider the environment. Please don't print this e-mail unless you really 
need to.

-----Original Message-----
From: Roberto Veiga [mailto:roberto_veiga@xxxxxxxxx] 
Sent: Tuesday, February 24, 2009 9:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Histogram worthwhile?

We have another performance problem to solve. The performance that was good, 
now is bad. Very bad with an unacceptable performance and nobody makes any 
changes to the system. Nor the analyst neither the dba. Why the performance is 
not ok anymore?

I think the answer is the data. The data is changing every day, every hour, 
every minute and that query that always was ok is taking a long time to finish. 
It is running slow and we have to have an explanation for this situation.

Oracle has a very good feature named histogram. And I think the histogram was 
created just for this. When the data change, the histogram can see that change 
and send information the the optimizer that uses that information to build a 
better access plan. So where is the problem?

First lets talk about bind variables. We always have heard that there are only 
benefits in using bind variables. This reduce the hard parses, relieve the 
memory consumption and let a clean code.

But when a column doesnt have values with a balanced distribution, bind 
variables could be a problem. Oracle has a phase before the access plan could 
be generated that is "bind variable peeking". The first value received by the 
query is the one that is going to be used by the optimizer to decide for the 
best access plan and this plan is going to be used for all future executions. 
But what if the second value can retrieve a different amount of data than the 
first one?

If my data has a skewed distribution and i am using bind variables, the 
optimizer is not going to rebuild the access plan every time the value changes 
even if I had collected histograms. For one specific value is better use  ,for 
example, the index idx1, for another value the best performance is achieved 
using the idx2 and for another value the best choice would be do a full table 

I dont know if this is a problem or if it is just a optimizer's behavior, but I 
am doing a research to find some alternatives to this problem. And I find some 
(thanks Tom Kyte):

.use bind variables but dont use histograms. so the plan is going to be the 
same for every single execution. one size fits all approach
.dont use bind variables but use histograms. so the optimizer will evaluate the 
plan every execution and discover the best plan
.use bind variables, use histogram but change the code. if the value is in this 
range, execute this query. if not, execute another query. i need  to know 
distribution data. using the width_bucket function could be a good way to 
discover how is the distribution

Anyone have another idea?



      Veja quais são os assuntos do momento no Yahoo! +Buscados

Other related posts: