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

"David Sanabria" <david.sanabria_at_hartfordthe.com> wrote in message news:6EMke.364$QE6.234_at_newssvr31.news.prodigy.com...
>I ran into an issue today that reminded me of a long-running question about
>Oracle and Remedy. I updated the statistics on one of our larger tables
>(TEC_EVENT, 5GB) which I cascaded down to the indexes. I used DBMS_STATS to
>compute the stats across the entire table (no samples). Immediately after I
>updated the stats, one of our power users (and sometime remedy developer)
>reported that his querying against this table had slowed to a crawl when he
>searched from the Remedy client.

I haven't heard of Remedy, but I can't actually see that that is especially important here, since it is the behaviour of Oracle optimizing sql statements that is likely your problem.

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
(IIRC it will assume 100 row tables of 10 blocks or so each). If you are going to use the CBO (and generally I think you should) then its an all or nothing thing, you'll want to give the CBO as much 'accurate' information as possible.

> Our database is currently in CHOOSE mode, which should use the stats when
> they are there or else use the Rule-based optimizer when not.

Not quite, see above. The CBO will also kick in if you have used features that the RBO doesn't know about.

> The previous DBA told me that he had updated the stats on the entire
> ARADMIN schema at one point and ended up deleting them shortly after
> because of performance complaints. As soon as I deleted the stats on the
> target table, the user indicated that performance had gone back up to his
> satisfaction.

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.

> 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).

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com