Home » RDBMS Server » Server Administration » Fragmentations
Fragmentations [message #209121] Wed, 13 December 2006 07:01 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Oracle Version : 9.2.0.7
db_block_size = 8192
INITITAL_EXTENT & NEXT_EXTENT = 109051904
Datafile Size : 6442450944
-----------------------------
Ref : Oracle Metalink Note : HOW TO STOP DEFRAGMENTING AND START LIVING: THE DEFINITIVE WORD ON FRAGMENTATION

When allocating files to a tablespace, make sure to set the size of the file to be a multiple of the extent size for the tablespace plus one block.

These extent sizes are chosen to be a multiple of five blocks since Oracle7 will round all extent sizes to a multiple of five
blocks. In Oracle8 extents won’t be rounded to a multiple of five blocks if they are a multiple of the minimum extent size
for the tablespace.
-----------------------------

Will this hold good in version 9 also?
if yes, So what could be the optimal size for extent size and datafle size.
I want the extent size around 100M and datafile around 6GB.

Please suggest
Brian.
Re: Fragmentations [message #209123 is a reply to message #209121] Wed, 13 December 2006 07:09 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
Why do not you try LMT with uniform extents. You will never have fragmentation issues.
Re: Fragmentations [message #209127 is a reply to message #209121] Wed, 13 December 2006 07:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Said recomendation is primarily for DMT (initial=next and a zero pctincrease).
With LMT, uniform extent size is the way to go.
It will ***almost*** fix all the fragmentation (still interblock fragmentation is possible which you cannot avoid).

[Updated on: Wed, 13 December 2006 07:18]

Report message to a moderator

Re: Fragmentations [message #209236 is a reply to message #209121] Wed, 13 December 2006 22:47 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Tablespaces are LMT, pct_increase = 0, uniform_size. That means above said sizes are ok !

Brian.
Re: Fragmentations [message #209309 is a reply to message #209236] Thu, 14 December 2006 04:08 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes
Previous Topic: Which file is currently being used
Next Topic: Error with OCI8 Driver
Goto Forum:
  


Current Time: Wed Dec 07 16:15:43 CST 2016

Total time taken to generate the page: 0.16757 seconds