程式全域性區域（Process Global Area），通常稱為程式全域性區域（PGA：Program Global Area），位於伺服器程式的程式私有記憶體。它包含全域性變數、資料伺服器程式的結構和控制資訊。此類資訊的示例是遊標的執行時區域。每次執行遊標時，都會在執行該遊標的伺服器程式的 PGA 記憶體區域中為該遊標建立一個新的執行時區域。

# 版本說明

在 Oracle8i 中，管理員透過小心地調整一些初始化引數來調整 PGA 的大小，比如 SORT_AREA_SIZ、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、CREATE_BITMAP_AREA_SIZE 等。

從 Oracle 9i 開始，提供了一個選項來完全自動化 PGA 記憶體的管理。管理員只需要使用新引入的初始化引數 PGA_AGGREGATE_TARGET，指定一個例項可用的最大 PGA 記憶體量。

直至 9iR2，PGA_AGGREGATE_TARGET 引數控制所有專用伺服器連線的工作區大小，但它對共享伺服器（也就是MTS）連線沒有影響，在這種情況下，*_AREA_SIZE 引數將優先。

在 10g 中，PGA_AGGREGATE_TARGET 控制由專用連線和共享連線分配的工作區。

從 11g 開始，自動記憶體管理（AMM）擴充套件到管理 SGA 和 PGA 記憶體。在 PGA 記憶體的記憶體壓力下，SGA 記憶體將被重新分配，以供程式使用，以滿足工作區的需要。另一方面，如果 PGA 記憶體被分配，記憶體可以被新增到 SGA 中的自動調優元件中，超出原來的 SGA 配置。

注意：對於 AMM，為 PGA_AGGREGATE_TARGET 設定一個顯式值將作為一個最小設定，AMM 將不會縮小到這個值以下。請參閱注：443746.1瞭解更多資訊。

設定 PGA_AGGREGATE_TARGET
==================================

我們設定這個引數時遇到的第一個問題是它的最佳值是多少?

為了確定 PGA_AGGREGATE_TARGET 引數的適當設定，建議遵循以下步驟

1. 根據以下規則對PGA_AGGREGATE_TARGET進行第一次估計
   - OLTP 系統
     PGA_AGGREGATE_TARGET = (實體記憶體* 80%)* 20%
   - DSS 系統
     PGA_AGGREGATE_TARGET =(實體記憶體* 80%)* 50%
2. 調優 PGA_AGGREGATE_TARGET 的第二步是使用可用的 PGA 統計資訊監視效能，看看 PGA_AGGREGATE_TARGET 的大小是否過大。

# UGA & CGA

### UGA (The User Global Area)

在專用服務（Dedicated Server）模式下，一個服務程式與一個使用者程式相關，PGA 就包括了堆空間和 UGA。而UGA（User Global Area 使用者全域性區）由使用者會話資料、遊標狀態和索引區組成。在共享服務（MTS）模式下，UGA 是 Shared Pool 或 Large Pool的一部分（依賴與配置）。

在專用服務模式下，程式和會話是一對一的；而在MTS模式下，程式和會話是一對多的關係。PGA 是服務於程式的，它包含的是程式的資訊；而 UGA 是服務於會話的，它包含的是會話的資訊。因此，MTS 模式下，PGA 和 UGA之間的關係也是一對多的。

UGA中包含了一個會話的資訊，包括：

1. 開啟遊標的永久區和執行區；
2. 包的狀態資訊，特別是包的變數；
3. Java會話的資訊；
4. 啟用的角色；
5. 啟用的跟蹤事件（ALTER SESSION SET EVENT …）；
6. 起作用的 NLS 引數（SELECT * FROM NLS_SESSION_PARAMETERS;）；
7. 所有開啟的db link；
8. 會話對於信任的Oracle的託管訪問標記（mandatory access control (MAC）

UGA 由兩組區組成，固定UGA和可變UGA（或者說UGA堆）。固定 UGA 包含了大概 70 個原子變數、小的資料結構以及指向 UGA 堆的指標。

UGA heap 中的段可以透過表 X$KSMUP 查到（它的結構和 X$KSMSP 相同）。UGA 堆包含了儲存一些固定表（X$表）的永久記憶體（依賴與特定引數的設定，如 OPEN_CURSORS，OPEN_LINKS 和 MAX_ENABLED_ROLES）。除此以外，大部分的 UGA 用於私有 SQL 區。UGA 記憶體的所在依賴於會話的設定。在專用服務模式下，會話和程式是一對一的關係，UGA 位於 PGA 中。固定 UGA 是 PGA 中的一段記憶體段，而 UGA堆是 PGA 的子堆。在 MTS 模式下，固定 UGA 是 shared pool 中的一段記憶體段，而 UGA 堆是 Large Pool 的子堆，如果從 large pool 分配失敗，則從 shared pool 中分配。

MTS 模式下，可以透過 Profile 中的 PRIVATE_SGA 項（透過 dba_profiles 檢視）來控制每個 UGA 佔用的 SGA 的總的大小，但是不建議這樣做。

### CGA (The Call Global Area)

與其他的全域性區不同，CGA（Call Global Area 呼叫全域性區）的存在是瞬間的。它只存在於一個呼叫過程中。對於例項的一些低層次的呼叫需要 CGA，包括：

1. 解析一條 SQL 語句；
2. 執行一條 SQL 語句；
3. 取一條 SELECT 語句的輸出值。

如果語句產生了遞迴呼叫，則需要為每個遞迴呼叫分配一個 CGA。如上所述，遞迴呼叫是在語句解析、最佳化器產生語句查詢計劃、DML 操作時需要查詢或修改資料字典資訊的呼叫。

無論 UGA 存在於 PGA 還是 SGA，CGA 都是 PGA 的 subheap。因為無論那種模式，會話在做呼叫時總需要一個進行進行處理。這一點很重要，特別是在 MTS 模式下時，如果發現一次呼叫很久沒有響應，則可能需要增加 PGA 的大小。

當然，呼叫並不是只透過 CGA 中的資料結構來工作。實際上，呼叫所需要的大部分的重要資料結構都來自於 UGA。例如私有 SQL 取和排序區都存放在 UGA 中，因為呼叫結束後，它們是被保留的。CGA 中只包含了那些呼叫結束後可以被釋放的資料。例如，CGA 中包含了直接 IO 快取、關於遞迴呼叫的資訊、用於表示式評估（產生查詢計劃時）的的堆空間和其他一些臨時資料。

Java 呼叫記憶體也分配在 CGA中。它被分為三部分空間：堆空間、新空間和老空間。在呼叫期間（呼叫長短依賴於使用期長短和大小），在新空間和老空間中的記憶體段不再使用的記憶體段將被垃圾收集器回收。

### realfree heap

Oracle 9.2 以後，有一個新的隱含引數：_use_realfree_heap。當設定這個引數為 true 時，Oracle 會為 CGA、UGA 單獨分配堆，而不從 PGA 中分配。它的預設值為 false，而當設定了 pga_aggregate_target 後，它的值自動被改為 true。

在 Oracle 12c 中，引數\_realfree_heap_pagesize_hint 被修改為 \_realfree_heap_pagesize，透過在 init/spfile 中設定以下引數來調整資料庫內的 realfree 堆頁面大小，並重新啟動資料庫。

```
_use_realfree_heap = TRUE
_realfree_heap_pagesize_hint = 262144
```

# 效能檢視

### V$SYSSTAT 和 V$SESSTAT

以下統計資料將有助於監測和調優：

- workarea memory allocated：PGA 記憶體的總數量專用於工作區域分配的 Kb。
- workarea executions - optimal：具有最優大小的工作區域的累計計數。例如，如果排序不需要溢位到磁碟，則定義最佳大小
- workarea executions - onepass：使用一次透過大小的工作區的累計計數。一次通道通常用於大的工作區域，在那裡不能避免溢位到磁碟。
- workarea executions - multipass：在多個通道中執行的工作區的累計計數。這應該避免，這是系統調優不佳的症狀。

工作區執行是最優的，一次執行或多次執行。

**使用 V$SYSTAT 監控私有記憶體**

下面檢視當前系統中 optimal、onepass、multipass 幾種不同排序下所佔的比率，關於排序應當儘可能的為 optimal，避免過多的 multipass

```sql
SELECT NAME profile,
       cnt,
       decode(total, 0, 0, round(cnt * 100 / total, 4)) percentage
  FROM (SELECT NAME, VALUE cnt, (SUM(VALUE) over()) total
          FROM V$SYSSTAT
         WHERE NAME LIKE 'workarea exec%');

PROFILE                                         CNT PERCENTAGE  
---------------------------------------- ---------- ----------  
workarea executions - optimal               1113054     99.999  
workarea executions - onepass                    11       .001  
workarea executions - multipass                   0          0    
```

下面檢視記憶體排序佔總排序的比率，此值越高越好。如此值較小，應結合 v$pga_target_advice 考慮調整 PGA

```sql
SELECT round(m.value / (m.value + d.value), 4) * 100 || '%' memory_disk_sort_ratio  
  FROM (SELECT value FROM v$sysstat WHERE NAME = 'sorts (memory)') m,  
       (SELECT value FROM v$sysstat WHERE NAME = 'sorts (disk)') d;  
    
MEMORY_DISK_SORT_RATIO  
-----------------------------------------  
100%
```

### V$PGASTAT

該檢視提供了關於 PGA 記憶體使用情況和自動 PGA 記憶體管理器的例項級統計資訊。例如：

```sql
SELECT * FROM V$PGASTAT;

NAME                                               VALUE 
-------------------------------------------------- ------------------
aggregate PGA target parameter                     524288000 bytes
aggregate PGA auto target                          463435776 bytes
global memory bound                                25600 bytes
total PGA inuse                                    9353216 bytes
total PGA allocated                                73516032 bytes
maximum PGA allocated                              698371072 bytes
total PGA used for auto workareas                  0 bytes
maximum PGA used for auto workareas                560744448 bytes
total PGA used for manual workareas                0 bytes
maximum PGA used for manual workareas              0 bytes
over allocation count                              0 bytes
total bytes processed                              4.0072E+10 bytes
total extra bytes read/written                     3.1517E+10 bytes
cache hit percentage                               55.97 percent
```

- aggregate PGA target parameter：當前PGA_AGGREGATE_TARGET的值

- aggregate PGA auto target：剩餘的能被工作區（workarea）使用的記憶體（AUTO模式），aggregate PGA auto target 不應該比 PGA_AGGREGATE_TARGET 的值小。您必須確保為在自動模式下執行的工作區域留有足夠的 PGA 記憶體。

- global memory bound：單條 SQL 序列操作的最高 PGA 記憶體使用量（AUTO模式）

- total PGA inuse：正被耗用的PGA（包括 workarea pl/sql 等所有佔用的 PGA）
  這給出了使用中的 PGA 記憶體總數。這個的細節 value 可以在 v$process 檢視的 PGA_USED_MEM 列中找到。

- total PGA allocated：當前例項已分配的 PGA 記憶體總量

- maximum PGA allocated：PGA 曾經擴張到的最大值

- total freeable PGA memory：PGA 的可釋放的大小

- process count：當前 process

- max processes count：最大時候的 process

- PGA memory freed back to OS：PGA 返回給作業系統的累計值

- total PGA used for auto workareas：PGA 分配給 auto workareas 的總大小
  用於自動工作區的總 PGA：這提供了系統使用的實際可調 PGA 記憶體。用於自動工作區的最大 PGA 給出了自例項啟動以來之前的統計所達到的最大值。

- maximum PGA used for auto workareas：PGA 可以分配給 auto workareas 大小的峰值

- total PGA used for manual workareas：PGA 分配給 manual workareas 的總大小

- maximum PGA used for manual workareas：PGA 可以分配給 manual workareas 大小的峰值

- over allocation count：例項啟動後，發生的分配次數，如果這個值大於 0，就要考慮增加 PGA 的值

- bytes processed：PGA 使用的位元組

- cache hit percentage：命中率
  $$
  PGA 快取命中率 = (處理的總位元組數 * 100) / (處理的總位元組數 + 讀取/寫入的總額外位元組數)
  $$

### V$SQL_WORKAREA_HISTOGRAM

該檢視顯示了自例項啟動以來，使用最佳記憶體大小、單次記憶體大小和多次記憶體大小執行的工作區域數量。該檢視中的統計資訊被細分為多個 bucket，這些 bucket 由工作區域的最佳記憶體需求定義。每個 bucket 由列LOW_OPTIMAL_SIZE 和 HIGH_OPTIMAL_SIZE 的值指定的最優記憶體需求範圍標識。

**例一**

以下查詢顯示所有非空儲存桶的統計資訊。

```sql
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       optimal_executions, onepass_executions, multipasses_executions
FROM   v$sql_workarea_histogram
WHERE  total_executions != 0;

LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
8      16      156255             0                   0
16     32      150                0                   0
32     64      89                 0                   0
64     128     13                 0                   0
128    256     60                 0                   0
256    512     8                  0                   0
512    1024    657                0                   0
1024   2048    551                16                  0
2048   4096    538                26                  0
4096   8192    243                28                  0
8192   16384   137                35                  0
16384  32768   45                 107                 0
32768  65536   0                  153                 0
65536  131072  0                  73                  0
131072 262144  0                  44                  0
262144 524288  0                  22                  0
```

- OPTIMAL_EXECUTIONS：是不需要透過臨時表空間交換的操作的次數。 
- ONEPASS_EXECUTIONS：是需要進行一次臨時表空間交換的操作次數。 
- MULTIPASSES_EXECUTIONS：是需要多次臨時表空間交換的操作次數。

原則上，如果 100％ 的操作都是記憶體操作，是最佳的選擇。如果記憶體不足，某些大型操作是 ONEPASS 的，也是可以接受的。 出現 MULTIPASSES 的操作會引起效能的急劇下降，此時應考慮調整 PGA target。

**例二**

您還可以使用 V$SQL_WORKAREA_HISTOGRAM 來查詢自啟動以來 OPTIMAL、ONEPASS 或 MULTIPASSES 的工作時間的百分比。

```sql
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
  FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
               sum(OPTIMAL_EXECUTIONS) optimal_count,
               sum(ONEPASS_EXECUTIONS) onepass_count,
               sum(MULTIPASSES_EXECUTIONS) multipass_count
        FROM   v$sql_workarea_histogram
        WHERE  low_optimal_size > 64*1024);   ---- for 64 K optimal size 
```

### V$SQL_WORKAREA_ACTIVE

該檢視可用於顯示例項中活動(或執行)的工作區域。較小的活動排序(小於64 KB)將從檢視中排除。使用此檢視可以精確地監視所有活動工作區的大小，並確定這些活動工作區是否溢位到臨時段。

**例如**

```sql
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

SID OPERATION         ESIZE     MEM       MAX MEM    PASS TSIZE
--- ----------------- --------- --------- --------- ----- -------
8   GROUP BY (SORT)   315       280       904         0
8   HASH-JOIN         2995      2377      2430        1   20000
9   GROUP BY (SORT)   34300     22688     22688       0
11  HASH-JOIN         18044     54482     54482       0
12  HASH-JOIN         18044     11406     21406       1   120000
```

### V$PGA_TARGET_ADVICE

```
SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
  FROM v$pga_target_advice;
```

- pga_target_for_estimate：預測的 pga_aggregate_target 引數的值。

- pga_target_factor：預測的 pga 的值與當前 pga 的值的比值。

- estd_pga_cache_hit_percentage：當 pga_aggregate_target 設定成某個 pga_target_for_estimate 的值時，估計的 cache hit 率。此列的值等於：bytes_processed / (bytes_processed + estd_extra_bytes_rw).

- estd_overalloc_count：如果 pga_aggregate_target 的值設定成 pga_target_for_estimate 的值。估計會出現的超過分配的次數（over-allocations）。如果該值不是 0，表明 pga_target_for_estimate 還不夠大，因此不應該把pga_target_for_estimate 設定成 pga_aggregate_target 的值，否則 oracle 將不會信任該值。超過分配說的是，實際分配給 PGA 的記憶體大小，超過 pga_aggregate_target 設定的值。

### 有關工作區域監控的其他查詢

##### 查詢需要最多快取記憶體的前十個工作區：

```sql
select * 
from
    (select workarea_address, operation_type, policy, estimated_optimal_size
       from v$sql_workarea
     order by estimated_optimal_size DESC)
where ROWNUM <=10;
```

##### 查詢使用最大記憶體的工作區百分比

```sql
select operation_type, total_executions * 100  / optimal_executions "%cache"
From v$sql_workarea
Where policy='AUTO'
And optimal_executions > 0
Order By operation_type;
```

##### 查詢系統中當前分配的前十大工作區域

```sql
select c.sql_text, w.operation_type, top_ten.wasize
From (Select *
      From (Select workarea_address, actual_mem_used wasize
            from v$sql_workarea_active
            Order by actual_mem_used)
      Where ROWNUM <=10) top_ten,
      v$sql_workarea w,
      v$sql c
Where w.workarea_address=top_ten.workarea_address
  And c.address=w.address
  And c.child_number = w.child_number
  And c.hash_value=w.hash_value;
```

##### 查詢分配過多和不足的記憶體百分比：

```sql
select  total_used,
        under*100/(total_used+1) percent_under_use,
        over*100/(total_used+1)   percent_over_used
From
        (Select
                sum(case when expected_size > actual_mem_used 
                                       then actual_mem_used else 0 end) under,
                sum(case when expected_size < actual_mem_used 
                                       then actual_mem_used else 0 end) over,
                sum(actual_mem_used) total_used
        From v$sql_workarea_active
        Where policy='AUTO') usage; 
```

Oracle：PGA 簡介

相關文章