Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2002
    Posts
    9

    Unanswered: Index tablespaces

    Hello All,

    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.

    Please help

  2. #2
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    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!

  3. #3
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    I want to know
    the size of your big tables
    the size of your big indexes
    the size of your big_index_tablespace, and all the extents in the tablespace are in same size?

    do you insert and delete your big table frequently?
    db2oracle

  4. #4
    Join Date
    Apr 2002
    Posts
    9
    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?

    Cheers

    To Kerloin,

    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.

    Cheers

    Guys

  5. #5
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    extent_size< 1~5 percent of index_size,
    so you the wasted size is nearly 1%~5%
    db2oracle

  6. #6
    Join Date
    Apr 2002
    Posts
    9
    Kerloin,

    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.

    Is this right?

  7. #7
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    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"

    http://technet.oracle.com/deploy/per...pdf/defrag.pdf

  8. #8
    Join Date
    Apr 2002
    Posts
    9
    Originally posted by tsclark
    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
    Does it really matter if I don't use a factor and say have
    sizes - 128k, 4m, 32m ?

    Your last post was spot on the mark. Cheers

  9. #9
    Join Date
    May 2002
    Location
    Phoenix, AZ
    Posts
    24
    I see no problem with what you are suggesting.

    The factor was really just a guiding principal to put a method to the madness.

    Best wishes...

  10. #10
    Join Date
    Apr 2002
    Posts
    9
    If your still online tsclark.

    Thanks for that,

    I have one more question if you can answer it I'd really appreciate it.

    What would be the best way to find out the amount of buffer cache available.
    And is there a way of looking at the buffers in the dirty and buffer queue's?

    Many thanks in advance.

  11. #11
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    if you can keep all extent in the same size ,that is best!
    but you can indeed have diffrent extent size in a tablespace,
    eg.
    small :64k,128k,256k
    middle:4m,8m,16m
    big:32m,64m,128m

    then you can get a good factor between fragementation and space usage.

    in fact, in oracle local tablespace managerment, it force extent size to be
    64k,1m,8m,...
    db2oracle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •