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

  • From: Rich <richa03@xxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx
  • Date: Mon, 10 Mar 2014 15:10:01 -0700

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

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
> @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
> 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 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: