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 - 21:58:58 CEST