CTAS table w/LOB on ASM waits on 'row cache lock'
- From: Rich <richa03@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 9 Mar 2014 21:20:05 -0700
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
Other related posts:
