Re: Oracle database statistics... Remedy doesn't seem to like them very much

From: David Sanabria <david.sanabria_at_hartfordthe.com>
Date: Wed, 01 Jun 2005 14:14:43 GMT
Message-ID: <429DC2D1.5060308@hartfordthe.com>

Niall Litchfield wrote:

> Do you really mean that queries against this table go slow, or that queries
> that join this table to others go slow? If the latter then this is exactly
> what I'd expect. If at least one of the objects has stats then the CBO will
> kick in, for objects that don't have stats (on your version anyway) the CBO
> will use default values for the estimation of query costs, these defaults
> will be way wrong

Queries from the Remedy system run slower, my reports run faster. It's a terrible paradox.

> Notwithstanding my view that the CBO is *generally* better than the RBO (and
> has been since at least 815 IMO) I'm curious as to why you are gathering
> stats - especially on a production system, do you already have performance
> issues? If I were wanting to introduce the CBO, or to change what stats I
> gathered and how, I'd start with a test system and important queries, gather
> stats on all objects in the schema (using
> dbms_stats.gather_schema_stats(owner, cascade => true) and possibly add in
> histogram gathering for certain objects later) and see how the queries
> performed.

The cost of Remedy's queries is very high and I am trying to improve response times with small tweaks to the DB. The target schema doesn't have _any_ statistics. In my experience as a developer (I'm an App DBA now by title rather than by virtue of training) the database returns data faster and at a lower cost when there are statistics. Cost is less important than response time so I'm not as concerned with that. I _am_ concerned that the system has such poor response to queries that run orders of magnitude faster when I have current statistics.

>>This table is a particularly good example of a one that needs to be 
>>reorganized and have its indexes rebuilt. The reason I was working on this 
>>table was because I received a request to schedule an index rebuild on 
>>this table once each week to compensate for a high turnover rate 
>>(add/delete). I hoped that starting with the stats would give a nice bump 
>>without having to contention issues while rebuilding the indexes.

>
>
> What makes you think the table needs to be reorganized? It sounds like a
> fairly normal table, i.e one that is subject to a normal pattern of dml, and
> therefore is unlikely to warrant reorganisation? Similarly regualr index
> reorganisation is usually an almost complete waste of time - though in many
> systems you won't notice the cost of doing it (increased downtime and slower
> index performance due to leaf block splitting).

The primary motivator is that we are using dictionary managed TS and this table has a very high churn rate and inline CLOBs. We are hoping to reduce the size of this table and it's indexes (>700MB used by indexes alone) by regular reorgs.

I appreciate your thoughtful response! The learning curve for all of this is steep enough that a helpful hand is greatly appreciated. :)

Dave Received on Wed Jun 01 2005 - 09:14:43 CDT