数据库空间估算

WindySky 2017-06-06

展开全文

在做数据库设计过程中必须要进行多个方面的计划，其中空间估算尤其是一个DBA所不能回避的问题。
空间估算的准确与否决定了
1。系统对资源的需求--投资问题
2。数据分布是否合理--数据结构设计问题
3。应用设计，特别是数据存取方面的设计--系统运行性能问题等。

同时，由于ORACLE数据类型的多样，数据模型的复杂，空间估算也代表了相当大的工作量。
通常的空间估算包括了对TABLE，INDEX，CLUSTER，ROLLBACK SEGMENT，TEMPORARY SEGMENT以及REDO LOG方面等的计算。在本文中重点给出了对TABLE，INDEX的估算。
应该强调的是估算工作主要针对数据库中比较大的表，以及在应用中变化量较大的表。

在进行空间估算之前，有必要再回顾以下认识：
1。对于数据块的认识：
。数据块的大小有DB_BLOCK_SIZE决定，并且只有通过重建数据库来得到调整。空间计算的根据来自于数据块。
。数据块由数据块头，数据区组成。数据区的使用由PCTFREE，PCTUSED控制。当INSERT发生时，
PCTFREE决定了该块所能容纳的最多的数据，即，剩余空间占数据块实际空间的百分比不小于PCTFREE。
空间估算中PCTFREE是很一个很重要的参数。
2。对于在表上进行的事务(TRANSACTION)类型要有一个清晰的认识，因为这会帮助如何更好的确定PCTFREE & PCTUSED。
例如：
如果PCTFREE的值太大，记录又可能很长，这会引起行链接(ROW-CHAINING)，同时空间利用率也不高；如果PCTFREE的值太小，会影响块数据记录的增长，容易引起行迁移(ROW-MIGURATING)；
如果PCTUSED的值太大，空间上似乎没什么问题，但数据块的操作上又有潜在的性能问题，因为数据块需要在
FREELIST上反复搬动；
如果PCTUSED的值太小，则又出现了空间利用率不高的问题。
3。PCTFREE & PCTUSED表示的是DB_BLOCK_SIZE减去数据块头后的百分比值。
4。ORACLE采用的是延迟写(DELAYED CLEANOUT)方式来完成事务处理，所以由DELETE及UPDATE等操作释放的空间并不会
马上被利用。这一点在数据变化频繁时需要加以考虑。
5。记录结尾空值(TRAILING NULLS)不占存储空间，但如果出现在记录中间，则会占用1 byte。所以ORACLE建议尽可能
将经常出现空值的列放在结构定义的尾部。
6。CHAR,DATE是定长数据类型，NUMBER，VARCHAR2，LONG，LONG RAW等是变长数据类型。其中DATE的长度固定为7 BYTES，
包括世纪，年，月，日，时，分，秒。

方法一：

对表的空间估算
根据以下步骤确定表对空间占用的情况。要注意的是这里给出的是该表初始建立时所占的空间。
一。数据块头的大小
block header = fixed header + variable transaction header + table directory + row directory
where:
fixed header = 57 bytes
variable transaction header = 23 * i (i 代表 INITRANS的值，这是个动态值，最大不超过MAXTRANS)
table directory = 4 * n (对于非CLUSTERED表来讲n=1)
row directory = 2 * x (x=该数据块中的记录数)
说明：
1。VARIABLE TRANSACTION HEADER代表的是发生在此数据块上的事务所占用的空间。每个事务占用23 BYTES，
一般情况下(INITRANS=1)，块头部会保留23 BYTES，更多的事务要占用空间会利用PCTFREE所保留的空间。所以
随着事务的增加，数据块头会向下“长”也是这个道理。
2。随着块中记录数量的增加，ROW DIRECTORY的值也会增加，这样也会增加块头部的大小。并且，即使后来删除了
这些记录，块头也不会“回缩”。
有了这些概念，如上述公式，假定INITRANS=1，数据块头的大小为：
block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes

二。块中可用的空间为：
available data space = (block size - total block header) -
((block size - total block header) * (PCTFREE/100))
假定BLOCK SIZE=2K，PCTFREE=10，即：
available data space = (2048 - (84 + 2x)) - ((2048 - (84 + 2x)) * (10/100))
= (1964 - 2x) - ((2048 - 84 - 2x) * (10/100))
= (1964 - 2x) - (1964 - 2x) * 0.1
= (1964 - 2x - 196 + 0.2x) bytes
= (1768 - 1.8x) bytes

三。计算每条记录的长度：
这取决于三个方面，
1。表中定义的列的个数
2。每列的类型
3。变长列的平均长度(根据与实际数据库类似的测试数据库)
SELECT AVG(NVL(VSIZE(col1), 1)) +
AVG(NVL(VSIZE(col2), 1)) +
... +
AVG(NVL(VSIZE(coln), 1)) "SPACE OF AVERAGE ROW "
FROM table_name;
注：记录结尾的列如果不空，上述公式有效；如果结尾的列为空，该列长被计为0。

举例来讲，我们创建了TEST表，
CREATE TABLE test ( A CHAR(10), B DATE, C NUMBER(10, 2));
我们可以通过
SELECT AVG(NVL(VSIZE(A), 1)) +
AVG(NVL(VSIZE(B), 1)) +
AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW "
FROM test;
来估算每条记录的大小。
如果测试数据库不存在，则需要分别考虑各列的情况。
1。对于定长列，每条记录的该列值也是定长；
2。对于变长列，首先要估计该列的平均值，然后再决定该值需要多大空间。
就TEST表来讲，A列与B列均为定长列，所以他们分别需要10及7 BYTES来存储。对于C列，
假设平均该值有8位长度，由于是数字类型，所以存储长度为：
average length column C = (average_precision/2 + 1) bytes
= (8/2 + 1) bytes
= 5 bytes

这样， average row (for columns)= (A + B + C)
= (10 + 7 + 5) bytes
= 22 bytes

四。记录平均存储空间为：
average row size = row header + F + V + D
其中:
row header = 3 bytes (非CLUSTER表)
F = 列长的总和(1 byte表示长度的列，CHAR, NUMBER, DATE, and ROWID types)
V = 列长的总和(3 bytes表示长度的列，CHAR, LONG, RAW, LONG RAW datatypes)
D = average row (for columns,from above)
说明：
1。由于每列数据在存储时需要额外的位来表示其长度，通常，如果该列值> 250，需要3 bytes表示，
如果 <250，需要1 byte表示，
2。对于NON-CLUSTERED表，average row size最小值为9，所以如果上述值小于9，则要用9 bytes计算。

再看TEST表，
average row size = row header + F + V + D
= 3+(1*3)+(3*0)+22
= 28 bytes

五。平均每块中的记录数为，
average number of rows per block = floor(available data space / average row size)
x = floor((1768-1.8x)/28)
28x =1768-1.8x
x = 59 (近似值)
根据记录量的大小，例如TEST表中有10000条记录，可以算得该表需要的数据块数量：
number of blocks
for the table = number of rows / average number of rows per block
= 10000/59
= 169 blocks (近似值)

从而得到该表可能占据的存储空间为169*db_block_size.
对索引空间的估算
一。数据块头的大小
block header size = fixed header + variable transaction header
其中:
fixed header = 113 bytes
variable transaction header = 23*i (i=INITRANS)
假定INITRANS=2，
block header size = 113 + (23 * 2) bytes
= 159 bytes

二。数据块中可供INDEX使用的空间为：
available data space = (block size - block header size) -
((block size - block header size) * (PCTFREE/100))
假定PCTFREE=10，DB_BLOCK_SIZE=2K
available data space = (2048 bytes - 159 bytes) -
((2048 bytes - 159 bytes) * (10/100))
= 1889 bytes - 188.9 bytes
= 1700.1 bytes

三。INDEX entry的长度
与表的计算方法类似，估算出被索引的列的平均长度之和，利用下面公式得到每一条INDEX ENTRY的长度。
bytes per entry = entry header + ROWID length + F + V + D
其中:
entry header = 1 byte
ROWID length = 6 bytes
F = 列长的总和(1 byte表示长度的列，CHAR, NUMBER, DATE, and ROWID types)
V = 列长的总和(3 bytes表示长度的列，CHAR, RAW datatypes)
D = 被索引列长之和 (from above)

如果我们在TEST表上建立了在A，B，C三列上的一个索引，则D=22，于是
bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes
= 32 bytes

四。于是，该索引所需要的数据块数量为：
number of blocks
for index = 1.1 * ((number of not null rows * avg. entry size) /
((floor (avail. data space / avg. entry size)) *
(avg. entry size))

注：这里带入了系数1.1主要考虑到INDEX的建立需要分支(BRANCH ENTRY BLOCKS)
number of blocks
for index = 1.1 * ((10000 * 32 bytes) / ((floor (1700/32 bytes)) *
(32 bytes))
= 208 blocks

从而得到该表可能占据的存储空间为208*db_block_size.

方法二：
利用ANALYZE进行空间估算
如果可以建立一个模拟环境(模拟数据或者实际数据)，也可以利用ANALYZE命令来进行空间估算。
具体方法为：
一。ANALYZE TABLE testtable COMPUTE STATISTICS
在DBA_TABLES中有下面的列
=========================================
AVG_SPACE 表中平均剩余空间
AVG_ROW_LEN 平均一条记录的长度
OWNER 表的所有者
TABLE_NAME 表名
NUM_ROWS 表中记录数
=========================================

则：
表的大小(估计值) = DB_BLOCK_SIZE* ceil(实际表中记录数/floor((DB_BLOCK_SIZE-AVG_SPACE)/AVG_ROW_LEN))

二。ANALYZE INDEX testindex COMPUTE STATISTICS
在DBA_INDEXES中有下面的列
=========================================
OWNER 索引的所有者
INDEX_NAME 索引名
TABLE_OWNER 索引的基表所有者
TABLE_NAME 索引的基表
LEAF_BLOCKS 索引中叶子块数
BLEVEL B-Tree level: B-tree的深度，
如果为0表示根与叶子在同一个LEVEL上.
=========================================

则：
索引的大小(估计值) = LEAF_BLOCKS*ceil(实际表中记录数/NUM_ROWS);
如果BLEVEL <> 0,还需要考虑再加上一个系数，用来包含用作分支(BRANCH)块的空间。
此外，在INDEX_STATS视图中包含了更详细的有关索引空间及结构上的分配情况，这需要事先进行
ANALYZE INDEX testindex VALIDATE STRUCTURE的动作。有兴趣可以详细参见该视图的定义。