Shreepad,
There is no problem with a segment having more than 1 extent. It is a common
misconception (another of those Myths, if you please) that all tables and
indexes should reside in 1 extent, or less than 10 extents even. There is a
paper available from the web - that many say is the definitive word on
fragmentation of extents, called "How to stop defragmenting and start
living", written by Juan Loaiza (sp?) which a google search should easily
find..
The basics of this paper are thus:
You should have a set of three tablespaces (for data and indexes) that each
deal with a UNIFORM extent size, ranging through 160k, 5120k and 160M at the
tablespace level. When creating a new object (table or index), you should
determine how large the table/index is going to be, and create it in the
appropriate tablespace, without defining specific storage clauses at the
table level - therefore inheriting the Tablespace storage clauses as a
default (gaining the uniform extent size for all objects within the
tablespace). Of course, if you are already using LMTs (Locally Managed
Tablespaces) with uniform extent allocation, then the need to do this has
gone away.
Once a table/index reaches 1024 extents (much more than the myth of 1-10
extents), then the object should be considered for a move up to a larger
extent sized tablespace (and will still, by all accounts, take up 32 extents
from the start of it's life in the new tablespace anyway - if moved straight
away).
I would again recommend reading the paper above - it's been a while since I
had the chance to read it, and I'm not sure if it's been updated at all
(anybody?), but it has an absolute wealth of information!
HTH
Mark
===================================================
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===================================================
http://www.cool-tools.co.uk
Maximising throughput & performance
-----Original Message-----
[EMAIL PROTECTED]
Sent: 30 April 2002 12:04
To: Multiple recipients of list ORACLE-L
Hi,
Thanks to all those who replied. Just clearing the structure.
I have Hp LVM and the tables and indexes are on different tablespaces and
different disks .
Since the growth pattern has changed drastically , the no of extents have
increased tremendously . Hence the need to
rebuild the indexes .
We plan to rebuild them into contiguous extents (only question being 1
extent or say 4-5 extents to take care of growth:--- 1 extent every 2
months )
Will the performance differ if they are on 1 extent or 5 extents (
both being contiguous) ?
thanks
shreepad
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
