They are already made 50% obsolete in 9i
Printable View
They are already made 50% obsolete in 9i
and whats all the Noise about Tablespace reorganisation. If there is no fragmentation then why do we need it.
Thanks
because everyone believed fragmentation is bad for performance
because fragmentation is bad for space management
and why is thatQuote:
Originally posted by pando
because fragmentation is bad for space management
because I can have 1000 free extents of 1MB each and none of them is next to each other (i.e not contiguosly) but all my segments need 2MB extent, so what does this means?
I have 1000MB free but I cant use any of them and I have to extend my datafile thus wasting 1GB of my disk space
This is called FreeSpace fragmentation and this is why we must use uniform extent sizes. REUSING them
Tim Gorman is right.
I do not expect one extent for one segment would improve performance.
I am administering a VLDB. There is one table that has 120 Million rows. Its size is 30GB. The indexes take around 40GB disk space. After I took over this database, I closely observed inserts and deletes on the table. Previously the initial extent was defined with 1GB and next 512M. When ever space is needed, new data files were added, the next extent size was changed to less than 512M. Finally the table had multiple size extents.
The data tablespace has more than 15 data files and the index tablespace has around 27 data files.
I did reorganize the table in such a way that minimum space was wasted on each data file. The initial and next size are 160M. So one 2000M data file can hold 12 extents of 160M each.
Before I load the data, I manually created around 6 extents on each data file. The idea is: I/O would occur on all data files.
The same logic was applied on the index segment.
For a large table, I do not allow Oracle to allocate the next extent, because Oracle always use the first available data file assigned to the tablespace. In that process many data files are not at all used for a longer time.
I DO NOT LIKE IT. That hurts performance.
I always spread the I/O on all the available disks.
With the tuning of SQL and I/O, I finally achieved 300% performance improvement.
Fragmentation within an extent is bad. That is a different story. I will talk about later.
[Edited by tamilselvan on 05-16-2002 at 05:39 PM]
Even in an ancient history that was only a myth, nothing else. Unless of course you were using single-user, single-session database, and all the selects on that fragmented table were perfomed by doing full table scans. And even if both the above condition were actually met (not very likely, I guess), your disc head was still forced to perform many zig-zag movements because of physical filesistem datafile fragmentation, so even in this case it is simply a myth, nothing more and nothing less.Quote:
Originally posted by WROOS
The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.
I am glad that ,here all are expert.
yes, there is no myth, but just fit with you situation.
such as transaction faced,report faced,or focus in batch job or data warehouse.
and in fact,uniform extents is not the highest SPACE Utilisation way, if you have thourands of tables, the size of tables are variable , then you may be need such 3 tablespace:
ts_smaill:contain 64k,256,512k
ts_middle:1m,4m,16m
ts_big:32m,64m,128m
There is no dening it, good segment management requires LMT's and unifom extents. A minumum of 3 Tablespace for data and three for indexes (each a different uniform extent size). It's what I try to use.
Cheers,
yes, there is no myth.
in fact, in many situation, 3 size of extent is not enough, if small table has big extent size, and it will wast space too.
we can do like this:
ts_small:64k,256k,512k
ts_middle:1m,4m,16m
ts_big:16m,32m,64m,128m