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

bugbear wrote:
> 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.

Search the docs for:
plan stability
histograms

The general idea is, give the CBO as accurate data as possible. In some cases (such as your user actions) you need to develop the statistics to make them right, and then fix them at that. Some kinds of data skew call for histograms.

http://www.jlcomp.demon.co.uk/stats.doc
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:34807121420113
http://www.centrexcc.com/papers.html

You might also want to poke around on the oracle-l archives for various arguments about collecting statistics.

jg

--
@home.com is bogus.
"Protecting privacy in the Internet economy is equivalent to
protecting the environment in an industrial economy."
http://www.signonsandiego.com/uniontrib/20060801/news_1b1givens.html