RE: CTAS table w/LOB on ASM waits on 'row cache lock'
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Mar 2014 16:04:50 +0000
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<mailto:richa03@xxxxxxxxx>>
Reply-To: "richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>"
<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 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:
