Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: storage parameters in create tabelspace

Re: storage parameters in create tabelspace

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 26 Aug 2001 22:37:39 +1000
Message-ID: <3b88ec5d@news.iprimus.com.au>

"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 don't know about any relationship between parameters, but I can tell you that multiple datafiles all on the same disk are a complete waste of time. The idea of multiple data files is to get a sort of striping effect across drives. (Although, as you have them here, there is still some benefit in terms of backup and recovery options).

The other thing is that it is simply plain daft to autoextend a 1/2Gig datafile by 512K increments. If those files ever run out of their initial allocation of space, they will be continually dribbling back down to disk for puny increases in size. You want a NEXT autoextend size of at least 10 or 20M to make the autoextension worthwhile (with your default storage clause, that would imply 20 or 40 extensions of your segment).

As things stand, your 'autoextend on next' clause is redundant, since if you were simply to say 'autoextend on', without a 'next' clause, then the datafiles would grow by whatever the segment requesting space has as its next extent size -and in your case, that is 512K anyway.

As for how you go about making decisions on these parameters: you want an extent size that minimises the autoextension that the segment must undergo during busy times, and an autoextend clause for the datafile that minimises trips to disk.

Regards
HJR
>
> 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
>
Received on Sun Aug 26 2001 - 07:37:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US