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

waeberd
waeberd used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Commented:
True, a "select" statement (or query) is not a "transaction" in Oracle, since a "transaction" implies that some data was added, changed or deleted.  So yes, "select" statements will need to counted separate from transactions, if you want to count total activity in an Oracle system.   But, counting queries is not something that Oracle tracks anywhere that I am aware of, at least by default.  You could turn on auditing, and that can count queries, but that also adds a performance penalty and creates a large amount of data to manage.

Then, getting a count of "transactions" in an Oracle database is likely to be somewhere between misleading and inaccurate.  Why?  The amount of work per "transaction" varies widely.  If someone changes a single customer phone number or address, that could be a "transaction".  Someone else may run a program that sets new prices for all (or a large group of) products.  This could also be a single "transaction".  But, that obviously involves a lot more work for Oracle than just adjusting a single phone number or address.  Unless you have the same application running on each Oracle instance, these comparisons may not mean much.

Then how exactly do you plan to count "transactions"?  Oracle does keep track of "transactions" and gives each one a unique System Change Number (SCN).  So, one way to count transactions would be to check what the highest SCN is at some point in time, then check that value again at some time interval later.  I must confess that I don't know exactly where to find this value easily.  (I've never had a need to go looking for it.)

Some counters that Oracle does track automaticall in every Oracle database include:
1. The number of logical block reads.
2. The number of physical block reads.
3. The number of disk writes.

It may be easier to work with some of the counters that Oracle does by default, than to choose what you want to count in Oracle.
johnsoneSenior Oracle DBA

Commented:
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;

Author

Commented:
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.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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 GeerlingsDatabase Administrator

Commented:
"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 GeerlingsDatabase Administrator

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

Author

Commented:
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 GeerlingsDatabase Administrator

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial