【脚本摘自大师tom 转载请注明作者】
【如有问题请联系[email protected] 欢迎指教】对于一名数据库开发人员来说,清楚地知道自己的语句的性能是非常重要的。本文提供脚本可以对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>
【如有问题请联系[email protected] 欢迎指教】对于一名数据库开发人员来说,清楚地知道自己的语句的性能是非常重要的。本文提供脚本可以对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>
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货