Re: storage parameters in create tabelspace

"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? 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 Received on Sun Aug 26 2001 - 03:31:52 CDT