> On Jan 30, 6:26 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > On Jan 30, 5:02 am, Robert Klemme <shortcut...@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'
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:
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)?
> 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.
No worries, it will just take a bit longer to become an Oracle guru. Eventually you'll get there. :-)
> Anyways, Charles has helped me out bigtime by pointing me in the right > direction: >>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebui... >>> 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)?
If your deletion is part of a batch job that is run infrequently then it's probably easiest to just add the index rebuild to the batch. Note the ONLINE flag which will slow down the rebuild a bit but do not require locking of the whole table. So your app can continue almost normally.
If you use partitioning in pre Oracle 11 you need to set up a job that will create partitions properly. And in all versions you need another job for the deletions (again assuming they are done on a regular basis).
For this not too uncommon scenario of data coming and going time based this MIN issue is really annoying. IMHO Oracle should do something about it - at least it would be nice if there was an option during index creation that would enable automatic index tree pruning at the cost of a bit of performance.
> Anyways, Charles has helped me out bigtime by pointing me in the right > direction: >>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebui... >>> 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
Sorry about getting into this so late - but your problem is a classic 'delete the left hand end' issue, and one of the reasons why you want to use the COALESCE command after a big delete.
It's probably somewhere in Richard's presentation, but when an index leaf block is emptied, it is linked to the free list, but also stays in place in the index structure.
Your min() query was doing a '(min/max)' full scan, which means it was going directly to the left (low) end of the index in expectation of finding the value there. However, since you've done a thorough delete of a lot of low-value rows, the run-time engine got to the bottom block, and had to walk a long walk through a lot of leaf blocks before finding the first leaf block with any data in it.
A call to coalesce will collapse together adjacent leaf blocks to reduce leaf block counts, and detach empty leaf blocks from the structure so that subsequent queries don't have to walk through them.
The cost / benefit balance is: each coalesce requires a full walk of the index - so don't do it when you have a large index with only a small amount of recoverable space.
failing to coalesce (for your query) requires a lot of empty leaf blocks to be walked - how many times do you want to let this happen, and how slow can the query be, before you coalesce.
> > Anyways, Charles has helped me out bigtime by pointing me in the right > > direction: > >>>http://richardfoote.files.wordpress.com/2007/12/index-internals-rebui... > >>> 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
> Sorry about getting into this so late - but your problem is > a classic 'delete the left hand end' issue, and one of the > reasons why you want to use the COALESCE command > after a big delete.
> It's probably somewhere in Richard's presentation, but > when an index leaf block is emptied, it is linked to the > free list, but also stays in place in the index structure.
> Your min() query was doing a '(min/max)' full scan, which > means it was going directly to the left (low) end of the index > in expectation of finding the value there. However, since > you've done a thorough delete of a lot of low-value rows, > the run-time engine got to the bottom block, and had to > walk a long walk through a lot of leaf blocks before finding > the first leaf block with any data in it.
> A call to coalesce will collapse together adjacent leaf blocks > to reduce leaf block counts, and detach empty leaf blocks > from the structure so that subsequent queries don't have to > walk through them.
> The cost / benefit balance is: > each coalesce requires a full walk of the index - so don't do > it when you have a large index with only a small amount of > recoverable space.
> failing to coalesce (for your query) requires a lot of empty > leaf blocks to be walked - how many times do you want > to let this happen, and how slow can the query be, before > you coalesce.
