Scenario
==========================
There is an application which is using several remote db_links
In addition, there is a monitor program, which running every 1 hour, to check that the db_links are active.
This monitor program does the check by simple select, and reports in case of an error.
The select :
SELECT SYSDATE FROM DUAL@<DB_LINK>
For one of the remote DB_LINKS the SQL hangs for 15 minutes, and then return an error:
SQL> SELECT SYSDATE FROM DUAL@ZAF_DBLINK;
SELECT SYSDATE FROM DUAL@ZAF_DBLINK
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
==========================
Investigation
==========================
Following tables can be checked
V$SESSION
V$SESSION_EVENT
V$SESSION_WAIT
V$LOCKED_OBJECT
DBA_DDL_LOCKS
V$ACCESS
DBA_DB_LINKS
V$SESSION
SELECT * FROM V$SESSION
WHERE UPPER(event) like '%JOB%'
There are several entries with event = 'jobq slave wait'
SELECT * FROM V$SESSION_EVENT
WHERE event = 'jobq slave wait';
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
--- ---------------- ----------- -------------- ----------- ------------ ----------
109 jobq slave wait 60 59 17582 293 294
110 jobq slave wait 11 11 3242 295 298 125 jobq slave wait 4 3 969 242 293
129 jobq slave wait 10 10 2941 294 295
133 jobq slave wait 26 25 7441 286 301
141 jobq slave wait 11 11 3240 295 296
142 jobq slave wait 11 11 3247 295 300
151 jobq slave wait 11 11 3238 294 299
164 jobq slave wait 11 11 3245 295 298
168 jobq slave wait 31 30 9105 294 299
170 jobq slave wait 11 11 3239 294 297
178 jobq slave wait 172 169 50138 291 299
192 jobq slave wait 20 19 5858 293 293
193 jobq slave wait 40 38 11718 293 293
200 jobq slave wait 11 11 3241 295 298
216 jobq slave wait 11 11 3243 295 298
V$SESSION_WAIT
SELECT SID, EVENT, WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS
FROM V$SESSION_WAIT
WHERE event = 'jobq slave wait';
SID EVENT WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ------------- ----------- -----------
105 jobq slave wait 2723168908 6 Idle
109 jobq slave wait 2723168908 6 Idle
110 jobq slave wait 2723168908 6 Idle
121 jobq slave wait 2723168908 6 Idle
125 jobq slave wait 2723168908 6 Idle
129 jobq slave wait 2723168908 6 Idle
133 jobq slave wait 2723168908 6 Idle
140 jobq slave wait 2723168908 6 Idle
141 jobq slave wait 2723168908 6 Idle
142 jobq slave wait 2723168908 6 Idle
145 jobq slave wait 2723168908 6 Idle
146 jobq slave wait 2723168908 6 Idle
148 jobq slave wait 2723168908 6 Idle
150 jobq slave wait 2723168908 6 Idle
155 jobq slave wait 2723168908 6 Idle
164 jobq slave wait 2723168908 6 Idle
168 jobq slave wait 2723168908 6 Idle
170 jobq slave wait 2723168908 6 Idle
174 jobq slave wait 2723168908 6 Idle
177 jobq slave wait 2723168908 6 Idle
178 jobq slave wait 2723168908 6 Idle
183 jobq slave wait 2723168908 6 Idle
191 jobq slave wait 2723168908 6 Idle
192 jobq slave wait 2723168908 6 Idle
200 jobq slave wait 2723168908 6 Idle
202 jobq slave wait 2723168908 6 Idle
203 jobq slave wait 2723168908 6 Idle
204 jobq slave wait 2723168908 6 Idle
216 jobq slave wait 2723168908 6 Idle
V$LOCKED_OBJECT
SELECT * FROM V$LOCKED_OBJECT
--no rows are returned
DBA_DDL_LOCKS
SELECT * FROM DBA_DDL_LOCKS
--no rows are returned
V$ACCESS
SELECT * FROM V$ACCESS
SID OWNER OBJECT TYPE
---------- ------------------------------ ---------- ------------------------
101 MY_USER DUAL NON-EXISTENT
101 PUBLIC DUAL SYNONYM
101 SYS DUAL TABLE
108 MY_USER DUAL NON-EXISTENT
108 PUBLIC DUAL SYNONYM
108 SYS DUAL TABLE
110 MY_USER DUAL NON-EXISTENT
110 PUBLIC DUAL SYNONYM
110 SYS DUAL TABLE
111 MY_USER DUAL NON-EXISTENT
111 PUBLIC DUAL SYNONYM
111 SYS DUAL TABLE
112 SYS DUAL TABLE
122 SYS DUAL TABLE
127 MY_USER DUAL NON-EXISTENT
127 PUBLIC DUAL SYNONYM
127 SYS DUAL TABLE
132 MY_USER DUAL NON-EXISTENT
132 PUBLIC DUAL SYNONYM
132 SYS DUAL TABLE
141 MY_USER DUAL NON-EXISTENT
141 PUBLIC DUAL SYNONYM
141 SYS DUAL TABLE
143 MY_USER DUAL NON-EXISTENT
143 PUBLIC DUAL SYNONYM
143 SYS DUAL TABLE
144 MY_USER DUAL NON-EXISTENT
144 PUBLIC DUAL SYNONYM
144 SYS DUAL TABLE
149 MY_USER DUAL NON-EXISTENT
149 PUBLIC DUAL SYNONYM
149 SYS DUAL TABLE
152 SYS DUAL TABLE
153 SYS DUAL TABLE
154 MY_USER DUAL NON-EXISTENT
154 PUBLIC DUAL SYNONYM
154 SYS DUAL TABLE
160 MY_USER DUAL NON-EXISTENT
160 PUBLIC DUAL SYNONYM
160 SYS DUAL TABLE
164 SYS DUAL TABLE
165 MY_USER DUAL NON-EXISTENT
165 PUBLIC DUAL SYNONYM
165 SYS DUAL TABLE
167 MY_USER DUAL NON-EXISTENT
167 PUBLIC DUAL SYNONYM
167 SYS DUAL TABLE
171 MY_USER DUAL NON-EXISTENT
This seems very weird. How can DUAL object be non existent?!
SELECT * FROM DBA_DB_LINKS WHERE db_link = 'ZAF_DBLINK';
No rows are returned!!!
After additional investigation, it appears that the db_link was dropped, but the PL/SQL code was still trying to check the connection to that db_link.
==========================
Resolution
==========================
After recreating the db_link:
SQL> SELECT 1 FROM SOME_TABLE@ZAF_DBLINK where rownum < 2;
1
----------
1
SQL> SELECT SYSDATE FROM DUAL@ZAF_DBLINK;
Again this SQL is stuck for 15 minutes, and then return the same error:
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
As a workaround, the SQL checking the db_link was replaced, as not to use the DUAL@ZAF_DBLINK.
ORA-04021: timeout occurred while waiting to lock object
As a workaround, the SQL checking the db_link was replaced, as not to use the DUAL@ZAF_DBLINK.
No comments:
Post a Comment