This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 5, 2013 2:03 PM by Gamblesk RSS

Lock Issue.

933257 Newbie
Currently Being Moderated

Hi,
    i am using 11G version 2 of oracle. We got a query timeout error in JAVA log and the app got slow down, so wanted to know the exact cause. Now we got the sql from java log. So by querying the DBA_HIST_ACTIVE_SESS_HISTORY with the sql_id within the time period, i am able to get the event - 'enq: TX - row lock contention' for that particular sql_id also the blocking session_id, 1000. This sql is an Update statement (UPDATE a SET c1='Y' WHERE p1='ABC'; NOTE- Unique index on 'p1') , ideally should get competed within seconds. But when i am querying the DBA_HIST_ACTIVE_SESS_HISTORY for that exact time period, for the session_id - 1000, i got no sql. How to get that exact query from session 1000, which was blocking the main(UPDATE) sql.

 

select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id = '1211jwer45n'
and
sample_time between
    to_date('25-JUL-13 11.45.00 PM','dd-MON-yy hh:mi:ss PM')
       and
    to_date('25-JUL-13 11.59.00 PM','dd-MON-yy hh:mi:ss PM')
order by session_id, sample_time;


SESSION_ID SAMPLE_TIME               SESSION EVENT                           WAIT_TIME TIME_WAITED SQL_ID               CH#
---------- ------------------------- ------- ------------------------------ ---------- ----------- ------------- ----------
      6882 25-JUL-13 11.57.43.182 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      6882 25-JUL-13 11.57.53.242 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      6882 25-JUL-13 11.58.03.342 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      6882 25-JUL-13 11.58.13.464 PM WAITING enq: TX - row lock contention           0    36742757 1211jwer45n          0
      7281 25-JUL-13 11.52.40.259 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.52.50.359 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.00.459 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.10.529 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.20.629 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.30.759 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.40.819 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.53.50.909 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.01.009 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.11.147 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.21.217 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.31.357 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.41.437 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.54.51.507 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.01.627 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.11.737 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.21.827 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.31.927 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.42.017 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.55.52.087 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.02.187 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.12.312 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.22.382 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.32.522 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.42.622 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.56.52.682 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.02.782 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.12.872 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.22.942 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.33.082 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.43.182 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.57.53.242 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.58.03.342 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7281 25-JUL-13 11.58.13.464 PM WAITING buffer busy waits                       0     1001434 1211jwer45n          0
      7528 25-JUL-13 11.47.27.441 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.47.37.511 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.47.47.621 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.47.57.721 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.07.819 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.17.919 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.28.029 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.38.099 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.48.179 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.48.58.279 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.08.339 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.18.439 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.28.579 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.38.639 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.48.709 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.49.58.809 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.08.899 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.18.999 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.29.129 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.39.189 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.49.279 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.50.59.369 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.09.439 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.19.539 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.29.639 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.39.699 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.49.799 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.51.59.889 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.52.09.969 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.52.20.069 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.52.30.199 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.52.40.259 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.52.50.359 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.00.459 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.10.529 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.20.629 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.30.759 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.40.819 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.53.50.909 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.01.009 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.11.147 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.21.217 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.31.357 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.41.437 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.54.51.507 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.01.627 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.11.737 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.21.827 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.31.927 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.42.017 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.55.52.087 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.02.187 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.12.312 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.22.382 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.32.522 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.42.622 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.56.52.682 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.57.02.782 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.57.12.872 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.57.22.942 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.57.33.082 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0
      7528 25-JUL-13 11.57.43.182 PM WAITING enq: TX - row lock contention           0           0 1211jwer45n          0


select session_id,sample_time, session_state, blocking_session, current_obj#, current_file#, current_block#, current_row#
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time between
   to_date('25-JUL-13 11.45.00 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('25-JUL-13 11.59.00 PM','dd-MON-yy hh:mi:ss PM')
and session_id in  (7281,7528,6882)
and event = 'enq: TX - row lock contention'
order by sample_time;


SESSION_ID SAMPLE_TIME               SESSION BLOCKING_SESSION CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#
---------- ------------------------- ------- ---------------- ------------ ------------- -------------- ------------
      7528 25-JUL-13 11.47.27.441 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.47.37.511 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.47.47.621 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.47.57.721 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.07.819 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.17.919 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.28.029 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.38.099 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.48.179 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.48.58.279 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.08.339 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.18.439 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.28.579 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.38.639 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.48.709 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.49.58.809 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.08.899 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.18.999 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.29.129 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.39.189 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.49.279 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.50.59.369 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.09.439 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.19.539 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.29.639 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.39.699 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.49.799 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.51.59.889 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.52.09.969 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.52.20.069 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.52.30.199 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.52.40.259 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.52.40.259 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.52.50.359 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.52.50.359 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.00.459 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.00.459 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.10.529 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.10.529 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.20.629 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.20.629 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.30.759 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.30.759 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.40.819 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.40.819 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.53.50.909 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.53.50.909 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.01.009 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.01.009 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.11.147 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.11.147 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.21.217 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.21.217 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.31.357 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.31.357 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.41.437 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.41.437 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.54.51.507 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.54.51.507 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.01.627 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.01.627 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.11.737 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.11.737 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.21.827 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.21.827 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.31.927 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.31.927 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.42.017 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.42.017 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.55.52.087 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.55.52.087 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.02.187 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.02.187 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.12.312 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.12.312 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.22.382 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.22.382 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.32.522 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.32.522 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.42.622 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.42.622 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.56.52.682 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.56.52.682 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.57.02.782 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.02.782 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.57.12.872 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.12.872 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.57.22.942 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.22.942 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.33.082 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.57.33.082 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.43.182 PM WAITING             1000       438974           252         273710            0
      6882 25-JUL-13 11.57.43.182 PM WAITING             1000       438974           252         273710            0
      7528 25-JUL-13 11.57.43.182 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.57.53.242 PM WAITING             1000       438974           252         273710            0
      6882 25-JUL-13 11.57.53.242 PM WAITING             1000       438974           252         273710            0
      6882 25-JUL-13 11.58.03.342 PM WAITING             1000       438974           252         273710            0
      7281 25-JUL-13 11.58.03.342 PM WAITING             1000       438974           252         273710            0
      6882 25-JUL-13 11.58.13.464 PM WAITING             7281       438974           252         273710            0

99 rows selected.


select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from DBA_HIST_ACTIVE_SESS_HISTORY
where session_id = 1000
and
sample_time between
    to_date('25-JUL-13 11.45.00 PM','dd-MON-yy hh:mi:ss PM')
       and
    to_date('26-JUL-13 01.00.00 AM','dd-MON-yy hh:mi:ss AM')
order by sample_time;

-- No rows returned

  • 1. Re: Lock Issue.
    Lakmal Rajapakse Expert
    Currently Being Moderated

    Most probably session 1000 has updated the row earlier but not committed it - that is why you do not see any activity for it. Try going back even further and see if there is any activity recorded for session 1000.

     


  • 2. Re: Lock Issue.
    933257 Newbie
    Currently Being Moderated


    yes by querying back for session - 1000, Still i cant find the same object from the Current_obj# column. But i am observing below waits(SQL*Net break/reset to client) for the same sid - 1000 and serial no- 4837 . No such sql statements. But again its logged way behind(9:00 PM) the actual issue occurrence time i.e 11:45 PM to 12:00

     

     

    USER_ID SESSION_ID SESSION_SERIAL# SAMPLE_TIME               SESSION EVENT                        WAIT_TIME TIME_WAITED SQL_ID           OBJ_NAME

                                                                               CH#

    --------- ---------- --------------- ------------------------- ------- ------------------------------ ---------- ----------- ------------- -----------

    ------------------------------------------------------------------- ----------

          111       1000            4837 25-JUL-13 09.12.07.594 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.12.17.704 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.12.27.854 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.12.37.924 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.12.48.044 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.12.58.144 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.13.08.214 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

          111       1000            4837 25-JUL-13 09.13.18.314 PM WAITING SQL*Net break/reset to client           0           0               TAB_ACC

                                                                                -1

  • 3. Re: Lock Issue.
    Lakmal Rajapakse Expert
    Currently Being Moderated

    Most probably this is what happened:

     

    At sometime before the above wait events occured - the session (sid=1000) updated a row in table

    And then some error occurred during a call to the database as indicated by the wait event - SQL*Net break/reset to client

    Most probably this error was not probably handled by the client program and the session hung doing nothing.

     

    Then at 11:45pm another session tried to update the same row in the table and got locked out waiting for session 1000 to release the row.

     


  • 4. Re: Lock Issue.
    Gamblesk Explorer
    Currently Being Moderated

    It doesn't matter how long ago it happened. If SID 1000 did some work and did not commit for whatever reason it is not going to be an issue until the same rows are attempted to be modified by another session.

  • 5. Re: Lock Issue.
    933257 Newbie
    Currently Being Moderated

    How can i possibly get the exact sql of the session 1000, which caused the issue.

  • 6. Re: Lock Issue.
    Lakmal Rajapakse Expert
    Currently Being Moderated

    You might not get the actual SQL that updated the row - not all SQLs are captured. But more importantly I think you need to find out what error occurred around 9pm - assuming session 1000 was coming from your application - check your application logs. Also you need to find out why it was hanging around doing nothing - maybe there is a problem in your application logic handling exceptions.

  • 7. Re: Lock Issue.
    Gamblesk Explorer
    Currently Being Moderated

    It might also be possible that a user did something and walked away without commiting. That depends on the application in question though.

  • 8. Re: Lock Issue.
    sb92075 Guru
    Currently Being Moderated

    892918 wrote:

     

    It doesn't matter how long ago it happened. If SID 1000 did some work and did not commit for whatever reason it is not going to be an issue until the same rows are attempted to be modified by another session.

    No other session can ever "see" uncommitted DML rows; so it can NOT be any issue!

  • 9. Re: Lock Issue.
    Gamblesk Explorer
    Currently Being Moderated

    Who said anything about seeing the data. If a session did some DML and didn't commit its not going to be come a locking issue until another session tries to update those same rows.

  • 10. Re: Lock Issue.
    Lakmal Rajapakse Expert
    Currently Being Moderated

    sb92075 what are you talking about !!

  • 11. Re: Lock Issue.
    933257 Newbie
    Currently Being Moderated

    I have one doubt here, what exact sqls does DBA_HIST_ACTIVE_SESS_HISTORY will hold? does it hold all the sqls fired be the session e.g 1000 till the exception occurred?

  • 12. Re: Lock Issue.
    Lakmal Rajapakse Expert
    Currently Being Moderated

    Not all - sessions are sampled - so SQLs that have short runtimes will may not be captured

  • 13. Re: Lock Issue.
    sb92075 Guru
    Currently Being Moderated

    933257 wrote:

     

    I have one doubt here, what exact sqls does DBA_HIST_ACTIVE_SESS_HISTORY will hold? does it hold all the sqls fired be the session e.g 1000 till the exception occurred?

     

    "DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY. See "V$ACTIVE_SESSION_HISTORY" for further interpretation details for many of these columns (except SNAP_ID,DBID, and INSTANCE_NUMBER)."

     

    SNAPSHOTS!

  • 14. Re: Lock Issue.
    933257 Newbie
    Currently Being Moderated


    Hi ,

          Its getting tough to scan the application log(due to many app server) , to know exact reason , why session 1000, got hung doing nothing. So my question is if i can debug the issue, by verifying alert log or enabling alert log in database level, as the issue occurred again. please let me know.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points