我的PGA我作主----搞清楚什么是真正的PGA

72 阅读 0 评论 48 点赞

我是靠谱客的博主动听铅笔，最近开发中收集的这篇文章主要介绍我的PGA我作主----搞清楚什么是真正的PGA，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

http://www.itpub.net/thread-1789987-1-1.html

1、PGA是什么，包括哪些部分，PGA的作用?

　　PGA是特定于进程的一段内存，是一个操作系统进程或线程专用的内存，不允许系统中的其它进程或线程访问，独立于SGA是server process的私有空间。
　　PGA包括非工作区和工作区。工作区在PGA内存中所占的比重很大，但PGＡ内存中并非只有工作区。PGA内存分配涉及很多方面，其中只有工作区在instance的控制之下。非工作区内存的大小不受任何参数的控制（连接进程（server process）所占的PGA内存,后台进程（background process）所占的PGA内存）
　　PGA的作用：
　　①私有SQL区域:存储server process执行SQL所需要的私有数据和控制结构，包括固定区域和运行时区域。
　　②会话空间:存放logon信息等会话相关的控制信息
　　③SQL工作区:排序操作(order by/group by/distinct/union等)，多表hash连接，位图连接，创建位图
　
2、pga_aggregate_target参数如何设置？
　　“理论上”　pga_aggregate_target参数用来控制instance使用PGA内存的总量，instance尽力保持在pga_aggregate_target限制以内，但如果实在无法保证，它也不会停处理，只是要求超过这个阈值。实际上这个pga_aggregate_target参数是控制工作区的排序操作(order by/group by/distinct/union等)，多表hash连接，位图连接，创建位图。pga_aggregate_target参数不能算是一个硬性限制，而更应该算是一个请求！！！

3、Oracle中观察PGA，可以从工作区（排序、HASH连接、位图）、Cache Cursor、批量读取缓存（arraysize）等几方面做测试，大家可以动动手？
sys@OCM> select c.sid,spid,pid,a.serial#
2 from (select sid from v$mystat where rownum<=1) c,v$session a,v$process b
3 where c.sid=a.sid and a.paddr=b.addr;
SID SPID PID SERIAL#
---------- ------------------------ ---------- ----------
125 4572 17 7
sys@OCM> select pga_max_mem/1024,pga_alloc_mem/1024,pga_used_mem/1024,program
2 from v$process where spid= 4572
3 order by pga_used_mem,pga_max_mem;
PGA_MAX_MEM/1024 PGA_ALLOC_MEM/1024 PGA_USED_MEM/1024 PROGRAM
---------------- ------------------ ----------------- -------------------------
1446.34766 1446.34766 1015.76563 oracle@ocm (TNS V1-V3)

sys@OCM> select count(*) from v$open_cursor where sid=125;
COUNT(*)
----------
12

（１）、Cache Cursor(非工作区)
　实验:缓存游标：session_cached_cursors
　sys@OCM> alter system set session_cached_cursors=200 scope=spfile;
System altered.
sys@OCM> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 cg number;
7 begin
8 mcur:=dbms_sql.open_cursor;
9 for i in 1..500 loop
10 for j in 1..10 loop
11 msql:='select id from t1 where id='||i;
12 dbms_sql.parse(mcur,msql,dbms_sql.native);
13 mstat:=dbms_sql.execute(mcur);
14 end loop;
15 end loop;
16 end;
17 /

PL/SQL procedure successfully completed.
sys@OCM> select pga_max_mem/1024,pga_alloc_mem/1024,pga_used_mem/1024,program
2 from v$process where spid= 4572
3 order by pga_used_mem,pga_max_mem;
PGA_MAX_MEM/1024 PGA_ALLOC_MEM/1024 PGA_USED_MEM/1024 PROGRAM
---------------- ------------------ ----------------- ------------------------------------------------
1510.34766 1510.34766 987.28125 oracle@ocm (TNS V1-V3)
（２）、批量读取缓存arraysize(非工作区) －－－－留给大家动作实验
　实验:缓存结果集：arraysize
　set arraysize 5000;
　set autot traceonly;
　select * from t2;--观察逻辑读
（３）、工作区（排序、HASH连接、位图）－－留给大家动作实验
　实验：工作区pga_aggregate_target
　alter system set pga_aggregate_target=500M;
　select * from t1 a,t1 b order by 1,2,3;
　_smm_max_size
　_pga_max_size
拿_pga_max_size计算出_smm_max_size，以_smm_max_size作为一个进程的最大内存。。。

4、ORA-4030错误的可能原因?
（１）PGA设置过大，导致物理内存耗尽
（２）操作系统内存限制设置不合理
（３）程序中分配内存部分出现死循环
（４）分配对象后没有释放

5、PGA手工管理下的优化要点？
（1）需要手工设置一系列参数
sort_area_size --每个会话可以用于内存排序的空间最大值
hash_area_size　　　　　--每个会话可以用于hash连接的内存空间最大值
bitmap_merge_area_size　--每个会话使用位图合并连接时的内存工作区域的最大值
create_bitmap_area_size --每个会话创建位图时可以使用的内存工作区域的最大值
sort_area_retained_size　
（2）WORKAREA_SIZE_POLICY参数 --manual
（3）设置适当的*_area_size
（4）DBA容易忽略sort_area_size以外的参数
（5）SORT_AREA_RETAINED_SIZE不宜设置过小
（6）注意ORA-4030，加强物理内存监控 ---*_area_size设的太大(process*sort_area_size)
（7）参数设置考虑大多数会话的情况，特殊需要在会话级修改*_area_size等参数
6、如何监控PGA？
sys@OCM> col PROGRAM for a25
sys@OCM> col spid for 9999
sys@OCM> set linesize 1000
sys@OCM> select program,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024
PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX
2 from V$PROCESS order by PGA_USED;
PROGRAM SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX
------------------------- ------------------------ ---------- ---------- ---------- ----------
PSEUDO 0 0 0 0
oracle@ocm (J001) 4592 566.832031 726.152344 0 726.152344
oracle@ocm (VKTM) 4428 568.824219 726.152344 0 726.152344
oracle@ocm (SMCO) 4562 568.824219 726.152344 0 726.152344
oracle@ocm (MMAN) 4442 571.824219 726.152344 0 726.152344
oracle@ocm (PSP0) 4438 571.824219 726.152344 0 726.152344
oracle@ocm (PMON) 4426 572.617188 726.152344 0 726.152344
oracle@ocm (QMNC) 4475 572.933594 726.152344 0 726.152344
oracle@ocm (DIAG) 4434 574.898438 726.152344 0 726.152344
oracle@ocm (GEN0) 4432 575.601563 726.152344 0 726.152344
oracle@ocm (MMNL) 4456 577.464844 726.152344 0 726.152344
oracle@ocm (J000) 4590 603.964844 1110.15234 0 1110.15234
oracle@ocm (CKPT) 4448 619.039063 941.890625 0 941.890625
oracle@ocm (DBRM) 4436 654.683594 790.152344 0 790.152344
oracle@ocm (W000) 4564 673.398438 854.152344 0 854.152344
oracle@ocm (RECO) 4452 709.132813 854.152344 0 854.152344
oracle@ocm (SMON) 4450 897.902344 4502.15234 1280 4502.15234
oracle@ocm (Q000) 4494 972.878906 1238.15234 0 1238.15234
oracle@ocm (TNS V1-V3) 4572 1015.76563 1254.34766 0 1254.34766
oracle@ocm (DIA0) 4440 1016.98047 1285.52344 64 1285.52344
oracle@ocm (CJQ0) 4492 1120.46094 4886.15234 3584 4886.15234
oracle@ocm (MMON) 4454 1522.73438 3597.62109 1856 3597.62109
oracle@ocm (Q001) 4496 3015.19922 3525.52344 64 3525.52344
oracle@ocm (DBW0) 4444 4373.25 4710.21484 0 4710.21484
oracle@ocm (ARC2) 4468 10899.6836 11734.1523 0 11734.1523
oracle@ocm (ARC1) 4466 10899.6836 11734.1523 0 11734.1523
oracle@ocm (ARC0) 4464 10899.6836 11734.1523 0 11734.1523
oracle@ocm (ARC3) 4470 10899.6836 11734.1523 0 11734.1523
oracle@ocm (LGWR) 4446 10927.3203 11734.1523 0 11734.1523
sys@OCM> select * from v$sysstat where name like '%uga%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
25 session uga memory 1 9.0205E+10 1856888586
26 session uga memory max 1 52221512 3840343119
sys@OCM> select * from v$sysstat where name like 'session%ga%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
25 session uga memory 1 9.0205E+10 1856888586
26 session uga memory max 1 52221512 3840343119
31 session pga memory 1 117045528 4148600571
32 session pga memory max 1 130611480 507777907
sys@OCM> select * from X$KSMPP order by KSMCHPTR;
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
-------- ---------- ---------- ---------------- -------- ---------- -------- ---------- --------
00B41EBC 57 1 free memory 0023A040 20 free 0 00
00B41EF4 56 1 dbgdInitEventGr 0023A054 48 freeabl 0 00
00B41F2C 55 1 diag pga 0023A084 8224 freeabl 0 00BAC604
00B41E4C 59 1 diag pga 0023C0C4 3292 freeabl 0 00BAC604
00B41E84 58 1 diag pga 0023CDA0 5000 freeabl 0 00BAC604
00B41DDC 61 1 diag pga 0023E148 4148 freeabl 0 00BAC604
00B41E14 60 1 diag pga 0023F17C 4144 freeabl 0 00BAC604
00B41D6C 63 1 diag pga 002401CC 724 freeabl 0 00BAC604
00B41DA4 62 1 diag pga 002404A0 7568 freeabl 0 00BAC604
00B41CFC 65 1 diag pga 00242250 724 freeabl 0 00BAC604
00B41D34 64 1 diag pga 00242524 7568 freeabl 0 00BAC604
00B41C8C 67 1 diag pga 002442D4 4148 freeabl 0 00BAC604
00B41CC4 66 1 diag pga 00245308 4144 freeabl 0 00BAC604
00B41ACC 75 1 permanent memor 00246358 3604 perm 0 00
00B41B04 74 1 Alloc environm 0024716C 2028 freeabl 0 022643B8
00B41B3C 73 1 kopolal void 00247958 552 freeabl 0 00
00B41B74 72 1 kopolal void 00247B80 308 freeabl 0 00
00B41BAC 71 1 kopolal void 00247CB4 220 freeabl 0 00
00B41BE4 70 1 kopolal void 00247D90 552 freeabl 0 00
00B41C1C 69 1 kopolal void 00247FB8 900 freeabl 0 00
00B41C54 68 1 sdbgrf: iosb 0024833C 128 freeabl 0 00
00B40998 18 1 permanent memor 0038F040 51216 perm 0 00
00B407D8 26 1 permanent memor 0039B870 1656 perm 0 00
00B40810 25 1 permanent memor 0039BEE8 44 perm 0 00
00B40848 24 1 free memory 0039BF14 20 free 0 00
00B40880 23 1 koh-kghu call h 0039BF28 36 freeabl 0 00
00B408B8 22 1 krbabrPgaReqCtx 0039BF4C 32 freeabl 0 00
00B408F0 21 1 krbabrPgaReqMsg 0039BF6C 292 freeabl 0 00
00B40928 20 1 kjztprq struct 0039C090 2068 freeabl 0 00
00B40960 19 1 KSFQ heap 0039C8A4 4144 recr 4095 0226C718
00B4197C 81 1 PLS PGA hp 00950010 2040 freeabl 0 022703E0
00B419B4 80 1 Alloc environm 00950808 8252 freeabl 0 022643B8
00B419EC 79 1 Alloc environm 00952844 8252 freeabl 0 022643B8
00B41A24 78 1 Alloc environm 00954880 4164 freeabl 0 022643B8
00B41A5C 77 1 Alloc environm 009558C4 8396 freeabl 0 022643B8
00B41A94 76 1 Fixed Uga 00957990 34416 freeabl 0 00
00B4182C 87 1 permanent memor 00990010 34680 perm 0 00
00B41864 86 1 free memory 00998788 8920 free 0 00
00B4189C 85 1 koh-kghu call h 0099AA60 1292 freeabl 0 00
00B418D4 84 1 PLS PGA hp 0099AF6C 8224 freeabl 0 022703E0
00B4190C 83 1 PLS PGA hp 0099CF8C 8240 recr 4095 022703E0
00B41944 82 1 Alloc environm 0099EFBC 4164 freeabl 0 022643B8
00B421CC 43 1 permanent memor 00C0B040 8036 perm 0 00
00B42204 42 1 permanent memor 00C0CFA4 208 perm 0 00
00B40490 41 1 krbrpcact 00C0D074 48 freeabl 0 00
00B42194 44 1 permanent memor 00C0D0C4 20496 perm 0 00
00B42124 46 1 permanent memor 00C120F4 7340 perm 0 00
00B4215C 45 1 peshm.c:Proces 00C13DA0 952 recr 4095 00C13D2C
00B4207C 49 1 permanent memor 00C14178 2920 perm 0 00
00B420B4 48 1 diag pga 00C14CE0 1228 freeabl 0 00BAC604
00B420EC 47 1 peshm.c:Proces 00C151AC 4144 freeabl 0 00C13D2C
00B4200C 51 1 permanent memor 00C161FC 7584 perm 0 00
00B42044 50 1 diag pga 00C17F9C 708 freeabl 0 00BAC604
00B41F64 54 1 diag pga 00C18280 1380 recr 4095 00BAC604
00B41F9C 53 1 kews sqlstat st 00C187E4 1812 freeabl 0 00
00B41FD4 52 1 KFK_IO_SUBHEAP 00C18EF8 5100 recr 4095 00C1714C
00B40CA8 4 1 permanent memor 02262458 56 perm 0 00
00B40CE0 3 1 Alloc environm 02262490 4108 freeabl 0 022643B8
00B40D18 2 1 Alloc environm 0226349C 2496 recr 4095 022643B8
00B40D50 1 1 kpuinit env han 02263E5C 1540 freeabl 0 00
00B40D88 0 1 allocate kzthsm 02264460 76 freeabl 0 00
00B40C00 7 1 external name 022644CC 20 freeabl 0 00
00B40C38 6 1 kzsna:login nam 022644E0 20 freeabl 0 00
00B40C70 5 1 Alloc environm 022644F4 8252 freeabl 0 022643B8
00B40BC8 8 1 kgh stack 02266550 17012 freeabl 0 00
00B40A40 15 1 permanent memor 0226A7E4 1376 perm 0 00
00B40A78 14 1 permanent memor 0226AD44 4648 perm 0 00
00B40AB0 13 1 permanent memor 0226BF6C 1800 perm 0 00
00B40AE8 12 1 KJZT context 0226C674 60 freeabl 0 00
00B40B20 11 1 KSZ pga subheap 0226C6B0 92 freeabl 0 00
00B40B58 10 1 KSFQ heap descr 0226C70C 92 freeabl 0 00
00B40B90 9 1 KGNFS pcontext 0226C768 224 freeabl 0 00
00B409D0 17 1 permanent memor 0226C868 8272 perm 0 00
00B40A08 16 1 free memory 0226E8B8 20 free 0 00
00B404C8 40 1 permanent memor 0226E8EC 3224 perm 0 00
00B40500 39 1 permanent memor 0226F584 2016 perm 0 00
00B40538 38 1 permanent memor 0226FD64 1184 perm 0 00
00B40570 37 1 skgfzctx 02270204 40 freeabl 0 00
00B405A8 36 1 KFIO PGA struct 0227022C 88 freeabl 0 00
00B405E0 35 1 skgfzctx 02270284 40 freeabl 0 00
00B40618 34 1 joxp heap 022702AC 296 freeabl 0 00
00B40650 33 1 PLS cca hp desc 022703D4 200 freeabl 0 00
00B40688 32 1 regheapd_kdlwpg 0227049C 92 freeabl 0 00
00B406C0 31 1 iovecheapd_kdlw 022704F8 128 freeabl 0 00
00B406F8 30 1 bcheapd_kdlwpga 02270578 164 freeabl 0 00
00B40730 29 1 sioheapd_kdlwpg 0227061C 116 freeabl 0 00
00B40768 28 1 KCFIS APPL FDS 02270690 412 freeabl 0 00
00B407A0 27 1 krbabrPgaRespMs 0227082C 292 freeabl 0 00
sys@OCM> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 113246208 bytes
aggregate PGA auto target 30670848 bytes
global memory bound 22648832 bytes
total PGA inuse 79166464 bytes
total PGA allocated 98160640 bytes
maximum PGA allocated 108169216 bytes
total freeable PGA memory 7012352 bytes
process count 29
max processes count 34
PGA memory freed back to OS 19857408 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 3121152 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 25147392 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 210
sys@OCM> Select pga_target_for_estimate/1024/1024 ||'M' "PGA"
2 ,estd_pga_cache_hit_percentage "SORT_Hit(%)"
3 ,estd_extra_bytes_rw/1024/1024 ||'M' "Read/Write"
4 ,estd_overalloc_count "IO"
5 From v$pga_target_advice;
PGA SORT_Hit(%) Read/Write IO
----------------------------------------- ----------- ----------------------------------------- ----------
13.5M 83 6.63671875M 2
27M 83 6.63671875M 2
54M 83 6.63671875M 2
81M 91 3.3984375M 1
108M 100 0M 0
129.599609375M 100 0M 0
151.19921875M 100 0M 0
172.7998046875M 100 0M 0
194.3994140625M 100 0M 0
216M 100 0M 0
324M 100 0M 0
432M 100 0M 0
648M 100 0M 0
864M 100 0M 0

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe

QQ: 252803295

Email: oracledba_cn@hotmail.com

Blog: http://blog.csdn.net/guoyJoe

ITPUB: http://www.itpub.net/space-uid-28460966.html

OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

DSI&Core Search Ⅰ群：127149411（加群验证信息回复：from guoyJoe@csdn）

DSI&Core Search Ⅱ 群：177089463 （加群验证信息回复：from guoyJoe@csdn）

DSI&Core Search Ⅲ群： 284596437（加群验证信息回复：from guoyJoe@csdn）

DSI&Core Search Ⅳ 群：192136702（加群验证信息回复：from guoyJoe@csdn）

DSI&Core Search Ⅴ群：285030382（加群验证信息回复：from guoyJoe@csdn）