Re: Tuning Nightmare - Advice Needed

Not totally sure of the environment in which users are issuing these queries, but it would not be difficult to time a query result and then have the application use the best query method based on the timing test for the rest of the session. IMHO of course.

-Ken

> I appreciate everyone's advice... Through much experimentation with
> various hints, We've have managed to find a bearable solution that
> appears to work across different accounts. Performance is not optimal
> but it is bearable. None of the queries take an excessively long
> amount of time, however at the same time non of the queries come back
> very quickly.
>
> We cannot implement a solution where we dynamically use hints or binds
> etc... because it can't be done on a per query basis. That which
> affects the query performance is simply the value of the constraints,
> not the queries themselves.
>
> Our head of engineering does not feel it to be feasible to have to
> build and especially maintain all this awareness of the data dynamics
> into the application code such that different constraint values in the
> where clause for the same query would use different hints etc... I'm
> also skeptical that I can get all the proper hints for the various
> data values. We are both under the impression that we cannot put too
> much faith in the CBO and want to decrease our risk of exposure to
> this type of problem.
>
> We are going to try alternative 'schematic' approaches to solving
> this. The ideas we are considering at the moment are: use of
> materialized views, schema denormalization and table partitioning.
> I'm not sure which approach we'll take, but we've refocused our
> efforts away from tuning the CBO. I firmly believe I've done all that
> I can do there...
>
> If anyone has any comments on the 3 options above, I'd love to hear
> them.
> Thanks for all your answers.
>
> Regards,
> Gavin
Received on Thu Jan 03 2002 - 14:02:20 CST