【原創】設定memory_target引數，通過 v$memory_target_advice分析資料庫最佳記憶體大小

Memory_target：1.是oracle 11g中的一個記憶體調整引數，11g對自動化管理記憶體方面又繼續加強了，原來10g中可以對SGA進行自動管理與分配，11g即可以自動管理SGA，又可以自動管理PGA，對這兩部分進行綜合管理，自動調整所有記憶體區的大小。11g中預設為0

現在把這幾個引數語法列舉一下，這是靜態引數需要重啟資料庫生效

alter system set memory_max_target= 1000m scope=spfile;
alter system set  memory_target= 1000m scope=spfile;
alter system set sga_max_size=600m scope=spfile;
alter system set pga_aggregate_target=400m scope=spfile;

2. memory_max_target 是設定 Oracle 能佔實體記憶體多大空間，一個是 Oracle SGA 區最大能佔多大記憶體空間+PGA區多大空間，memory_max_target是memory_target上限值，如果只設定了memory_max_target沒有設定memory_target，則Oracle認為memory_target=0不使用記憶體自動管理。

3.如果只設定memory_target，沒有設定memory_max_target，則Oracle自動將memory_max_target設定為memory_target。

4.如果同時設定這兩個值，則memory_target的上限值為memory_max_target。

這是我的資料庫上的引數值

LEO1@LEO1> show parameter memory_max_target

NAME                                 TYPE        VALUE

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

memory_max_target                    big integer 652M

LEO1@LEO1> show parameter memory_target

NAME                                 TYPE        VALUE

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

memory_target                        big integer 652M

5. 10g 的 sga_max_size 是動態分配 Shared Pool Size,database buffer cache,large pool,java pool，redo log buffer 大小的，根據 Oracle 執行狀態來重新分配 SGA 各記憶體區大小。 PGA 在 10g 中需要單獨設定（即手工管理）。

實驗

下面我們通過以下的幾個命令來讓大家清楚memory_target 的設定與PGA和SGA的關係

（1）memory_target設定為非0值

Memory_Target =SGA_TARGET+PGA_AGGREGATE_TARGET  ，大小等於memory_max_size 一致。

sga_target和pga_aggregate_target都設定了大小，則這兩個引數將做為最小起始值

sga_target  設定大小， pga_aggregate_target  沒有設定大小  

           那麼 pga_aggregate_target 初始化值 =memory_target-sga_target

sga_target  沒有設定大小， pga_aggregate_target  設定大小  

           那麼 sga_target 初始化值 =memory_target-pga_aggregate_target

sga_target  和 pga_aggregate_target都沒有設定大小 Oracle 11g 將根據資料庫執行狀態自動分配大小。 但在資料庫啟動時會有一個固定比例來分配：

         sga_target =memory_target *60%     pga_aggregate_target=memory_target *40%

（2）memory_target沒有設定或等於0（11g中預設為0）

11g中預設為0則初始狀態下取消了 memory_target 的作用，完全和10g在記憶體管理上一致，完全向下相容。

   （也有三種情況來對 SGA 和 PGA 的大小進行分配）

SGA_TARGET 設定值，則自動調節 SGA 中的 shared pool,buffer cache,redo log buffer,java pool,larger pool記憶體區，PGA 則依賴 pga_aggregate_target 的大小單獨設定。 sga和 pga不能自動增長和自動縮小。

SGA_target 和PGA_AGGREGATE_TARGET  都沒有設定，SGA 中的各記憶體區大小都要明確設定，不能自動調整各記憶體區大小。PGA 不能自動增長和收縮。

memory_max_target設定而memory_target =0  這種情況和10g 一樣不使用記憶體自動管理

LEO1@LEO1> show parameter target

NAME                           TYPE        VALUE

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

archive_lag_target                  integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                big integer  652M

memory_target                    big integer  652M

parallel_servers_target              integer     8

pga_aggregate_target               big integer  0

sga_target                        big integer  0

現在我們看到sga_target和pga_aggregate_target的值都是0，由oracle自動調整大小，memory_target和memory_max_target的大小是652M

LEO1@LEO1> select * from v$memory_target_advice;    分析資料庫最佳記憶體大小

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION

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

        489                .75         5522              1.0002          0

        652                  1         5521                   1         0

        815               1.25         5518               .9994          0

        978                1.5         5517               .9993          0

       1141               1.75         5517               .9992          0

       1304                  2         5517               .9992          0

MEMORY_SIZE：oracle建議的記憶體大小

MEMORY_SIZE_FACTOR：記憶體基線因子，0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2

ESTD_DB_TIME：For current memory size (MEMORY_SIZE_FACTOR = 1), the amount of database time required to complete the current workload. For a proposed memory size, the estimated amount of database time that would be required if the MEMORY_TARGET parameter were changed to the proposed size.（官方文件解釋）

當記憶體基線因子為1時，完成當前資料庫工作量所需要的所有資料庫時間（即所有使用者消耗的資料庫時間），這是一個建議值，它會根據memory_target引數的改變而改變

ESTD_DB_TIME_FACTOR：For a proposed memory size, ratio of estimated database time to current database time（官方文件解釋）

消耗資料庫時間的比例因子

VERSION：Version number of this recommendation (this snapshot of the V$MEMORY_TARGET_ADVICE view

v$memory_target_advice這個檢視快照的版本號

1.當memory_target=489M時，ESTD_DB_TIME=5522

2.當memory_target=652M時，ESTD_DB_TIME=5521    當前值

3.當memory_target=815M時，ESTD_DB_TIME=5518

4.當memory_target=978M時，ESTD_DB_TIME=5517

5.當memory_target=1141M時，ESTD_DB_TIME=5517

6.當memory_target=1304M時，ESTD_DB_TIME=5517

從如上的系統資源消耗情況來看，memory_target=489M是之前652M的四分之三，但ESTD_DB_TIME才增加了1價效比非常高，我們可以把memory_target=修改成489M，節約我們的記憶體資源

LEO1@LEO1> alter system set memory_max_target=489m scope=spfile;

System altered.

LEO1@LEO1> alter system set memory_target=489m scope=spfile;

System altered.

LEO1@LEO1> show parameter target

NAME                           TYPE        VALUE

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

archive_lag_target                  integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                big integer  652M

memory_target                    big integer  652M

parallel_servers_target              integer     8

pga_aggregate_target               big integer  0

sga_target                        big integer  0

現在只是修改了spfile引數檔案的內容，需要重啟資料庫才生效

LEO1@LEO1> shutdown immediate   

ORA-01031: insufficient privileges       許可權不足

LEO1@LEO1> conn / as sysdba         切換sys使用者

Connected.

SYS@LEO1> shutdown immediate       關閉例項

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@LEO1> startup

ORACLE instance started.

Total System Global Area  513585152 bytes

Fixed Size                  2214856 bytes

Variable Size             314573880 bytes

Database Buffers          188743680 bytes

Redo Buffers                8052736 bytes

Database mounted.

Database opened.

SYS@LEO1> show parameter target

NAME                                 TYPE        VALUE

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

archive_lag_target                  integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                big integer  492M      oracle做了一點點修正

memory_target                    big integer  492M      

parallel_servers_target              integer     8

pga_aggregate_target               big integer  0

sga_target                        big integer  0

小結：我們可以根據v$memory_target_advice檢視來合理的調整memory_target的記憶體值，提高資源利用率。