RE: shared pool latching issue and missing time in trace file

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_at_gmx.de
> Subject: Re: shared pool latching issue and missing time in trace file
> Date: Tue, 11 Jun 2013 22:29:06 +0200
> To: zhang_gary2000_at_yahoo.com; oratune_at_yahoo.com; usn_at_usn-it.de; xiang_rao_at_hotmail.com; oracle-l_at_freelists.org
>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
                                               

--
http://www.freelists.org/webpage/oracle-l