Re: Oracle 9 - tablespace default storage

"Jens H. Hamann" <jhh_at_agramkow.dk> wrote in message news:4035e1ae$0$1603$edfadb0f_at_dread14.news.tele.dk...
> Hello Howard, thank you for your time to explain it to me.
>
> I am confused though, does this mean that i should create different
> tablespaces for each size of table i have?

Sort of, but not quite! It means that you should have a small set of possible extent sizes, and a different tablespace for each one. When you create a table, instead of umming and ahhing and saying "this needs 37K extents", you drop it into the 64K extent size tablespace. When you think a table should have 128K extents, you might drop it into the 1MB extent tablespace. But no, you wouldn't create a tablespace with uniform 37K extents and another with uniform 128K extents. Stick with the standard 5 sizes, and drop tables into the tablespace with the "nearest" or most appropriate standard size.

>
> For example a small table that only hold little data and is updated
> infrequent should reside in tablespace for small tables (lets say 100k
> extents). And a larger table that hold lots of data and is updated
> frequently should be in a tablespace for large tables, i.e. 10M extends.

Well, I wouldn't pick those particular sizes, but that's the sort of idea. It introduces another complication. Just because a bunch of tables use the same extent size doesn't mean they should all get dropped into the same tablespace. You've I/O contention issues to worry about, as you're hinting at. So you might have several, say, 64K-extent tablespaces. One for infrequent DML, one for frequent DML. Perhaps different ones for tables which are frequently read simultaneously by users. And so on.

>
> If the above is true this means in fact that i would have to create three
> data tablespaces and the same amount of index tablespaces i.e. six
> tablespaces one for each extent type 100k/1M/100M.

I should have read through your entire post before starting. You've got it exactly right (except those odd extent sizes again!!!). It is true, however, that you would not necessarily create 3 separate index tablespaces just because you created three separate DATA tablespaces. Indexes don't have to be separated from their tables, at least not for performance reasons, though management convenience might suggest to keep them separated. The real issue is again where your I/O conflicts are happening. Depending on your application's access patterns, you may need fewer or more index-specific tablespaces.

>
> Please advise?
>
> best regards
> Jens Hamann

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------



>

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message

> news:4035d078$0$15135$afc38c87_at_news.optusnet.com.au...

> >

> > "Jens H. Hamann" <jhh_at_agramkow.dk> wrote in message

> > news:4035c482$0$1611$edfadb0f_at_dread14.news.tele.dk...

> > > Hi, I am having a strange problem with the storage parameters in

Oracle


> > > 9.2.0.1 database.

> > > I create a tablespace with some default storage parameters but when i

am


> > > adding a table to the tablespace i can not override the NEXT

statement?


> >

> > Why would you want it to in the first place? No-one should ever have

> > segment-specific storage clauses that mention extent sizes. Ever.

> >

> > > No matter what i set the NEXT storage parameters to, it is always the

> same

> > > as the tablespace default, the INITIAL is overriden just fine.

> >

> > You have locally managed tablespaces, probably. In 9i these are the

> default

> > unless you explicitly demand EXTENT MANAGEMENT DICTIONARY when you

create


> > the tablespace. In LMT, initial is sort-of respected, but next is

> completely

> > and utterly ignored. What you've actually got, too, is autoallocated

LMT.


> >

> > > I have included my script below, can someone please point out what

could


> > be

> > > wrong.

> >

> > Apart from most of it, you mean? It's an OK script for 8.0, sort of. But

> it

> > is woefully out of date for 9i. Autoextension on data files is

convenient,


> > but it's not particularly good practice, nor good for performance. At

> least

> > you've remembered to put in a NEXT and a MAXSIZE clause. But the default

> > storage clause is largely not applicable in 9i. MAXEXTENTS and NEXT for

> > example are irrelevant, as is PCTINCREASE.

> >

> > To actually request PCTINCREASE for a segment when you create it is

sheer


> > and utter madness, even in Oracle version 7!

> >

> > And to have differntly-sized extents for different segments in the same

> > tablespace is a recipe for fragmentation, even in Oracle version 7.

> >

> > You need to get thee to tahiti.oracle.com and have a hunt around for

> > references to locally managed tablespace and why it's such a good idea.

> You

> > could google on the topic, too.  What you're doing has no real place in

a


> 9i

> > database.

> >

> > As an example of where the problems start:

> >

> > connect / as sysdba

> > SQL > CREATE TABLESPACE JHH_DATA DATAFILE

> > '/u01/app/oracle.oradata/jhh_data.dbf' SIZE 50M

> > 2     AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K

> NEXT

> > 24K

> > 3     MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

> >

> > Tablespace created.

> >

> > SQL>  select extent_management from dba_tablespaces

> > 2          where tablespace_name='JHH_DATA';

> >

> > EXTENT_MAN

> > ------------

> > LOCAL

> >

> > Regards

> > HJR

> >

> >

> >

> > >

> > > Thanks in advance!

> > > Jens Hamann

> > >

> > > CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_DATA_00.dbf' SIZE

50M


> > > AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT

> 24K

> > > MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

> > > CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_IDX00.dbf' SIZE 50M

> > > AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT

> 24K

> > > MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);

> > > CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE

'C:\OraDb\JHH_TEMP00.dbf'


> > SIZE

> > > 50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;

> > > CREATE USER JHH IDENTIFIED BY JHH;

> > > GRANT DBA, CONNECT TO JHH;

> > > ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE

> JHH_TEMP;

> > > ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON

JHH_INDEX


> > > QUOTA UNLIMITED ON JHH_TEMP;

> > >

> > > CREATE TABLE JHH.LARGE_TABLE (

> > >   COLUMN_1  NUMBER (5)    NOT NULL,

> > >   COLUMN_2  NUMBER (5),

> > >   COLUMN_3  VARCHAR2 (50),

> > >   CONSTRAINT PK_LARGE_TABLE

> > >   PRIMARY KEY ( COLUMN_1 )

> > >     USING INDEX

> > >      TABLESPACE JHH_INDEX PCTFREE 10

> > >      STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))

> > >  TABLESPACE JHH_DATA

> > >    PCTFREE 10   PCTUSED 40

> > >    INITRANS 1   MAXTRANS 255

> > >  STORAGE (

> > >    INITIAL 10M NEXT 10M PCTINCREASE 0

> > >    MINEXTENTS 1 MAXEXTENTS 2147483645 )

> > >    NOCACHE;

> > >

> > > CREATE TABLE JHH.SMALL_TABLE (

> > >   COLUMN_1  NUMBER (5)    NOT NULL,

> > >   COLUMN_2  NUMBER (5),

> > >   COLUMN_3  VARCHAR2 (50),

> > >   CONSTRAINT PK_SMALL_TABLE

> > >   PRIMARY KEY ( COLUMN_1 )

> > >     USING INDEX

> > >      TABLESPACE JHH_INDEX PCTFREE 10

> > >      STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))

> > >  TABLESPACE JHH_DATA

> > >    PCTFREE 10   PCTUSED 40

> > >    INITRANS 1   MAXTRANS 255

> > >  STORAGE (

> > >    INITIAL 64K NEXT 64K PCTINCREASE 0

> > >    MINEXTENTS 1 MAXEXTENTS 2147483645 )

> > >    NOCACHE;

> > >

> > >

> > >

> >

> >

>

>