storage parameters

[Guest_imported]

Hi,
i have been assigned with determining the storage parameters for different database objects based on estimates. Is it as simple as estimating the amount of transactions that will be carried out every day, whether the tables will be acccessed for read only purposes etc.?
Or is there more to it. Detailed suggestions solicited.
Thanks in advance.
Birdie

 

[jcote]

I can give you an idea of how I handle those parameters.

First, the initial and next. I always use the same initial and next, for all my tables, except maybe for those big tables. For instance, I have 128k for most of my tables, and 1M or 10M for the big ones. That way, if a table is dropped and free space is created in the tablespace, this space will be used to its maximum since all the extents are the same size. Ex: if a extent that uses 160k is removed from the tablespace, then it can be filled by another one which might be 148k, leaving 12k of unsused space, that might never be used again. If you always use the same extent size, this won't happen. Of course, pctincrease is always set to 0.

The other parameter I might pay attention to is the pctfree. If the a table is almost never updated, or that these update do not affect the size of the row, put a pctfree very low (5-10). If a lot of updates happens, you have to put a pctfree large enough so that the all the rows in a block will have enough room to grow within the block (DB_BLOCK_SIZE).

Hope this was helpfull.

 

[Guest_imported]

U can also choose to let Oracle do the work.
On Oracle 8.1.7 perhaps 8.16 U can let space allocation be determined "by the tablespace". Oracle calls it "locally managed tablespaces". First all your data is stored into 40 K pieces, If many data is filled into 1 table, then Oracle determines next extents size I think its is 1 M next time 8 M next time 64 M.
If U think your database is to be smaller than 20 Gb, I think this works really fine for U purpose.
Define once, runs fine no admin overhead until U get pretty large tables.
Check out manuals,
regards
SteenBdk

 

[wolf60s]

Interesting, steenbdk. Could you tell me how to do it in detail? What parameter should be set? Where the parameter located? Thanks.