oracle学习笔记 PGA管理和调整
oracle的pga的管理
pga内存是如何运作的、如何去管理讲一下
一）oracle的两种工作方式
简单提一下关于oracle的两种工作方式
Dedicated Server 和 Shared Server
在实际的工作中我们基本上都在用
Dedicated Server专用的服务器模式
而不用Shared Server模式
正常的情况
一个用户的连接，连上来以后
针对这个连接专门的启一个oracle的serverprocess
用户连接和serverprocess对接
这个会话中用户连接的所有的请求由对接的serverprocess响应
这个serverprocess专门为这个用户连接服务
它们之间是一对一的关系
这叫专用服务器模式即Dedicated server
另外一种模式
oracle启来一堆进程
叫dispatcher叫调度进程
再启一堆shared server进程就是serverprocess
比如启了8个serverprocess进程和3个dispatcher调度进程
这时一个用户连接连上来了
随机的选一个调度进程
再一个连接上来选另外一个调度进程
每一个连接上来都依次的选一个调度进程
现在有三个调度进程
如果上来90个连接
一个dispatcher就负责30个连接
这时只有8个serverprocess
而90个连接会送一些sql语句过来
比如其中一个客户连接会送sql语句过来
负责的dispatcher会把这个sql语句放到一个队列中
所有三个dispatcher只要他们收到sql语句
他们就会往队列里面放
dispatcher负责收集客户连接送来的sql语句放到队列里面去
然后8个serverprocess它们会不停的查这个队列
比如其中一个serverprocess轮询这个队列
发现里面有一个sql拿出来就去执行去
所以这8个serverprocess不停的去轮询这个队列
找到sql语句就执行
会出现一种情况
90个连接
对应着3个dispatcher对应着8个serverprocess
真正工作的是8个serverprocess
dispatcher只是一个调度的作用它的负载很小
主要的负载在serverprocess上
这叫shared server
这两种方式都各有好处
Dedicated Server专用的服务器模式
每个连接有专用serverprocess
每个连接的请求会得到最及时的响应
Shared Server模式
减少了serverprocess的数量
8个serverprocess为90个连接服务
所以对后者第二种服务器模式来讲
比如说我是个电子商务网站是一个数据库
这个数据库有一万个人要同时访问它
这一万个人访问时候量都不大但人非常多
一万个连接访问数据库
这个时候如果说用第一种方式的话
这个数据库根本就受不了一万个serverprocess
我们只能用第二种方式
第二种方式就适合对数据库的连接量特别大
同时每一个连接负载又很小的情况
第一种情况是主要的一般用Dedicated Server的serverprocess
对数据库的连接数量有限
而且每一个连接的负载都比较大
比如说有200个连接对数据库的连接
而且200个连接每个连接的负载都比较大
我们就用dedicated serverprocess
关键看每个连接的负载的情况。
这就是专用和共享
以后的学习主要以serverprocess为例来讲
以Dedicated Server方式来讲，专用服务器模式
二）自动和手动的关系
pga管理方式有两种
一种是auto自动
一种是手动
从oracle9i开始现在都在用自动，手动我们已经不用了
因为自动非常的好，手动很差
自动和手动的方式在生活中其它的方面也时时见到
如现在有人想造无人驾驶汽车
计算机本身就是以实现自动执行为主的
基本上所有的代码都在自动的一句一句的执行着
它的自动过程
是按预先的设计预先安排步骤
每一步按预先想到的它可能的结果
以及每个结果后可能的方向
设计出程序下一步执行什么
计算机发展中将许多手工一步步完成的任务功能自动化，实现了高效率
但有很多方面是无法完成自动化的
要更多的控制一个软件也需要手动的多一些
手动越多执行者对功能的控制就可以越深越彻底
完全控制和自动化过程是计算机内相互矛盾的一个方面
需要和人交互的方面越多，自动化能够实现的方面就越少
如有个过程需要人给它个数据
你非要自动它自动的给它一个数据让它自动执行
那么它正确的可能性是很小的
另外按自动化运行时
这个过程中碰到了没有想到的情况自动就会失败
即使按计划执行，如果有一步执行时出错整个自动过程也会无法完成
想把所有会出现的情况都想到不太现实
更不要说全部做到
其实自动化能做到的事很少，绝大部分都是它做不了的事情
计算机你觉得它能做的事很多
经过一代代人的努力，在计算机内虚拟出了不少现实世界的东西
仔细想想它能做的事也都在计算机内，和它外面的大千世界比比，比例还是很小的
按照自动化具有的局限性，我感觉无人驾驶汽车能实现的自动化功能并不会很多
计算机无人操作都是无法运行的，汽车没人管它怎么开啊！
oracle中许多东西还在一步步实现自动化
有些东西实现了自动化后你会觉得手动感觉差一些
总体上计算机自动化有好处也是必须的
但它牺牲了一部分的控制功能也增加了错误的发生可能
三）手动设置工作区
oracle有内存结构
磁盘有文件
有从外部用户的多个连接
每个连接上来后都要分配pga空间
pga空间里面最主要的是工作区
工作区里面最主要的就三个区
一个是和排序相关的
一个和hash相关的
一个和位图相关的
主要这三个区
手动设置
是手工的设sortarea，hasharea和bitmap
手工的设这三个区的大小
比如设sort为2M
hash是2M
bitmap是1M
这个时候对每一个serverprocess来讲
它会分到5M的空间
当然实际比5m会多一些
pga的别的内存空间不管它，它很小，oracle分一点就够了
对这三个区
oracle还有一些参数
现在这些参数还有
SQL> 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
如上面有sort_area_size、hash_area_size参数说明这些区还有
我们以前是手工设这些区手工设大小
手工设大小有问题
比如我设了5M了
就意味着
所有的每一个session链上后都会得到5M的空间
不管你用还是不用都会得到5M的空间
就可能出现
第一空间不够
如一个session的排序量和hash量特别的大
所以空间可能不够
第二如一个session负载很轻
它的空间就可能有空余
手工设就有这个问题
手工设那几个参数
是表示给每一个session分的大小
每一个serverprocess分的大小
手动的原来很有问题
四）数据文件和临时文件
oracle的数据文件叫dbf文件
oracle到底有多少dbf文件呢
我们来查一下
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jiagulun/users01.dbf
/u01/app/oracle/oradata/jiagulun/sysaux01.dbf
/u01/app/oracle/oradata/jiagulun/undotbs01.dbf
/u01/app/oracle/oradata/jiagulun/system01.dbf
/u01/app/oracle/oradata/jiagulun/example01.dbf
从结果看我们的数据库有5个dbf文件
这些文件里面放的是表等基础数据
oracle除了这些数据文件以外
还有一类文件叫临时文件
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jiagulun/temp01.dbf
从结果看oracle只有一个临时文件
oracle在磁盘上
有数据文件data和temp临时文件
就是oracle的文件可分两大类
一类是temp一类是data
data是放数据的
temp是临时文件
临时文件意味着数据是临时的
丢了也没事
五）内存排序和磁盘排序
对一个session来讲
它需要的sortarea和hasharea或者bitmaparea
这三个空间可能很大
我们只给它分了2m、2m和1m只分了5m
这时出了一个问题
比如其中一个session
执行一个sql语句有排序
排序以后它需要4m的一个内存空间
这时sortarea只给它分了2m
这时这个serverprocess就会在临时文件上即临时表空间上划出一块空间来
pga的sortarea这块空间这个排序区和这块磁盘区
两个合起来把这个排序完成
也就是
session将一部分数据先写到磁盘上
然后一块块拿出来去排序
排序时pga的排序空间不够
这个serverprocess会利用磁盘来进行磁盘排序
如果使用sortarea的话叫内存排序
如果使用磁盘，我们就叫磁盘排序
我们知道磁盘速度很慢
因为物理io速度很慢
出现磁盘排序这个sql语句就会执行的很慢
执行磁盘排序时又分两种情况
第一我只往磁盘中读写了一次
这样的话影响的还比较小
还有一种情况
我一次不行写了多次也就读写了多次
serverprocess在pga里面排序的时候分三种情况
第一种情况叫 完全内存排序
第二种情况是 使用磁盘排序但只使用磁盘一次
第三种情况是 使用磁盘多次
如果出现第三种情况
这次sql语句执行速度就相当慢了
肯定你会感觉到它的速度很慢
所以说我们以后需要关注一下oracle数据库到底有没有发生磁盘排序
因为我们知道只要发生磁盘排序就一定意味着
这一次执行的速度是比较慢的
六）自动分配PGA
1）自动分配PGA的好处
这是我们讲了手动有这个坏处
自动跟手动不一样
自动非常简单
我们只需要给所有的serverprocess分配一个大的pga空间就可以了
pga空间，只分配一个空间
比如我分配了2G
这个时候每个serverprocess建立的时候
都会从pga里面申请一块空间
当serverprocess空间不够的时候它会再去申请
当用完了以后它会再释放出去
对每个serverprocess来讲它所获得的pga空间是弹性的
这样好一些
只要我的pga空间足够大
基本上会出现
空间不够了我申请一下空间，用完了我释放出去，灵活分配
这样发生磁盘排序的概率就小多了
2）自动分配PGA的限制
某个serverprocess它在某个瞬间它需要非常大的一块内存空间
我们会无限的给它吗
pga总的空间一共2g
如果全给了这个serverprocess意味着
别的serverprocess会发生磁盘排序
比如一共有五十个进程
其中一个进程占了绝大部分的pga
另外50个进程就可能同时发生磁盘排序
这时整个速度会降下来
所以在自动管理里面
每个serverprocess能够自动获得的pga的空间我们是有限制的
不是随便
3）如何设置自动管理
设自动管理的两个参数
把workarea_size_policy设为auto
然后设pga_aggregate_target的大小
比如我设了512m
workarea_size_policy默认就是auto
我们主要设
pga_aggregate_target
这个参数就是自动管理模式里面给pga的总的空间量
在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;
查询这两个参数的值：
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 90M
pga_aggregate_target默认特别小只90M
当然太小了，因为我们当前使用的是非生产环境，90M无所谓
因为我们没有什么连接
到底需要满足多少连接到底该设多大呢
一般是物理内存的80%*20%是16%
一般生产里面都是16G的空间
16G*16% = 2.56G 接近3G给pga
七）pga_aggregate_target只是临界值
pga_aggregate_target
这个参数比如我设了90M
将来使用有可能低于90M有可能高于90M
正常情况下一开始用不了90M
它不用那么多
一开始serverprocess少的时候
比如一开始只有三个serverprocess
其中一个要排序，
它需要10M，好就给它10M
它需要20M就给它20M
基本上能满足它
每个serverprocess从pga里面总的分配空间里面分配
一开始分配的pga是直线上升，需要多少就分配给它多少
当总需要到了90M时
就是那三个serverprocess需要的pga空间使用完了90M
这时第四个或者它本身又要pga空间的时候
这时oracle会做一件事情
原来pga是90M
它会从oracle的内存中又分配到10M
现在是100M
举例：
那三个serverprocess一开始
第一个需要10M第二个需要20M第三个需要30M
都满足了因为还没有超过90M
第四个连上来以后
比如前三个把90M用完了
第四个上来时它需要10M
这时90M里面没空间了
oracle会再去申请10M或20M空间到总的pga里面
但是第四个只会分给它5M
就是一旦超过pga_aggregate_target设的值
它分的就少了
比如第四个需要10M就分给了它5M
空间分配的时候
一开始是分配线是直线关系，你需要多少分多少
最后这条线变成了曲线就平缓了
pga空间随着到达90M以后
每个人再需要空间的时候它就不给总需求的大小了
就慢慢少了
pga_aggregate_target只是一个临界值
serverprocess在要空间时在没有达到临界值时以前
你需要多少它给你多少
当然不要超过每个session的最大值
一旦到了临界值分配的会慢慢变少越来越少
比例越来越少
你需要100M它可能只给你20M
所以说实际的pga的使用空间
有可能超过pga_aggregate_target
八）和PGA相关的参数
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
是这部分内容使用到的参数
我们看open_cursors
是一个会话可以同时执行的sql语句的数量
这个参数一般设在200以上就可以
_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';
结果
Name Value Desc
------------- ------ --------------------------------------------------
_pga_max_size 200M Maximum size of the PGA memory for one process
从结果看这个参数是200M
也就是对每个session来讲最大只能分200M
对当前来讲每个session最多在pga空间只能分200M
看它的描述
Maximum size of the PGA memory for one process
翻译一下就是：每个进程的pga内存最大的尺寸
老师平时的规划
先查一下这个参数，现在是200M
然后估计一下，比如
本实例有40个session
给它分配大小最大40*200M就是8G
给它10G没有意义
oracle结构里面有多少会话
基本上应该知道
平时ps命令看一下也可以
这是_pga_max_size参数
九）pga_aggregate_target该设多大
oracle数据库里面
pga_aggregate_target到底设多大
有几种方法
1）按公式
就照着上面给的公式设
内存总量乘80%再乘20%
这是一种办法
2）按会话的多少
另外一种办法
看一下有多少session
可以通过语句
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process where PROGRAM like '%jiagulun%';
来查
查出来当前有多少连接
SQL> select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process where PROGRAM like '%jiagulun%'; 2
SPID PROGRAM PGA_MAX_MEM PGA_ALLOC_MEM PGA_USED_MEM PGA_FREEABLE_MEM
------------ ------------------------------------------------ ----------- ------------- ------------ ----------------
3242 oraclejiagulun@redhat4 2987661 2332301 1236865 524288
5519 oraclejiagulun@redhat4 366221 366221 209921 0
9363 oraclejiagulun@redhat4 366221 366221 209921 0
5197 oraclejiagulun@redhat4 1021581 795249 751105 0
.
.
.
11254 oraclejiagulun@redhat4 366221 366221 209921 0
11269 oraclejiagulun@redhat4 366221 366221 209921 0
118 rows selected.
结果有118行说明目前数据库有118个连接
因为我的实验机自己操作不当而且没有关机很长时间了，现在保留了很多的连接
如果关一下数据库然后重启
结果：
SQL> select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process where PROGRAM like '%jiagulun%'; 2
SPID PROGRAM PGA_MAX_MEM PGA_ALLOC_MEM PGA_USED_MEM PGA_FREEABLE_MEM
------------ ------------------------------------------------ ----------- ------------- ------------ ----------------
30019 oraclejiagulun@redhat4 3118733 2332301 1171945 786432
30021 oraclejiagulun@redhat4 1218189 1218189 644925 0
30004 oraclejiagulun@redhat4 2463373 2463373 1431253 0
30013 oraclejiagulun@redhat4 1218189 1218189 646989 0
30016 oraclejiagulun@redhat4 4167309 3249805 1929077 589824
30023 oraclejiagulun@redhat4 1873549 1873549 1230669 0
6 rows selected.
然后用SQL Developer再连一下数据库
再次查询
SQL> select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process where PROGRAM like '%jiagulun%'; 2
SPID PROGRAM PGA_MAX_MEM PGA_ALLOC_MEM PGA_USED_MEM PGA_FREEABLE_MEM
------------ ------------------------------------------------ ----------- ------------- ------------ ----------------
30019 oraclejiagulun@redhat4 3118733 2332301 1171945 786432
30021 oraclejiagulun@redhat4 3511949 2397837 1377789 720896
30004 oraclejiagulun@redhat4 2463373 2397837 1431253 655360
30106 oraclejiagulun@redhat4 3053197 2528909 1231785 1048576
30013 oraclejiagulun@redhat4 1218189 1218189 646989 0
30016 oraclejiagulun@redhat4 4167309 3249805 1929077 589824
30023 oraclejiagulun@redhat4 1873549 1808013 1230669 196608
7 rows selected.
多了一行说明
说明SQL Developer又建了一个连接
每个连接使用的曾经最大的空间为PGA_MAX_MEM列的数据
当前分配了PGA_ALLOC_MEM
实际使用为PGA_USED_MEM
剩余的大小PGA_FREEABLE_MEM列
结果中为每一个进程的使用pga的一个情况
如果目前有7个进程
每个最大200M的话
最多就是7*200M为1400M就是1.4G
这样分也可以
分1.4G肯定够了肯定会够
这是pga的第二种设置方式
3）根据统计信息预测计算
还是访问
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;
因为刚启动的数据库看不出有效的结果：
Estimate PGA Target Cache Hit(%) Extra Read/Write Over alloc count
----------------------------------------- ------------ ----------------------------------------- ----------------
11.25M 100 0M 1
22.5M 100 0M 1
45M 100 0M 1
67.5M 100 0M 0
90M 100 0M 0
108M 100 0M 0
125.9990234375M 100 0M 0
144M 100 0M 0
162M 100 0M 0
180M 100 0M 0
270M 100 0M 0
360M 100 0M 0
540M 100 0M 0
720M 100 0M 0
14 rows selected.
我下面还是使用有118个进程时得到的结果
执行结果：
Estimate PGA Target Cache Hit(%) Extra Read/Write Over alloc count
----------------------------------------- ------------ ----------------------------------------- ----------------
11.25M 98 21.7265625M 8
22.5M 98 21.7265625M 8
45M 98 21.7265625M 8
67.5M 100 0M 2
90M 100 0M 0
108M 100 0M 0
125.9990234375M 100 0M 0
144M 100 0M 0
162M 100 0M 0
180M 100 0M 0
270M 100 0M 0
360M 100 0M 0
540M 100 0M 0
720M 100 0M 0
14 rows selected.
结果14行数据
Estimate PGA Target列是估计的pga大小，假设设置这么大
Cache Hit(%)是命中率
Extra Read/Write是额外的读写
Over alloc count内存不够用磁盘读写次数
以第一行为例
如果pga_aggregate_target大小设置为11.25M的话
命中率Cache Hit(%) 在内存里面操作的命中率
我们希望pga的所有的操作都在内存里面
但有的操作不一定在内存里面有可能在磁盘上
Extra Read/Write
是如果把pga设为11.25M
这时可能会发生21.7265625M的物理磁盘的读写
Over alloc count
指会出现8次io的读写
就是有8次内存的溢出需要磁盘的读写
我们希望溢出是零最好
根据上面的查询结果
我们就设到90M
这样所有的操作都在内存里面
就没有物理读写了
over alloc count就是0了
对我们来讲
90M以后都是0
设为90,108或144都一样
可以设90就行了
在空间足够的情况设为360M也可以
这也是一种方法
这种方法最节省空间
4）三种方法比较
最浪费空间的
是前两种
前两种最浪费空间的是第二种
就是200M乘上预估的进程的数量
这三种方法都可以
老师建议的方法
先根据进程的数量这里假如是7个
预估一下可设为1.4G
然后数据库跑一段时间以后
再通过第三个sql语句查一下
到底需要多大
然后总大小再适当的减少一些，减少一下
这样对oralce的影响最小
老师们在实际工作中是这么做的
十）查询PGA内存使用情况的一些语句
下面仍以有118个进程为例
1）
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;
执行结果
(P.PGA_TARGET_FOR_ESTIMATE)/1024/1024 ESTD_PGA_CACHE_HIT_PERCENTAGE
------------------------------------- -----------------------------
11.25 98
22.5 98
45 98
67.5 100
90 100
108 100
125.999023 100
144 100
162 100
180 100
270 100
360 100
540 100
720 100
14 rows selected.
和上面一个预测语句效果一样
不过换了一个sql语句，把数据精简了一下
把命中率大于95%的预估值找出来了
这样会好一些
2）
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$pga_target_advice视图得到的信息
3）
现在看一下这个sql语句
select * from V$SYSSTAT
where name like '%sort%';
执行结果
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
341 sorts (memory) 64 75970 2091983730
342 sorts (disk) 64 0 2533123502
343 sorts (rows) 64 3609071 3757672740
这是数据库的系统统计信息
第二列可看出是排序操作
内存排序sorts (memory)值是75970次
磁盘排序sorts (disk)是0次
参加排序的行数sorts (rows)是3609071行
从结果看磁盘排序没有多少
目前为止数据库很正常没有发生磁盘排序
这是理想的
4）
当前会话的一些信息
SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';
NAME VALUE
------------------------------ ----------
session pga memory 1502804
session pga memory max 1896020
当前会话session使用pga的大小是1502804
最大曾经使用到1896020
这里是当前的会话
因为使用的工具就连接到数据库上
就是执行这个sql语句的会话
意义不是很大
5）
也是当前进程的情况
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)));
结果
SPID PROGRAM PGA_MAX_MEM PGA_ALLOC_MEM PGA_USED_MEM PGA_FREEABLE_MEM
------------ ------------------------------------------------ ----------- ------------- ------------ ----------------
9214 oracle@redhat4 (TNS V1-V3) 1843825 1581681 761297 0
这也是当前进程的pga的使用情况
当前进程我们并不关心
我们关心所有进程
6）
select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process where PROGRAM like '%jiagulun%';
这个语句前面执行过了查询当前连到数据库的进程及其PGA使用情况
这个sql语句还是很有意义的
这些都是老师积累的一些sql语句
以后用到的时候可以随时使用随时查
7）
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 )
));
执行结果
PROGRAM SPID CATEGORY ALLOCATED USED MAX_ALLOCATED
------------------------------------------------ ------------ --------------- ---------- ---------- -------------
oracle@redhat4 (TNS V1-V3) 9214 SQL 67740 37792 1238888
oracle@redhat4 (TNS V1-V3) 9214 PL/SQL 2068 136 2068
oracle@redhat4 (TNS V1-V3) 9214 Other 1708481 1708481
这是当前会话
会话的SPID是9214
当前会话pga空间分为3个
SQL、PL/SQL和Other
ALLOCATED是已经分配的情况
USED是已经使用的情况
MAX_ALLOCATED是曾经最大分配的大小
其实我们主要关心sql工作区
这里面比较关注的是
所有进程PGA使用情况还有pga到底该设多大的几个sql语句
比较有意义
前面课程中老师讲课的内容都是在专有服务器模式下讲的。
这就是我们讲的一些pga的一些情况
包括怎么设设多大
十一）资料查询总结
老师的课讲完了但是本人有一些事情没有弄清楚
比如PGA的结构
老师上节课说
实际上没有大的意义
不知什么原因
所以在网上找了不少资料
遗憾的是一个内存结构有好多种说法
而且每个组件的位置说的也不一样
于是把最后的希望放到了oracle的官方文档
从9到11的官方文档，都看了关于oracle的内存结构的说明
令人惊讶的是各个版本的说明
虽然大体一样，但有的组件的位置说明中也是无法统一
看来oracle的升级改变了具体的位置
造成网上和各个官方文档的说明都不一致
废了这么大的劲，不想白费功夫
虽然自己无法统一它们的位置
我这里还是把自己收集到的具体信息说一下
网上的说法太乱，下面以官方文档为主虽然也有些乱
1）oracle的内存分区
oralce的内存分为两大部分
一个是SGA一个是PGA
有些最细的分法分为了四个部分：
SGA（System Global Area），即系统全局区，Oracle中最重要的内存区。
PGA（Process Global Area），即程序全局区，一个进程的专用的内存区。
UGA（User Global Area），即用户全局区，与特定的会话相关联。
CGA (CALL Global Area），即调用全局区
官方文档中对SGA和PGA说的很详细，另外两个只是提了提
2）PGA的分区
PGA中主要分为以下三部分内容：
私有SQL区 Private SQL Area
游标和SQL区 Cursors and SQL Areas
会话内存 Session Area
1、私有SQL区
又分为两个区
永久区 Persistent Area
运行区 Run-Time Area
在官方文档中有这么一句话
When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.
所以运行时区就是这个sql语句的工作的地方
官方文档中还有这么一段话
For complex queries (for example, decision-support queries), a big portion of the runtime area is dedicated to work areas allocated by memory-intensive operators such as the following:
Sort-based operators (order by, group-by, rollup, window function)
Hash-join
Bitmap merge
Bitmap create
复杂的查询会分出一大部分的runtime area给SQL Work Areas
所以SQL Work Areas属于PGA中的private sql area中的runtime area
根据所分的子区，SQL Work Areas里面所做的工作有：
排序
多表查询时的hash join
使用到bitmap时的操作
2、游标和SQL区
这里的游标指的就是每一个此会话执行的每一个sql语句
每个sql语句执行时PGA分给它一个私有SQL区
同时生成一个游标指向它，这个cursor就在这个区它属于PGA
3、会话区
保存的是客户连到服务器的每个会话的信息
3）PGA分区的位置
上面说到了PGA的每个区
在专有服务器模式和共享服务器模式下
有的区的位置会有区别
各个oracle版本有的区的位置也有区别
这里只以在oracle 10G官方文档中一张表来说明
Memory allocation depends, in some specifics, on whether the system uses dedicated or shared server architecture. Table 8-1 shows the differences.
Table 8-1 Differences in Memory Allocation Between Dedicated and Shared Servers
Memory Area Dedicated Server Shared Server
-------------------------------------------------------------------------------------------------------------------
Nature of session memory Private Shared
-------------------------------------------------------------------------------------------------------------------
Location of the persistent area PGA SGA
-------------------------------------------------------------------------------------------------------------------
Location of part of the runtime area
for SELECT statements PGA PGA
-------------------------------------------------------------------------------------------------------------------
Location of the runtime area
for DML/DDL statements PGA PGA
-------------------------------------------------------------------------------------------------------------------
专有服务器模式下所有区都在PGA中
共享服务器模式下
会话内存是共享的，所以在SGA
私有SQL区中的永久区在SGA中，运行区在PGA
别的其它的区都在PGA中
4）SQL Work Areas中的各个区
在SQL Work Areas中的几个区是最重要的
在一个语句经过解析生成执行计划时决定使不使用
Oracle数据库中的优化器又叫查询优化器（Query Optimizer）
它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。
Oracle的优化器有两种，基于规则的优化器（RBO）与基于代价的优化器（CBO)
RBO: Rule-Based Optimization 基于规则的优化器
CBO: Cost-Based Optimization 基于代价的优化器
CBO是一种比RBO更加合理、可靠的优化器
ORACLE已经声明在ORACLE9i之后的版本中，RBO将不再支持
1、多表连接
在查看执行计划中，大家熟知的Oracle表的连接方式有几种主要表连接：嵌套循环连接、排序合并连接、哈希连接三种
嵌套循环连接 Nested loop join 可用于任何连接
排序合并连接 Sort merge join 主要用于不等价连接，如<、 <=、 >、 >=
哈希连接 Hash join 仅用于等价连接
哈希连接只适用于CBO、它也只能用于等值连接条件
主要是在连接时对一个表的连接键进行hash运算得到一个hash表存于hash area中
对另一个表的连接键也进行hash运算然后用hash值于第一个表进行连接
各种连接只有在hash join时使用hash area
2、位图
位图主要是作为一种索引使用，hash连接中也用到它
oracle中的索引最常用的是B*Tree索引和Bitmap索引
BITMAP INDEX只有CBO模式下支持，适用于静态读取的表
位图索引不能被规则优化器(RBO)使用
位图就是包含0和1两种位值的一个序列
位图索引对每个列的索引用多个这样的序列来表示
在一个表中的要建索引的列
判断其中的某一行是不是某一个值
如果是某一个值
这个列的位图索引中表示这个值的位图中
表示此行的这一位的值就是1
不是此值就是0
一个列有多少个存在的值
就会有多少个这样的序列就是位图
这些位图组合
就形成了此列的位图索引
hash连接中使用位图表示包含每个hash值的Hash Bucket是否为空
oracle建立索引时只有建立位图索引时用到位图区
3、SQL Work Areas中各区的使用
采用哪种索引方式和采用哪种连接方式
是在生成执行计划时有由优化器选择决定的
当然也可在编写程序时强制使用
每种工作区工作时都有可能会出现三种模式：optimal，one-pass，multi-pass
Optimal（优化方式）：指所有处理可以在内存中完成；
Onepass：大部分操作可以在内存中完成，需要使用磁盘一次
Multipass：大量操作需要产生磁盘交互，需要使用磁盘多次，性能极差
5）调度进程dispatcher
专用服务器模式一个连接产生一个serverprocess它使用的PGA包含了所有进程需要的组件
共享服务器模式下把有些组件放到了SGA中
用户连接要先连接dispatcher，
此调度进程把每个连接来的会话放到SGA中的请求队列REQUEST QUEUE中
空闲的服务器进程从请求队列拿出一个来执行
处理完成后把结果放到响应队列RESPONSE QUEUE中
这个时候调度进程DISPATCHER再从RESPONSE QUEUE中取出结果返回给各个用户
这里的REQUEST QUEUE和RESPONSE QUEUE位置都在SGA中
6）UGA和CGA
在谈到共享服务器具有的一些缺点时
网上有这么两段话
共享服务器存在独占事务的可能，因为如果一个会话的事务运行时间过长，它独占共享资源，其它用户只能等待。
如果客户端一次连接终身使用(会话生命周期内)，使用共享服务器模式的意义不大。
因为大部分时间，一个会话就连接到一个服务器进程，无法共享服务器进程。
说明：
在请求队列中的会话
虽然多个会话可以分享一个服务器进程
对单个serverprocess来说执行时是顺序执行的
它拿出来一个会话在自己的PGA中执行
执行结束后把结果返回给调度进程
这时它才会再到请求队列中再拿一个会话执行
单个服务进程对会话是顺序的一个个的执行
为什么要拿出这么一段话
因为serverprocess的另外两个全局区
UGA和CGA
网上有人把私有SQL区、游标和SQL区、会话内存分给了UGA
然后UGA属于PGA
在共享服务器模式下部分的UGA属于SGA
但官方文档中在11G才有了UGA并且只有两个区
session variables和OLAP page pool
官方文档中这么说
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session.
说明UGA中包含的都是会话相关的信息
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.
UGA在共享服务器模式下都分配在SGA，在独占服务器模式下都在PGA空间
官方文档就是说UGA只有两个区，并且在共享服务器模式下都属于SGA
结合上面说的
shared server mode模式下服务器进程对会话都是顺序执行
不会有一个服务器进程接过来另一个进程执行了一半的sql语句继续执行
所以说明网上把PGA的大部分内容分给UGA没有意义
而且把UGA中的除会话相关的分区放到SGA中也没有意义
另外CGA在官方文档中没有找到说明
网上说不管什么情况它都是PGA的一个子堆，这里就不说了
上面的说明应该已经可以把PGA的整个结构和位置说明了。
7）意外
不过遗憾的是
在官方文档中找到了这么一句话：
Part of the runtime area can be located in the SGA when using shared servers.
部分的运行时区在共享服务器模式下可以分配给SGA。
怎么办呢?
又没有详细说明！
只能理解有种情况可以强制的非自动的手动分配runtime area中的一部分到SGA。
不多说了，只好到此为止！
文字：韵筝
2017年1月9日
2483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
