Oracle-OCP学习笔记:PGA

实际工作中，Oracle中有两个很重要：ServerProcess 和 PGA。

SGA:Shared Global area

PGA 内存作用和构成 (Private Global Area)

一个server process占用一个PGA(内存空间)独占。

1.PGA 作用

PGA 存储与server process相关的一些会话，被单个serverprocess独占

2 、PGA构成

1 ）private SQL area

2 ）Session Memory

（hold a session’s variables (logon information) and otherinformation related to the session.）

3 ）SQL Work Areas:   sql工作区(重点研究对象), server process 执行 sql 语句时，需要这个一样由PGA提供的工作空间。

Sql 工作区的组成部分：

WORKAREA_SIZE_POLICY

sort_area_size    # 排序区域

sort_area_retained_size # 释放空间的大小,也就是最低保留的空间大小

hash_area_size    #hash 连接时用的空间

hash_join_enable  #

bitmap_merge_area_size # 位图空间

create_bitmap_area_size 

open_cursors   # 一个会话可以同时执行SQL语句的数量,一般配置200M

_pga_max_size   # 每个会话可以分配PGA空间最大的容量

查看为每个ServerProcess分配空间的最大值：

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';

查看各区的大小：

SYS AS SYSDBA@ORCL>show parameter area;

NAME                   TYPE       VALUE

------------------------------------ --------------------------------------------------

bitmap_merge_area_size           integer        1048576

create_bitmap_area_size      integer        8388608

hash_area_size                integer        131072

sort_area_retained_size      integer        0

sort_area_size                integer        65536

workarea_size_policy          string         AUTO

PGA 分配空间的方式：

手工分配：手动指定sort,hash,bitmap空间的大小，这个空间指为每个server process分配的空间。

如果sort_area_size分配小了，PGA中的 排序 就会利用磁盘进行排序，这个sql执行效率就会很低。

Server process 执行的sql语句如果在磁盘中排序分三种情况：

1.  完全内存排序

2.  使用磁盘排序，但只使用一次

3.  使用磁盘排序，并使用多次，速度会变得非常慢

自动分配：

自动分配时，PGA获取的空间是有限制的。

分配PGA空间大小参考：

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%

3 、PGA管理方式

Dedicated Server 、Shared Server

Dedicated Server: 连接和server process是一一对应的关系,适用于一般使用，对数据库的连接数量有限制，每个连接的负载比较大。

Shared Server: 通过调度进程来分配server process进程,来执行语句，适用于对数据库的连接数量非常大，但每个连接的负载比较小的情况。

目前大都是用Dedicated Server.

如果是大型电商网站，访问很多，单个负载很小的话，就用Shared Server。

5 、重要参数

6 、重要视图

V$PGASTAT

SELECT * FROM V$PGASTAT;

查看PGA需要分配多大空间才合适：

方法一：

V$PGA_TARGET_ADVICE :

Select pga_target_for_estimate/1024/1024 ||'M'"Estimate PGA Target",estd_pga_cache_hit_percentage "CacheHit(%)",estd_extra_bytes_rw/1024/1024 ||'M' "ExtraRead/Write",estd_overalloc_count "Over alloc count" Fromv$pga_target_advice

select(p.PGA_TARGET_FOR_ESTIMATE)/1024/1024,p.ESTD_PGA_CACHE_HIT_PERCENTAGE fromv$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 FROMv$pga_target_advice;

方法二：

查看为每个ServerProcess分配空间的最大值：

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';

通过系统状态值，查看是否有磁盘排序的情况发生：

V$SYSSTAT 、V$SESSTAT 

select * from V$SYSSTAT where name like '%sort%';

查看当前会话使用的情况：

select name,value from v$statname a,v$mystat b wherea.statistic#=b.statistic# and a.name like '%pga%';

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

查看数据库总的连接数：

V$PROCESS

selectspid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem fromv$process where PROGRAM like '%ORCL%'

-- 查看PGA各个区域使用情况

selectp.PROGRAM,p.SPID,pm.CATEGORY,pm.ALLOCATED,pm.USED,pm.MAX_ALLOCATED fromv$process p,v$process_memory pm where p.PID=pm.PID and p.SPID in (select spidfrom v$process where addr in

(select paddr from v$sessionwhere sid in

(select distinct sid fromv$mystat )

));