Re: Oracle index rebuilding performance

ryanbreakspear_at_norubbishgmail.com wrote:
> Hi Guys,
>
> Thanks for the replies. Daniel, re-reading my post I understand the
> confusion over versions I'm at 8.1.7 hopefully going to 9.2.?.
>
> I know that the rebuilding of the tables/indices isn't a solution,
> that's why I'm posting! As I said before I've used SQL Server for a
> few years but don't have much experience with Oracle. The current
> Optimizer_Mode is CHOOSE. I don't know when the database was created,
> but the original Delphi application which talks to it was created in
> 1999, I imagine it created in 8.1.7.

The big thing about choose is, if any of the tables in a query has stats on it, then the CBO is used. So, if any one of those tables doesn't get stats, or if only one does, or if any are stale, the CBO may act really brain-damaged. I can't remember anything about Delphi, so I can't comment on its relationship with CBO (I know other contemporaneous tools would get sometimes very confused when not RBO, and some tools you can just plug in stats and it just works better. The latter is how it is supposed to be in theory...)

>
> Joel, what you said about the indexes maybe not be created is
> interesting. The performance difference is exactly what I'd expect
> between indexes being there and not. This doesn't explain why it would
> get slower over (a rather short period of) time though.

Possible simply being due to the wanted blocks being in memory from the operations. You can figure that out with the V$BH table (google for recent posting here of scripts, or look in Oracle Performance Tuning guide). The docs also have instructions on how to set up tracing and dumps to find out what is actually going on, and with CBO you really need someone around who can do stuff like that.

You also should check out wait statistics, available in command line or OEM. Some locks get taken out during the operations you mention, and maybe something is happening like the db writer gets way behind waiting for the locks to resolve, or something on that order.

>
> The rebuilding code is exactly as i stated in the earlier post ALTER
> TABLE <tablename> MOVE TABLESPACE
> <spacename>. Then ALTER INDEX indexname REBUILD. There are three
> different namespaces in the schema, one for archiving, one for large
> tables, and one for small ones.

Well, I for one am not yet real clear on what exactly is happening. If you only have those three, where do you move the tables from and to?

>
> I'll push for the upgrade, and do some reading about updating
> statistics. Thanks again for all your help.
>
> Ryan
>
>
> .

jg

--
@home.com is bogus.
Not very sporting:
http://www.signonsandiego.com/uniontrib/20050603/news_6m3bass.html