Problem solve Get help with specific problems with your technologies, process and projects.

Simple way to measure consumed resources

This SQL*Plus script offers a way to measure consumed resources in V$tables.

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'

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close