最近有一个业务功能要上线,生产数据库环境之前已经到位,目前要做的是估算下,业务数据量对数据库空间,有何影响。开发同学根据表字段定义,分别统计出了最大占用空间,以及预计占用空间量,计算得很细致。
今儿碰巧看见了一篇MOS文章,《How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (文档 ID 1585326.1)》,其中介绍了一些存储过程,可以用来估算表和索引空间,用起来比较方便,所以做一些实验说明下,会有用武之地,当然之前就说过,工具是用来方便执行一些功能,但前提是需要从原理上理解,融会贯通,将他变为自己的知识,这才是王道。
首先对于表的估算,用到的是DBMS_SPACE包中的CREATE_TABLE_COST这个存储过程,这个存储过程有两个版本,第一个版本是根据平均行长,估算表的容量,第二个版本是根据列的信息,估算表的容量,究竟有何区别?
这是对这两个版本的介绍,
这是这两个版本的定义,
总结下可以看出,这两个版本的相同点,就是需要预计行数、pct_free参数以及表空间名称,这几个参数,输出均有使用量(used_bytes),以及分配量(alloc_bytes)这两个维度。不同点就是,版本一需要平均行长(avg_row_size),版本二需要CREATE_TABLE_COST_COLUMNS类型的变量(colinfos)。
这是对于这两个存储过程,输出信息说明,
创建测试表,手工收集表的统计信息,
可以看出,测试表平均行长是68,
用版本一,输入参数为表空间名称,平均行长,预计数据量(10万行),pct_free,输出参数为ub和ab,显示使用的字节数为8036352,分配的字节数为8388608,
上面则是根据平均行长,预估的表容量,下面实际测试10万条数据,检索user_segments视图,其占用空间为9437184字节,
可知,根据版本一存储过程,计算出表预计空间量为8M,实际表占用空间量为9M,
使用版本二的存储过程,其中CREATE_TABLE_COST_COLUMNS类型要求写出所有字段,包括类型和长度,计算出使用的空间容量为9314304,分配的字节数为9437184,这和实际分配的表空间一致,
版本二比版本一计算准确,原因是因为版本二用了字段定义,包括类型和长度,外加存储属性,版本一则是根据平均行长,来预计空间,会相对不准。但需要注意,这是计算的字段极限值,比如VARCHAR2(30)并未用满,则可能实际占用空间,并不是此值,只能说明版本二计算的容量,任何情况下不会超过,但有可能用不了这些。
版本二这块,MOS中对于两个字符串类型,用的是CHAR,并未直接用VARCHAR2,有如下解释,
Note : we changed VARCHAR2 to CHAR to get the maximum possible estimation .
这点有些迷惑,而且使用CHAR和VARCHAR2计算出来的一样,不知道如何解释,有理解的朋友,还请指教!
这是CREATE_TABLE_COST_COLINFO的定义,
这是存储过程其他字段描述,
其次,对于索引空间的预算,使用的是CREATE_INDEX_COST存储过程,
他的使用有几点注意,
其中参数DDL为需要创建的索引,此处为a和b字段建索引,显示使用的字节数为3800000,分配的字节数为6291456,
创建实际索引,检索user_segments视图,其占用索引空间容量为6291456,说明上面的存储过程,预计的结果是准确的,
总结:
1. 对于表和索引空间的预估,可以使用DBMS_SPACE包的CREATE_TABLE_COST和CREATE_INDEX_COST存储过程,虽然没有看这两个存储过程的实现,但猜测平均行长算法,会根据预计行数,做一些计算,字段定义算法,则会根据每个字段的长度,和预计行数,做一些计算,其实和我们手工根据这些算法,计算的方式类似,只是封装起来,便于调用。
2. CREATE_TABLE_COST根据列字段定义预估,是比较准确的,根据平均行长,并不很准确。需要注意的是,这里计算的是字段极限值,不会超过此值,但有可能实际用不了这些。
如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)