Re: Query takes 1min 15 secs and tables are indexed !

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Tue, 01 Aug 2006 09:24:52 +0100
Message-ID: <44cf0fd5$0$22134$ed2619ec@ptn-nntp-reader01.plus.net>

DA Morgan wrote:
> Big Charles wrote:

>>
>> I'm wondering:
>>
>> - Should I run Statistics from time to time, like every 3 days, or once
>> a week... why?

>
>
> You should read the Concepts docs at tahiti.oracle.com to get the
> answer to this, and other related, questions. Run DBMS_STATS as
> often as required to provide the optimizer with the best possible
> information on the tables, indexes, and their data.

Our company has (historically) used a cron (on unix) to gather statistics (on both Sybase and Oracle sites).

We normally do this at some ungodly hour in the morning so that the (quite high) cpu and I/O load does not intrude on user operations.

However...

Some of our tables contain information about temprary user actions, and since most user's "check work in" at the end of the day, the statistics are not as representative as we (or the query optimiser) might like.

You could say that our systems are a rather unfortunate mix of OLTP activity that is then archived - all in one DB.

I would welcome any insight into how our statistics could be improved.

BugBear Received on Tue Aug 01 2006 - 03:24:52 CDT