Oracle 10G啟動時SGA大小，oracle10gsga大小

Oracle 10G啟動時SGA大小，oracle10gsga大小

Oracle 10g 庫啟動的SGA大小由sga_target與sga_max_size決定，分下面三種情況討論

sga_target=sga_max_size

參數檔案指定值

*.sga_target=599785472

啟動

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
參數檔案指定值

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

啟動資料庫

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

參數檔案指定值

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

而資料庫實際啟動的大小

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

實驗結論
由以上三個實驗可以看到，資料庫啟動時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為準