Intro:- The database I administer has three tablespaces small, medium and large, the indexing on these tablespaces are in there own tablespace small_index, medium_index and large_index. Now the extents for each are 4kb, 4mb and 128mb. I am mainly looking at the large_index.
The block size for the db is 8kb. Therefore you need to use 16384 blocks for each of the large_index extents. Now not all of these indexes use this amount of space.
I created a sql query that told me I was wasting over 4GB of space on the large_indexes tablespace.
I also ran a sql query to find out how much wastage I would have if I only used 8mb for the extent size on the large_indexes tablespace. The answer was only 200mb.
And this is the question:-
If I dropped the large_indexes tablespace and recreated it with 8mb extents, it would drop the wastage a huge amount. But would it affect the performance.
Obviously if I did this there would be some indexes that would have to use alot of extents due to the reduction in size from 128mb to 8mb.
Perhaps you should consider rebuilding the smaller indexes, specifiying the target tablespace to be the medium_index. Your large objects may verywell need the 128mb extents. You want at least 5 extents so that no more than 20% of the space is wasted, but so many that you are constantly having to dynamically add extents during normal growth.
Make sure you have the spare space in the medium indexes datafiles first!
To tsclark :- Thanks for your input but I am only concerned with the large_indexes tablespace, the small and medium ones are fine. The initial extent is 128mb and there is no need for this, it is far too big. If I drop and recreate the large_indexes with 8mb extents I will save 4GB but obviously I will have a few of the indexes in the large_indexes tablespace using alot more extents because the extent size is smaller.
What I'm asking is will the db run slower if an index has more extents even if these extents are smaller.
example of sizing:- A23747394 is an index in the large_index tablespace and it is an index for the table ADDRESS and column Name.
It uses 130mb of space in the large_indexes tablespace, therefore two 128mb extents are given to it. With this you have one extent fully used, and one extent using 2mb of the second 128mb extent. Therefore a wastage of 126mb.
So if I dropped this index and recreated it with a 8mb extent, you would find you would need 17 extents, 16 would be full used and the 17th one would use 2mb out of the 8mb. Therefore a wastage of only 6mb, compared to 126mb.
What I want to know is will this affect performance in anyway if I do this?
Size of large_tables:- It uses 6GB of a 12.5GB tablespace
Size of large_indexes :- It uses 9.5GB of 10.5GB tablespace
All extents in the large_indexes tablespace are the same 128mb. Initial and extents atre 128mb
I don't insert or delete the tables in the large tablespace regularly.
So your saying that the extent size should not be the same for all the large indexes in the large_index tablespace.
Your saying that for each of the indexes in the large_index tablespace they should have extents equal to between 1% and 5% of the size of that individual index.
NO. You absolutely must have every object in a tablespace use the same extent size to prevent unusable space due to fragementation.
There is no real impact on performance whether you have 1 extent or 100 when they are of decent size (>= your multiblock read count). Don't go nuts - practical limit is about 1000. I always try to keep mine in double digits.
You setup has a factor of 32 between sizes - 128k, 4m, 128m
I prefer to use a factor of 16 between sizes - 256k, 4m, 64m
If a segment (object) in the 4m ts has more than 60 extents, I consider
moving it to the 64m ts.
Based on the information you have given so far, it seems that you could move your objects to a 2nd tablespace of 4m extents.
Also, have you considered the long term growth/size of these objects?
Please read the following article, "How to stop defragmenting and start living"