Re: Investigating those locks

Hello Jan,

thank you very much for your reply,
I will try this one and let the NG know
the result.
Thank you again.

Axel

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> schrieb im Newsbeitrag news:b1817p$110c5l$1_at_ID-152732.news.dfncis.de...
> Hi, Axel.
>
> If You want to find the SQL statement that is actually locking odere even
> blocking others,
> You may query the v$sqlarea
>
> This is part of my stored procedure to find and eventually kill blocking
> sessions. It protocols
> the SQL also:
>
> --find only blocking sessions,
> --locking/inactive only is not critical.
>
> CURSOR curBlockers
> IS
> SELECT
> s.username, --who
> s.user#, --who(UserID)
> s.sid, --Session
> s.serial#,
> s.status,
> s.type,
> s.program,
> s.module,
> s.action, --by what
> s.client_info,
> s.sql_address,
> s.sql_hash_value,
> s.logon_time,
> s.lockwait,
> s.terminal
> FROM v$session s
> WHERE s.sid IN
> (
> SELECT DISTINCT
> l.sid
> FROM v$lock l
> WHERE l.lmode > 0
> AND l.block > 0 --! Locks waiting for Locks
> )
> AND s.type != 'BACKGROUND'; --without Oracle Backgrounds
>
> --what is the SQL behind -- this is what You maybe after:
> CURSOR curBlockerSQL
> (
> cur_rSQL_ADDRESS RAW,
> cur_nSQL_HASH_VALUE NUMBER
> )
> IS
> SELECT sql_text FROM v$sqlarea
> WHERE address = cur_rSQL_ADDRESS --< v$session
> AND hash_value = cur_nSQL_HASH_VALUE --< v$session
> --SQL-Identifier
> ;
>
>
> HTH
>
> Jan=)
>
> "Axel Stahl" <stahl_at_pdg-online.de> schrieb im Newsbeitrag
> news:3e369de7_at_news.celox.de...
> > 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 Wed Jan 29 2003 - 08:36:39 CST