Re: Index creation monitoring + select/inserts

From: joel garry <>
Date: Sat, 10 Oct 2009 17:56:53 -0700 (PDT)
Message-ID: <>

On Oct 10, 2:54 am, briandba <> wrote:
> thank you for your answer, i understand that each dbms is different
> and i must understand the concepts behin them.
> but now i for some tasks my users are also accustomed to specific
> answers. index rebuilding time, access, select,are only a litte part.
> And if i understand that i must take another pat in oracle, it's
> harder for me to answer my customers, it will be finished when it's
> finised.
> Anyway i'm going to buy these books ans read, read ,read :)

Not long after I posted my answer, a user came up to me and asked how long until her (infrequent data generation) process would take. Of course, I had to say I didn't know, but did she try it in the test system? She had, and it had taken 4 hours. So I said I'd look and see if I can figure it out (sometimes I can using the tools available), but it would likely take less than 4 hours.

So I started poking around, and immediately saw in EM that it was doing something I almost never see, lots of parallel read waits. So I delved into that in the docs and on the intertubes, and found this was one of those examples where one person published incorrect/out of date information, and it has propagated all over the place. Fortunately, Tanel Poder had published a few comments on it, and I learned something new. It is well known that for some operations Oracle will ask the I/O system for a single block, and for others it will ask for multiple blocks. Obviously, if you are doing a full table or index scan you want to suck up a lot of blocks. Some situations it is kind of a tossup whether to get single or multiple blocks. Turns out there is an internal table that tracks some of those situations - Oracle will try doing multi block reads, and track how often it has to discard the extra blocks. Then it will decide based on previous results whether one way is better than another. Of course, if it is doing this, and you do something infrequent but similar, it may decide wrong. You can research which way is best for your sql, and tell it how if it insists on doing it wrong, but that is a bit late when someone is asking you about something already running.

Now, as it turned out, it completed in about an hour in production. Is this due to smarts, the different configuration, or what (both going to the same SAN)? I'll never know, but if it is 4 times faster, I'll pretend I don't care, though I'm curious and wish I had time to dig into it more. Of course, my boss hates that it doesn't work like DBase, and would certainly get upset if I got uppity and asked if he preferred it take 4 hours. Why the user would even be asking after she already knew it could take 4 hours... well, that's a user.

Some people say the proper answer is to get plans and metrics for everything before it is put in production. I think that is impossible for an ERP.


-- is bogus.
Received on Sat Oct 10 2009 - 19:56:53 CDT

Original text of this message