Oracle Database Discussions

Hello,

Thanks in advance!!

Whenever, our system receives a flood of events, we try to update the database by first locking a certain row using "select for update". However, the system seems to lock up (I think it is very slow in processing) for a long time as evident in the query below. Can anybody provide some insights was what we might be doing incorrectly as far as the database set up, tuning etc.

One connection has an exclusive rowlock on a table called NODE and an exclusive lock on SYS.ALL_CATALOG. Others are all waiting to get a lock on SYS.ALL_CATALOG (actually it is some SYS.XXX table all the time). All connections are actually making the same "select for update" call.

When I execute the query as follows I get a result which shows a very long wait time (approx: 515 seconds). isn't this too much?

SQL> select count(*), SUM(time_waited), max(max_wait) from v$session_event where event = 'enqueue';

COUNT(*) SUM(TIME_WAITED) MAX(MAX_WAIT)
---------- ---------------- -------------
1 158919 308



We are running Oracle 9.2.0.0 on a Windows 2K machine with 2G memory and dual 2.8GHz processor. The application is a Java based using a thin JDBC driver that comes with the 9.2.0.0 CD.



SQL> set linesize 132 pagesize 66
SQL> break on Kill on username on terminal
SQL> column Kill heading 'Kill String' format a13
SQL> column res heading 'Resource Type' format 999
SQL> column id1 format 9999990
SQL> column id2 format 9999990
SQL> column lmode heading 'Lock Held' format a20
SQL> column request heading 'Lock Requested' format a20
SQL> column serial# format 99999
SQL> column username format a10 heading "Username"
SQL> column terminal heading Term format a8
SQL> column tab format a35 heading "Table Name"
SQL> column owner format a9
SQL> column Address format a18
SQL> select nvl(S.USERNAME,'Internal') username,
2 nvl(S.TERMINAL,'None') terminal,
3 L.SID||','||S.SERIAL# Kill,
4 U1.NAME||'.'||substr(T1.NAME,1,20) tab,
5 decode(L.LMODE,1,'No Lock',
6 2,'Row Share',
7 3,'Row Exclusive',
8 4,'Share',
9 5,'Share Row Exclusive',
10 6,'Exclusive',null) lmode,
11 decode(L.REQUEST,1,'No Lock',
12 2,'Row Share',
13 3,'Row Exclusive',
14 4,'Share',
15 5,'Share Row Exclusive',
16 6,'Exclusive',null) request
17 from V$LOCK L,
18 V$SESSION S,
19 SYS.USER$ U1,
20 SYS.OBJ$ T1
21 where L.SID = S.SID
22 and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
23 and U1.USER# = T1.OWNER#
24 and S.TYPE != 'BACKGROUND'
25 order by 1,2,5
26 /

Username Term Kill String Table Name Lock Held Lock Requested
---------- -------- ------------- ----------------------------------- -------------------- --------------------
EMS3 unknown 11,507 SYS.ALL_CATALOG Exclusive
EMS3 unknown 18,116 EMS3.NODE Row Exclusive
EMS3 unknown 11,507 EMS3.NODE Row Share
EMS3 unknown 13,6 EMS3.NODE Row Share
EMS3 unknown 22,56 EMS3.NODE Row Share
EMS3 unknown 23,6 EMS3.NODE Row Share
EMS3 unknown 24,6 EMS3.NODE Row Share
EMS3 unknown 25,7 EMS3.NODE Row Share
EMS3 unknown 26,10 EMS3.NODE Row Share
EMS3 unknown 40,8 EMS3.NODE Row Share
EMS3 unknown 39,7 EMS3.NODE Row Share
EMS3 unknown 38,6 EMS3.NODE Row Share
EMS3 unknown 37,6 EMS3.NODE Row Share
EMS3 unknown 36,6 EMS3.NODE Row Share
EMS3 unknown 35,10 EMS3.NODE Row Share
EMS3 unknown 34,7 EMS3.NODE Row Share
EMS3 unknown 33,6 EMS3.NODE Row Share
EMS3 unknown 32,7 EMS3.NODE Row Share
EMS3 unknown 57,6 EMS3.NODE Row Share
EMS3 unknown 56,6 EMS3.NODE Row Share
EMS3 unknown 55,10 EMS3.NODE Row Share
EMS3 unknown 54,7 EMS3.NODE Row Share
EMS3 unknown 53,6 EMS3.NODE Row Share
EMS3 unknown 52,9 EMS3.NODE Row Share
EMS3 unknown 51,6 EMS3.NODE Row Share
EMS3 unknown 50,8 EMS3.NODE Row Share
EMS3 unknown 49,7 EMS3.NODE Row Share
EMS3 unknown 67,6 EMS3.NODE Row Share
EMS3 unknown 66,10 EMS3.NODE Row Share
EMS3 unknown 65,7 EMS3.NODE Row Share
EMS3 unknown 64,6 EMS3.NODE Row Share
EMS3 unknown 63,8 EMS3.NODE Row Share
EMS3 unknown 62,6 EMS3.NODE Row Share
EMS3 unknown 61,7 EMS3.NODE Row Share
EMS3 unknown 60,8 EMS3.NODE Row Share
EMS3 unknown 59,7 EMS3.NODE Row Share
EMS3 unknown 58,6 EMS3.NODE Row Share
EMS3 unknown 48,7 EMS3.NODE Row Share
EMS3 unknown 47,7 EMS3.NODE Row Share
EMS3 unknown 46,6 EMS3.NODE Row Share
EMS3 unknown 45,6 EMS3.NODE Row Share
EMS3 unknown 44,9 EMS3.NODE Row Share
EMS3 unknown 43,7 EMS3.NODE Row Share
EMS3 unknown 42,7 EMS3.NODE Row Share
EMS3 unknown 41,6 EMS3.NODE Row Share
EMS3 unknown 30,7 EMS3.NODE Row Share
EMS3 unknown 29,6 EMS3.NODE Row Share
EMS3 unknown 28,6 EMS3.NODE Row Share
EMS3 unknown 27,6 EMS3.NODE Row Share
EMS3 unknown 13,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 18,116 SYS.ALL_CATALOG Exclusive
EMS3 unknown 23,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 25,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 67,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 66,10 SYS.ALL_CATALOG Exclusive
EMS3 unknown 65,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 64,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 63,8 SYS.ALL_CATALOG Exclusive
EMS3 unknown 62,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 61,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 60,8 SYS.ALL_CATALOG Exclusive
EMS3 unknown 59,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 58,6 SYS.ALL_CATALOG Exclusive

Username Term Kill String Table Name Lock Held Lock Requested
---------- -------- ------------- ----------------------------------- -------------------- --------------------
EMS3 unknown 57,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 56,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 55,10 SYS.ALL_CATALOG Exclusive
EMS3 unknown 54,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 53,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 52,9 SYS.ALL_CATALOG Exclusive
EMS3 unknown 51,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 50,8 SYS.ALL_CATALOG Exclusive
EMS3 unknown 49,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 48,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 47,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 46,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 45,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 44,9 SYS.ALL_CATALOG Exclusive
EMS3 unknown 43,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 42,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 41,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 40,8 SYS.ALL_CATALOG Exclusive
EMS3 unknown 39,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 38,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 37,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 36,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 35,10 SYS.ALL_CATALOG Exclusive
EMS3 unknown 34,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 33,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 32,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 30,7 SYS.ALL_CATALOG Exclusive
EMS3 unknown 29,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 28,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 27,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 26,10 SYS.ALL_CATALOG Exclusive
EMS3 unknown 24,6 SYS.ALL_CATALOG Exclusive
EMS3 unknown 22,56 SYS.ALL_CATALOG Exclusive

96 rows selected.

SQL>