This discussion is archived
8 Replies Latest reply: Nov 16, 2009 11:40 AM by 635325 RSS

CKPT Row Share lock blocking analyze job

635325 Newbie
Currently Being Moderated

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


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