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
Wonderful article! That is the type of information that are meant to be
shared across the web. Shame on Google for not positioning this publish higher!
Come on over and talk over with my web site .
Thank you =)
Thank you!
Hi everyone, it’s my first go to see at this web site, and
paragraph is truly fruitful designed for me, keep up posting these
posts.
Thank you!