Re: Tuning Nightmare - Advice Needed

You may want to explore the use of a good ETL tool ( Like Ascential Software's DataStage ) to create, on a regular basis, denormalized tables and aggregate tables for your users to access....Exploration of the data needs of your users, and the use of a Quality Management tool can identify the data and the structure needed to optimize access ease and response time...

Just my 2c..

gdas1_at_yahoo.com (gdas) wrote:

>"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

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Thu Jan 03 2002 - 09:38:01 CST