Re: Investigating those locks

On Tue, 28 Jan 2003 16:12:38 +0100, "Axel Stahl" <stahl_at_pdg-online.de> wrote:

>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
>
>

Please check out the locking scripts provided in $oracle_home/rdbms/admin and the corresponding doco. They haven't changed so you can use the 8i or the 7.3.4 doco as well.

>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jan 28 2003 - 10:24:21 CST