テーブル設計
テーブル設計は、論理設計におけるエンティティをテーブルとし、テーブルの非正規化は物理設計段階では積極的には行わず、パフォーマンス上問題となる可能性がある部分の洗い出しのみを行い、ランニングテストの結果を基にチューニング段階で行います。
5.2.1.容量概算手順
ユーザテーブルの容量概算は以下の手順に従って算出し、余裕率10%を上乗せした値をinitialエクステントサイズとしinitialエクステントサイズの10%をnextエクステントサイズとします。
０．調査対象テーブル構成
SQL>DESC LOCATION
名前 NULL? タイプ
------------------------------- -------- ----
LOC_ID NUMBER(10)
LOCATION_NAME CHAR(30)
PROVINCE_ID VARCHAR2(2)
LOC_TYPE VARCHAR2(2)
１．動的パフォーマンス表V$TYPE_SIZEより固定値を調査する。
$ svrmgrl
SVRMGR>connect internal
SVRMGR>select type, description, type_size
2> from v$type_size
3> where type in ('UB1','UB4','SB2','KCBH','KTBIT','KTBBH', 'KDBH','KDBT');
TYPE DESCRIPTION TYPE_SIZE
-------- -------------------------------- ----------
UB1 UNSIGNED BYTE 1 1
UB4 UNSIGNED BYTE 4 4
SB2 SIGNED BYTE 2 2
KCBH BLOCK COMMON HEADER 20
KTBIT TRANSACTION VARIABLE HEADER 24
KTBBH TRANSACTION FIXED HEADER 48
KDBH DATA HEADER 14
KDBT TABLE DIRECTORY ENTRY 4
２．動的パフォーマンス表V$PARAMETERよりDB_BLOCK_SIZEを調査します。
SVRMGR> select value from v$parameter where name = 'db_block_size';
VALUE
------------------------------------------------------------
2048
--- ここからは机上で実施 ---
３．データに使える領域（SAD）＝ データブロックサイズ - ブロックヘッダ を計算します。
db_block_size - （KCBH + UB4 + KTBBH + (initrans-1) * KTBIT + KDBH）
2048 - （20 + 4 + 48 + (1-1) * 24 + 14）
= 1962 Bytes initrans=１ (デフォルト)
４．使用可能なデータ領域（ADS）を計算します。 ここで仮定としてPCTFREE 20とします。
ceil (SAD * (1-pctfree/100))-KDBT
ceil (1962 * (1-20/100)) - 4
ceil (1962 * 0.8) - 4
=1959 Bytes ceil()：切上げ
５．１行あたりの使用領域（SPR）を計算します。カラムサイズ２５０バイトを基点に加算値が変わります。
Colum size including type length =
Colum size + (1, if colum size < 250 , else 3)
LOC_ID 6 + 1 = 7
LOCATION_NAME 30 + 1 = 31
PROVINCE_ID 2 + 1 = 3
LOC_TYPE 2 + 1 = 3
TOTAL = 44 Bytes (TCS)
６．行サイズ(ROWSZ)を計算します。
ROWSZ＝行ヘッダー + TCS
（3 * UB1） + TCS = 47 Bytes
７．１行あたりの使用領域(SPROW)を計算します。
max (UB1 * 3 + UB4 + SB2, ROWSZ) + SB2
max (1 * 3 + 4 + 2, 47) + 2 = 49
max (9,47) + 2 = 49 Bytes
８．ブロックあたりの平均行数(RPB)を計算します。
floor (ADS / SPROW) = floor (1959 / 49) = 39 row per data block floor()：切捨て
９．対象となる表の行数が390,000行と仮定して、LOCATION表について適切な記憶領域パラメータを計算します。
ceil (390,000 rows / 39 row per data block) = 10,000 blocks
10,000 * 2048 = 20,480,000 Bytes
INITIAL
25,765,888 * 1.1 = 22,528,000 = ２２ＭＢ (切上げ)
NEXT
22 * 0.1 = ２ＭＢ
5.2.2.PCTFREE・PCTUSEDの算出
テーブルを作成する際に、PCTFREEおよびPCTUSEDを決定する必要があります。しかし、すべてのテーブルに対して最適な値を定義するのは困難でありますが、一般的な値、もしくは必要に応じて統計情報を取得して決定します。
一般的な値
参照が中心のメタ情報テーブルには、PCTFREE５％・PCTUSED７０％、更新が頻繁に行われる時系列テーブルはPCTFREE２０％・PCTUSED７０％とし、numberやvarchar2といった可変長項目が多いテーブルについてはPCTFREE３０％・PCTUSED８０％とします。
統計情報の取得
PCTFREE
１．テーブルの統計情報を取得します。
SQL>analyze table SAMPLE compute statistics;
２．ビューUSER_TABLESより統計結果を取得し、１ブロック当りの行数を取得します。
SQL>select num_rows, blocks, num_rows/blocks from user_tables
２>where table_name = ‘SAMPLE’;
３．頻度の高いと思われるUPDATEを実行し、再度２の手順を実行する。
SQL>update SAMPLE set sampno = 100
２>where sampname = ‘PCTCHECK’;
ここで１ブロック当りの行数をチェックしてください。差がない場合はPCTFREEの値が適しています。しかし、変化がない場合もPCTFREEが余分に取られている可能性があるため、３－１の手順より平均値を求め、値が大きい場合はPCTFREEを小さくする検討をします。
３－１．ビューUSER_TABLESより統計結果を取得し、１ブロック当りの行数の平均を取得します。
SQL>select num_rows, blocks, num_rows/blocks, avg_space from user_tables
２>where table_name = ‘SAMPLE’;
PCTUSED
PCTUSEDは特に算出方法がなく、APでの処理形態で考えるべきです。例えば、PCTFREEが４０％で削除処理が頻繁に発生するAPでは、表内のブロックは４０％しか使用されません。
5.2.3.自動作成されるインデックスの設定
Oracle７より、表の主キー制約やユニーク制約を設定すると、Oracleによって暗黙的にインデックスが作成されます。このとき作成されるインデックスのSTORAGE句は、ユーザ表領域のDEFAULT STORAGEが採用されてしまいます。これを避けるため、次の手順を行います。
CREATE TABLEで、PRIMARY KEY・UNIQUE KEY制約を定義しない。
ALTER TABLEの CONSTRAINT句で、USING INDEX句を指定する。
テーブルTESTの作成例を示します。
SQL>create table TEST
2>(col number(10), char(30))
3> tablespace USR_DATA
4> default storage(initial 1M, next 100K pctincrease 0);
SQL>alter table TEST
2>add constraint PK_TEST primary key(col )
3>using index
4>pctfree 20
5>tablespace IDX_DATA
6>storage(initial 400K next 40K pctincrease 0);
5.2.4.設定指針
テーブル作成における指針を示します。
・更新系 minextents =1 , maxextents =249 , pctfree = 20 , pctused = 70とします。
・参照系 minextents =1 , maxextents =249 , pctfree = 5 , pctused = 70とします。
・可変長 minextents =1 , maxextents =249 , pctfree = 30 , pctused = 80とします。
・INITIAL * 1.05, NEXT(INITIAL * 1.05) * 0.1
SQL>create table LOCATION (
2>LOC_ID NUMBER(10)
3>LOCATION_NAME CHAR(30)
4>PROVINCE_ID VARCHAR2(2)
5>LOC_TYPE VARCHAR2(2))
6>pctfree 30
7>pctused 80
8>tablespace USERS
9>storage (initial 22M next 2M pctincrease 0);
SQL>
詳細については、「Oracle８Server 管理者ガイド」の付録A「スキーマ・オブジェクトの領域の見積もり」を参照してください。
最終更新：2008年11月15日 22:16
