Re: Opinions for this storage parameters.

"Fernando Ruza" <fruza_at_yahoo.com> wrote in message news:3d8dfca3.0207030850.5b1de75d_at_posting.google.com...
> Well, first thanks for the replys.
>
> The examples was not the best ones. I chose a very small tables
> however I also
> have big tables and I do the same analysis. The analysis was what I
> wanted to
> evaluate with the post.
>
> Right, I used PCTINCREASE to 1 for the reason that Christopher said.
> Also I like the Table Extent Sizes for at least 64k and multiples of
> it. Also I like the Richard example for several tablespaces with
> default storage parameters for big, medium and small tables.
>
> However, I have decided to use locally-managed tablespaces because
> everybody agrees that the performace it's better.

I don't! LMTs are wonderful things, because they largely eliminate the worry about numbers of extents, and guarantee no fragmentation. But as for performance -forget it. Unless you have horrible contention on the data dictionary tables used to allocate extents in dictionary managed tablespaces, then you won't see much of a performance boost.

>However, with this
> kind of tablespaces I don't know if it's worth to use the storage
> parameters when I create a table/index,

Not really. Just 'create table X (column type, colum type) tablespace X; If you specify a storage clause, mostly it's ignored anyway if X happens to be locally managed. However, note that PCTFREE and PCTUSED are not part of the storage clause, and still need to be thought about carefully, and specified (like I just didn't!).

>also the PCTFREE and PCTUSED
> parameters. I think all of the extents are of the same size, however
> for some big tables I would like to include all the rows in one big
> extent so that I would like to choose the size of this ones. Can I do
> this ??

No, and why would you want to? The *entire* point of LMTs is that you are freed from worrying about this sort of issue. And trying to put everything in one extent gets you what, exactly? Nothing, is the answer.

>I'm trying to find some documentation about this but I would
> prefer to hear your experience opinions.
>
> These are the commands I plan to use to create the locally-managed
> tablespaces:
>
> CREATE TABLESPACE DATA
> DATAFILE '/home/oracle/u01/oradata/axialdb/data01.dbf'
> SIZE 500M
> AUTOEXTEND ON NEXT 25M
> MAXSIZE UNLIMITED
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
>

Autoextension of data files is a big, big no-no, if you value performance. Of course, if convenience is more what you're after, then at least you've remembered to set NEXT. I would not set MAXSIZE unlimited, however. There are known bugs when Oracle tries to push the file size beyond various limits (4Gb seems to be common).

Regards
HJR
> CREATE TABLESPACE INDEXES
> DATAFILE '/home/oracle/u01/oradata/axialdb/indexes01.dbf'
> SIZE 250M
> AUTOEXTEND ON NEXT 10M
> MAXSIZE UNLIMITED
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
>
> Regards,
>
> Fernando.
>
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:<FVzU8.26569$Hj3.82346_at_newsfeeds.bigpond.com>...
> > Hi Christopher,
> >
> > Regarding PCTINCREASE = 1
> >
> > I've been described by various DBAs in sunny Canberra as a realistic
twit
> > (I've been called worse but we won't go there) over discussions on this
> > subject.
> >
> > With regard to Dictionary Managed Tablespaces I've followed this basic
> > philosophy. Don't use the following storage clauses when defining an
object
> > (INITIAL, NEXT, PCTINCREASE), leave it to the default storage clause of
the
> > tablespace you place the object in. Set the default storage clause of
the
> > tablespace such that INITIAL and NEXT are the same (lets say 10M) and
> > PCTINCREASE = 0.
> >
> > Now, EVERY object that lives in the tablespace has EXACTLY the same
sized
> > extents. If I were to drop an object, I leave ONLY 10M "holes" in the
> > tablespace. And you're absolutely correct, SMON doesn't automatically
> > coalesce such a tablespace (because it's default PCTINCREASE = 0). But,
so
> > what ? If as you describe an object needs to extend, it will either
reuse
> > EXACTLY one of the holes previously left behind or use a new piece of
the
> > tablespace. However, fragmentation is NOT going to be an issue to me. So
> > SMON, put your feet up and relax for a while.
> >
> > But the non believers cry, what if I only have a small table. 10M is way
too
> > big and would waste space. Then it gets assigned to another tablespace
(with
> > default storage of INITIAL and NEXT set to say 64K) where they are also
> > sized exactly the same without wasting space.
> >
> > INITIAL and NEXT are still vital considerations when defining a table.
The
> > only difference is that I pick an appropriate tablespace rather than
picking
> > an appropriate storage clause. I actually believe that setting
PCTINCREASE
> > to 1 is possibly the worst value to set it to as it kinda guarantees
objects
> > being created of all manner of sizes.
> >
> > In my view, Locally Managed Tablespaces kinda supports this approach as
it
> > automatically implements such a philosophy (when used with the uniform
> > clause). When using LMT, this discussion is no longer relevant.
> >
> > I'm sure this has been discussed many times here but the non believers
are
> > still out there and I'm still an idealistic twit !!
> >
> > Cheers
> >
> > Richard
> >
> > "Christopher Merry" <merryct_at_constructingbits.com> wrote in message
> > news:ui5g5c3kijfude_at_corp.supernews.com...
> > > Just wanted to add a couple of comments to those already mentioned. I
> > don't
> > > think you really need to over-analyze the settings though!
> > >
> > > 1) Table Extent Sizes (in general)
> > >
> > > I would take a look at your table extents (not so much for this table
but
> > > for future reference). If Oracle decides to perform a full-table
scan, a
> > > single fetch returns the value of DB_MULTI_BLOCK_READ_COUNT *
> > DB_BLOCK_SIZE.
> > > The defaults for these values results in 64k [DMBRC (8) *
DB_BLOCK_SIZE
> > (8k)
> > > = 64k]. That is the reason it is very common to set extent sizes to
the
> > > value of 64k. In general, you are better off setting your extent
sizes in
> > > multiples of this value (64k, 128k, 256k, etc).
> > >
> > > 2) PCTINCREASE value of 1
> > >
> > > The PCTINCREASE value of 1 is not necessarily a bad idea. It just
depends
> > > on the circumstances. The only reason I use this setting is when I am
> > > dealing with a dictionary managed tablespace. This value will force
SMON
> > to
> > > coalesce the tablespace freespace periodically (any value greater than
0
> > > causes this).
> > >
> > > Here is the scenario when I like this value:
> > > Typically, allocation of a new extents takes place during an INSERT
> > > operation. Many times, the INSERT takes place during more active
times of
> > > the day. (Of course not always but it stands to reason that more
INSERT
> > > will take place during busier times of the day) During this extent
> > > allocation, if Oracle cannot find sufficient freespace (dictionary
> > managed)
> > > SMON performs freespace coalescence. This is not an optimal time for
this
> > > operation. If SMON has already coalesced the freespace at a more
> > convenient
> > > time, it MAY save a bit of processing at the moment. Of course this
is
> > not
> > > a huge deal, but it is one philosophy.
> > >
> > > By the way, a PCTINCREASE of 1 is not going to cause tremendous growth
of
> > > the future extents, but it is a bit of an annoyance if you are a very
> > > structured DBA that expects to see certain NEXT value patterns.
> > >
> > > 3) PCTFREE for an index
> > >
> > > This setting is really only used to setup the index. Once the index
has
> > > been created and Oracle begins its "balancing act", Oracle over-rides
the
> > > original PCTFREE value.
> > >
> > > It is nice to see someone taking the time to setup their environment
with
> > > careful forethought. So many times I have seen nothing more than
Oracle's
> > > default storage parameters used.
> > >
> > > cm
> > >
> > >
> > > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > > news:LdtU8.26320$Hj3.81112_at_newsfeeds.bigpond.com...
> > > > Hi Fernando,
> > > >
> > > > I think it's great you've taken the trouble to perform analysis on
your
> > > > table and tried to select appropriate storage parameters.
Unfortunately
> > many
> > > > don't bother and take easy or general (albeit inappropriate)
options.
> > > >
> > > > I guess since the tables are so tiny, you're not going to go too
wrong
> > here
> > > > no matter what you do (except perhaps waste heaps of unnecessary
space).
> > A
> > > > few points I would make.
> > > >
> > > > I agree with Howard and suggest you store these little darlings in a
> > locally
> > > > managed tablespace and use a small uniform extent size (64K is not
> > uncommon
> > > > for small objects). Yes you will waste a little space (but not much
in
> > the
> > > > scheme of things) but all other such little babies can also be
housed in
> > > > this tablespace keeping your extent sizes consistent.
> > > >
> > > > The appropriate setting of PCTREE (for the table) is dependent of
the
> > > > potential growth of your rows . The appropriate PCTFREE (for the
index)
> > is
> > > > dependent on the amount of free space you want allocated for
subsequent
> > > > inserts. Considering the table is static, low (possibly 0) is
> > appropriate.
> > > >
> > > > I'm not sure about the formula you use for calculating the PCTUSED
> > values.
> > > > It kind of depends on the average length of a row (which is small in
> > your
> > > > case so you're OK) and the amount of DML (particularly deletes) you
have
> > > > (which is none so you're OK). The key thing you want to avoid is
having
> > > > 100 - (PCTFREE + PCTUSED) to be less than the space occupied by an
> > average
> > > > row. Being taken off the freelist below the PCTUSED marker is not a
> > pretty
> > > > thing. Note this no longer an issue with segment space management in
9i.
> > > >
> > > > The PCTINCREASE of 1 is not a good idea. With dictionary managed
> > tablespaces
> > > > it simply creates extents of differing sizes and possible
fragmentation
> > > > issues. If you use a locally managed tablespace then it's not an
issue.
> > > >
> > > > Again well done for the analysis. With larger tables, it all good
stuff
> > in
> > > > deciding appropriate tablespaces and such to use.
> > > >
> > > > Good Luck
> > > >
> > > > Richard
> > > >
> > > >
> > > > "Fernando Ruza" <fruza_at_yahoo.com> wrote in message
> > news:3d8dfca3.0207020210.
> > > > 6962a4be_at_posting.google.com...
> > > > > Hi,
> > > > >
> > > > > I'm creating all the objects for one application in an Oracle
> > > > > database. After reading a lot about sizing tables and indexes and
at
> > > > > the end following the rules commented in the book "Oracle8i DBA
> > > > > Handbook" I have decided the following storage parameters for an
> > > > > static table.
> > > > >
> > > > > I just would like to know your opinions about the analysis to
obtain
> > > > > the datas and if these parameters are ok for this kind of table.
> > > > >
> > > > >
> > > > > -- Table CLIENTES
> > > > > -----------------
> > > > > -- Database Block Size: 8192 bytes (8kb)
> > > > > -- PCTFREE: 5 % => 8102 * 0.05 = 405 bytes
> > > > > -- PCTUSED: PCTUSED + PCTFREE = 85 => PCTUSED = 80
> > > > > -- Average Row Length: 100 bytes (Get it with command ANALYZE)
> > > > > -- Number of expected rows: 50 - 100
> > > > > -- Block space available: 8192 - 90 (Fixed block header) = 8102 -
405
> > > > > = 7697 bytes
> > > > > -- Rows per Block: 7697 / 100 (bytes per row) = 76,97 ~ 77 rows.
> > > > > -- Number of Blocks: 100 (rows expected) / 77 (rows per Block) =
1,29
> > > > > ~ 2 Blocks
> > > > >
> > > > > -- Table CLIENTES
> > > > > CREATE TABLE CLIENTES
> > > > > (CODCLIENTE NUMBER NOT NULL,
> > > > > NOMBRECLIENTE VARCHAR2(30),
> > > > > RAZONSOCIAL VARCHAR2(50),
> > > > > CONTACTO VARCHAR2(50),
> > > > > DIRECCION VARCHAR2(50),
> > > > > CODIGOPOSTAL VARCHAR2(5),
> > > > > LOCALIDAD VARCHAR2(50),
> > > > > PROVINCIA VARCHAR2(50),
> > > > > PAIS VARCHAR2(50),
> > > > > CIF VARCHAR2(15),
> > > > > TFNO1 VARCHAR2(15),
> > > > > TFNO2 VARCHAR2(15),
> > > > > FAX VARCHAR2(15),
> > > > > FECHAALTA DATE,
> > > > > FECHABAJA DATE,
> > > > > OBSERVACIONES VARCHAR2(200)
> > > > > )
> > > > > TABLESPACE DATA
> > > > > STORAGE ( INITIAL 16384
> > > > > NEXT 16384
> > > > > MINEXTENTS 1
> > > > > MAXEXTENTS 2
> > > > > PCTINCREASE 1
> > > > > )
> > > > > PCTFREE 5
> > > > > PCTUSED 80;
> > > > >
> > > > > -- Primary Key of CLIENTES
> > > > > --------------------------
> > > > > -- Database Block Size: 8192 bytes (8kb)
> > > > > -- PCTFREE: 5 % => 8192 * 0.05 = 405 bytes
> > > > > -- Average index lenght: 26 bytes (Get it with SELECT
> > > > > AVG(NVL(VSIZE(Col1),0)) Avg_Row_Length FROM table_name;)
> > > > > -- Space used per row = Avg_Row_Length + Number of columns +
Number
> > > > > of long columns + 8 header bytes.
> > > > > -- Number of expected rows: 50 - 100
> > > > > -- Block space available: 8192 - 161 (Fixed index block header) =
8031
> > > > > - 405 = 7626 bytes
> > > > > -- Rows per Block: 7626 / 26 (bytes per row) = 293,30 ~ 293 rows.
> > > > > -- Number of Block: 100 (rows expected) / 293 (rows per Block) = 1
> > > > >
> > > > > -- Creamos Primary Key
> > > > > ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES
> > > > > PRIMARY KEY (CODCLIENTE)
> > > > > USING INDEX
> > > > > TABLESPACE INDEXES
> > > > > STORAGE ( INITIAL 8192
> > > > > NEXT 8192
> > > > > MINEXTENTS 1
> > > > > MAXEXTENTS 1
> > > > > PCTINCREASE 1
> > > > > )
> > > > > PCTFREE 5;
> > > > >
> > > > >
> > > > > Thanks in advance for any reply,
> > > > >
> > > > > Fernando.
> > > >
> > > >
> > >
> > >
Received on Wed Jul 03 2002 - 14:58:58 CDT