Size of SGA during Oracle 10 Gb startup

Size of SGA during Oracle 10 Gb 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> 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
Parameter file value

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

Start Database

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

Parameter file value

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

The actual startup size of the database

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

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.