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

From: Rich <richa03_at_gmail.com>
Date: Sun, 9 Mar 2014 21:20:05 -0700
Message-ID: <CALgGkeBm8_qX6BBrZinH=HoEv2YJjhddBt2Y9yw6bkGbDdJBLg_at_mail.gmail.com>

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?


Received on Mon Mar 10 2014 - 05:20:05 CET

Original text of this message