SGA variable size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SGA variable size

Hybrid View

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    SGA variable size

    Here is a snapshot of my SGA:

    Total System Global Area 419430400 bytes
    Fixed Size 1979168 bytes
    Variable Size 318770400 bytes
    Database Buffers 92274688 bytes
    Redo Buffers 6406144 bytes

    Other than shared_pool_size, java_pool and large_pool is there any other
    initializtion variable that controls the size of the variable size.

    SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';

    SUM(BYTES)
    ----------
    79696632

    SQL> select sum(bytes) from v$sgastat where pool = 'java pool';

    SUM(BYTES)
    ----------
    33554432

    SQL> select sum(bytes) from v$sgastat where pool = 'large pool';

    SUM(BYTES)
    ----------
    8388608

    I am thinking there must be since the values don't add up to 318770400.
    Is there something else that goes into computation of variable size. Note:
    I am trying to make this DB use less memory and I am looking where I can
    start.

    Thanks

  2. #2
    Join Date
    Feb 2009
    Posts
    91
    To take this even a step further I tried setting the sga_target so Oracle
    can manage my SGA. I did not see any noticeable differences. My values
    look the same.

    Do I need to do something else to make the sga_target work?



    SQL> startup;
    ORACLE instance started.

    Total System Global Area 419430400 bytes
    Fixed Size 1979168 bytes
    Variable Size 318770400 bytes
    Database Buffers 92274688 bytes
    Redo Buffers 6406144 bytes
    Database mounted.
    Database opened.
    SQL> alter system set sga_target=200M;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup;
    ORACLE instance started.

    Total System Global Area 419430400 bytes
    Fixed Size 1979168 bytes
    Variable Size 318770400 bytes
    Database Buffers 92274688 bytes
    Redo Buffers 6406144 bytes
    Database mounted.
    Database opened.
    SQL>

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Oracle will change the value on fly when ever it's required. That means if you have a environment where there is not much load then you may see the values almost not changing.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    and over the time, you can see the past resize operations done by Oracle from v$sga_resize_ops view.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width