Mystat.sql

--The mystat.sql and its companion, mystat2.sql, are used to show the increase in some Oracle “statistic” before and
--after some operation.

-- 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) = lower('&S')
/
set echo on

--mystat2.sql
-- mystat2.sql reports the difference (&V is populated by running the first script, mystat.sql—it uses the
-- SQL*Plus NEW_VAL feature for that. It contains the last VALUE selected from the preceding query):
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

-- For example, to see how much redo is generated by an UPDATE statement, we can do the following:
@mystat "redo size"
set echo off

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               648


update big_table set owner = lower(owner) where rownum <= 1000;

@mystat2
set echo off

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                             97040           96,392

-- This shows our UPDATE of 1,000 rows generated 96,392 bytes of redo.