oracle requests counters

I am using IIS counters to monitor "bussiness" of IIS. Specially I like 2 of them:

1. Current Anonymous Users (The number of users who currently have an anonymous request pending with the WWW service. In IIS 6.0, Current Users (Anonymous or NonAnonymous) is the number of requests currently being worked on by the server)
2. Total Anonymous User (The number of users who have established an anonymous request since the WWW service started. This counter does not increment when files are being served from the kernel cache.)

Because my bottle neck is database, which happend to be Oracle 10g, I wonder if similar counters could be taken from Oracle server (on database level).

Basicly speaking I would like to know how many request to database ABC is waiting to be served at the moment of my request, and how many request was served since (last reset,开发者_高级运维 beginnig of the day...)

How I could get this data of Oracle Server ?

---

V$SESSION can be used to determine how many database sessions are active at the current instant in time. This query will show you the number of user sessions (rather than background sessions that the Oracle database itself creates) are active at the current instant. You may want to further restrict this to be the number of active sessions where the USERNAME is the user that your middle tier is connecting as or the MACHINE from which the session is created is one of your middle tier servers.

SELECT COUNT(*)
  FROM v$session
 WHERE status = 'ACTIVE'
   AND type = 'USER'

There is no easy mapping in Oracle for the "number of requests served" of a web browser. From a database perspective, there aren't any markers of when a "request" begins and ends. You could potentially count transactions but the Oracle database itself is constantly issuing transactions in the background which is likely to cause problems if you wanted a measure that would map closely to the number of web pages served.

That said, however, using counters to diagnose and monitor Oracle database performance is not a particularly good idea. Oracle has much more sophisticated monitoring and tuning tools available. Depending on the edition (standard or enterprise) and whether you've licensed the Performance and Tuning Pack, you'd be much better served grabbing an AWR report from a time period when the database was the bottleneck and analyzing that to see what needs to be tuned.