Re: CTAS table w/LOB on ASM waits on 'row cache lock'

  • From: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>
  • To: "richa03@xxxxxxxxx" <richa03@xxxxxxxxx>, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 10 Mar 2014 15:17:44 +0000

Hi Rich,

Hmm.....I looked at the definition of GV$ROWCACHE, using 
V$FIXED_VIEW_DEFINITION.  I expected to find certain cache ids were filtered 
out, for whatever reason.  But, That's apparently not the case.
GV$ROWCACHE is defined as:

select inst_id,kqrstcid,decode(kqrsttyp,1,'PARENT','SUBORDINATE'), decode(kqrstt

yp,2,kqrstsno,null),kqrsttxt,kqrstcsz,kqrstusg,kqrstfcs, kqrstgrq,kqrstgmi,kqrst

srq,kqrstsmi,kqrstsco,kqrstmrq,kqrstmfl, kqrstilr,kqrstifr,kqrstisr from x$kqrst

And there are no where clause predicates.  So, whatever is in X$KQRST is what's 
being returned.  I even tried querying X$KQRST directly, but it made no 
difference.  No CACHEID 11 was returned.  (I also am running on 
Linux x86-64.)

I think you'll need to open an SR, unless someone else on this list, with more 
internals knowledge than I, comes up with a better idea.


From: Rich <richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>>
Reply-To: "richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>" 
Date: Monday, March 10, 2014 at 12:20 AM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: CTAS table w/LOB on ASM waits on 'row cache lock'

Hi List,
Oracle on RHEL 6.3 w/ASM.

We are full CTASing a table (27M rows with LOB - ~250GB) into another TS (to 
change chunk/block size) (please don't ask "why not move" - it's a long story 
I'd rather not have to recount).  We used sizable initial extents to avoid 
space allocation during the creation (and the space allocation completed in 
~4min).  We now see all (9) W### processes (KTSJ Slaves) waiting on 'row cache 
lock' while executing the [internal] SQL 'select obj# from obj$ where dataobj# 
= :1'.  I can't tell if the W processes are progressing, but I don't see them 
burning CPU over ~3 hours.  From v$session_longops, it appears that the CTAS is 
progressing, however, we would like it to progress faster and would like to 
know if there is any way to avoid this/these waits (not necessarily right now 
for this run, but for future runs).

I see that the cache id being waited on (P1) is 11 for all of the W processes, 
however cannot find that [cache#] in v$rowcache.

My guess is that cache id 11 is hidden because it's LOB/securefile related 
(Oracle appears to like hiding LOB related things), however, Google and MOS 
search hasn't turned up anything for me and I very much doubt I'm the first one 
who has seen this behavior.

Anyone have any knowledge of mysterious rowcache #11?


Other related posts: