mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统 计结果的开始值:
set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
mystat2.sql用于报告统计结果的变化情况(差值):
set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
例如,要查看某个UPDATE生成的redo数,可以使用以下命令:
sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat.sql "redo size"
NAME VALUE
---------------------------------------------------------------- ----------
redo size 756293676
redo size for lost write detection 0
redo size for direct writes 0
sys@DEVELOP> update t1 set aac009='1';
795680 rows updated.
sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat2.sql
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 1139306496 1,139,306,496
redo size for lost write detection 0 0
redo size for direct writes 0 0
由此可见,795,680行记录的UPDATE会生成1,139,306,496字节的redo.