PGA 記憶體的管理 (zt)

一、ＰＧＡ的概念
服務程式啟動的時候，被建立的非共有的記憶體領域。只能被建立的程式所訪問。
專用模式：一個Session一個服務程式
共有模式：多個Session一個服務程式，服務程式與Dispatcher無關。

二、PGA包含的內容
PRIVATE　SQL領域：SQL的作業空間，用於儲存變數資訊，結果資訊，中間結果。
SESSION Memory:用於儲存於SESSION相關的一些資訊。如SESSION級別的引數，變數。
PRIVATE　SQL領域裡，包含用於進行以下處理的空間
排序
Hash結合
Bitmap Merge
Bitmap 做成


三、PGA記憶體調整以後有什麼好處
排序，Hash結合等處理也在PGA中進行，如果PGA的記憶體不夠的話，就會的任務進行拆分
然後把不能放到記憶體的部分放到磁碟上去，這樣的話就會處理速度大大的降低。而這種
時候適當的調整PGA的大小，可能大大提高系統的效能。


四、PGA記憶體所需大小的估算方法
按照下面的優先順序。
1、如果是系統的更新，可以根據原有系統的效能。例如，想在原有系統上效能提高50%
那麼，記憶體也在原有系統上增加50%。
2、參照類似的系統。如果在使用環境，使用者數量，內容上，效能上都差不多的系統。也可以參考
3、按照以下公司大概估算
同時使用系統的使用者數 × 10M
4、PGA和SGA的總量可以按照以下公式
A: 更新處理需要的記憶體
commit數/秒 × 每一個事務的資料量 × 緩衝的儲存時間
B: 檢索結果需要的記憶體
檢索事務數/秒×每一件的資料量 × 同時使用的使用者數
C:總的記憶體 A + B

五、PGA現在使用大小的確認方法
PGA的使用情況透過對一下幾個動態效能表的查詢進行管理
V$PROCESS、
1、V$PROCESS能夠檢視每一個程式（包括系統的後臺程式）分配到的，使用的，可以Free的記憶體
PGA_USED_MEM 現在使用的
PGA_ALLOC_MEM 分配到的（包含未釋放到OS的）
PGA_FREEABLE_MEM 可以釋放的
PGA_MAX_MEM 曾經分配的最大記憶體
USERNAME 作業系統程式的使用者，不是客戶SESSION的使用者

2、V$PROCESS_MEMORY 每一個程式中，SQL,PL/SQL，OTHER等每一個部分分配到的記憶體
ALLOCATED
USED
MAX_ALLOCATED

3、v$bgprocess 後臺程式的資訊。paddr有值得程式才處於啟用狀態

4、v$session SESSION的資訊，專用模式是透過paddr可以找到process

5、v$shared_server v$dispatcher

系統使用中的PGA總數（M）

select trunc(sum( pga_alloc_mem )/(1024*1024),2) from v$process

六、PGA關聯的引數設定
從9i開始，PGA的大小可以由資料庫自動管理，不用再手工的去設定*_AREA_SIZE引數。
一、自動管理
pga_aggregate_target 不是PGA的最大記憶體，PGA使用的記憶體有可能超過這個大小，它是sort , hash join 等使用的作業區的大小
workarea_size_policy AUTO 使用自動管理 MANUAL 手工管理

下面的SQL驗證了PGA的總記憶體數量可以超過 pga_aggregate_target 所設定的數量
SQL> show parameter pga_aggre
NAME TYPE VALUE
------------------------------------ ----------- ------------
pga_aggregate_target big integer 91M
SQL> select trunc(sum( pga_alloc_mem )/(1024*1024),2) from v$process;
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
50.14
SQL> alter system set pga_aggregate_target= 50M scope=BOTH ;
SQL> show parameter pga_a
NAME TYPE VALUE
------------------------------------ ----------- ----------
pga_aggregate_target big integer 50M
SQL> show parameter work_area_
SQL> show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ----------
fileio_network_adapters string
workarea_size_policy string AUTO
SQL> select trunc(sum( pga_alloc_mem )/(1024*1024),2) from v$process;
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
43.61
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
45.99
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
46.83
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
48.14
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
48.14
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
49.33

TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
50.52
SQL> /
TRUNC(SUM(PGA_ALLOC_MEM)/(1024*1024),2)
---------------------------------------
51.11



七、PGA大小的調整

在檢查PGA的問題是，下面的幾個效能表將會非常有用
v$pgastat

aggregate PGA target parameter 初始化引數pga_aggregate_target的值
aggregate PGA auto target 可以實際使用的PGA記憶體，太小時需要調整
global memory bound 能夠使用的SQL作業區域的最大值
與_smm_max_size 和_smm_min_size 有關
total PGA used for auto workareas 使用中的SQL作業區的大小。SQL執行完成，變為0

PGA大小調整的關鍵是SQL作業區的使用方式
optimal : 所有的處理都在記憶體中執行
onepass : 有一部分處理不在記憶體中
multipass : 很多的處理不在記憶體中
效能最佳化的目標
optimal 的PERCENTAGE >= 90%
multipass 的 PERCENTAGE = 0%
用下面的SQL來檢查每個部分的百分比
SELECT name profile,
cnt,
decode( total, 0, 0, round( cnt * 100 / total)) percentage
FROM ( SELECT name, value cnt, ( SUM( value ) OVER()) total
FROM v$sysstat
WHERE name LIKE 'workarea exec%');

PROFILE CNT PERCENTAGE
-------------------------------------------------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0



八、PGA每一個服務程式 serer process的記憶體分配
每一個server process能夠分配到的記憶體（不包括session等，主要是SQL的作業區域）的最大值和最小值由下面的隱藏引數控制，下面使default值的設定
_smm_min_size: max(pga_aggregate_target 的 0.1%(1MB 以內), 128KB)
_smm_max_size: min(pga_aggregate_target 的 5%, _pga_max_size/2)
注意：並不是server process一建立，這些記憶體就會被使用，記憶體只有再必須的時候才會被分配。這個的最大，最小不是對一個server process說的，而是對於一個，operation type ,例如
sort , hash join . 所以，一個server process 有可能可以分配到兩個。當_smm_max_size也不能完成處理的時候，系統可能會自動暫時的擴張pga_aggrate_target的大小，
-----------------------------------------------------------------
隱藏引數的確認方法：
用SYS使用者執行以下SQL
select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value"
from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_smm_min_size%';

隱藏引數的修改方法：
SQL> create pfile='d:huddy.ora' from spfile ;
SQL> shutdown immediate ;
SQL> create spfile from pfile='d:huddy.ora';
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified

SQL> select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value"
2 from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_smm_min_size%';

_smm_min_size
minimum work area size in auto mode
8096
-----------------------------------------------------------------

當pga_aggrate_target的記憶體用完的時候，系統並不會因此而不讓新的server process產生
而是會自動的去取得超過pga_aggrate_target的記憶體來用。