Re: Opinions for this storage parameters.

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 - 04:29:21 CEST