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: Opinions for this storage parameters.

Re: Opinions for this storage parameters.

From: Fernando Ruza <fruza_at_yahoo.com>
Date: 3 Jul 2002 09:52:46 -0700
Message-ID: <3d8dfca3.0207030852.7b19dc99@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. 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, 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 ?? 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; 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 - 11:52:46 CDT

Original text of this message

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