相克军_Oracle体系_随堂笔记007-PGA
2014-07-14 20:39 AlfredZhao 阅读(1609) 评论(0) 编辑 收藏 举报实际工作中,Oracle中有两个很重要:Server Process 和 PGA。
PGA内存作用和构成
1、PGA作用
2、PGA构成
1)private SQL area
2)Session Memory
(hold a session’s variables (logon information) and other information related to the session.)
3)SQL Work Areas
![](https://images.cnblogs.com/cnblogs_com/jyzhao/592116/o_%e5%9b%be7-1%ef%bc%9aPGA%e5%86%85%e5%ad%98%e7%bb%93%e6%9e%84%e7%9a%84SQL%20Work%20Areas.png)
3、PGA管理方式
4、Dedicated Server、Shared Server
目前大都是用Dedicated Server.
如果是大型电商网站,访问很多,单个负载很小的话,就用Shared Server。
5、重要参数
PGA_AGGREGATE_TARGET
在OLTP系统中,典型PGA内存设置应该是总内存的较小部分(例如20%),剩下80%分配给SGA。 OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20% 在DSS系统中,由于会运行一些很大的查询,典型的PGA内存最多分配70%的内存。 DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50% SQL> alter system set workarea_size_policy=auto scope=both; SQL> alter system set pga_aggregate_target=512m scope=both;
WORKAREA_SIZE_POLICY
sort_area_size sort_area_retained_size hash_area_size hash_join_enable bitmap_merge_area_size create_bitmap_area_size open_cursors _pga_max_size
select ksppinm "Name", ksppstvl/1024/1024 ||'M' "Value", ksppdesc "Desc" from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm ='_pga_max_size';
6、重要视图
V$PGASTAT
SELECT * FROM V$PGASTAT;
V$PGA_TARGET_ADVICE
Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target" ,estd_pga_cache_hit_percentage "Cache Hit(%)" ,estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write" ,estd_overalloc_count "Over alloc count" From v$pga_target_advice
select (p.PGA_TARGET_FOR_ESTIMATE)/1024/1024,p.ESTD_PGA_CACHE_HIT_PERCENTAGE from v$pga_target_advice p where p.ESTD_PGA_CACHE_HIT_PERCENTAGE>95;
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;
V$SYSSTAT 、V$SESSTAT
select * from V$SYSSTAT where name like '%sort%';
select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$PROCESS
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem from v$process where spid in (select spid from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat)));
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem from v$process where PROGRAM like '%jiagulun%'
--查看PGA各个区域使用情况
select p.PROGRAM,p.SPID,pm.CATEGORY,pm.ALLOCATED,pm.USED,pm.MAX_ALLOCATED from v$process p,v$process_memory pm where p.PID=pm.PID and p.SPID in (select spid from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat ) ));
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」