Re: Session Statistics

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: 3 Jul 1999 01:20:36 GMT
Message-ID: <7ljoh4$l8g$2@news.seed.net.tw>

Brian Peasland <peasland_at_email.msn.com> wrote in message news:O$qsAl2w#GA.327_at_cpmsnbbsa05...
> Can anyone tell me how to get statistics such as the number of transactions
> or number of sessions that the Oracle RDBMS has processed since it was
> started? I need to try and automate some sort of statistics gathering
> process. Is there a v$ table I can query that will give me this information?
> How about a sequence that I can check the CURVAL for?

To find out the number of transactions:

    select value from v$sysstat where name='user commits'

To find out the number of sessions:

    select value from v$sysstat where name='logons cumulative'

IMHO, there is no way to know the accurate current value of a sequence, since the real current value is cached in memory. When you reference the sequence and there is no cached values in memory, Oracle adds the sequence by the number of CACHE_SIZE, and puts the cache values in memory. You just can query the STORED last value in the table SYS.SEQ$ (or DBA_SEQUENCES).

The following script says it (the default cache size is 20):

SQL> select last_number from dba_sequences   2 where sequence_owner='SCOTT' and sequence_name='S2';

LAST_NUMBER

---

    2002062

SQL> select scott.s2.nextval from dual;

  NEXTVAL

---

  2002062

SQL> select last_number from dba_sequences   2 where sequence_owner='SCOTT' and sequence_name='S2';

LAST_NUMBER

---

    2002082

SQL> select scott.s2.nextval from dual;

  NEXTVAL

---

  2002063 Received on Fri Jul 02 1999 - 20:20:36 CDT