SGA size at Oracle10G startup

SGA size at Oracle10G startup

The size of the SGA started in Oracle 10g Database is determined by sga_target and sga_max_size. The following three cases are discussed:

Sga_target = sga_max_size

Parameter file value

*.sga_target=599785472

Start

SQL> startupORACLE instance started.Total System Global Area 599785472 bytesFixed Size 2022632 bytesVariable Size 171967256 bytesDatabase Buffers 423624704 bytesRedo Buffers 2170880 bytesDatabase mounted.Database opened.

Sga_target <sga_max_size
Parameter file value

*.sga_max_size=800m*.sga_target=700m

Start Database

SQL> startupORACLE instance started.Total System Global Area 838860800 bytesFixed Size 2024496 bytesVariable Size 297798608 bytesDatabase Buffers 536870912 bytesRedo Buffers 2166784 bytesDatabase mounted.Database opened.

SQL> select 838860800/1024/1024 from dual;838860800/1024/1024------------------- 800SQL> show parameter sga_NAME TYPE VALUE------------------- ----------- -----------------------sga_max_size big integer 800Msga_target big integer 700M

sga_target>sga_max_size

Parameter file value

*.sga_max_size=600m*.sga_target=700m

The actual startup size of the database

SQL> startupORACLE instance started.Total System Global Area 734003200 bytesFixed Size 2023656 bytesVariable Size 192941848 bytesDatabase Buffers 536870912 bytesRedo Buffers 2166784 bytesDatabase mounted.Database opened.

SQL> select 734003200/1024/1024 from dual;734003200/1024/1024------------------- 700SQL> show parameter sga_ NAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 700Msga_target big integer 700M

Experiment conclusion
From the above three experiments, we can see that the size of the SGA during database startup is determined by the greater limit in SGA_TARGET and SGA_MAX_SIZE.

When SGA_TARGET <= SGA_MAX_SIZE, SGA_MAX_SIZE prevails.
When SGA_TARGET> SGA_MAX_SIZE, assign the value of SGA_TARGET to SGA_MAX_SIZE, and then use SGA_MAX_SIZE as the standard.