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

From: Xiang Rao <xiang_rao_at_hotmail.com>
Date: Tue, 11 Jun 2013 10:41:44 -0400
Message-ID: <BLU177-W9AC585E440F29342DC14395850_at_phx.gbl>

Yes, if shared pool is the issue, at minimum I would look at shared pool memory usage from v$sgastat, before and after the specific parsing, especially "free memory" and data dictionary cache like "KQR L PO".  

'latch: row cache objects' is usually related to row cache activities. So chech v$rowcache, including USAGE, DLM_REQUESTS, DLM_RELEASES, etc, before and after your tests. From my personal experience, if there is a large cursor (Heap size 75750K exceeds notification threshold (51200K)) needs memory, you have a very large row cache, and shared pool was short of available memory because of fragmentation or whatever, you are going to have a lot of fun when your row cahce gets squeezed.  

BTW, do you use automatical memory management?  

Thanks,  

Xiang  

> Date: Tue, 11 Jun 2013 06:44:13 -0700
> From: oratune_at_yahoo.com
> Subject: Re: shared pool latching issue and missing time in trace file
> To: keydana_at_gmx.de; oracle-l_at_freelists.org
>
> It will help tremendously to know which release of Oracle you are using; shared pool issues can (and do) vary by release.
>
> I will ask if you have used Tanel Poder's sgastatx.sql (http://blog.tanelpoder.com/files/scripts/sgastatx.sql) to report on all of the various memory areas in the shared sub-pools. It is an excellent tool I have used numerous times to report memory allocation and diagnose issues with the shared pool when ORA-04031 errors crop up. You can see which sub-pools are being depleted the fastest and, possibly, decide how to correct the problem.
>
> As to where your time is spent when running this query as a non-SYS user, have you generated tkprof output from them to see if the execution plans are the same? I would expect them to differ somewhat and seeing those plans may give another clue to help you solve this time mystery.
> David Fitzjarrell
>
>
>

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

Received on Tue Jun 11 2013 - 16:41:44 CEST