计算SQL语句生成redo,undo大小,以及表的大小
--redo,undo
select name, value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and (v$statname.name = 'redo size' or
v$statname.name = 'undo change vector size');
--测量session的redo
在执行语句前和后执行,然后相减
select value
from v$mystat, v$statname
where v$mystat.statistic# =v$statname.statistic#
and v$statname.name ='redo size';
--测量dmp导入的redo(不过这个最后在测试环境下做,生产环境下可能不准)
select value
from v$sysstat, v$statname
where v$sysstat.statistic# =v$statname.statistic#
and v$statname.name ='redo size';
用这个存储过程就方便很多
create or replace procedure measure_redo( p_sql in varchar2 )
as
l_start_redo number;
l_redo number;
begin
select a.value
into l_start_redo
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'redo size';
execute immediate p_sql;
commit;
select a.value-l_start_redo
into l_redo
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'redo size';
dbms_output.put_line
( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' ||
substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
end;
--undo
select sid from v$mystat whererownum=1;
selectvalue "undo change vector size"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and a.name ='undo change vector size' and b.sid ='956'
--session
SELECT vm.sid, vs.name, vm.VALUE
FROM v$mystat vm, v$sysstat vs
WHERE vm.statistic# = vs.statistic#
AND vs.name IN
('cleanouts only - consistent read gets',
'session logical reads',
'physical reads',
'physical reads direct',
'redo size');
--表的大小
select s.bytes/1024/1024
from user_segments s
where segment_type ='TABLE'and segment_name='PUB_DEPARTMENT';