Thanks, Mark and Johathan! We might get a chance to test this further. If we do, I'll have DBAs look at the child[ren] - they didn't catch it this time. On Mon, Mar 10, 2014 at 9:04 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > If you look at x$kqrst it seem to be a fixed size array reported from > the SGA, and if you query addr, lag(addr,1) over address you get a constant > 72 bytes between adjacent items - so if there are any hidden items they're > before the beginning of the array or after the end of the array. So that 11 > looks suspect. I did wonder if it might be reporting the child latch > rather than the row cache entry, if so it's the latch covering > dc_constraints. > > Have you checked v$latch_children to see if there are any row cache > latches with a very large number of gets, spin gets or sleeps. If the > problem is in the row cache the latches might give you a clue. > > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on > behalf of Mark Bobak [Mark.Bobak@xxxxxxxxxxxx] > *Sent:* 10 March 2014 15:17 > *To:* richa03@xxxxxxxxx; Oracle-L Freelists > *Subject:* Re: CTAS table w/LOB on ASM waits on 'row cache lock' > > 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 11.2.0.3.0 > 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. > > -Mark > > From: Rich <richa03@xxxxxxxxx> > Reply-To: "richa03@xxxxxxxxx" <richa03@xxxxxxxxx> > Date: Monday, March 10, 2014 at 12:20 AM > To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx> > Subject: CTAS table w/LOB on ASM waits on 'row cache lock' > > Hi List, > Oracle 11.2.0.3 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? > > TIA, > Rich >