估计数据库大小

Question

我必须做一些项目，需要估计DB的大小。我将在项目中使用Oracle，因为我们将拥有大量的事务和数据。但在“演示文稿”中，我需要说明，在5年内，我对DB增长的期望有多大。

因此，我有一个PDF格式，它可以计算行大小和其他东西，但适用于Server。我想为甲骨文这么做。

pdf是一个估算公式--没有真正的DB，所以我不能查询DB来检查实际大小(这是我在这里搜索时在其他帖子中看到的)。

Server行的公式是

Row_Size = Fixed_Data_Size +Variable_Data_Size +Null_Bitmap +4

这个公式也适用于甲骨文？此外，"4“是行的标题的大小(我不知道如何翻译英文的意思)

所以，我必须在甲骨文里这样做。Oracle中的值4是多少？其他的事情我想我已经解决了。

Answer 1

理想情况下，您应该创建数据库，加载一些示例数据，测量大小，并进行外推。这就是，到目前为止，用5年时间估算数据库大小的更准确的方法。

如果您确实想要计算数据库大小，通常首先要计算出在单个块中容纳多少行。为了简单起见，我们假设行永远不会被删除，而更新永远不会改变行的大小。我们还假设没有使用压缩。否则，事情会变得更加复杂。

计算行中数据的大小。对于固定大小的数据类型(即DATE、CHAR)，这只是类型的大小。对于可变大小的数据类型(即NUMBER、VARCHAR2)，这是列中数据的平均大小。有几个字节的额外开销，但是你可以非常安全地忽略它--它们将被错误地估计实际数据的大小以及随后估计每个块的行数时的错误所淹没。

如果您期望每一行都有x字节的数据，则每个块的行数将为

<<rows per block>> = 
  floor( <<database block size>> * 
          (1 - <<pctfree of table>>/100) / 
          <<size of row>> ) + 1

假设一行小于<<database block size>> * <<pctfree of table>>/100。如果行较大，则不要将1添加到floor的结果中。

一旦知道每个块的行数，表的估计大小将为

<<size of table>> = 
  ceil( <<number of rows in table>> / <<rows per block>> ) *
    <<database block size>>

遍历了一个示例

我的数据库块大小为8k，我们将假设我使用的是默认的PCTFREE 10 (这意味着10%的块保留用于将来的更新，以增加行的大小)。我将创建一个简单的两列表

SQL> create table foo(
  2    foo_id number,
  3    foo_str varchar2(100)
  4  );

Table created.

如果foo_id将成为主键，其值为1到100万，则每个foo_id将占用1到7个字节的空间。但是我从测试中也知道，平均来说，它将花费6个字节(实际上是5.89字节)。当然，foo_id值越大，平均每个foo_id所需的空间就越大。Oracle平均每个元素需要1.1个字节来存储数字1-10，1.92字节用于存储1-100，2.89字节用于存储1-1,000，3.89字节用于存储1-10,000，4.89字节用于存储1-100，5.89字节用于存储1-1,000,000。因此，让我们对我们的示例进行估计，foo_id将需要6个字节，而foo_str将需要50个字节，因为平均foo_str大约是50个字节。因此，我们将估计行大小为56字节。

每个块的行数。

<<rows per block>> = 
  floor( 8192 * 
          (1 - 10/100) / 
          56 ) + 1

计算到每块132行。如果我们想估计一百万行表的大小，

<<size of table>> = 
  ceil( 1000000 / 132 ) *
    8192

计算值为59.19 MB。

现在，让我们来测试一下我们的估计

我们将插入100万行，其中foo_id从1到1,000,000，foo_str是一个字符串，随机长度介于1到100之间。

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    select level, dbms_random.string( 'p', dbms_random.value(1,100))
  3      from dual
  4*  connect by level <= 1000000
SQL> /

1000000 rows created.

我们对平均行长的估计是对的(请注意，实际上，您不会这么接近--您对可变列大小的估计不会那么准确)。

SQL> exec dbms_stats.gather_table_stats( 'SCOTT', 'FOO' );

PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf

  1  select avg_row_len, num_rows
  2    from user_tables
  3*  where table_name = 'FOO'
SQL> /

AVG_ROW_LEN   NUM_ROWS
----------- ----------
         56    1000000

但实际的桌子有多大？最常见的衡量标准是查看段的大小，即72 MB。

SQL> select sum(bytes)/1024/1024 mb
  2    from user_segments
  3   where segment_name = 'FOO';

        MB
----------
        72

我们的猜测比预期的少了20%，那时候我们对一排的估计是完美的。这是因为Oracle将空间分配给我们忽略的块表，称为区段。这方面有不同的算法，取决于表空间的设置。假设最近的Oracle版本中的所有表都在本地托管表空间中，您将在统一范围分配和自动范围分配之间进行选择。在我的示例中，我使用的表空间使用自动范围分配。相应地，准确的算法可能取决于您正在使用的Oracle版本。在我的例子中，前16个区段是64 kb，接下来63个区段是1MB，最后一个区段是8MB。

SQL> ed
Wrote file afiedt.buf

  1  select bytes, count(*)
  2    from user_extents
  3   where segment_name = 'FOO'
  4   group by bytes
  5*  order by bytes
SQL> /

     BYTES   COUNT(*)
---------- ----------
     65536         16
   1048576         63
   8388608          1

这意味着我可能有点不走运，我的数据比79个区段(总计64 MB )的数据要多一点，所以我不得不分配一个大小为8MB的第80区，总共72 MB。我们可以使用数据库管理系统_太空包裹获得更多关于正在使用的空间的详细信息。当我们这样做时，我们看到实际上我们只使用了分配的72 MB中的66.22MB。所以我们的实际估计误差只有10%。

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_space_used NUMBER;
  3   l_space_allocated NUMBER;
  4   l_chained_pct NUMBER;
  5  BEGIN
  6    dbms_space.object_space_usage(
  7      'SCOTT',
  8      'FOO',
  9      'TABLE',
 10      NULL,
 11      l_space_used,
 12      l_space_allocated,
 13      l_chained_pct);
 14    dbms_output.put_line('Space Used: ' || TO_CHAR(round(l_space_used/1024/1024,2)) || ' MB');
 15    dbms_output.put_line('Space Allocated: ' || TO_CHAR(l_space_allocated/1024/1024) || ' MB');
 16    dbms_output.put_line('Chained Percentage: ' || TO_CHAR(l_chained_pct));
 17* END;
SQL> /
Space Used: 66.22 MB
Space Allocated: 72 MB
Chained Percentage: 0

PL/SQL procedure successfully completed.

有捷径吗？

如果使用的是最新版本的Oracle，则可以使用dbms_space.create_table_cost过程来估计表的大小。有几种方法可以做到这一点。第一个选项是传递行的大小。使用我们的56个字节估计，这将产生一个64 MB的表大小估计和63.52 MB使用的表大小估计，非常接近

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4  BEGIN
  5    dbms_space.create_table_cost('USERS',
  6                                 56,
  7                                 1000000,
  8                                 10,
  9                                 l_used_bytes,
 10                                 l_allocated_bytes);
 11    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 12    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 13* END;
SQL> /
Used Bytes: 63.52 MB
Alloc Bytes: 64 MB

PL/SQL procedure successfully completed.

还可以指定表中列的数据类型。但是，由于没有办法指定平均大小，这往往不如自己指定的精确。但是，在我们的例子中，这是非常好的，因为我们的VARCHAR2列恰好被字符串填充，这些字符串平均是该列最大大小的一半。但是，在我们的例子中，它正确地估计了分配的大小为72 MB，使用了67.94 MB。

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2   l_used_bytes NUMBER;
  3   l_allocated_bytes NUMBER;
  4   l_cols sys.create_table_cost_columns;
  5  BEGIN
  6    l_cols := sys.create_table_cost_columns(
  7            sys.create_table_cost_colinfo('NUMBER',10),
  8            sys.create_table_cost_colinfo('VARCHAR2',100));
  9    dbms_space.create_table_cost('USERS',
 10                                 l_cols,
 11                                 1000000,
 12                                 10,
 13                                 l_used_bytes,
 14                                 l_allocated_bytes);
 15    dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
 16    dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
 17* END;
SQL> /
Used Bytes: 67.94 MB
Alloc Bytes: 72 MB

PL/SQL procedure successfully completed.

Answer 2

有这样一个公式。您可以在Oracle7手册附录中找到它。还有一个关于Oracle Metalink的说明，它涉及相同的主题:范围和块空间计算以及在Oracle数据库中的使用(ID 10640.1)。