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.
2 from v$process where spid= 4572
3 order by pga_used_mem,pga_max_mem;
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設的太大(processsort_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%';
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
![實驗樓sql進階之成績管理系統的資料操作(window)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)