Re: long running select min(timestamp) query

On Jan 31, 3:09 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On Jan 30, 6:26 pm, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
>
> > On Jan 30, 5:02 am, Robert Klemme <shortcut..._at_googlemail.com> 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,http://tahiti.oracle.comis 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:
>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf
>> 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