Re: Calculate table, index (storage parameters)

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Wed, 26 Aug 1998 15:23:25 GMT
Message-ID: <01bdd115$35a276c0$a504fa80@mndnet>

Hi

I am sure you will get lot of suggestions and there is no one right way.

Here is one way to estimate table and index size, if the table and indexes are already populated. There are ohter ways too, such as using the command ANALYZE, given later here on table or ANALYZE INDEX VALIDATE STRUCRURE on index.

Populate the table with some sample data and try to come up with an estimate with the following formula:

select ( sum(vsize(col1)) + sum(vsize(col2)) + ....... sum(vsize(colN)) )/count(*)

    from table_name;

This will give you the actual size in bytes of Oracle internal representation of all the data per record in that table. Same can be done for the indexed columns.

Because of the PCTFREE, PCTUSED parameters of CREATE command and other overhead, multiply the number of bytes generated by the above SQL statements by 2. These are the number of bytes required per record for the table(s) and the index(s).

Set storage parameter PCTINCREASE for the user tablespaces to 1. This ensures that the SMON system daemon process will continually COALESCE the continguous free space in these tablespaces as required. In fact all user tablespaces, except the once set up for Oracle such as SYSTEM, TOOLS, RBS etc., should have their PCTINCREASE set to 1. This will rduce fragmentation.

alter tablespace tablespace1 default storage ( pctincrease 1);

There are other opinions, but I prefer all my data in one large initial extent if I know what the size of the table going to be. Make your next extent anywhere from 20 to 50% depending on growth.

Oracle Administrator’s Guide, Chapter 8 - Managing Schema Objects, recommends the following settings for different scenarios:

1. Scenario: Common activity includes UPDATE statements that increase the size of the rows. Here “increase the size of the rows” means that a column value(s) is increased in size, such as a description column value increases from 15 bytes to 100 bytes.

	Settings:	PCTFREE  =  20
				PCTUSED =  40



2.	Scenario:	Most activity includes INSERT and DELETE statements, and

UPDATE statements do not increase the affected rows.

	Settings:	PCTFREE  =  5
				PCTUSED = 60



3.	Scenario:	The table is very large.  Therefore storage is a primary

concern. Most activity includes read-only transactions.

	Settings:	PCTFREE  =  5
				PCTUSED = 90

On indexes you can only set PCTFREE. There is no provision for PCTUSED on indexes.

For both the tables and the indexes set the STORAGE parameter PCTINCREASE to 1, rather than using the default parameter of 50 %, so that you have control over the NEXT_EXTENT via ALTER command.

Do not worry too much about sizing the indexes in the beginning, they can be always rebuilt later when the tables have actual data.

ANALYZE statistics command is helpful to check the used BLOCKS and EMPTY_BLOCKS for a table. This command is CPU intensive, therefore on large tables always use ESTIMATE STATISTICS rather than COMPUTE STATISTICS.

Use the following command in SQL*Plus.

ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 10000 ROWS

        BLOCKS and EMPTY_BLOCKS are the actual used blocks and the actual empty blocks in tables ALL_TABLES and USER_TABLES. Also there are columns num_rows and avg_row_length that you may want to look at.

A large number of EMPTY_BLOCKS would indicate that disk space may be wasted and PCTUSED parameter may need to be reset.

So there is my 2 cents worth !!!

Oracleguru
www.oraclguru.net
oracleguru_at_mailcity.com

LEVINIR <levinir_at_aol.com> wrote in article <1998082600092000.UAA01155_at_ladder01.news.aol.com>...
> I need a help in setting storage parameters (iniital extent, next extent,
> pctincrease, etc) when I create tables and specially indexes for the
> application. May be someone already have a formula that you use for this
> purpose. I tried to use Oracle Server Administrator Guide (Appendix),
somehow
> complicated.
>
> Thanks in advance.
> Irina.
>
>
Received on Wed Aug 26 1998 - 10:23:25 CDT