テーブル・インデックスの容量見積方法

はじめに

昔はテーブル容量見積Excelシートが日本オラクル社から提供されていたりしましたが、残念ながら現在は提供されていません。しかし、実は、Oracle10gから、Oracle Database自身がPL/SQLユーティリティ・パッケージの形で容量見積ツールを提供していますので、容量見積自体は可能です。なお、動作確認環境は、18cXEです。

概要

テーブルの容量は、領域管理用のツールをまとめたDBMS_SPACEパッケージのうち、CREATE_TABLE_COSTというプロシージャで見積もることが可能です。インデックスの容量見積はDBMS_SPACE.CREATE_INDEX_COSTプロシージャです。

CREATE_TABLE_COSTプロシージャは2種類の見積方法を提供します。どちらも見積に必要な情報を入力すると、テーブルの容量(PCTFREEなどのブロック内のオーバーヘッドを考慮した値)と、テーブルの容量に表領域のエクステント計算を加味したアロケート容量の2種類の見積容量を戻します。
一方CREATE_INDEX_COSTプロシージャは1種類の見積方法を提供します。

テーブルの容量見積：平均レコード長とレコード件数から見積もる方法

既存のテーブルがx件に増えたらどのくらいの容量になるのか、というシナリオに合った見積方法です。テーブルのレコード長の計算方法をご存じの方であれば、机上見積も可能です。プロシージャの呼び出し形式は以下の通りです。

DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN  VARCHAR2,
   avg_row_size       IN  NUMBER,
   row_count          IN  NUMBER,
   pct_free           IN  NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

引数名	IN/OUT	説明
tablespace_name	IN	表領域名。存在する表領域名を指定する必要があります
avg_row_size	IN	平均レコード長。既存のANALYZE済みのテーブルであれば、{CDB/DBA/ALL/USER}_TABLESのAVG_ROW_LEN列の値を利用するのが簡単です
row_count	IN	レコード件数
pct_free	IN	PCTFREEパラメータの値。既存のテーブルであれば、{CDB/DBA/ALL/USER}_TABLESのPCT_FREE列の値を利用するのが簡単です
used_bytes	OUT	テーブル容量。PCTFREE値、ヘッダサイズなど、ブロック内のオーバーヘッドが考慮されている値です
alloc_bytes	OUT	アロケート容量。表領域のエクステント計算を加味したテーブル容量

以下、既存の表をベースに、実際に使用してみます。
まず、AVG_ROW_LENとPCT_FREEとTABLESPACEの値を確認します。対象は、EMPNO列を9桁に拡張しているEMP表です。実際に1億件入っています。

SQL> select num_rows, avg_row_len, pct_free, tablespace_name
  2    from user_tables where table_name = 'EMP';

  NUM_ROWS AVG_ROW_LEN   PCT_FREE
---------- ----------- ----------
TABLESPACE_NAME
------------------------------------------------------------
 100000000          39         10
USERS

上記の値をそのまま転記して使用することももちろん可能ですが、折角なのでPL/SQLを使用して動的に取得して計算してみます。件数は実件数と同じ1億件で試してみました。

SQL> set serveroutput on
SQL> declare
  2    avg_row_len number;
  3    pct_free    number;
  4    tablespace_name varchar2(30);
  5    used_bytes number;
  6    alloc_bytes number;
  7  begin
  8    select avg_row_len, pct_free, tablespace_name
  9      into avg_row_len, pct_free, tablespace_name
 10      from user_tables where table_name = 'EMP';
 11    dbms_space.create_table_cost(
 12        tablespace_name, avg_row_len, 100000000, pct_free,
 13        used_bytes, alloc_bytes
 14    );
 15    dbms_output.put_line('テーブル容量 : '
 16      || to_char(used_bytes / 1024 / 1024, '999,990') || 'MB');
 17    dbms_output.put_line('アロケート容量 : '
 18      || to_char(alloc_bytes / 1024 / 1024, '999,990') || 'MB');
 19  end;
 20  /
テーブル容量 :    4,516MB
アロケート容量 :    4,544MB

PL/SQLプロシージャが正常に完了しました。

実際の容量と比較してみます。

SQL> select blocks * 8192 / 1024 /1024 as "ACTUAL_SIZE(MB)"
  2    from user_tables where table_name = 'EMP';

ACTUAL_SIZE(MB)
---------------
     4774.53906

実際値が見積値よりも5%強オーバーしています。5GB近いサイズのテーブルでこの程度の誤差なら、問題ないように思います。
CREATE_TABLE_COSTプロシージャは、平均長をベースとした見積を行います。対して現実のテーブルは、各レコードの実際の長さやレコードの格納順、更新による断片化などに起因する格納効率の変化により、見積上の値とはどうしても乖離が発生してしまいます。実際にデータを格納しているテーブルにて見積との誤差が大きい場合は、テーブルの断片化の可能性もご確認下さい。また、実際の容量見積の際は、見積値に余裕を持たせた値を採用するようにしてください。

テーブルの容量見積：列情報とレコード件数から見積もる方法

いわゆる机上見積に近いのはこちらの方法となります。先の平均レコード長から見積もる方法と、2番目の引数が異なり、2番目の引数として列情報を指定します。ただし、指定の方法がちょっと面倒です。プロシージャの呼び出し形式は以下の通りです。

DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN  VARCHAR2,
   colinfos           IN  SYS.CREATE_TABLE_COST_COLUMNS,
   row_count          IN  NUMBER,
   pct_free           IN  NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

引数名	IN/OUT	説明
tablespace_name	IN	表領域名。存在する表領域名を指定する必要があります
colinfos	IN	列情報。具体的な引数指定方法は後述します
row_count	IN	レコード件数
pct_free	IN	PCTFREEパラメータの値。既存のテーブルであれば、{CDB/DBA/ALL/USER}_TABLESのPCT_FREE列の値を利用するのが簡単です
used_bytes	OUT	テーブル容量。PCTFREE値、ヘッダサイズなど、ブロック内のオーバーヘッドが考慮されている値
alloc_bytes	OUT	アロケート容量。表領域のエクステント計算を加味したテーブル容量

colinfosパラメータのデータ型が、SYS.CREATE_TABLE_COST_COLUMNSとよくわからないデータ型になっています。SQL*PlusのDESCコマンドを使用して、どのような方なのかを確認してみましょう。

SQL> desc SYS.CREATE_TABLE_COST_COLUMNS
 SYS.CREATE_TABLE_COST_COLUMNS VARRAY(50000) OF SYS.CREATE_TABLE_COST_COLINFO
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 COL_TYPE                                           VARCHAR2(200)
 COL_SIZE                                           NUMBER

SYS.CREATE_TABLE_COST_COLUMNS型は、SYS.CREATE_TABLE_COST_COLINFO型のVARRAY(50000)と表示されました。VARRAYは配列のデータ型で、括弧内は要素数です。SYS.CREATE_TABLE_COST_COLINFO型は、COL_TYPEとCOL_SIZEの2列で構成されるユーザー定義型(オブジェクト型)です。COL_TYPEは「CHAR」や「NUMBER」などのデータ型を、COL_SIZEは桁数を指定します。ユーザー定義型への代入は、該当の型のコンストラクタ・メソッドをコールすることで行います。と、文章で説明してもわかりにくいと思うので、具体的な指定方法は、以下のサンプルを参照してください。

SQL> set serveroutput on
SQL> declare
  2    col_data sys.create_table_cost_columns;
  3    used_bytes number;
  4    alloc_bytes number;
  5  begin
  6    col_data := sys.create_table_cost_columns(
  7      sys.create_table_cost_colinfo('number', 9),
  8      sys.create_table_cost_colinfo('varchar2', 10),
  9      sys.create_table_cost_colinfo('varchar2', 9),
 10      sys.create_table_cost_colinfo('number', 4),
 11      sys.create_table_cost_colinfo('date', null),
 12      sys.create_table_cost_colinfo('number', 7),
 13      sys.create_table_cost_colinfo('number', 7),
 14      sys.create_table_cost_colinfo('number', 2)
 15    );
 16    dbms_space.create_table_cost(
 17        'USERS', col_data, 100000000, 10,
 18        used_bytes, alloc_bytes
 19    );
 20    dbms_output.put_line('テーブル容量 : '
 21      || to_char(used_bytes / 1024 / 1024, '999,990') || 'MB');
 22    dbms_output.put_line('アロケート容量 : '
 23      || to_char(alloc_bytes / 1024 / 1024, '999,990') || 'MB');
 24  end;
 25  /
テーブル容量 :    6,056MB
アロケート容量 :    6,080MB

PL/SQLプロシージャが正常に完了しました。

6～15行目が、列情報の指定の個所となります。DATE型のような桁数指定のないデータ型の桁数はNULLを指定してください。

件数などの条件は先のレコード長ベースの見積と同様に指定していますが、先ほどの見積結果よりも35%程度大きな値の見積結果になりました。これは、レコード長ベースだと実際の平均レコード長ということで、格納されている実態の長さをベースに計算するのに対し、列情報ベースの場合、指定した長さをベースに計算するためです。言い換えると、列情報ベースの場合、指定した長さのデータが入っている前提で見積もるということです。ですので、可変長項目の桁数は、定義長ではなく、実際に利用される平均長を指定すると、より実態に近い値になることが期待できます。それでも、平均レコード長ベースの場合と同様の理由により、見積値と実際値の乖離は発生します。

インデックスの容量見積

先に悲しいことに言及しておきますが、DBMS_SPACE.CREATE_INDEX_COSTプロシージャを利用して、インデックスを机上見積することはできません。
DBMS_SPACE.CREATE_INDEX_COSTプロシージャを使用した容量見積は、データが入っており、統計情報も取得されている既存のテーブルに対して、仮にこの列とこの列にインデックスを作成したらどの程度の容量になるのか、を見積もるシナリオを想定して作成されています。

これは、インデックスの容量は、既存のデータの入っているテーブルに対してCREATE INDEXした場合と、0件のテーブルにCREATE INDEXしてからデータをINSERTした場合では、同じデータ内容であっても実消費量が大きく異なる(後者の方が大きくなる)ためだと思われます。また、NULLとなるデータが存在するとインデックスのレコードは作成されないので、実容量は実データに大きく左右されてしまいます。要は机上見積だと誤差が大きすぎるということですね。もっとも、索引の見積自体、誤差は大きくなりがちなので、このプロシージャでも割と誤差が出るのが実情です。

DBMS_SPACE.CREATE_INDEX_COSTプロシージャの呼び出し形式は以下の通りです。

DBMS_SPACE.CREATE_INDEX_COST (
   ddl             IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);

引数名	IN/OUT	説明
ddl	IN	CREATE INDEX文
used_bytes	OUT	テーブル容量。PCTFREE値、ヘッダサイズなど、ブロック内のオーバーヘッドが考慮されている値
alloc_bytes	OUT	アロケート容量。表領域のエクステント計算を加味したテーブル容量
plan_table	IN	使用するPLAN TABLE。デフォルトはNULL

CREATE INDEX文のオプションは指定しなくとも動きますが、指定した方がより正確な値になります。

SQL> set serveroutput on
SQL> declare
  2    used_bytes number;
  3    alloc_bytes number;
  4  begin
  5    dbms_space.create_index_cost(
  6        'create index ind1 on emp (empno)',
  7        used_bytes, alloc_bytes
  8    );
  9    dbms_output.put_line('インデックス容量 : '
 10      || to_char(used_bytes / 1024 / 1024, '999,990') || 'MB');
 11    dbms_output.put_line('アロケート容量 : '
 12      || to_char(alloc_bytes / 1024 / 1024, '999,990') || 'MB');
 13  end;
 14  /
インデックス容量 :      572MB
アロケート容量 :    2,304MB

PL/SQLプロシージャが正常に完了しました。

サンプルではDDL文を簡単にしていますが、実際に利用される際は、オプションもできる限り指定してください。

実際値と比較してみます。比較対象はアロケート容量になります。

SQL>  select to_char(bytes / 1024 / 1024, '999,990') || 'MB'
  2     from user_segments where segment_name = 'PK_EMP';

TO_CHAR(BYTES/1024/1
--------------------
   1,808MB

見積値とは-22%程度の誤差があります。ちなみに、このインデックスは再編成済みです。この例とは逆に、実容量が見積容量の1.5倍くらいのサイズになった経験もあります。伝聞なら倍のサイズになったと聞いたこともあります。インデックスの場合、前述のとおり、テーブルに比べて変動要素が多く、見積値と実際値の乖離が大きいため、テーブルよりも大幅に余裕値を見ておいてください。過少見積より過剰見積の方が影響は軽微ですので。