asked on

Transactions per second / SQL-Queries per second on oracle servers. how?

Hello experts,

On several existing and running Oracle instances I would like to measure server activity.

My wish is to monitor:
* the number of transactions per time interval
* the number of executed reads per time interval

In my undestanding, a SELECT statement will not count as a transaction as it is not modifying anything on the database. Is this assumption correct? If yes, I need to distinguish between transactions and read-only selects.

How can I measure / monitor those two numbers?

Thanks & regards,

Daniel

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

Theoretically, a transaction in oracle is defined as the work between commits or roll backs. If you are looking for those numbers, I look at the statistics name 'user commits' and 'user rollbacks'. That would give you the number of "transactions", however it may not be what you are really looking for.

select name, value
from v$sysstat
where name in ('user commits','user rollbacks');

If you really want to know the scn, you can get it with this query:

select dbms_flashback.get_system_change_number scn from dual;

waeberd

ASKER

I also found out that I could use the STATSPACK utilities for monitoring
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/statspac.htm

This looks like a rather heavy procedure (own tablespace, etc).

If I measure logical disk I/O, is it possible to compare these values between different instances?

Thank you!

btw. I will be offline for a couple of days.

slightwv (䄆 Netminder)

A different tablespace for Statspack is a suggestion. Snapshots run pretty quick.

Burleson has an article that uses statspack to monitor 'transactions' per second:
http://www.dba-oracle.com/m_transactions_per_second.htm

Logical I/O is reading blocks from the buffer cache in memory. If this translates across instances would probably be based on the test cases executed against them and the different configurations.

Mark Geerlings

"If I measure logical disk I/O, is it possible to compare these values between different instances?"
The answer to this, like the answer to many Oracle questions is: "maybe" or "that depends".

Comparing this value between two different Oracle database will only be meaningful if *ALL* of these conditions are true:
1. The servers have the same O/S.
2. The servers have the same amount of RAM.
3. The servers have the same number, type and speed of CPUs.
4. The servers have identical disk systems.
5. The Oracle database versions and patch levels are identical
6. The application (version and usage) is identical, or at least very similar.

Mark Geerlings

And onr more very important one:
7. The databases have identical paramater files (init*.ora or spfile).

waeberd

ASKER

Hi experts,

and thanks for all the helpful inputs. I understand that comparing measurements between different oracle instances will be tricky...

I will probably relate on the default counters Oracle first. I've also found the Burleson article, which is nice.

One thing I'm also interested in is measuring how many users are "writers" compared to the "readers" of our system. This ratio would be very interesting.

Any suggestion of how to get "readers" / "writers" ratio?

Thanks again!

Mark Geerlings

Oracle is not designed to track users or their activity on "reads" compared to "writes" by default. You may (or may not) be able to get some clues to this by the security the various users have. Some users may have "select" privileges only. They will do only "reads" (plus whatever background writes Oracle does to keep track of their logins, their disk reads, etc.) But, if all users have "insert, update and delete" privileges as well as "select" privileges, you won't be able to use their security to help you determine this.

If you turn on auditing in Oracle, that can count the total number of select, insert, update and delete statements by user. But, be careful with this option! That adds a performance penalty, and it generates a *LARGE* amount of data that must be managed.