Skip to Main Content
  • Questions
  • What is the best way to track transaction counts historically for trend analysis?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Debbie.

Asked: February 01, 2007 - 11:47 am UTC

Last updated: February 02, 2007 - 10:38 am UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

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?

and Tom said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exact query ?

Star, February 01, 2007 - 7:29 pm UTC

We are not able to execute the said query.

Also, let us know the difference between sysstat and sesstat... In FTS I am seeing some values though there is no any FTS... ?

FTS means Full Table Scans.

Thanks,
Star Nirav

Tom Kyte
February 02, 2007 - 10:38 am UTC

why can't you execute the said query.


my car won't start. why not?


I don't have any idea what your last sentence there means. "in fts i am ...." that doesn't compute

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.