Statistics held in V$tables help to reveal the load of certain SQL statements on the DBMS. However, the statistical values given are absolute, so a comparison is needed to know the load of your portion of SQL code. Here you'll find SQL*Plus code to facilitate such a measurement easily and quickly. It was tested on versions 9.0 and 9.2, but it uses features existing in versions 8 and 7, too.
To know the amount of bytes of redo records generated by your SQL, you may use the following:
VARIABLE absolute NUMBER VARIABLE delta NUMBER BEGIN :absolute:= 0; END; / BEGIN SELECT m.value, m.value - :absolute INTO :absolute, :delta FROM v$mystat m, v$statname n WHERE m.statistic# = n.statistic# AND n.name = 'redo size'; END; / SAVE delta REPLACE HOST echo PRINT delta >> delta.sql UPDATE emp SET sal = sal; @delta
The last two lines will be a template for the following measurements:
YOUR SQL CODE @delta YOUR SQL CODE @delta ... and so on. Change 'redo size'
This was first published in November 2004