Investigating those locks

Hello,

I read some postings regarding Oracle locks, but there was nothing that would have solved my problems, so sorry if this was answered sometime here.

Following scenario:
- Compaq Server with 4 cpu's (correct recognized by init.ora)

• 1 gig of RAM
• Oracle 8.0.5 Enterprise installed
• appr. 120 users connected to Oracle via our ERP software.

Following symptoms:
From time to time, users heavily complain about very poor performance.
When we look via SQL-Plus onto Oracle,
there appear some locks, which stay there some minutes, which seem to force the other users to wait. When those locks are released, everything seem to work normally. With following script we look via SQL-plus for those locks: ----snip----
COL username FOR A20
COL machine FOR A20
select distinct a.username,a.sid,a.serial#,a.machine from v$session a, v$_lock b
where a.saddr=b.saddr;
----end snip------

There we should see the one who seems to cause the(se) lock(s).
To investigate what goes wrong there, we need to know what SQL-Statement there was/is just processed, which caused the lock to occur.

My question now is:
Is there any possibility (sql-statement) to view, what sql-statement was sent to the database, to cause the lock of this session?
There are many columns in the v$session table, but I see there nothing which can tell me in "clear words" what exactly happened / what the session is waiting for.

I would appreciate every little info about that. Than you.

Axel Received on Tue Jan 28 2003 - 09:12:38 CST