PGA学习笔记:

PGA作用:

a) 会话数据排序(sort, hash_join, group-by, bitmap merge and bitmap index create)(不够就去temp space取)

b) 会话权限审核，可以快速读取权限

c) 保存绑定变量

d) 游标区

PGA关键参数:

Pga_aggregate_target:所有session一共使用的最大PGA上限。

Workarea_size_policy: 用于开关PGA内存自动管理功能。(默认auto, manual时内存分配会用是sort_area_size参数)

_pga_max_size(隐藏参数):每个session只能用到一半_pga_max_size值大小的内存

Optima:所有操作都在内存中进行

Onepass:使用最小写磁盘操作，大部分在内存中进行

Multipass:当workarea太小的话将会发生大量磁盘操作，性能急剧下降

AWR>Advisory Statistics> PGA Memory Advisory

PGA常用命令:

----查看PGA 总大小

select name, value/1024/1024 MB from v$parameter where name ='pga_aggregate_target';

alter system set pga_aggregate_target=128M;(effect immediately)

---查看SQL语句排序消耗PGA的大小

select sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,

last_tempseg_size from v$sql l,v$sql_workarea a

where l.hash_value=a.hash_value

and sql_text='select a.serial_no,a.bankid,b.accountbalance from money_io_list a,money_io_list_sub b where a.serial_no=b.serial_no';

---查看optimal 执行次数及比例

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;

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

Metalink ID: 223730.1

The automatic SQL execution memory management feature is enabled by setting the 

parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of 

PGA_AGGREGATE_TARGET in the initialization file. These two parameters can also be set dynamically using the ALTER SYSTEM command.

The size of a work area can be controlled and tuned. Generally, bigger work areas 

can significantly improve the performance of a particular operator at the cost of

higher memory consumption. Ideally, the size of a work area is big enough that it

can accommodate the input data and auxiliary memory structures allocated by its

associated SQL operator. This is known as the optimal size of a work area (e.g.

a memory sort). When the size of the work area is smaller than optimal 

(e.g. a disk sort), the response time increases, because an extra pass is performed 

over part of the input data. This is known as the one-pass size of the work area. 

Under the one-pass threshold, when the size of a work area is far too small compared 

to the input data size, multiple passes over the input data are needed. This could 

dramatically increase the response time of the operator. This is known as the multi-pass 

size of the work area.

How To Tune PGA_AGGREGATE_TARGET 

----------------------------------

The first question we will have when we set this parameter is what is the best 

value for it? 

To determine the appropriate setting for PGA_AGGREGATE_TARGET  parameter we

recommend to follow the following steps 

1- Make a first estimate for PGA_AGGREGATE_TARGET  based on the following rule 

- For OLTP systems 

   PGA_AGGREGATE_TARGET  = ( * 80%) * 20%

- For DSS systems 

   PGA_AGGREGATE_TARGET  = ( * 80%) * 50%

So for example, if we have an Oracle instance configured on system with 16G of 

Physical memory, then the suggested PGA_AGGREGATE_TARGET parameter value we 

should start with incase we have OLTP system is (16 G * 80%)*20% ~= 2.5G and 

incase we have DSS system is (16 G * 80%)* 50% ~= 6.5 G. 

In the above equation, we assume that 20% of the memory will be used by the OS, 

and in OLTP system 20% of the remaining memory will be used for 

PGA_AGGREGATE_TARGET and the remaining memory is going for Oracle SGA 

memory and non-oracle processes memory. So make sure that you have 

enough memory for your SGA and also for non-oracle processes 

This view shows the number of work areas executed with optimal memory size, one-

pass memory size, and multi-pass memory size since instance start-up.

----optimal, one-pass, multi-pass memory size使用情况

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;

You can also use V$SQL_WORKAREA_HISTOGRAM to find the percentage of times work areas were executed in optimal, one-pass, or multi-pass mode since start-up.

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 

This view can be used to display the work areas that are active (or executing) 

in the instance. Small active sorts (under 64 KB) are excluded from the view. 

Use this view to precisely monitor the size of all active work areas and to 

determine if these active work areas spill to a temporary segment.

Example :

----当前active的SQL memory和temp size的使用情况

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

This output shows that session 12 (column SID) is running a hash-join having its

work area running in one-pass mode (PASS column). This work area is currently

using 11406 KB of memory (MEM column) and has used, in the past, up to 21406 KB

of PGA memory (MAX MEM column). It has also spilled to a temporary segment of

size 120000 KB. Finally, the column ESIZE indicates the maximum amount of memory

that the PGA memory manager expects this hash-join to use. This maximum is dynamically

computed by the PGA memory manager according to workload.

When a work area is deallocated—that is, when the execution of its associated SQL

operator is complete—the work area is automatically removed from the 

V$SQL_WORKAREA_ACTIVE view.

V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and 

over allocation count in V$PGASTAT will be impacted if you change the value of 

the initialization parameter PGA_AGGREGATE_TARGET.

The following select statement can be used to find this information 

---查看cache hit percentage

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

       ESTD_OVERALLOC_COUNT

FROM   v$pga_target_advice;

The output of this query might look like the following:

TARGET_MB  CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

---------- -------------- --------------------

63         23             367

125        24             30

250        30             3

375        39             0

500        58             0

600        59             0

700        59             0

800        60             0

900        60             0

1000       61             0

1500       67             0

2000       76             0

3000       83             0

4000       85             0

From the above results we should set the PGA_AGGREGATE_TARGET parameter to a 

value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value

we can set is 375 ( where ESTD_OVERALLOC_COUNT is 0)

After eliminating over-allocations, the goal is to maximize the PGA cache hit

percentage, based on your response-time requirement and memory constraints.

V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how the statistics displayed

by the performance view V$SQL_WORKAREA_HISTOGRAM will be impacted if you

change the value of the initialization parameter PGA_AGGREGATE_TARGET. You can

use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view detailed

information on the predicted number of optimal, one-pass and multi-pass work

area executions for the set of PGA_AGGREGATE_TARGET values you use for the

prediction.

Version specific notes:

Until 9iR2, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for 

all dedicated server connections, but it has no effect on shared servers (aka 

MTS) connections and the *_AREA_SIZE parameters will take precedence in this 

case. 

In 10g, PGA_AGGREGATE_TARGET controls workareas allocated by both dedicated and 

shared connections. 

As of 11g, Automatic Memory Management (AMM) expands to managing both SGA and 

PGA memory.   Under memory pressure for PGA memory, SGA memory will be 

re-allocated for use by a process to accommodate workarea needs.  On the 

flip-side, if PGA memory is under allocated, memory can be added to the 

auto-tuned components in the SGA beyond the original SGA configuration.

NOTE:   With AMM, setting an explicit value for PGA_AGGREGATE_TARGET will

act as a minimum setting that AMM will not shrink below.  See Note:443746.1

for more information.

How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]

AWR>Advisory Statistics> PGA Memory Advisory

Preferred and easiest way of monitoring and setting pga_aggregate_target parameter (PGA) is section 'PGA Memory Advisory' in the AWR and Statspack reports.

PGA Memory Advisory for DB: AAA Instance: aaa End Snap: 20555

PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
16 0.1 13,406,708.5 1,150,524.0 92.0 98,500
32 0.3 13,406,708.5 1,149,545.5 92.0 98,500
64 0.5 13,406,708.5 1,149,545.5 92.0 98,500
96 0.8 13,406,708.5 1,149,545.5 92.0 98,500
128 1.0 13,406,708.5 370,864.9 97.0 98,343
154 1.2 13,406,708.5 358,442.9 97.0 73,884
179 1.4 13,406,708.5 345,671.0 97.0 51,419
205 1.6 13,406,708.5 325,909.7 98.0 34,441
230 1.8 13,406,708.5 208,594.9 98.0 8,993
256 2.0 13,406,708.5 158,403.9 99.0 4,272
384 3.0 13,406,708.5 105,314.7 99.0 826
512 4.0 13,406,708.5 99,935.0 99.0 176
768 6.0 13,406,708.5 98,714.6 99.0 22
1,024 8.0 13,406,708.5 98,433.7 99.0 0
------------------------------------------------------------------------

In this section, you first find the row where field 'Size Factr' is 1.00. The field 'PGA Target Est(MB)' of this row will show your current PGA setting - figure 128 in the above example. Other fields (columns) you will be interested in are: 'Estd Extra W/A MB Read/ Written to Disk ' and 'Estd PGA Overalloc Count'.

When you go down or up the advisory section from the row with 'Size Factr' = 1.00, you get estimations for Disk usage - column 'Estd Extra W/A MB Read/ Written to Disk ' - for bigger or smaller settings of pga_aggregate_target. The less Disk usage figure in this column, usually the better.

Your first goal is to have such a setting of pga_aggregate_target, that number in the column 'Estd Extra W/A MB Read/ Written to Disk ' does not substantially reduce any more, see figure 99,935.0 in the example AWR report.
In other words, further increases of pga_aggregate_target won't give any more benefit. Column 'Size Factr' = 4.0 shows that current PGA size should be increased by 4 times (to 512MB) to reach this goal.

Column 'Estd PGA Overalloc Count' shows estimations of how many times database would need to request from OS more PGA memory than the amount shown in the 'PGA Target Est(MB)' field of the respective row. Ideally this field should be 0, and that is your equally important second goal. In the given example this goal is achieved with pga_aggregate_target = 1,024MB.

In many cases 'Estd PGA Overalloc Count' figures reach 0 before the number in 'Estd Extra W/A MB Read/ Written to Disk ' stabilizes, as in the following example:

PGA Memory Advisory for DB: BBB Instance: bbb End Snap: 15315

Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
179 0.1 2,741,061.8 1,671,995.0 62.0 42,214
359 0.3 2,741,061.8 1,625,275.4 63.0 39,903
717 0.5 2,741,061.8 1,148,570.8 70.0 22,967
1,076 0.8 2,741,061.8 455,187.2 86.0 2,433
1,434 1.0 2,741,061.8 302,362.3 90.0 2
1,721 1.2 2,741,061.8 294,467.8 90.0 0
2,008 1.4 2,741,061.8 273,153.5 91.0 0
2,294 1.6 2,741,061.8 273,075.2 91.0 0
2,581 1.8 2,741,061.8 272,980.1 91.0 0
2,868 2.0 2,741,061.8 272,980.1 91.0 0
4,302 3.0 2,741,061.8 272,980.1 91.0 0
5,736 4.0 2,741,061.8 272,980.1 91.0 0
8,604 6.0 2,741,061.8 272,980.1 91.0 0
11,472 8.0 2,741,061.8 272,980.1 91.0 0
------------------------------------------------------------------------

Question whether increase from the current actual size is possible for a given database, should be always investigated. The answer depends on how much of total memory (SGA+PGA) can be allocated for this database on this box, i.e. take into account memory needs of other databases, software and OS residing on the box.