RE: LMT Migration

AK
The link for How to Stop Defragmenting and Start Living is here: http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 <http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049>  

This is a classic paper and I consider it essential to carefully study this paper before you embark on your LMT adventure. You don't want to have misunderstandings and then have to redo your work.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee] Sent: Tuesday, July 22, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L Subject: Re: LMT Migration

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 ----- From: AK <mailto:oramagic_at_hotmail.com> To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list ORACLE-L 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 ? What is better approach to divide indexs A ) should I devide them that based on size ( big, small ) and create seperate tablespaces with different values for extent size . 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 Received on Tue Jul 22 2003 - 14:10:55 CDT