Hi all,
There is a job that executes automatically every night. Today developer told me that the job hang this monday, but it became normal from this Tuesday.
I queried from dba_hist_active_sess_history and found as follows:
SESSION_ID SQL_ID PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID SESSION_TYPE SESSION_STATE BLOCKING_SESSION BLOCKING_SESSION_STATUS EVENT SEQ# P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS WAIT_CLASS_ID WAIT_TIME TIME_WAITED XID CURRENT_OBJ#
440 *cwz3m9gqd05u1* 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 *0* *0* *0* 48847 0 0005000500014E4D *93554*
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
It hang for long time, then killed the job and started it again. dba_hist_active_sess_history shows as follows:
277 *avusyfc49h2zw* 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 *92436*
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 2929706 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 2929706 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 2929706 000C000D00014026 92436
440 cwz3m9gqd05u1 95742 1 96156 1 FOREGROUND ON CPU NOT IN WAIT 3141 0 0 0 48847 0 0005000500014E4D 93554
277 avusyfc49h2zw 95742 1 96020 4 FOREGROUND WAITING 440 VALID enq: TX - row lock contention 85 name|mode 1415053316 usn<<16 | slot 327685 sequence 85581 Application 4217450380 0 0 000C000D00014026 92436
...
select * from dba_hist_sqltext where sql_id='cwz3m9gqd05u1';
SELECT COUNT(1) FROM
A WHERE ...
'93554' is the unique index of table
B
select * from dba_hist_sqltext where sql_id='avusyfc49h2zw';
insert into
C (...) select ... from
D
'92436' is the unique index of table
E
Questions:
1 Before the job was stopped, why it hang? There is no EVENT here.
2 Why CURRENT_OBJ# is not in the current SQL? What can cause this? Can you give me an example?
3 Why p1, p2, p3 of the session 440 are '0'?
Thanks,
Best regards.
Edited by: Siko lee on 2011-4-3 上午1:05