oracle中pga的含义及作用,PGA_官方说明和个人理解

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