How busy your database is?

What a transaction?

A transaction is one logical piece of work in the database, or 'the execution of a program that includes database access operations'. Some transactions are read-only transactions while other transactions update, or write, to the database. All transactions must exhibit the ACID properties. Transactions that make changes to the database are completed when they are committed or rolled back.

SQL> select count(*) from emp;

Count(*)
--------
14

Here Since no data was modified in this transaction, a commit or rollback was unnecessary. It can be considered as read-only transaction.

The following query updates data in the database:

SQL> delete from emp where empno=7934;

1 row deleted.

SQL> commit;

Commit complete.

In the above example, we made a change to the database and committed that change. On successful commit, the transaction is complete and a new one begins.

In some cases, one transaction can consist of multiple SQL statements.

SQL> update emp set ename='JOHNSON' where empno=7876;

1 row updated.

SQL> update emp set ename='JACKSON' where empno=7902;

1 row updated.

ORA9I SQL> rollback;

Rollback complete.

In this example, we issued different SQL statements before rolling back the transaction. All the SQL statements in this example comprised one transaction. The transaction ended with the ROLLBACK statement. These are not separate transactions.

COUNTING TRANSACTIONS

Oracle keeps track of this as a statistic which gets reset when the instance is brought down. So if you want to keep track of your transactions, you’ll have to capture this information before the database is brought down.

The V$SYSSTAT and V$SESSTAT are dynamic performance views where the statistics are stored.

The V$SYSSTAT view gives us 'database-wide' statistics. The V$SESSTAT gives us statistics for each current session. As soon as the current session ends, the statistics are removed from this view. Also, V$SESSTAT does not show the statistic name. You’ll have to join this view with V$STATNAME on the STATISTIC# column to get the statistic name. V$SYSSTAT does have the statistic name.

COUNTING COMMITS AND ROLLBACKS

Oracle has made it very easy for us to count the number of commits in the database.

SQL> select name,value from v$sysstat where name='user commits';

NAME VALUE
-------------------- ----------
user commits 26

SQL> update emp set sal=sal*1.03 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select name,value from v$sysstat where name='user commits';

NAME VALUE
-------------------- ----------
user commits 27

we looked for the ’user commits’ statistic before and after our UPDATE statement. As can be expected, this statistic was increased by one after we committed our work.

Similarly, there is a statistic for measuring rollbacks.

SQL> select name,value from v$sysstat where name='user rollbacks';

NAME VALUE
-------------------- ----------
user rollbacks 12

SQL> update emp set sal=sal*1.05 where empno=7902;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> select name,value from v$sysstat where name='user rollbacks';

NAME VALUE
-------------------- ----------
user rollbacks 13

The ’user rollbacks’ statistic shows how many rollbacks have been issued.

SQL> select name,value from v$sysstat
2 where name in ('user rollbacks','user commits');

NAME VALUE
-------------------- ----------
user commits 27
user rollbacks 13

SQL> select count(*) from emp;

Count(*)
--------
13

SQL> select name,value from v$sysstat
2 where name in ('user rollbacks','user commits');

NAME VALUE
-------------------- ----------
user commits 27
user rollbacks 13


COUNTING SELECTS

So how do we count SELECT statements? There is another statistic, 'user calls', which is as close as we are going to get.

SQL> select name,value from v$sysstat where name='user calls';

NAME VALUE
-------------------- ----------
user calls 49635

SQL> select count(*) from emp;

Count(*)
--------
13

ORA9I SQL> select name,value from v$sysstat where name='user calls';

NAME VALUE
-------------------- ----------
user calls 49641


Average number of commits and rollbacks per day


SQL> select value/up_days as tx_per_day
from (select sum(value) as value from v$sysstat
where name in ('user commits','user rollbacks')),
(select sysdate-startup_time as up_days from v$instance);

TX_PER_DAY
----------
1527355.99

If you database does not have a high number of DML statements, then the above query will not give you a true indicator of how busy the system is.

SQL> select value/up_days as calls_per_day
from (select value from v$sysstat where name='user calls'),
(select sysdate-startup_time as up_days from v$instance);

CALLS_PER_DAY
-------------
15614059.8



Note: This Document/blog is only and only meant to guide/help the newbies.