Re: Opinions for this storage parameters.

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:15:28 CEST