Simple way to measure consumed resources

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'