Monitoring question

Hi,

I hope my question is not *too* silly,
but I would like to know if there is any view inside the data dictionary to see in real time which user is producing temporary bottlenecks in the database -

the point is:
I want to know what is happening *at that very time* when I see jams and hear users start complaining, but most DD Views like v$sysstat show _accumulated data_ ... I went through many v$* views in the manuals, but I was not successful to find something leading to this direction. and v$sqlarea show the SQL, but not the loads produced by it; I see just placeholders for bind variables.

A fellow in my team just wondered
why is there no similar functionality
compared to the System Monitor on Windows NT or such, or top on linux, to see what is happening in time. You cannot even see any progress information in SQL+ ...  ... for the moment I answered him well Oracle is just not M$ ... but in a way he is right and he made me curious. It should be very important to any DBA to see session loads at one hit, isnīt it ? And I began to wonder how DBAs with much bigger dbs than mine, and thousands of user sessions are doing this ...

Sometimes, mostly on Monday and Friday mornings, I have a jam situation for about 1 hour
where everybody is waiting for app responding, but I have no blockings anymore. It is just 30+ users active at the same time
using a couple of inhouse apps (Forms).
So I cannot see who is to "blame" *in that very moment* (of course not the user, but the app (s)he is using ...).

I know there are many possible reasons:
data model (20%), app (50%), network (10%), server parameters (10%), old hardware etc.
and I want to isolate the problem,
but yet I am still lacking some techniques to do so. I want to overcome guessing it might be this or that app ... so v$session and v$sqlarea etc, is not enough for analysis.

I admit that I am still not very experienced in monitoring, so I appreciate any comments.

Is there a startegy or some points to start from in complex situations like this ?
Thank You in advance.

Jan

My system:
Oracle 8.1.7 EE on a central AIX-Server 4.3.3, dedicated server, C/S network to 20 local subsediaries.
Oracle Forms & Reports inhouse app. Bind variables are used all over the place,
and a fairly small DB of 20 GB, 200 users, jam starting when 30+ active. Received on Tue Jul 08 2003 - 04:07:33 CDT