1.介绍

tom的mystat脚本其目的是获取一个操作的某个统计结果变化情况。

其中由两个脚本构成，mystat记录状态的初始状态，mystat2记录最终状态，并显示差异。

2.创建mystat脚本

sys@ORCL>ho vim 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

3.创建mystat2脚本

sys@ORCL>ho vim 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

4.调用测试

sys@ORCL>@mystat "undo change vector size"

sys@ORCL>set echo off

NAME                                          VALUE

---------------------------------------- ----------

undo change vector size                       25324

sys@ORCL>

sys@ORCL>drop table t1 purge;

Table dropped.

sys@ORCL>@mystat2

sys@ORCL>set echo off

NAME                                              V DIFF

---------------------------------------- ---------- ----------------

undo change vector size                       34660            9,336

----再测试BUG：

sys@ORCL>@mystat "undo"

sys@ORCL>set echo off

NAME                                          VALUE

---------------------------------------- ----------

DBWR undo block writes                            0

undo change vector size                       34660

transaction tables consistent reads - un          0

do records applied

data blocks consistent reads - undo reco          0

rds applied

rollback changes - undo records applied           0

auto extends on undo tablespace                   0

total number of undo segments dropped             0

global undo segment hints helped                  0

global undo segment hints were stale              0

local undo segment hints helped                   0

local undo segment hints were stale               0

undo segment header was pinned                    0

IMU undo retention flush                          0

IMU undo allocation size                         72

SMON posted for undo segment recovery             0

SMON posted for undo segment shrink               0

16 rows selected.

sys@ORCL>drop table t2 purge;

Table dropped.

sys@ORCL>@mystat2

sys@ORCL>set echo off

NAME                                              V DIFF

---------------------------------------- ---------- ----------------

DBWR undo block writes                            0                0

undo change vector size                       44044           44,044

transaction tables consistent reads - un          0                0

do records applied

data blocks consistent reads - undo reco          0                0

rds applied

rollback changes - undo records applied           0                0

auto extends on undo tablespace                   0                0

total number of undo segments dropped             0                0

global undo segment hints helped                  0                0

global undo segment hints were stale              0                0

local undo segment hints helped                   0                0

local undo segment hints were stale               0                0

undo segment header was pinned                    0                0

IMU undo retention flush                          0                0

IMU undo allocation size                        640              640

SMON posted for undo segment recovery             0                0

SMON posted for undo segment shrink               0                0

16 rows selected.

关于bug解释

reference                            杨廷琨博客   http://l0gjmyxs8.blog.itpub.net/4227/viewspace-214965/

杨大师指出：Tom给出的脚本中在输入的统计项两边追加了%，使得统计项可以模糊匹配，这虽然方便了查询输入，但是很容易造成输入的关键字匹配多个统计项。而一旦出现这种情况，则MYSTAT2脚本中的差异统计就会完全错误：将脚本从LIKE方式改为等于的方式，这样可以避免错误的发生

改善

将脚本从LIKE方式改为等于的方式，这样可以避免错误的发生，但是这样修改之后，必须要输入完整的统计信息name，如果不完整的name，返回会出现“no rows selected”，其实也不太方便。

我的理解是，第一个脚本不改，然后增加一个脚本，和第一个脚本一致，但在where条件中将%改为等号，第二个脚本也是将%该为等号，这样第一次调用脚本相当于查询，然后才是真正记录并统计。

如：

（1）mystat脚本创建（主要起查询作用）

sys@ORCL>ho vim mystat.sql

set echo off

 set verify off

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

（2）mystat1脚本创建（记录需要统计对比的信息）

sys@ORCL>ho vim mystat1.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) = lower('&S')

/

 set echo on

（3）mystat2脚本创建（前后对比信息记录）

sys@ORCL>ho vim 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) =lower('&S')

 /

 set echo on

下面进行测试：

1.执行第一个脚本：假设我们要记录语句执行前后redo size的变化，但是记不住name的全名，可以先执行mystat脚本，这时可以不用书写完整的名字，只需输入redo就可以查询到

sys@ORCL>@mystat "redo size"

sys@ORCL>set echo off

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

redo size                                                             29000

redo size for lost write detection                                        0

redo size for direct writes                                               0

2.执行第二个脚本：然后执行mystat1脚本，并把name书写完整，当然，如果你知道name的完整名字，可以跳过第一个脚本

sys@ORCL>@mystat1 "redo size"

sys@ORCL>set echo off

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

redo size                                                             29000

3.需要做对比的sql语句

sys@ORCL>create table t1  as select * from dba_objects;

Table created.

4.执行第三个脚本：可以看到sql语句执行前后对比信息

sys@ORCL>@mystat2

sys@ORCL>set echo off

NAME                                                      V            DIFF

---------------------------------------------------- ---------- ----------------

redo size                                                 84936           55,936