Re: Recording Oracle transaction levels

From: <>
Date: Thu, 11 Sep 2008 04:04:34 -0700 (PDT)
Message-ID: <>

On 11 Sep., 12:12, Johne_uk <> wrote:
> Hi,
> Just a quick question.
> My manager is in the process of setting up some KPIs for our IT
> department (mostly general interest for other depts to look at). One
> such metric he has requested is 'the actual number of transactions
> executed within the previous hour'.
> I don't think this is possible because, as far as I know, Oracle
> doesn't keep any record of the number of transactions it has executed
> and taking regular snapshots of how many transactions are running at
> that exact time is going to be pretty meaningleess as some
> transactions can be as short as a mill-second.
> So the question being - is my assumption above correct or is there
> another way to do this that I'm unaware of?
> Thanks in advance
> John

Hi John,

It can be done. In more ways. Here's one :

In 10g you have the view


which reads the undo tablespace and tracks all transactions recorded here.

your managers request could then be fullfilled with

select count( distinct commit_scn)
from flashback_transaction_query
where start_scn > sysdate-1/24

Beware that you, in order to be able to keep track of, say, last hour's transactions, must
a) set the parameter UNDO_RETENTION to at least 3600 (one hour) b) Ensure your active undo tablespace has retention guarantee enabled

  • Kenneth Koenraadt
Received on Thu Sep 11 2008 - 06:04:34 CDT

Original text of this message