Re: Tuning index tablespaces

Tarby777 wrote:

> or instance parameters that could be tweaked.

Well, there's optimizer_index_caching and optimizer_index_cost_adjust. Anything else and you are mucking about with things that are going to hurt more than help, except maybe in some very limited circumstances, such as a system that has critical batching time requirements. So for example, if you increase multiblock read count up to the max for your system, and fully pack index and data blocks, you might indeed be able to show some performance gains - at the cost of worse performance in other circumstances, and then horrible performance if you add or update a little data so Oracle has to chain blocks all over the place. The lesson to be learned here is to use appropriate pctfree etc for each object, don't try to do that in the tablespace. Changing the blocksize is only to allow for transportable tablespaces, it limits how you can tune objects in practice. Even DKB admits you can only use keep and recycle buffer pools with the default blocksize. So the granularity of assigning particular objects to multiple pools is lost. Your large blocksize indices muck up the modified LRU algorithms for your default buffer pool. And that's assuming your CBO is properly deciding to use index range scans and there are no bugs.

Your real misapprehension seems to be in tuning methodology. You need one. Cary Milsap's appears to be pretty good.

jg

--
@home.com is bogus.
"A.10.06 (Bundled) This is what you get when you haven't bought the
ANSI C compiler for HP-UX 10.01. It's nicknamed the "brain-dead C
compiler" because it's completely useless for anything other than
rebuilding the /hp-ux kernel. You should unpack the ANSI C compiler
from the Application CD-ROMs, assuming you've bought it !" - HP porting
center