This was first published on https://blog.dbi-services.com/vmystat-delta-values (2018-02-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
Here is a little script I use from time to time to look at V$MYSTAT values and displaying on one line a set of statistics with their delta value between two calls. The first script, _mystat_init.sql, initializes the variables. The second one displays the values, such as:
SQL> @ _mystat_diff.sql db block changes redo size undo change vector size redo entries ---------------- ---------------- ----------------------- ---------------- 57,371 15,445,852 6,111,608 37,709
Those two scripts are generated by defining the statistics:
define names="'redo size','redo entries','undo change vector size','db block changes'"abd running the following to spool the two scripts:
sqlplus -s / as sysdba <<'END' set pagesize 0 feedback off linesize 1000 trimspool on verify off echo off with stats as ( select rownum n,stat_id,name from (select stat_id,name from v$statname where name in (&names) order by stat_id) ) select 'define LAG'||stat_id||'=0' from stats union all select 'column "CUR'||stat_id||'" new_value '||'LAG'||stat_id||' noprint' from stats union all select 'column "DIF'||stat_id||'" heading '''||name||''' format 999G999G999G999' from stats . spool _mystat_init.sql / spool off with stats as ( select rownum n,stat_id,name from (select stat_id,name from v$statname where name in (&names) order by stat_id) ) select 'set termout off verify off' from dual union all select 'select ' from dual union all select ' '||decode(n,1,' ',',')||'"CUR'||stat_id||'" - '||'&'||'LAG'||stat_id||' "DIF'||stat_id||'"' from stats union all select ' '||',nvl("CUR'||stat_id||'",0) "CUR'||stat_id||'"' from stats union all --select ','''||'&'||'1'' comments' from dual --union all select q'[from (select stat_id,value from v$mystat join v$statname using(statistic#) where name in (&names)) pivot (avg(value)for stat_id in (]' from dual union all select ' '||decode(n,1,' ',',')||stat_id||' as "CUR'||stat_id||'"' from stats union all select '))' from dual union all select '.' from dual union all select 'set termout on' from dual union all select '/' from dual . spool _mystat_diff.sql / spool off END
Then, in sqlplus or SQLcl, you run:
SQL> _mystat_init.sqlto initialize the values to 0 and:
SQL> @ _mystat_diff.sqleach time you want to display the difference from last call.