关于sga_max_size的测试

由于系统的特殊要求，我们想要将以前固定在sga_max_size中的一部分内存释放出来，还给OS，因此，引出了下面的一个

  如果在启动数据库的参数文件中从没有设置过sga_max_size，那么这个参数就是SGA各部件的和。如果曾经设置了这个参数，那么分为集中情况：

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora
SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 403772836
SQL> show sga

Total System Global Area  403772836 bytes
Fixed Size                   452004 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL>
SQL> create pfile='/home/oracle/lunar.ora' from spfile;

File created.

Elapsed: 00:00:00.00
SQL>

///
1，修改 sga_max_size 为一小于SGA各个pool的尺寸综合的值，如10M：
///
[oracle@ts01 oracle]$ cat lunar.ora| grep sga_max_size
#*.sga_max_size=209715200
*.sga_max_size=10M
[oracle@ts01 oracle]$

然后测试：
SQL> startup pfile=/home/oracle/lunar.ora
ORACLE instance started.

Total System Global Area  403772836 bytes
Fixed Size                   452004 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show sga

Total System Global Area  403772836 bytes
Fixed Size                   452004 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 403772836
SQL>
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 167772160
large_pool_size                      big integer 16777216
olap_page_pool_size                  integer     33554432
shared_pool_reserved_size            big integer 8388608
shared_pool_size                     big integer 167772160
SQL> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 33554432
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
session_cached_cursors               integer     0
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
结论：
如果设置的 sga_max_size 小于实际的SGA中各个pool的尺寸总和的大小，
那么 sga_max_size 的值会被oracle自动以实际的SGA的总尺寸代替。
///

///
2，在pfile中，不设置 sga_max_size 的值：
///
[oracle@ts01 oracle]$ cat lunar.ora| grep sga_max_size
#*.sga_max_size=10M
[oracle@ts01 oracle]$

然后测试：
SQL> startup pfile=lunar.ora
ORACLE instance started.

Total System Global Area  403772836 bytes
Fixed Size                   452004 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show sga

Total System Global Area  403772836 bytes
Fixed Size                   452004 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 403772836
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

结论：
如果不设置 sga_max_size ，
oracle会自动的以实际的SGA的总尺寸来设置sga_max_size 的值。
///

 

 

///
3，在pfile中，设置 sga_max_size 的值为大于SGA中各个pool的尺寸总和的值：
但是 sga_max_size 的值相对于所有可用的物理内存来说，是一个合理的值。
///
[oracle@ts01 oracle]$ cat lunar.ora| grep sga_max_size
*.sga_max_size=600M
[oracle@ts01 oracle]$

然后测试：
SQL> startup pfile=lunar.ora
ORACLE instance started.

Total System Global Area  638654140 bytes
Fixed Size                   452284 bytes
Variable Size             603979776 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 167772160
large_pool_size                      big integer 16777216
olap_page_pool_size                  integer     33554432
shared_pool_reserved_size            big integer 8388608
shared_pool_size                     big integer 167772160
SQL> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 33554432
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
session_cached_cursors               integer     0
SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 638654140
SQL> show sga       

Total System Global Area  638654140 bytes
Fixed Size                   452284 bytes
Variable Size             603979776 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL>

 

结论：
此时，sga_max_size的实际的值和pfile中的sga_max_size指定的值是一样的。
///

 

 

 

///
4，在pfile中，设置 sga_max_size 的值为大于SGA中各个pool的尺寸总和的值：
而且 sga_max_size 的值相对于所有可用的物理内存来说，是一个很不合理的值，
比如，远远大于可用物理内存。
///
[oracle@ts01 oracle]$ cat lunar.ora| grep sga_max_size
*.sga_max_size=2G
[oracle@ts01 oracle]$

然后测试：
SQL> startup pfile=lunar.ora
ORA-27123: unable to attach to shared memory segment
Linux Error: 22: Invalid argument
Additional information: 1
Additional information: 1114116
SQL>

结论：
此时，数据库因为不能合理的分配内存和不能启动。
///