Re: SGA / Latch Question

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

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#sthref492 http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref404

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::::P11_QUESTION_ID:8759826405304

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-jimmy-buffett-parlays-lifestyle-/?uniontrib