Re: SGA / Latch Question

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
>
> Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory...http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create...
>
> 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
> determination.
>
> jg
> --
> @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