How to estimate the database size in Oracle

I. Description

A netizen asked me to store a query result set to the temporary table. If I estimated the size of the temporary table, I wanted to calculate it through the statistical block. Later I thought that this method was not very operational. It is best to estimate the size before the query operation is executed.

Check the ALL_TABLES table, which has a field avg_row_len. The unit of value is bytes. You can use this field to make an estimation.

AVG_ROW_LEN *

NUMBER

Average length of a row in the table (in bytes)

Http://download.Oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_2117.htm#i1592091

You can estimate the size of the entire database based on the table size. In the project test phase, you can estimate all objects to estimate the size of the database after the system goes online. You can plan the storage based on the data. Note that you need to leave enough storage space for the backup. Generally, backup requires 2-3 times the database space. If the database is 100 GB, the backup space should be more than GB.

Based on the dba_segments view, you can view the Objects occupying the storage space in the database:

SYS @ anqing2 (rac2)> select distinctsegment_type from dba_segments;

 

SEGMENT_TYPE

------------------

LOBINDEX

INDEX PARTITION

TABLE PARTITION

NESTED TABLE

ROLLBACK

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

CLUSTER

TYPE2 UNDO

 

11 rows selected.

 

Here, we mainly use tables and indexes. Estimate the size of all tables and indexes, and then estimate the size of the database by adding them.

Ii. Estimate the table size

Table size = number of records * Average field size (avg_row_len)

Avg_row_len can be queried using the following SQL statement. The Unit is bytes.

SYS @ anqing2 (rac2)> selecttable_name, avg_row_len from all_tables where table_name = 't1 ';

 

TABLE_NAME AVG_ROW_LEN

-----------------------------------------

T1 93

If table T1 is estimated to be 10 million rows in the future, the size of table T1 is 1000 w * 93 bytes.

3. Estimate the index size of a table

The All_indexes view does not have the avg_row_len field on all_tables. However, we can estimate the ratio of view to table size. We can estimate the table size. The index size can be estimated by this ratio.

SQL> create index idx_t1_created on t1 (created)

SQL> exec dbms_stats.gather_table_stats ('sys ', 'T1', cascade => TRUE)

SYS @ anqing2 (rac2)> selectsegment_name, segment_type, bytes, blocks from dba_segments where segment_namein ('t1', 'idx _ T1_CREATED ');

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

-----------------------------------------------------

T1 TABLE 6291456 768

IDX_T1_CREATED INDEX 2097152 256

Calculate the ratio of indexes to tables:

SYS @ anqing2 (rac2)> select (2097152/6291456) * 100, (256/768) * 100 from dual;

(2097152/6291456) * 100 (256/768) * 100

----------------------------------

33.3333333 33.3333333

The ratio of bytes to blocks is the same, that is, the index is 33% of the table.If the estimated table size is 1000 M, the corresponding index size is 1000 M * 33% = 330 M. 

The sum of the sizes of all tables and indexes is the estimation of the size of the entire database.