【脚本摘自大师tom 转载请注明作者】
【如有问题请联系lpc19598188@gmail.com 欢迎指教】
对于一名数据库开发人员来说,清楚地知道自己的语句的性能是非常重要的。本文提供脚本可以对sql语句生成的redo进行测量
--创建测试表
SQL> create table t2 as select * from emp;
Table created
-- 在执行要测量的sql前先执行下面的语句,在提示输入中输入redo size
SQL> set verify off
SQL> column value new_val V
SQL> define S="&1"
SQL> set autotrace off
Cannot SET AUTOTRACE
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 17124
--执行被测量的sql语句
SQL> update t2 set empno = rownum;
15 rows updated
--看一下commit前的情况
SQL> set verify off
SQL> select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 17124 0
SQL>
SQL>
SQL> commit;
Commit complete
--commit后有结论了:
SQL> set verify off
SQL> select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 20908 3,784
SQL>
【如有问题请联系lpc19598188@gmail.com 欢迎指教】
对于一名数据库开发人员来说,清楚地知道自己的语句的性能是非常重要的。本文提供脚本可以对sql语句生成的redo进行测量
--创建测试表
SQL> create table t2 as select * from emp;
Table created
-- 在执行要测量的sql前先执行下面的语句,在提示输入中输入redo size
SQL> set verify off
SQL> column value new_val V
SQL> define S="&1"
SQL> set autotrace off
Cannot SET AUTOTRACE
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 17124
--执行被测量的sql语句
SQL> update t2 set empno = rownum;
15 rows updated
--看一下commit前的情况
SQL> set verify off
SQL> select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 17124 0
SQL>
SQL>
SQL> commit;
Commit complete
--commit后有结论了:
SQL> set verify off
SQL> select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 20908 3,784
SQL>