8 Replies Latest reply: Nov 16, 2009 7:40 PM by 635325 RSS

    CKPT Row Share lock blocking analyze job


      I have a user who is running an analyze job that is hanging.

      When I check the locks I find:

      Blocking lock - CKPT
      session ID 89
      serial #1
      process id 17732
      Lock type RO
      Mode Held Row Share
      Mode Requested None
      Time in current mode 24226

      Blocked - db_util_user_schema (a statistics job)
      session ID 56
      serial #20976
      process id 1484
      Lock type RO
      Mode Held Row Share Row Exclusive
      Mode Requested Exclusive
      Time in current mode 24226

      There is no SQLID for session 89.

      The SQL for session 56 is:
      select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ parallel(t,24) parallel_index(t,24) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */substrb("REM_AUG_NM",1,32) val, ntile(254) over (order by nlssort(substrb("REM_AUG_NM",1,32),'NLS_SORT = binary')) bkt from sys.ora_temp_1_ds_202268 t where substrb("REM_AUG_NM",1,32) is not null) group by val) group by maxbkt order by maxbkt

      I don't find any unusual trace files or entries in the alert log

      I'm drawing a blank on this one and any help would be appreciated!


      Edited by: user632322 on Nov 16, 2009 11:43 AM