Coding TricksOracle's method for estimating database size
one. illustrate
a netizen asked me to store the result set of a query in a temporary table, and if i estimated the size of the temporary table, i thought about it by counting block. later, i thought, this method is not very operational. it is best to estimate the size before the query operation is performed.
Take a look at the ALL_TABLES table with a field: avg_row_len. The value is in bytes. You can use this field to make an estimate.
|
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
BASED ON THE ESTIMATE OF TABLE SIZE, YOU CAN ESTIMATE THE SIZE OF THE ENTIRE DATABASE. DURING THE PROJECT TESTING PHASE, ESTIMATES CAN BE MADE BASED ON ALL OBJECTS, SO THAT THE SIZE OF THE DATABASE CAN BE ESTIMATED AFTER THE SYSTEM IS ONLINE, AND STORAGE CAN BE PLANNED BASED ON THIS DATA. IT IS IMPORTANT TO NOTE HERE THAT YOU SHOULD LEAVE SUFFICIENT STORAGE SPACE FOR BACKUPS. A TYPICAL BACKUP REQUIRES 2-3 TIMES AS MUCH SPACE AS DB. IF DB IS 100G, THEN IT IS BEST TO GIVE BACKUP SPACE ABOVE 200G.
depending on dba_segments view, you can view the objects in the database that take up storage space:
[email protected](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 ARE THE TABLES AND INDEXES. BY ESTIMATING THE SIZE OF ALL TABLES AND INDEXES, YOU CAN ESTIMATE THE SIZE OF THE DB BY ADDING UP.
the size of the estimates table
size of table s record number s average field size (avg_row_len)
Avg_row_len can be queried by SQL below. It is in bytes.
[email protected](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, it will be 1000w x 93bytes in size.
three. estimate the size of the index on the table
All_indexes view does not have a avg_row_len field on the all_tables, but we can estimate it by a ratio of the view and table size. The size of the table can be estimated, and the size of the index 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)
[email protected](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:
[email protected](rac2)> select (2097152/6291456)*100,(256/768)*100 from dual;
(2097152/6291456)*100 (256/768)*100
--------------------- -------------
33.3333333 33.3333333
The ratio from bytes and blocks is the same, i.e. the index is 33% of the table. So if the size after the estimate table is 1000M, then the corresponding index size is 1000M x 33% x 330M.
add up the size of all tables and indexes to an estimate of the size of the entire database.
-------------------------------------------------------------------------------------------------------
QQ:492913789
Email:[email protected]
Blog: http://www.cndba.cn/dave
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)
DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 (FULL) DBA5 GROUP: 142216823 (FULL)
DBA6 GROUP: 158654907 (FULL) DBA7 GROUP: 69087192 (FULL) DBA8 GROUP: 172855474
DBA SUPERGROUP 2: 151508914 DBA9 GROUP: 102954821 CHAT GROUP: 40132017 (FULL)
--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected