oracle資料庫記憶體分配（sga和pga）

1)         原則：SGA+PGA+OS使用記憶體<總物理RAM

 

 

2)         透過sga+pga就能大概判斷系統oracle使用了多少記憶體了

32位版本的oracle最大支援1.75GB的SGA

 

sga元件包括shared pool、 data buffer、 large pool

SGA=. db_block_buffers*db_block_size+ shared_pool_size+ log_buffer+Java_pool+size+large_pool_size

 

oracle推薦OLTP(on-line Transaction Processing)系統oracle佔系統總記憶體的80%,然後再分配80%給SGA,20%給PGA。也就是

SGA=system_total_memory*80%*80%

PGA=system_total_memory*80%*20%

SGA佔的實體記憶體不能超過75%，最多隻能佔70%，否則Oracle可能會出現各種的異常

 

 

3)         11g 中新增MEMORY_MAX_TARGET 引數是設定Oracle能佔OS多大的記憶體空間

11g MEMORY_MAX_TARGET =SGA+PGA

 

 

4)         10g及其以後版本的SGA_MAX_SIZE 引數

表示Oracle SGA 區最大能佔多大記憶體空間

 

 

5)         修改SGA大小（需要重啟例項，為了以防萬一，修改前先備份PFILE檔案）

1.         SQL> show parameter sga;  //檢視當前SGA，是1.7GB

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1696M

sga_target                           big integer 0

 

2.         SQL> alter system set sga_max_size=2048M scope=spfile;//修改sga最大值到2GB

 

System altered.

 

3.         重啟資料庫報錯

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORA-00844: Parameter not taking MEMORY_TARGET into account

ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEMORY_TARGET 1778384896.

SQL> show parameter memory

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 1705 Serial number: 5

可以透過sga_max_size(11g新引數)調整oracle在啟動時所使用的最小sga大小，但該引數不能超過memory_target（ORACLE使用記憶體大小）所指定的大小

如memory_target為1G,那麼sga_max_size只能小於或等於1G

否則在啟動時會出現以上錯誤提示：

SQL> show parameter memory_target;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_target                        big integer 1696M

 

MEMORY_TARGET相當於sga_target + pga_aggregate_target的總和

 

4.         利用之前備份的PFILE啟動資料庫，修改memory_target和sga_max_size引數，再重啟順利

SQL> alter system set memory_target=3200M scope=spfile;

 

System altered.

 

SQL> alter system set sga_max_size=2048M scope=spfile;

 

System altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area 2137886720 bytes

Fixed Size                  2215064 bytes

Variable Size             520094568 bytes

Database Buffers         1610612736 bytes

Redo Buffers                4964352 bytes

Database mounted.

Database opened.

//正常啟動

 

5.         查詢PGA大小，這裡是102MB

SQL> select value/1024/1024||'M' M from v$pgastat where name like 'total PGA allocated';

 

M

-----------------------------------------

102.5947265625M

 

6.         PGA_AGGREGATE_TARGET-此引數用來指定所有session總計可以使用最大PGA記憶體，預設是0不設定

如果要設定PGA最大值

SQL> show parameter pga;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 0

SQL> alter system set pga_aggregate_target=500M scope=spfile; //修改後重啟資料庫

 

System altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area 2137886720 bytes

Fixed Size                  2215064 bytes

Variable Size             520094568 bytes

Database Buffers         1610612736 bytes

Redo Buffers                4964352 bytes

Database mounted.

Database opened.

SQL> show parameter pga_aggregate_target; //生效

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 500M