(oralce)pga_aggregate_target與workarea_size_policy相互關係驗證

pga_aggregate_target與workarea_size_policy相互關係驗證

先放上結論：

1. 當pga_aggregate_target設置為非0， 手工設置（workarea _size_policy設置為manual），這時使用的 manual管理模式，而不是auto管理模式

2. 當記憶體為自動管理時（2個條件同時滿足，pga_aggregate_target設置為非0，workarea _size_policy設置為auto），sort_area_size不生效；當記憶體為手動管理時（1個條件，workarea _size_policy設置為manual，不管pga_aggregate_target是什麼值），sort_area_size生效。

問題：

今日，從網上看到一篇總結pga方面參數配置的文章，其中提到<2>點，workarea _size_policy設置為manual，pga_aggregate_target設置為非0，會使用自動記憶體管理。

我對於其中的第二點持懷疑態度，遂做一個測試。

單個session的PGA記憶體使用量限制有比較多的概念，現在統一梳理一下：

1、關於PGA記憶體是自動管理還是手工管理，以及*_area_size（例如 sort_area_size）參數是否生效

workarea_size_policy:該參數定義資料庫PGA記憶體是否自動管理，同時涉及到SQL運行中各種記憶體參數設置是否生效；當PGA記憶體為自動記憶體管理（workarea_size_policy =auto），有關SQL運行的各種記憶體參數設置（例如sort_area_size 排序記憶體大小）均不生效，但至於SQL運行的各種記憶體參數設置生否真的生效，還與pga_aggregate_target值有關，具體如下：

（1）workarea _size_policy設置為auto，pga_aggregate_target設置為0 ，不會使用自動記憶體管理，因為當pga_aggregate_target設置為0時，workarea _size_policy自動設置為manual。

（2）workarea _size_policy設置為manual，pga_aggregate_target設置為非0，會使用自動記憶體管理。因為，如果設置pga_aggregate_targe為非0，workarea _size_policy自動設置為auto，因此，使用自動管理。

-- 經過自己的測試，這個說法是不正確的，當pga_aggregate_target設置為非0， 手工設置（workarea _size_policy設置為manual），這時使用的 manual管理模式，而不是auto管理模式

（3）如果不設置pga_aggregate_targe，那麼系統預設也是使用自動管理，並且設置該參數的值為SGA的20%和10M中較大的值。

總之：當記憶體為自動管理時（2個條件同時滿足，pga_aggregate_target設置為非0，workarea _size_policy設置為auto），sort_area_size不生效；當記憶體為手動管理時（1個條件，workarea _size_policy設置為manual，不管pga_aggregate_target是什麼值），sort_area_size生效。

測試：

主要通過看v$pgastat 的 如下4個指標來觀察到底用了什麼方式的pga管理，到底是 manual還是 auto

total PGA used for auto workareas          
maximum PGA used for auto workareas         
total PGA used for manual workareas         
maximum PGA used for manual workareas       

1.首先禁用AMM

memory_max_target                    big integer 0
memory_target                        big integer 0

2. 系統目前狀態， pga_aggregate_target=224M , workarea_size_policy= AUTO

3. 手動設置 workarea_size_policy= manual

註釋： 在設置為manual之後，測試了一下重新將 pga_aggregate_target設置為另外一個值，這時並不會將workarea_size_policy自動設置為auto，看來官網上說的是從0設置為其他值時，才會將policy自動改為auto

4. 重啟實例，可以看到這4個相關指標都是0

5.創建測試表

SQL> create table x as select * from dba_objects;

Table created.

SQL> insert into x select * from x;

86672 rows created.

SQL> insert into x select * from x;

173344 rows created.

SQL> commit;

Commit complete.

6. 執行一個較大的排序查詢，select * from x order by OBJECT_ID,LAST_DDL_TIME;

7.再次觀察 v$pgastat , 

可以看出  manual的2個相關指標有變化了，而 auto都是0，這裡說明瞭 真實採用的是 manual方式。

8.改回 auto再試試

9.執行sql前

total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                                       0 bytes
total PGA used for manual workareas                                   67584 bytes
maximum PGA used for manual workareas                                274432 bytes

10.執行sql  select * from x order by OBJECT_ID,LAST_DDL_TIME;

11. 再次觀察，發現auto指標變化了， 而 manual毫無變化

total PGA used for auto workareas                                    429056 bytes
maximum PGA used for auto workareas                                 7204864 bytes
total PGA used for manual workareas                                   67584 bytes
maximum PGA used for manual workareas                                274432 bytes

12， 經過幾次反覆的驗證，證明瞭 pga是採用auto模式還是manual模式，是由參數  workarea_size_policy控制的，而跟 pga_aggrate_target沒關係。

結論：

（2）workarea _size_policy設置為manual，pga_aggregate_target設置為非0，會使用自動記憶體管理。
    因為，如果設置pga_aggregate_targe為非0，workarea _size_policy自動設置為auto，因此，使用自動管理。

-- 經過自己的測試，這個說法是不正確的，當pga_aggregate_target設置為非0， 手工設置（workarea _size_policy設置為manual），這時使用的 manual管理模式，而不是auto管理模式

總之：當記憶體為自動管理時（2個條件同時滿足，pga_aggregate_target設置為非0，workarea _size_policy設置為auto），sort_area_size不生效；

         當記憶體為手動管理時（1個條件，workarea _size_policy設置為manual，不管pga_aggregate_target是什麼值），sort_area_size生效。