Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
PGA是特定於server process的一段記憶體，它是Oracle在一個server process啟動時建立的，一個Oracle程式擁有一個PGA記憶體區,一個PGA只能被擁有它的那個服務程式所訪問是非共享的。因此，PGA中的結構是不需要Latch保護的。
所以，在oracle作業系統上ps -ef|grep oracle |grep LOCAL |grep -v grep|awk '{print $2}'看到的process消耗的記憶體其實就是單個程式消耗的PGA+OS級的記憶體

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.
PGA_AGGREGATE_TARGET引數指的是所有process程式消耗的PGA值

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.

The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA.

An analogy for a PGA is a temporary countertop workspace used by a file clerk（PGA就好像是檔案員使用的臨時檯面工作空間）. In this analogy（在這個比喻中）, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

An instance PGA (collection of all PGAs) for an instance that is not configured for shared servers. You can use an initialization parameter to set a target maximum size of the instance PGA . Individual PGAs can grow as needed up to this target size（單個PGAS可以根據需要增長到pga_aggregate_target目標大小，說明只有一個process時，它的PGA也可以增長到最大的pga_aggregate_target值，所以不存在一個PGA最大就是2G這樣的說法，但是隱含引數_pga_max_size確實又是明確指定了一個process最大可用的值，_pga_max_size:Maximum size of the PGA memory for one process）
所以有這樣的操作經驗：晚上如果只跑一個含sort排序的sql作業即一個session，可以臨時調大pga的引數至實體記憶體那麼大，這樣的話，這個session就全部使用PGA記憶體，而不會使用臨時表空間，即這個sql不會有sorts（disk）

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join,bitmap merge, and bitmap create) will be automatically sized.
If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and PGA_AGGREGATE_TARGET is also set to a positive value, the PGA_AGGREGATE_TARGET value acts as the minimum value for the size of the instance PGA.
pga_aggregate_target設定為非0時，自動把WORKAREA_SIZE_POLICY設定為AUTO
設定了MEMORY_TARGET時，則pga_aggregate_target是可以使用的PGA的最小值

dedicated server模式下PGA=SQL Work Areas+Session Memory（也稱UGA，包含Private SQL Area）

SQL Work Areas
A work area is a private allocation of PGA memory used for memory-intensive operations. For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.
SORT_AREA_SIZE：specifies (in bytes) the maximum amount of memory Oracle will use for a sort，預設65535bytes.
After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
_smm_max_size:maximum work area size in auto mode (serial)
每個程式的工作區的大小
_smm_px_max_size:maximum work area size in auto mode (global)
所有並行查詢的SLAVE程式能夠用到的PGA總量。
假如每個程式使用的排序區不能超過1G，並行度20建立索引，總共可以使用的排序區大小為20*1G=20G，但是同時還受引數_SMM_PX_MAX_SIZE的控制，所有的slave佔用的記憶體不能超過_SMM_PX_MAX_SIZE的值

Example 14-1 Query Plan for Table Join
SQL> SELECT *
2 FROM employees e JOIN departments d
3 ON e.department_id=d.department_id
4 ORDER BY last_name;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 106 | 9328 | 7 (29)| 00:00:01 |
|* 2 | HASH JOIN | | 106 | 9328 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |

In Example 14-1, the run-time area tracks the progress of the full table scans. The session performs a hash join in the hash area to match rows from the two tables. The ORDER BY sort occurs in the sort area.
If the amount of data to be processed by the operators does not fit into a work area, then Oracle Database divides the input data into smaller pieces. In this way, the database processes some data pieces in memory while writing the rest to temporary disk storage for processing later.
The database automatically tunes work area sizes when automatic PGA memory management is enabled. You can also manually control and tune the size of a work area.
Generally, larger work areas can significantly improve performance of an operator at the cost of higher memory consumption. Optimally, the size of a work area is sufficient to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases because part of the input data must be cached on disk. In the extreme case, if the size of a work area is too small compared to input data size, then the database must perform multiple passes over the data pieces, dramatically increasing response time.
當work area空間不足時，sort等操作就會使用臨時表空間

SQL Work Areas在PGA記憶體中所佔的比重很大，也只有SQL Work Areas受引數PGA_AGGREGATE_TARGET影響；而SQL Work Areas之外的記憶體，不被pga_aggregate_target和_pga_max_size所限制。所以你經常會看到PGA的大小超過pga_aggregate_target，理論上pga_aggregate_target引數用來控制instance使用PGA記憶體的總量，但如果實在無法保證，它也不會停處理，貌似Oracle 2015年OOW上的一份PPT提到，12C之前版本，PGA最多可用的記憶體可達到PGA_AGGREGATE_TARGET設定值的三倍
pga在兩種情況下，可以超過pga_aggregate_target限制
一：plsql程式定義了大陣列，需要分配Pga記憶體，但是大小不受pga_aggregate_target控制
二：系統繁忙，大量程式連線上來，pga大小會超過pga_aggregate_target（大量的非SQL Work Areas佔用記憶體，且不受這個引數控制）

Session memory也稱User Global Area (UGA)
The UGA is memory associated with a user session.
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state
If a session loads a PL/SQL package into memory, then the UGA contains the package state, which is the set of values stored in all the package variables at a specific time (see "PL/SQL Packages"). The package state changes when a package subprogram changes the variables. By default, the package variables are unique to and persist for the life of the session.

The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA
UGA的位置取決你如何連線oracle。如果是專用伺服器連線，UGA在PGA中建立；如果是共享伺服器連線，UGA則在SGA中建立。

使用者所發出的請求，執行時是在pga中執行。若在pga中的UAG中命中，則無須軟解析，此時稱為軟軟解析。pga中的遊標指database_buffer_cache中的資料行。返回時，是一批批，而非一條條。
軟軟解析：同一條SQL被同一個session執行N次以後，該SQL就會一直存在於PGA的opened cursor區域裡。下次該同一個session執行同一條SQL的時候，就不需要重新生成metadata。

Private SQL Area
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

Do not confuse(迷惑) a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.

A cursor is a name or handle to a specific private SQL area. you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.

A private SQL area is divided into the following areas:
The run-time area
This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.

The persistent area
This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.
1、每個執行sql語句的會話，都有一個private sql area，儲存server process執行SQL所需要的私有資料和控制結構，包括繫結資訊的固定區域和執行時區域。。
2、當多個使用者執行相同的sql語句，此sql語句儲存在一個稱為shared sql area。此share sql area被指定給這些使用者的private sql area
3、共享伺服器模式：private sql area位於SGA的share pool或large pool中
專用伺服器模式：private sql area位於PGA中

set autotrace traceonly stat時顯示有sorts（disk）有值，就表示使用了臨時表空間

Performance impact of memory
Optimal:Input data fits into the work area(SQL工作區完全可以滿足執行sql所需的記憶體)
One-pass:Perform one extra pass over input data(與臨時表空間進行一次I/O)
Multi-pass:Perform several extra passes over input data（與臨時表空間進行多次I/O）
排序在記憶體完成，則PGA使用率是100%
排序還需要使用臨時表空間，則PGA使用率低於100%

多少排序是在PGA中完成的，不到100%說明使用了臨時表空間
select * from v$pgastat where name='cache hit percentage'

評估PGA該設定多少
select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1

查詢正在消耗PGA的SQL
select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE
from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;

查詢正在消耗臨時表空間的SQL
select v$sql.sql_id,v$sql.sql_fulltext,swa.TEMPSEG_SIZE/1024/1024 TEMPSEG_M, swa.* from v$sql_workarea_active swa,v$sql where swa.sql_id=v$sql.sql_id and swa.NUMBER_PASSES>0

查詢一個會話session、process平均消耗多少PGA，檢視下面avg_used_M值
select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;

查詢因PGA不足而使用臨時表空間的最頻繁的10條SQL語句
select * from
(
select OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,
sum(OPTIMAL_EXECUTIONS) optimal_cnt,sum(ONEPASS_EXECUTIONS) as onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) as mpass_cnt,s.sql_text
from V$SQL_WORKAREA swa, v$sql s
where swa.sql_id=s.sql_id
group by OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,sql_text
having sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by sum(ONEPASS_EXECUTIONS) desc
)
where rownum<10

PGA_官方說明和個人理解

相關文章