Grokbase › Groups › Oracle › oracle-l › July 2008
FAQ
Oracle EE 10.2.0.3
Solaris 10, running in a zone

These are development databases so SGA is very small. I am not setting
sga_max_size or sga_target. I set the sga sizes as follows:

db_cache_size=128M
shared_pool_size=100M
log_buffer=10485760
java_pool_size=0

According to the Reference manual, if sga_max_size is not set is should be the
sum of the individual components (buffer pool, shared pool, etc).

That is now what happens. The buffer cache is increased to 256M and
sga_max_size is set to 372M.

SQL> show sga

Total System Global Area 390070272 bytes

Fixed Size 2030264 bytes
Variable Size 104858952 bytes
Database Buffers 268435456 bytes
Redo Buffers 14745600 bytes

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 372M

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 256M

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 100M

I can set sga_max_size and that should solve it but I'm wondering why this is
working this way.

Keith

Search Discussions

  • Rjamya at Jul 30, 2008 at 10:57 pm ⇧
    Keith,

    do you have 4 CPUs? then it might make sense because in absence of
    sga_target setting, db_cache_size will default to 4mb * #cpus * granule
    size.

    I am guessing granule size as 16M so, if you have 4cpus, the min
    db_cache_size will be 256m (or 8 cpus if granule size is 8m).

    Raj
  • Keith Moore at Jul 31, 2008 at 2:45 am ⇧
    Raj,

    Thanks. You put me on the right track but the granule size is actually 4M and...

    NAME TYPE VALUE

    ------------------ ----------- ------------------------------
    cpu_count integer 64

    Here's the deal. These are the Sun "cool thread" servers and while our zone
    only has 1 real CPU, each CPU has 64 threads of execution and to Oracle that
    looks like 64 CPUs.

    We moved the zone from an older server (32 threads per CPU) to a newer server
    (64 threads per CPU) and the buffer cache doubled in size.

    I don't know what if anything I can do about this. Need to research further.

    Here's another calculated value that is way too high.

    NAME TYPE VALUE
    ----------------------- ----------- ------------------------------
    parallel_max_servers integer 185

    BTW, on CPU intensive tasks these servers are about 25% the speed of other Sun
    processors. I've heard they are equivalent to a 300 MHz processor. They are
    best suited to applications like web servers where there are many processes
    but each request is small. OLTP databases might be another good use.

    I'm not sure our development environment with a few users is the right
    application. On the other hand we are running 20 databases on a relatively
    cheap server. Either way, I wasn't consulted.

    Keith
    Keith,

    do you have 4 CPUs? then it might make sense because in absence of
    sga_target setting, db_cache_size will default to 4mb * #cpus * granule
    size.

    I am guessing granule size as 16M so, if you have 4cpus, the min
    db_cache_size will be 256m (or 8 cpus if granule size is 8m).

    Raj
    --
    http://www.freelists.org/webpage/oracle-l
  • Bradd Piontek at Jul 31, 2008 at 3:29 am ⇧
    Keith,
    Have you tried setting cpu_count to something a bit more reasonable for
    the T2 chip you are running on or setting sga_target = 0

    Bradd Piontek
    Oracle Blog: http://piontekdd.blogspot.com
    Linked In: http://www.linkedin.com/in/piontekdd
    On Wed, Jul 30, 2008 at 9:45 PM, Keith Moore wrote:

    Raj,

    Thanks. You put me on the right track but the granule size is actually 4M
    and...

    NAME TYPE VALUE
    ------------------ ----------- ------------------------------
    cpu_count integer 64

    Here's the deal. These are the Sun "cool thread" servers and while our zone
    only has 1 real CPU, each CPU has 64 threads of execution and to Oracle
    that
    looks like 64 CPUs.

    We moved the zone from an older server (32 threads per CPU) to a newer
    server
    (64 threads per CPU) and the buffer cache doubled in size.

    I don't know what if anything I can do about this. Need to research
    further.

    Here's another calculated value that is way too high.

    NAME TYPE VALUE
    ----------------------- ----------- ------------------------------
    parallel_max_servers integer 185

    BTW, on CPU intensive tasks these servers are about 25% the speed of other
    Sun
    processors. I've heard they are equivalent to a 300 MHz processor. They are
    best suited to applications like web servers where there are many processes
    but each request is small. OLTP databases might be another good use.

    I'm not sure our development environment with a few users is the right
    application. On the other hand we are running 20 databases on a relatively
    cheap server. Either way, I wasn't consulted.

    Keith

    Keith,

    do you have 4 CPUs? then it might make sense because in absence of
    sga_target setting, db_cache_size will default to 4mb * #cpus * granule
    size.

    I am guessing granule size as 16M so, if you have 4cpus, the min
    db_cache_size will be 256m (or 8 cpus if granule size is 8m).

    Raj

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Keith Moore at Jul 31, 2008 at 4:38 pm ⇧
    Yes, the default granule size is 4M.

    select ksppinm, ksppdesc, kspftctxvl, kspftctxdf
    from x$ksppi, x$ksppcv2
    where (x$ksppi.indx+1) = x$ksppcv2.kspftctxpn
    and ksppinm = '_ksmg_granule_size';

    KSPPINM KSPPDESC KSPFTCTXVL KSPFTC

    ------------------- ---------------------- -------------- ------
    _ksmg_granule_size granule size in bytes 4194304 TRUE

    Keith
    Keith,

    Did you find the granule size by looking at _ksmg_granule_size? It's mostly
    determined by processes.

    Yong
    Raj,

    Thanks. You put me on the right track but the granule size is actually 4M
    and...

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 30, '08 at 10:15p
activeJul 31, '08 at 4:38p
posts5
users3
websiteoracle.com

3 users in discussion

Keith Moore: 3 posts Rjamya: 1 post Bradd Piontek: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase