Oracle database locks enqueue



Today i receive a monitor alert about some locks in one of the databases i manage, so i run this query because is faster than traditional queries based in v$lock view:

SQL> SELECT * FROM dba_waiters where MODE_HELD <>'None' or MODE_REQUESTED <>'Share';

WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD                                MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
--------------- --------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
            398             336 Transaction                Exclusive                                Share                                        327692    9591040
            397             336 Transaction                Exclusive                                Share                                        327692    9591040
            394             336 Transaction                Exclusive                                Share                                        327692    9591040
            373             336 Transaction                Exclusive                                Share                                        327692    9591040
            347             336 Transaction                Exclusive                                Share                                        327692    9591040
            333             336 Transaction                Exclusive                                Share                                        327692    9591040
            316             336 Transaction                Exclusive                                Share                                        327692    9591040
            294             336 Transaction                Exclusive                                Share                                        327692    9591040
            293             336 Transaction                Exclusive                                Share                                        327692    9591040
            242             336 Transaction                Exclusive                                Share                                        327692    9591040
            201             336 Transaction                Exclusive                                Share                                        327692    9591040
            197             336 Transaction                Exclusive                                Share                                        327692    9591040
            194             336 Transaction                Exclusive                                Share                                        327692    9591040
            172             336 Transaction                Exclusive                                Share                                        327692    9591040
             88             336 Transaction                Exclusive                                Share                                        327692    9591040
             57             336 Transaction                Exclusive                                Share                                        327692    9591040
             13             336 Transaction                Exclusive                                Share                                        327692    9591040

17 rows selected.

As you can see SID=336 is root blocking
So client ask me to kill this session so i killed it by doing..

SQL> SELECT SID, SERIAL#, STATUS, SERVER  FROM V$SESSION  WHERE SID=336;

  SID    SERIAL# STATUS     SERVER
----- ---------- ---------- ---------
  336      40580 ACTIVE     DEDICATED

An after get needed info to kill session i killed it:

SQL>ALTER SYSTEM KILL SESSION '=336,40580';
Statement processed.

but this session is still running due a rollback is performing... this is because when a DML is launched, Oracle makes changes to the table immediately, even if you don't commit. Old blocks are write to UNDO till you commit or rollback.
This method is good if you commit the transaction, but if you rollback, blocks have to be written back to the base table.. So, for example, imagine you have been running a DML for an hour and after that you do a rollback, then all writes to the datafiles makes during that hour must to be written back again from UNDO tablespace to original datafiles..

column SID format 9999
column SPID format 9999999
column USERNAME format a15
column PROCESS format 99999
column MACHINE format a15
column PROGRAM format a55
column STATUS format a10
col LOGON_TIME for a27

select S.INST_ID,S.SID, P.SPID, S.USERNAME, S.MACHINE, S.PROGRAM, S.STATUS, to_char(LOGON_TIME, 'Dy DD-Mon-YYYY HH24:MI:SS') LOGON_TIME from gv$session S,  gv$process P where P.addr=S.paddr and S.INST_ID=P.INST_ID and S.SID=336;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

   INST_ID   SID SPID         USERNAME        MACHINE         PROGRAM                                                 STATUS     LOGON_TIME
---------- ----- ------------ --------------- --------------- ------------------------------------------------------- ---------- ---------------------------
         1   336 22569        ORA_app          hostname02     serverSDM@hostname02   (TNS V1-V3)                       KILLED     Fri 29-Sep-2017 17:04:18

So after kill session, we can see it running on database in killed status...

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more