Message-Id: <25988.338916@fatcity.com> From: "Tanel Poder" Date: Tue, 22 Jul 2003 19:46:56 +0300 Subject: Re: LMT Migration This is a multi-part message in MIME format. ------=_NextPart_000_0296_01C3508A.01D6D030 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi! Make sure your extents are in size or multiples of your = block_size*db_file_multiblock_read_count. This might help in performance = when doing index fast full scans. Also, if you use striped disks, you = might want to match it with stripe width. But number of extents doesn't cause any performance problems, it = actually never did *for normal operations* where not much extent = allocation or deallocation was done. I personally prefer keeping number = of extents per segment less than 100, but don't get nervous when the = number is 1000 either. About separating, if you got big application, you could make 4 = tablespaces, a big and small extent one for both applications. = Separating applications only gives you some benefit from administrative = point of view, you can have different backup&recovery strategies for = different applications.. but in small to medium databases, this is not = much of an issue either. Just a note, if you don't want to move your indexes online, then use = rebuild command with tablespace (and nologging) clause, don't drop & = recreate, rebuild will be faster & generates less IO that way. Tanel. ----- Original Message -----=20 From: AK=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, July 22, 2003 7:54 PM Subject: LMT Migration At present we have one tablespace containign all indexes . Some = indexes are big in size some are small . Currently tablespace is dict = managed. This tablespace currently highly fragmented . Now I am planning to move the indexes to a LMT. Now how to decide what = should be the extent size for uniform extents ?=20 What is better approach to divide indexs=20 A ) should I devide them that based on size ( big, small ) and create = seperate tablespaces with different values for extent size .=20 b) or seperate them based on modules sooo that accounting and = manufactring related indexes goes to different tablespace. Does number of extents is a performance issue in LMT as well ? Any = experience ? Thanks, -ak ------=_NextPart_000_0296_01C3508A.01D6D030 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi!
 
Make sure your extents are in size or = multiples of=20 your block_size*db_file_multiblock_read_count. This might help in = performance=20 when doing index fast full scans. Also, if you use striped disks, you = might want=20 to match it with stripe width.
But number of extents doesn't cause any = performance=20 problems, it actually never did *for normal operations* where not much = extent=20 allocation or deallocation was done. I personally prefer keeping number = of=20 extents per segment less than 100, but don't get nervous when the number = is 1000=20 either.
 
About separating, if you got big = application, you=20 could make 4 tablespaces, a big and small extent one for both = applications.=20 Separating applications only gives you some benefit from administrative = point of=20 view, you can have different backup&recovery strategies for = different=20 applications.. but in small to medium databases, this is not much of an = issue=20 either.
 
Just a note, if you don't want to move = your indexes=20 online, then use rebuild command with tablespace (and nologging) clause, = don't=20 drop & recreate, rebuild will be faster & generates = less IO=20 that way.
 
Tanel.
----- Original Message -----
From:=20 AK
To: Multiple recipients of list = ORACLE-L=20
Sent: Tuesday, July 22, 2003 = 7:54=20 PM
Subject: LMT Migration

At present we have one tablespace = containign all=20 indexes . Some indexes are big  in size some are small . = Currently=20 tablespace is dict managed. This tablespace currently highly = fragmented=20 .
Now I am planning to move the indexes = to a LMT.=20 Now how to decide what should be the extent size for uniform extents ? =
What is better approach to divide=20 indexs 
A )  should I=20 devide them that based on size ( big, small ) and create seperate = tablespaces=20 with different values for extent size .
b)    or seperate = them based=20 on modules sooo that accounting and manufactring related indexes goes = to=20 different tablespace.
 
Does number of extents is a = performance issue in=20 LMT as well ? Any experience ?
 
Thanks,
-ak