Re: Tuning Nightmare - Advice Needed

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1008932551.8849.1.nnrp-02.9e984b29_at_news.demon.co.uk>...
> The first bit of this advice is extreme.
>
> This is a DSS system - the CPU time for
> parsing every query could well be tiny
> compared to the run time of the typical
> query. The number of queries parsed could
> be so small that the time lost in latching
> could be non-existent.
>
> Bind variable usage tends to be very good
> for OLTP systems where you execute millions
> of very small, nearly identical, queries per day.
>
> Literal usage can be highly appropriate for
> DSS systems where you execute thousands
> of very large, dramatically skewed, queries
> per day.
>
> Somewhere in the middle you may want to
> be picky about some queries using binds,
> some using heavy hinting, and some being
> recognised by the program that generates
> the SQL.
>
>

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 - 01:00:42 CST