Re: storage parameters in create tabelspace

On Sun, 26 Aug 2001 10:31:52 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
>news:iv7hot09mo9lggee7kons9p7nb97oam88c_at_4ax.com...
>> Dear all,
>>
>> An index tablespace, holding several bitmap indexes, has three
>> datafiles. One datafile was extended to be very big (12G) by a mistake
>> which uses this tablespace as a query user's temporary tablespace.
>> These indexes are of only 500M bytes as a whole, while the tablespace
>> occupies 14G bytes (12G+2G+2G). I decided to drop the tablespace, and
>> re-create all indexes in it. I will make these datafiles 512M
>> initially and extendable to 1024M for each of them, the SQL is as:
>>
>> CREATE TABLESPACE "TS_IX_ORDER" NOLOGGING DATAFILE
>> 'D:\TWDM\DATFILE\TSORDERIDX1.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
>> 512K MAXSIZE 1024M,
>> 'D:\TWDM\DATFILE\TSORDERIDX2.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
>> 512K MAXSIZE 1024M,
>> 'D:\TWDM\DATFILE\TSORDERIDX3.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
>> 512K MAXSIZE 1024M
>> DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 20480
>> PCTINCREASE 0 )
>>
>> I am not sure about the relationship between AUTOEXTEND clause and the
>> DEFAULT STORAGE clause, the former means this datafile can extend in
>> 512K (NEXT) steps, up to 1024M as total datafile size, while the
>> latter means any object without its own STORAGE clause will allocate
>> its extents in 512K (NEXT) steps, up to 20480 (MAXEXTENS) as total
>> tablespace size. As I observed, any objects in the tablespace will
>> span three datafiles evenly. Can anyone tell me how you make decisions
>> about these parameters? Thanks in advance.
>>
>> Dino
>>
>
>All your three datafiles are on one disk.
>There is no advantage in having three datafiles on one single disk.
>There are two good reasons for a tablespace to have multiple datafiles
>- more than 2G is necessary
>- the files are located on different disks
>You state your indexes require 500M.
>Why allocate three times as much in three different files on one disk?
The current size is 500M, and I wish I don't have to worry about the tablespace or datafiles in a year, so 1.5G to 3G would be a good guess for me to deal with the continous historical table growth and possible added bitmap indexes.

>You current parameters imply the following.
>- As soon as the 512M limit is reached, each extension of an index (next
>extent 512K) will result in an extension of the file (next 512K).
>You are going to create a performance penalty on that tablespace.
>The next parameter in the autoextend clause (if you want to use the
>autoextend clause at all, as you will loose control over those datafiles)
>definitely needs to be much bigger than the next extent in the default
>storage clause.
>I would extend the tablespace with 5 or 10 M, to avoid frequent extention of
>the datafiles.
>
>Regards,
>
>Sybrand Bakker, Senior Oracle DBA
>
>

The 5M or 10M is a good advice for me to see how you make decisions about the NEXT parameter. But there seems to be no discussion about the relationship between tablespace object parameters and datafile storage parameters.

Dino Received on Sun Aug 26 2001 - 05:44:55 CDT