Quote:> 1. How important is it - for performance reasons - to have as few
> 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.
Quote:> 2. I have read somewhere that for tables one should set pctincrease
> (instead of 0, as I would otherwise prefer) - as otherwise coalesce
> 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
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.