Re: Histogram worthwhile?
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: roberto_veiga@xxxxxxxxx
- Date: Tue, 24 Feb 2009 08:27:51 -0600
Another option would be to use a stored plan (profile) for the particular
query.
On Tue, Feb 24, 2009 at 8:01 AM, Roberto Veiga <roberto_veiga@xxxxxxxxx>wrote:
> 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 scan.
>
> 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?
>
> Thanks
>
> Veiga
>
>
>
>
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbuscados.yahoo.com
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
