SGA / Latch Question

On Dec 30, 1:34 pm, Mark D Powell wrote:
> On Dec 30, 1:12 pm, mrdjmag... DeleteThis @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
 >> Stay informed about: SGA / Latch Question 

On Dec 30, 11:55 am, mrdjmag... DeleteThis @aol.com wrote:
> On Dec 30, 1:34 pm, Mark D Powell wrote:
>
>
>
>
>
> > On Dec 30, 1:12 pm, mrdjmag... DeleteThis @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....#sthref
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create....#sthref

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...ESTION_

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-j...y-buffe
 >> Stay informed about: SGA / Latch Question 

On Dec 30, 5:32 pm, joel garry wrote:
> On Dec 30, 11:55 am, mrdjmag....RemoveThis@aol.com wrote:
>
>
>
>
>
> > On Dec 30, 1:34 pm, Mark D Powell wrote:
>
> > > On Dec 30, 1:12 pm, mrdjmag....RemoveThis@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/memo......http
>
> 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:::1...
>
> 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 --
 >> Stay informed about: SGA / Latch Question