PGA学习笔记

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总大小

selectname,value/1024/1024MBfromv$parameterwherename='pga_aggregate_target';

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

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

selectsql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,

last_tempseg_sizefromv$sql l,v$sql_workarea a

wherel.hash_value=a.hash_value

andsql_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 执行次数及比例

SELECToptimal_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

(SELECTdecode(sum(total_executions),0,1,sum(total_executions)) total,

sum(OPTIMAL_EXECUTIONS) optimal_count,

sum(ONEPASS_EXECUTIONS) onepass_count,

sum(MULTIPASSES_EXECUTIONS) multipass_count

FROMv$sql_workarea_histogram

WHERElow_optimal_size >64*1024);---- for 64 K optimal size

Metalink ID: 223730.1

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

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

How to Read PGA Memory AdvisorySection 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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12180666/viewspace-1042355/，如需转载，请注明出处，否则将追究法律责任。