I've never really known how much I can 'trust' the views *advice views so I've never really made decisions based on them. Our system was restarted about a week ago so things might still be in the process of balancing out-I don't know how much data the advice views needs. I've updated my spreadsheet to show the results from v$pga_target_Advice and v$sga_target_advice http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dFZBNE0wU0JpUW1Na1hVYlBfeFVYOEE&hl=en On Wed, Mar 10, 2010 at 5:15 PM, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx>wrote: > Have you checked v$sga_target_advice (or the GUI equivalent) and > v$db_cache_advice? > > > > > > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Neil Kodner > *Sent:* Wednesday, March 10, 2010 3:10 PM > *To:* Jared Still > *Cc:* Paul Drake; oracle-l-freelists > *Subject:* Re: How to troubleshoot heavy RAM consumption > > > > Thanks. As an aside, while we're having memory/paging issues, is there a > good way to tell if our SGA is in fact too large? One of the challenges > that we face is that one of the heavier-used applications does not use > prepared statements and that has the potential to pollute the shared pool. > We enable cursor-sharing at the session level for these users. > > On Wed, Mar 10, 2010 at 2:51 PM, Jared Still <jkstill@xxxxxxxxx> wrote: > > Neil, > > > > You may want to try running the attached script pga_advice > > to see how much PGA Oracle thinks you need to meet current usage. > > > > The script pga_workarea_active.sql will show how the memory > > is being used. It doesn't show which sessions, I don't think I > > have a query for that, but someone else posted the tables needed > > if you want to figure that out. > > > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > Oracle Blog: http://jkstill.blogspot.com > Home Page: http://jaredstill.com > > > > > On Wed, Mar 10, 2010 at 11:42 AM, Neil Kodner <nkodner@xxxxxxxxx> wrote: > > Great advice. I'll look at sessions by PGA usage in v$process. I didn't > realize that the pga_aggregate_target was that soft of a limit. Is there a > better source for determining exactly what, at the session level, is eating > up the PGA memory? > > > > What are some factors that can contribute to sessions using, and hanging on > to pga? > > > > As far as parallel goes, we're not doing a whole lot during business hours. > > > On Wed, Mar 10, 2010 at 10:51 AM, Paul Drake <bdbafh@xxxxxxxxx> wrote: > > Neil, > > It looks like you have some memory free in the large and java pools. > Is this running with automatic memory management? > Still, that's not in the ballpark of a pga that should be 6 GB going to 14 > GB. > > It appears that the max pga_aggregate used was around 14 GB. > You appear to be on the right track. > Its not that the pga_aggregate_target setting is too small, its that > it isn't being respected. > You're probably going to want to drill down into pga mem usage. > Perhaps you have a few sessions that would be better off with a manual > workarea policy. > Is there a large amount of parallel work going on? > > If you hike the value for pga_aggregate_target, it might make the > problem worse as Oracle may extrapolate into using even larger amounts > of pga memory (than the peak of 14 GB). > > Paul > > > > On Wed, Mar 10, 2010 at 12:14 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote: > > Our 32gb solaris 9 server running two instances of 10gr2 > > One small db to support OID (sga max size 288M, pga aggregate 32M) and > one > > of our production dbs(6gb pga aggregate target, 12gb sga_max_size). > > According to top, before the workday starts, we are sitting at 10-12gb > > available physical memory. Once our users get working, that number drops > to > > 500-600mb available. Top reports 29G swap in use, 11G swap free. OEM > says > > that we're performing heavy swapping and I'm not sure how to resolve > this. > > We typically have 1500-1700 connections. My gut tells me that pga might > be > > too small and that sga is too large, but i'm not really able to tell. > > My v$pgastat and v$sgastat can be viewed at > > > http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dFZBNE0wU0JpUW1Na1hVYlBfeFVYOEE&hl=en > > > > > > > > > > > > ------------------------------ > Privileged/Confidential Information may be contained in this message or > attachments hereto. Please advise immediately if you or your employer do not > consent to Internet email for messages of this kind. Opinions, conclusions > and other information in this message that do not relate to the official > business of this company shall be understood as neither given nor endorsed > by it. >