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

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_at_gmail.com<mailto:richa03_at_gmail.com>> Reply-To: "richa03_at_gmail.com<mailto:richa03_at_gmail.com>" <richa03_at_gmail.com<mailto:richa03_at_gmail.com>> Date: Monday, March 10, 2014 at 12:20 AM To: Oracle-L Freelists <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> 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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 10 2014 - 16:17:44 CET