1.指定した表の各列の平均サイズを調べ、1レコード当りの平均長を求める。
2.平均レコード長から、Oracle内部で必要なオーバヘッドを計算する。
3.上記を合計して、1レコードの平均格納サイズを求める。
DBA権限を所有するユーザで実行する。
このスクリプトは、以下のスクリプトを生成し、実行する。
1.tmp_avg_row_len.sql : 1行当りの平均長を求めるスクリプト。
2.tmp_row_overhead.sql : 1行当りに必要なオーバヘッドを求める。
set echo off
accept TABLE_OWNER prompt ‘表の所有者名を入力 >> ‘
accept TABLE_NAME prompt ‘平均長を調査する表名を入力>> ‘
set term off
set echo off
set feed off
set veri off
set pages 0
set lines 1000
set trimspool on
define SCRIPT_NAME1=tmp_avg_row_len.sql
define SCRIPT_NAME2=tmp_row_overhead.sql
ttitle off
clear col
col dummy noprint
col AVG_ROW_LEN form 999999 new_value V_AVG_ROW_LEN
col ROW_OVER_HEAD form 999999 new_value V_ROW_OVER_HEAD
–*******************************************************************
— 行の平均長を求めるSQLスクリプト &SCRIPT_NAME1 を生成
–*******************************************************************
spool &SCRIPT_NAME1
select
1 dummy,
‘select ceil(avg(nvl(vsize(‘||column_name||’),0)) ‘ from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id=1
union
select
rownum+10 dummy,
‘+avg(nvl(vsize(‘||column_name||’),0)) ‘ from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id <>
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
1000 dummy,
‘+avg(nvl(vsize(‘||column_name||’),0)) ‘ from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id =
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
1001 dummy,
‘) AVG_ROW_LEN from &TABLE_OWNER..&TABLE_NAME;’ from dual
order by 1
;
spool off
–*******************************************************************
— 行のオーバーヘッドを求めるSQLスクリプト &SCRIPT_NAME2 を生成
–***************************** **************************************
spool &SCRIPT_NAME2
select 0 dummy ,’select sum(len) ROW_OVER_HEAD from ‘ from dual
union
select
1 dummy,
‘(select 3 * ‘||type_size|| ‘ len from dual’ from v$type_size where type=’UB1′
union
select
2 dummy,
‘union all select 1 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) < 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id=1
union
select
2 dummy,
‘union all select 3 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) >= 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id=1
union
select
rownum+10 dummy,
‘union all select 1 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) < 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id <>
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
rownum+100 dummy,
‘union all select 3 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) >= 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id <>
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
10000 dummy,
‘union all select 1 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) < 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id =
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
10001 dummy,
‘union all select 3 len from &TABLE_OWNER..&TABLE_NAME having AVG(NVL(VSIZE(‘||column_name||’),0)) >= 250′
from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
and column_id <> 1
and column_id =
(select max(column_id) from dba_tab_columns
where owner=upper(‘&TABLE_OWNER’)
and table_name=upper(‘&TABLE_NAME’)
)
union
select
10002 dummy,
‘);’ from dual
order by 1
;
spool off
–*******************************************************************
— 生成したスクリプトを実行し、行の平均長、行オーバヘッドを求め、変数
— V_AVG_ROW_LEN、およびV_ROW_OVER_HEADに格納する
–*******************************************************************
@&SCRIPT_NAME1
@&SCRIPT_NAME2
–*******************************************************************
— 変数の値を合計し、1行当りの平均レコード長を求め、表示する。
–*******************************************************************
set pages 1000
set head on
set term on
col AVG_ROW_LEN form 9999 head “平均レコード長”
select &V_AVG_ROW_LEN + &V_ROW_OVER_HEAD AVG_ROW_LEN from dual;
set feed on
undefine V_AVG_ROW_LEN
undefine V_ROW_OVER_HEAD
undefine TABLE_NAME
clear col
ttitle off
set echo on
