在做数据库设计过程中必须要进行多个方面的计划,其中空间估算尤其是一个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的动作。有兴趣可以详细参见该视图的定义。

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注