Tablespace sizing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tablespace sizing

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    Can someone please explain to me or atleast point me in the right direction so I can read about properly sizing a tablespace.

    I have read several books,papers,help questions, etc on tablespace sizing and so far everything explains how Oracle allocates initial/next extents, etc. What I need to know is how do I calculate or guesstimate the proper setting for initial/next extent on the tablespace? For example. Let's say I have 20 tables varying in row size. I want to assign the tables into 2 tablespaces. How do I figure out what the best initial/next extent sizes need to be for that tablespace given the tables that will go into that tablespace?. I know what my db_block_size is. I know what the OS block size it.

    Also, I have read that the best thing to do is to specify the extent sizes at the tablespace level and not at the table level. If this is the case, what happens to tables that are created with extent sizing options and the tablespace the tables were placed in was also created with the extent sizing options? Which sizing options are used?

    Thanks.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    extent size MUST always be the same, in other words no matter how big, how fast is your table growing they should share same size extents therefore if you are using 8i it's suggested you use Locally Managed Tablespace because they ensure uniform extent sizes

    You create several tablespaces

    Code:
    micro sized      64K
    small sized       128k
    medium sized   4M
    big sized          128M
    huge sized       512M
    then just create your objects according your criteria, if you think objA is huge and grows fast you put it in 512M tablespace and so on

    if you specify storage when you create a table then that storage would be used (tablespace storage would be ignored) but by doing so you are altering the uniform extent strategy

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I suggest you follow Pando's advise plus you also consider LMTs for indexes.


  4. #4
    Join Date
    Sep 2001
    Posts
    163
    I understand extent sizes must be the same and that is why you specify the extents on the tablespace and not on the individual tables. But I guess where I am confused is with things like this. You said if objA is huge and grows fast put it in the 512M tablespace. That makes sense. But what if you had a small table (row size is small) but it will grow very quickly or if you have a very large table (row size is very large) but this table will not grow very much (say maybe 50-100 rows a year). Then were do you put these types of tables?

  5. #5
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Large or small does not usually apply to rowsize in this arena, but to the number of rows. Anyway, it all goes to total storage space needed for the table.

    1,000,000 rows * 128 rowsize is larger than 100 *1,280.

    Another issue is how the data is used. If you are heavy into OLTP (transaction level work) as opposed to OLAP (warehousing and reporting), you will be better off with smaller extents. Oracle tends to retrieve an extent at at time (if the OS buffer permits), so extent size vs system buffers is also a small factor.

    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by jrpm
    Oracle tends to retrieve an extent at at time (if the OS buffer permits), so extent size vs system buffers is also a small factor.
    Oracle *never* retrieves data from database in extents, only in single database blocks or DB_FILE_MULTIBLOCK_READ_COUNT number of blocks. So it is irrelevant if extent is small or large for that matter. If the segment is often read with full scan then extent size should be N*DB_FILE_MULTIBLOCK_READ_COUNT, but the actual size of the extents is irrelevant for the I/O efficiency.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width