Oracle 10g 库启动的SGA大小由sga_target与sga_max_size决定,分下面三种情况讨论
sga_target=sga_max_size
参数文件指定值
*.sga_target=599785472
启动
SQL> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes
Variable Size 171967256 bytes
Database Buffers 423624704 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
从Total System Global Area 599785472 bytes可以看出,启动时值为599785472 ,与配置的参数值*.sga_target=599785472相等。
sga_target < sga_max_size
参数文件指定值
*.sga_max_size=800m
*.sga_target=700m
启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2024496 bytes
Variable Size 297798608 bytes
Database Buffers 536870912 bytes
Redo Buffers 2166784 bytes
Database mounted.
Database opened.
SQL> select 838860800/1024/1024 from dual;
838860800/1024/1024
-------------------
800
SQL> show parameter sga_
NAME TYPE VALUE
------------------- ----------- -----------------------
sga_max_size big integer 800M
sga_target big integer 700M
我们虽然指定了*.sga_target=700m,但启动时数据库实际分配的SGA却是800M.从参数上看sga_target还是700M,这也表明了另外一点,数据库实际分配的SGA可能会比指定的sga_target值要大。
sga_target>sga_max_size
参数文件指定值
*.sga_max_size=600m
*.sga_target=700m
而数据库实际启动的大小
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2023656 bytes
Variable Size 192941848 bytes
Database Buffers 536870912 bytes
Redo Buffers 2166784 bytes
Database mounted.
Database opened.
SQL> select 734003200/1024/1024 from dual;
734003200/1024/1024
-------------------
700
SQL> show parameter sga_
NAME TYPE VALUE
----------------------------- ----------- --------------------------
sga_max_size big integer 700M
sga_target big integer 700M
可以看到实际运行中,sga_max_size并没有以参数文件指定的为准,而是与sga_target值相等了。
实验结论
由以上三个实验可以看到,数据库启动时SGA的大小由SGA_TARGET和SGA_MAX_SIZE中的较大值决定
当SGA_TARGET <= SGA_MAX_SIZE时,以SGA_MAX_SIZE为准
当SGA_TARGET > SGA_MAX_SIZE时,将SGA_TARGET的值赋予SGA_MAX_SIZE,然后以SGA_MAX_SIZE为准