Re: Investigating those locks

Hello,

thank you for your reply.
I will look for those scripts,
maybe the "secret" of those locks can
be revealed :-)

Thank you,

Axel

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> schrieb im Newsbeitrag news:ajbd3vomsnsjrul1m4ml0mt34e1chfjigl_at_4ax.com...
> 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 Wed Jan 29 2003 - 08:39:18 CST