Oracle 10G启动时SGA大小

Oracle 10G启动时SGA大小

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.

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>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的大小由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为准