Re: long running select min(timestamp) query

From: <>
Date: Thu, 31 Jan 2008 05:49:43 -0800 (PST)
Message-ID: <>

On Jan 31, 3:09 am, Robert Klemme <> wrote:
> On Jan 30, 6:26 pm, "" <> wrote:
> > On Jan 30, 5:02 am, Robert Klemme <> wrote:
> > > I doubt it makes a difference for the min/max types of queries but do
> > > you update statistics on a regular basis, or at least after such mass
> > > deletes? There *may* be an impact if concurrently running queries are
> > > less efficient because of stale statistics and your IO becomes slower
> > > just because of increased concurrent IO load.
> > I don't do anything regularily, which is bad yes, but ianaodba,
> Then please do yourself a favor ask whoever is responsible for the
> database to set up statistics gathering.
> > so i
> > don't even know what to do, i try to let this intelligent system
> > handle itself :)
> Oracle is getting better with manageability IMHO but you cannot let a
> DB left unattended.
> > How do i know if i have system statistics set?
> From an earlier posting in this group:
> select pname, pval1, sname
> from sys.aux_stats$
> where sname = 'SYSSTATS_MAIN'
> Btw, your friend. It's even searchable.
> Kind regards
> robert

I probably should've prefaced this entire thread with - I'm an applications developer, not a dba :) I know this is a lame excuse, but i simply don't have the time nor mandate to become an oracle guru, i'm simply tasked with getting an application to work with oracle (and a variety of other db's). Every once in a while i get cycles to deal with issues like this, but i certainly don't spend enough time doing it.

Anyways, Charles has helped me out bigtime by pointing me in the right direction:
>> Pages 133-135 show what is happening in your situation. It appears that this is one of the few cases where an index may need to be rebuilt...

I rebuilt the index, performed the search, got a result instantly, only 3 consistent gets!!!

One followup question, that you'll all likely laugh at. I got into this "mess" by having a table that we frequently perform delete from TABLE where DATE < ? - this situation is going to continue to arise. Should i simply schedule frequent index rebuilds? I understand partitioning the data is probably the way to go, but what is frequent rebuilds the simplest solution here (by simple, i mean least knowledge/ testing/sql involved)?

Thanks again to all your help!!! Sincerely, Bob Received on Thu Jan 31 2008 - 07:49:43 CST

Original text of this message