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

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.

[@more@]

PGA學習筆記

相關文章