tkprof it. see what that says. Since it runs "fast" when cached -- i would have to say that the large buffer cache is not having a negative impact but rather that it must be scanning a TON of entries in the index (doesn't matter how many rows it RETURNS -- it matters how many rows it had to INSPECT for the like).

I don't know what the indexes are one, but say its on last_name, first_name and you supply '%x%', it'll read EVERY index entry and if it has an x in it -- it'll then do the index probe into the other table. That could be a TON of singleton (slow) IO's.

An index is a data structure (a pretty complex one). Many times -- depending on what you do, they end up with "extra space" in them. If you rebuild them (as you did) they get smaller -- but lo and behold over time -- they grow again. This "extra space" is really needed -- sometimes its used, sometimes not used.

When you rebuild (compact) the index, all of this space goes away. The deleted entries (caused by deletes or updates) are removed. Well, we NEED those slots -- we are going to use them tommorow! The next day we won't but the day after we need them again.


Well, we'll have to split blocks - move data around -- grow the structure to get this "extra space" back again. In a couple of days or weeks -- the index will be exactly the same size it was before -- with all of that extra space. Getting that space back added overhead.


So, any index on a column that is updated frequently could cause this to happen. As you update it -- you'll leave a "deleted" entry where the old value was and use or make space for an entry. So, say you

update t set x = 'M' where id = 55; (x was 'C' before the update)

well, that leaves an empty slot on the block where that 'C' was before the update and gets space where the 'M' was. In a freshly compacted index -- we might have had to split the block where the update ended up (the index block the M goes on).

Now, the next guy that does:

update t set x = 'C' where id = 66;

will be able to use the space we left behind from the first 'C' that is not there anymore.

The problem after a compact is there are no more "deleted" entries, no place to go and we'll have to split the index until there is.

Many indexes have a steady state they'll get into over time, regardless of how often you "reorg" them and you pay the price of getting into this steady state over and over.

A large buffer cache is in the eye of the beholder. Yes, an excessively large one can cause issues -- but you were happy with the prior performance....

Many oracle books are not 100% accurate or only tell half of the story. Me, I've never actually rebuilt an index due to "fragmentation". Get Jonathan Lewis's book -- practical Oracle8i, building efficient databases. Lots of good common sense in there.

We provide that which customers demand. The rebuild online is GREAT for moving indexes from tablespace to tablespace, changing storage parameters, or after a mass load (eg: set the indexes UNUSABLE, load, REBUILD the indexes -- that way you NEVER EVER lose them ;)

So you see -- rebuild has many other alternate uses.

I would ask you -- now that you have spent UNTOLD hours, incurred serious performance issues, tore your hair out fixing the problem -- was it worth it?

Did you get measurable performance increases?
Was it an efficient use of your time?

Think about those two questions before you do your next reorg.

Well, it depends on how you delete the data. Do your indexes grow infinitely large over time? If not -- we must be doing some space reuse... (the indexes tend to stay pretty balanced).

If the indexes grow infinitely larger over time -- they are a candidate for rebuilds. If not, -- make sure you MEASURE response time BEFORE and AFTER a rebuild to see if you GAINED anything. Write a program that randomly selectes via the index a couple of thousand/million times, use sql_trace and tkprof. rebuild the index and do it again. compare. If all you got back was a little space (which will get allocated AGAIN very soon) don't rebuild. If the performance goes up measurably, consider rebuilding at some point in the future.

define "fragments"

If you mean "over time, due to updates and deletes, my index is 50% 'whitespace' and I think I should rebuild it" -- you might be very wrong (or you might be right).

Indexes, like people, have a certain weight they like to be at. No matter how many times I go on a diet, lose weight -- I end up back at the same weight (well, maybe a little heavier even). Same with an index.

This quote:
A reorg could actually slow down an OLTP system, all of those freshly rebuilt,
compact indexes must now get broken down again, split the blocks -- get back to
where they were -- plump but comfortable.

from above typifies what I mean. We put the index on a diet (rebuilt it) but it goes right back. Why? because your updates and deletes CONTINUE on this data and the index moves all around. An index like this might just want to be 50% whitespace forever and no amount of rebuilding will change that. You can put it on a diet (rebuild) as often as you like -- it'll just consume resources getting "fat" again.

"Siebel DBA" is what is known as an oxymoron.


Ask them why they propose such things. You do not size a buffer cache by numbers of users, that is silly.

Pre_Page_Sga is equally as doubtful.

In order to tune a system, you must diagnose what people are waiting on. do you have high IO waits? Is it likely that you are aging blocks out frequently causing to you read and re-read the same blocks? if not, increasing the buffer cache will just consume more ram (could make you slower) and pre-paging will just make you take longer to startup.