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'

This was first published in November 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close