Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Storage parameter setting for indexes

Re: Storage parameter setting for indexes

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 02 Nov 2000 14:15:26 GMT
Message-ID: <8trstm$2jb$1@nnrp1.deja.com>

In article <9f9M5.1105$Pqb.180101632_at_news.telia.no>,   "Randi Wølner" <rw_at_computas.no> wrote:
> 1. How important is it - for performance reasons - to have as few
 extents as
> possible for indexes ?
>

There are advocates on both sides of that question -- on the one hand, the fewer fragments you have in an index the better performance should become. On the other hand, uniform extent sizing allows Oracle to extend the table/index with less work and allows re-use of deallocated extents without coalescing the tablespace. Both can improve performance, and both can hinder performance. Large extents can hinder performance due to Oracle needing large amounts of contiguous space for the next extent which it may not find readily. Uniform extents are, as the name implies, all the same size eliminating the need for large contiguous blocks of free space in a datafile but can create a large number of scattered extents for a given table/index resulting in a fragmented read of the tablespace to find the desired data.

> 2. I have read somewhere that for tables one should set pctincrease
 to 1
> (instead of 0, as I would otherwise prefer) - as otherwise coalesce
 would
> not be done automatically. Is this true for indexes too?
>

It is not the tables but the tablespaces that will cause SMON to coalesce the contiguous free space when the PCTINCREASE is set to 1. There are certainly reasons to set the PCTINCREASE on your tablespaces to 1, especially on fairly active tablespaces that result in free space fragmentation, especially with older releases of Oracle (7.0, 7.1 and, I believe, 7.2, that don't have the 'ALTER TABLESPACE ... COALESCE' command). However it is not unusual to schedule a job to run several times a day, either with cron (on UNIX) or at (with NT), to coalesce tablespaces. Since the contiguous free space in tablespaces can be coalesced with great ease as compared with early versions of 7 and, yes, version 6, I would recommend against setting the tablespace PCTINCREASE values to 1. Yes, it is a small increase, and it would take quite a while for geometric 1% increase of an initial 1 Megabyte extent to actually become a trouble spot in the tablespace but that same PCTINCREASE value is also passed on, as the default, to all tables created in that tablespace when the table PCTINCREASE value is not specified. 1 table an 1% increase is one thing, a large number of tables, all at a 1% increase, is another. I would set your tablespaces PCTINCREASE to 0 and run a scheduled coalesce during the day if you're having trouble with 'fragmented' contiguous free space.

> Thanks in advance!
>
> Best regards,
> Randi Wølner
>
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Nov 02 2000 - 08:15:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US