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'
