RE: shared pool latching issue and missing time in trace file
- From: Xiang Rao <xiang_rao@xxxxxxxxxxx>
- To: "keydana@xxxxxx" <keydana@xxxxxx>, Gary Zhang <zhang_gary2000@xxxxxxxxx>, David Fitzjarrell <oratune@xxxxxxxxx>, Martin Klier <usn@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Jun 2013 17:07:24 -0400
Hi Sigrid,
I don't see any issue with row cache. But your subpool 3 has relatively low
free memory and high SQLA (sub pool unbalance).
If memory alert is generated by your query parsing, I suggest you to look at
v$sqlarea (and v$sql) for shareable_mem and version_count. v$sesstat and
V$SESSION_EVENT before and after can also help.
Thanks,
Xiang
> From: keydana@xxxxxx
> Subject: Re: shared pool latching issue and missing time in trace file
> Date: Tue, 11 Jun 2013 22:29:06 +0200
> To: zhang_gary2000@xxxxxxxxx; oratune@xxxxxxxxx; usn@xxxxxxxxx;
> xiang_rao@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>
> Martin, David, Xiang, Gary,
>
> many thanks for your suggestions and hints. To answer your questions
> (starting with those that are answered fastest) -
>
> 1) Oracle version is 11.2.0.3.6, and the instance is part of 4-node cluster.
> 2) Execution plan is the same throughout all executions, independent of
> parsing schema.
>
> 3) Thanks a lot David for pointing me to Tanel's sgastatx script - I've run
> it before and after another execution from PL/SQL Developer (which again did
> produce the time gap and associated memory notification). I'm attaching the
> two output files - it'd be great if you could tell me what to conclude
> (myself I'm not going to speculate right now, I have to get some background
> first :-))
>
> 4) Martin, thanks a lot for the link to your presentation, I'll read it after
> finishing this mail :-) Actually for child cursor problems I haven't checked
> yet (I'll do so tomorrow), being aware that in general there's quite a lot of
> hard parsing going on (and so, less of a chance for child cursor excesses to
> occur). This special statement uses (varying) literals exclusively.
>
> 5) Xiang, thanks for pointing me to v$rowcache. I'm attaching before and
> after snapshots of the view. Myself, here too I'm not sure what to conclude -
> there are getmisses in some areas but I cannot judge their severity (just in
> case you wonder, the high number of dlm conflicts for dc_sequences must to
> related to other concurrent activity). We're using ASMM.
>
>
> I've still been wondering about the "time gap" in the trace file, and whether
> the explanation is that there is actually no time gap, since in the end the
> time does get accounted for in the (dep=0) statement's parse cpu/parse
> elapsed - only I got confused by watching the "frozen" trace file emitting
> the (too) short latch waits from time to time, and then - as soon as the
> alert log has stated its "memory notification" - bursting into the recursive
> statements and finally, the statement's statistics itself.
> So I now think that during the time nothing seemed to happen the process was
> actually on cpu,- performing memory rearrangements in the shared pool? Does
> this make sense?
> (In the meantime it also occurred to me to test again using strace, to find
> out what's actually going on - I'll do that tomorrow, too).
>
> Thanks again for your time -
> Sigrid
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
