We are wanting to easily track how many transactions are handled daily, weekly, monthly, and yearly in our applications for charge back as well as for trend analysis and performance analysis. What is the easiest and best way to accurately get these counts?
by taking statspack snapshots over time, it'll gather much more than you ask for - but you will likely ask for more over time.
short of that, you would set up a job to capture the number of user commit (v$sysstat) and a timestamp and v$instance.startup_time. This job would run at least daily (so you can see the daily increase). But bear in mind - that you will lose some counts across database "shutdowns" (unless you incorporate into your shutdown scripts a final "count the number of transactions").
ops$tkyte%ORA9IR2> create table t ( tim timestamp, dt date, commits number );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t
2 select systimestamp,
3 v$instance.startup_time,
4 v$sysstat.value
5 from v$instance, v$sysstat
6 where v$sysstat.name = 'user commits'
7 /
1 row created.
ops$tkyte%ORA9IR2> begin
2 for x in 1 .. 100
3 loop
4 insert into t2 values (x);
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> insert into t
2 select systimestamp,
3 v$instance.startup_time,
4 v$sysstat.value
5 from v$instance, v$sysstat
6 where v$sysstat.name = 'user commits'
7 /
1 row created.
ops$tkyte%ORA9IR2> select *
2 from (
3 select tim,
4 dt,
5 commits,
6 lead(commits) over (partition by dt order by tim) next_val,
7 lead(commits) over (partition by dt order by tim) - commits diff
8 from t
9 )
10 where next_val is not null
11 /
TIM DT COMMITS NEXT_VAL DIFF
------------------------------ --------- ---------- ---------- ----------
01-FEB-07 02.42.49.703262 PM 31-JAN-07 323 423 100
you would then be able to trunc the dates down to whatever you wanted - aggregate the counts and using lead - produce the report.