Ok, that really helped me a lot thanks so much!
"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:4035e5c7$0$29132$afc38c87_at_news.optusnet.com.au...
>
> "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;
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Fri Feb 20 2004 - 04:57:33 CST
