Re: Investigating those locks

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Wed, 29 Jan 2003 08:51:21 +0100
Message-ID: <b1817p$110c5l$1@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 - 01:51:21 CST