-
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
-
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>
-
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.
-
and over the time, you can see the past resize operations done by Oracle from v$sga_resize_ops view.
Thanks,