Re: Recording Oracle transaction levels

On 11 Sep., 12:12, Johne_uk <edg..._at_tiscali.co.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

  flashback_transaction_query

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