当前session生成redo,undo大小,统计信息，以及表的大小

计算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';