Re: Recording Oracle transaction levels
Date: Thu, 11 Sep 2008 04:04:34 -0700 (PDT)
On 11 Sep., 12:12, Johne_uk <edg..._at_tiscali.co.uk> wrote:
> 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
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)
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