Tablespace sizing

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.

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

I suggest you follow Pando's advise plus you also consider LMTs for indexes.

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?

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.

Quote:

---

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.