Re: SGA / Latch Question
Date: Wed, 31 Dec 2008 08:02:38 -0800 (PST)
On Dec 30, 5:32 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 30, 11:55 am, mrdjmag..._at_aol.com wrote:
> > On Dec 30, 1:34 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> > > On Dec 30, 1:12 pm, mrdjmag..._at_aol.com wrote:
> > > > Hi,
> > > > This just seems weird. I look at our SPFILE: *.shared_pool_size=2G
> > > > I query v$parameter: 1073741824
> > > > I ran a query:
> > > > SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAILPCT_SHARED_POOL_AVL
> > > > ---------------- ---------------- ------------------------------------
> > > > 2107.04836 1024 0205.766441
> > > > Ok, so if I have 2GB in my spfile, and I bounced my database, why is
> > > > it reading 1GB, unless I'm a dork and cannot read properly.......Size
> > > > = 1GB and used = 2GB??
> > > > Our sql library cache miss ratio says 99%, using that Spotlight
> > > > utility and the latch waits are about 10%. So, we figure we have a
> > > > problem.....
> > > > DUH??
> > > Please provide the folowing information:
> > > Full Oracle version
> > > values of database parameters:
> > > sga_max_size
> > > sga_target
> > > pga_target
> > > workarea_size_policy
> > > shared_pool_size
> > > db_cache_size
> > > The actual query or queries you are using since the source cannot
> > > always be determined just from seeing output.
> > > HTH -- Mark D Powell --
> > Database: 10gr2
> > sga_max_size=12G
> > sga_target=10G
> > pga_aggregate_target=800M (No pga_target defined)
> > workarea_size_policy='AUTO'
> > shared_pool_size=2G
> > db_cache_size=3000M
> This shared pool parameter is being automatically tuned by Oracle, so
> looking at the setting that would be used if you were "manual" may be
> a bit confusing. You might want to google around for good
> descriptions of workarea_size_policy. But be careful, there are a lot
> of myths and falsehoods floating about. The advice tables for setting
> pga and those things seem pretty good in my experience, though some
> things just keep asking for more and more. But it is nice and easy to
> just use EM to check them out.
> Here's a start: http://asktom.oracle.com/pls/asktom/f?p=100:11:4452652851819884::::P1...
> Since you have another thread that seems to show strange code, it is
> possible the advice tables and anything based on them may be fooled.
> They are just tools, and any such tools have built-in assumptions.
> There is no pga_target.
> Some other important pieces to the puzzle:
> Are you using shared servers? (ohhh, that difference isn't supposed
> to be true anymore...)
> Which _exact_ version of Oracle are you using? 10gR2 is not good
> enough, it should be something like 10.2.0.4.0
> Which platform are you on? (OS, version, hardware)
> And as Mark asked, show the query you are using to make the
> @home.com is bogus.
> $40M/year: http://www3.signonsandiego.com/stories/2008/dec/30/1b30buffett212021-...- Hide quoted text -
> - Show quoted text -
Joel, thanks for following up.
MRDJMAG, you may want to check out the information in the following views
v$sga_dynamic_components v$sga_resize_ops v$sga_current_resize_ops
HTH -- Mark D Powell -- Received on Wed Dec 31 2008 - 10:02:38 CST