Oracle PGA作用&work_mode

专有模式下ORACLE会给每个连接分配一个服务进程（Server Process），这个服务进程将为这个连接服务。为这个服务进程分配的内存叫做PGA。PGA不需要Latch也不需要Lock，永远不会发生竞争。

PGA组成中主要包括两部分：Session Area和Private SQL Area。
Session Area
包括会话跟踪信息，使用alter session命令修改的环境参数、打开的DB Links、会话特有的角色等。

Private SQL Area
Server Process每执行一个SQL都需要申请一个Private SQL Area，就是Cursor。
Private SQL Area可以分成两个部分Persistent Area和Run-Time Area。Private SQL Area中保存的是每个Session私有的信息，例如Cursor行数。Shared Pool中有一个Public SQL Area，保存的是执行计划等共享信息。两者截然不同。

Server Process在执行SQL语句前，必须在Shared Pool中定位语句的Shared SQL Area。在PGA中，情形类似，比如在PGA中定位该SQL语句的Private SQL Area。如果定位失败（找不到），服务器必须为其分配一个Private SQL Area，并初始化。这个过程消耗大量CPU Cycle。

Persistent Area存放的是绑定变量，数据类型转换等Cursor结构信息，Cursor关闭时，该区域释放。

Run-Time Area在SQL运行过程中使用。大小依赖于SQL语句操作方式，处理数据行数和每行记录大小，与处理数据量与处理步骤成正比。如果是DML，语句执行完就释放；如果是SELECT，在记录全部传给客户端或者取消查询后才释放。

一个SQL语句执行完后，Run-Time Area就会被释放，而Private SQL Area可以被其它SQL语句重用，重用时同样必须初始化。
PGA可以同时包含多个Private SQL Area，Server Process也会使用重用算法，增大Private SQL Area的重用。一个大的PGA可以避免Private SQL Area的置换，相应减少CPU开销。

PGA寻找Cursor的过程如下：
是否在某个OPEN CURSOR，如果是，执行；如果否，继续下一步。
是否在SESSION CACHED CURSOR，如果是，执行；如果否，继续下一步。
是否在HOLD CURSOR，如果是，执行；如果否，继续下一步。
OPEN CURSOR，继续下一步。
检索SQL AREA，继续下一步。
是否可重用判断，如果是，软解析，执行；如果否，执行下一步。
硬解析，执行。

Oracle 9i开始PGA实现了自动调整，Private SQL Area也叫做SQL Work Area，PGA自动调整参数WORKAREA_SIZE_POLICY（默认为AUTO）

可以使用以下语句查看PGA总的分配情况：
SQL>SELECT SPID, PROGRAM, PGA_MAX_MEM MAX, PGA_ALLOC_MEM ALLOC, PGA_USED_MEM USED, PGA_FREEABLE_MEM FREE
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)
)
);

可以使用以下语句查看PGA各个分区分配大小：
SQL>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 SPID IN
(SELECT SPID FROM V$PROCESS WHERE ADDR IN
(SELECT PADDR FROM V$SESSION WHERE SID IN
(SELECT DISTINCT SID FROM V$MYSTAT)
)
);

SQL语句执行过程
SQL完成执行过程
1、Syntactic：语法分析，这一步检查SQL语法结构。
2、Semantic：语义分析，查看对象是否存在，有否访问权限。
3、Parent Cursor：在Library Cache中检查语句的Cursor是否存在，存在就重用，把PGA的Private SQL Area做关联，然后直接执行（这个过程叫软解析）。如果没有就申请一个Parent Cursor，Parent Cursor就是用来保存SQL语句的。
4、View Merge：如果用户验收用到了视图，把视图语句合并到用户语句中。
5、Statement Transformation：把Complex SQL转换成Simple SQL，比如展开子查询。
（4、5也叫逻辑优化Logical Optimization）
6、Optimization：确定最佳访问路径。基于CBO来做，也叫做物理优化Physical Optimization。
7、QEP Generation：生成执行计划Query Execution Plan，产生Child Cursor，执行计划会保存在Child Cursor中，并和Parent Cursor相关联。在Child Cursor中保存的是执行计划和执行环境信息（优化器模式）。
（1~7这个部分叫做完整的硬解析）
8、SEP Execution：执行语句，生成结果。
每个SQL语句和它的执行计划都存在Library Cache中，Oracle对Library Cache的访问也就基于Hash方式。

Parent Cursor和Child Cursor
Parent Cursor只保存SQL语句的原始文本。
Child Cursor保存SQL语句的执行计划和环境信息。

Shared Pool中保存的一个SQL语句的执行计划可以有多个，每个计划是和具体条件相关的。

条件、优化器参数都会影响执行计划，使得一个SQL语句有不同的执行计划。ORACLE对这些计划和匹配的环境作为Child Cursor保存，并和Parent Cursor相关联。

Parent Cursor和Child Cursor都可以被重用，减少Parsing对CPU、内存、锁消耗。Parent Cursor保存的是SQL语句文本，因此Parent Cursor可重用条件是文本完全匹配，包括大小写，空格等。

可以用以下语句查看Cursor在SQLAREA中的情况：
SQL>SELECT SQL_ID, SQL_TEXT, EXECUTIONS FROM V$SQLAREA;

可以进行以下实验来证明同一语句(Parent Cursor)多个Child Cursor的情况：
SQL>ALTER SESSION SET OPTIMIZER_MOD=ALL_ROWS;
SQL>SELECT COUNT(*) FROM TABLEA;
SQL>ALTER SESSION SET OPTIMIZER_MOD=FIRST_ROW_10;
SQL>SELECT COUNT(*) FROM TABLEA;
SQL>SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, OPTIMIZER_MODE, PLAN_HASH_VALUE
FROM V$SQL
WHERE SQL_TEXT=’SELECT (*) FROM TABLEA’;
会有两条记录。

查看V$SQL_SHARED_CURSOR可以区分Child Cursor。原因可见_MISMATCH字段。环境因素不同，则相应值为Y。
SQL>SELECT * FROM V$SQL_SHARED_CURSOR WHERE SQL_ID=’XXXX’;

V$SQL和V$SQLAREA都记录了一个SQL语句运行的运行信息，包括SQL语句文本，执行次数，解析次数等。V$SQLAREA统计Parent Cursor的SQL执行信息，V$SQL是统计在Child Cursor上执行信息。

Cursor有关参数
OPEN_CURSOR参数
控制每个SESSION能够同时打开的CURSOR数量，超过了就会出现ORA-1000错误。
可以使用以下语句查看该参数设置和曾达到的最大值。
SQL>select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and p.name = ‘open_cursors’
group by p.value;

*注：代码中关闭了Cursor后，ORACLE把这部分资源标识为“closeable”，而非立即释放。直到新Cursor需要使用这部分空间的时候旧Cursor才会被释放。
“Currently Open Cursors”并不能区分“closeable”的Cursor。

如果要确定一个业务逻辑需要的Cursor数量，可以进行一下处理。先运行一次完整业务逻辑，然后运行如下代码：
SQL>select max(a.value) as cumu_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and p.name = ‘open_cursors’
and a.sid = (select max(sid) from v$mystat)
group by p.value;

SESSION_CACHED_CURSORS参数
默认为20，定义改参数后如果一个SQL语句被执行3次，这个CURSOR就被CACHE，免去了重新初始化过程，提高效率。
可以查看统计量“SESSION CURSOR CACHE HITS”和“PARSE COUNT(TOTAL)”。
前者代表CACHED CURSOR中SQL的数量（完全不PARSE）；后者代表总数量。
两者相减得到解析数量。

SQL>select cache.value cache_hits, prs.value all_parses, prs.value-cache.value sess_cur_cache_not_used
from v$sesstat cache, v$sesstat prs, v$statname nm1, v$statname nm2
where cache.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic# = nm2.statistic#
and nm2.name = 'parse count (total)'
and cache.sid = &sid and prs.sid = cache.sid;

CURSOR_SHARING参数
定义CURSOR共享模式，有三种EXACT（精确）、FORCE（强制）、SIMILAR（相似）。
缺省使用EXACT模式，系统部自动合并共享CURSOR，只有书写完全一致的才能共享。
SIMILAR，ORACLE会自动将非绑定变量转换为绑定变量，同时会做PEEKING（监控），如果判断是可以共享的就共享SQL。
如果某个WHERE条件里的字段存在柱状图，那么PEEKING过程会认为这个SQL的共享不安全，将不进行共享，而产生一个新的Child Cursor。这种情况下，只有非绑定变量的值是相同的，PARSER才认为共享是安全的，此时不产生新Child Cursor。

CURSOR_SPACE_FOR_TIME参数
缺省是FALSE，设成TRUE后SHARED SQL AREA当CURSOR打开的时候是驻留在共享池里的。PGA中的CURSOR的私有内存执行完SQL也不关闭，下次执行的时候直接使用。节省时间。坏处是占用大量内存，尤其在共享池碎片较为严重的情况下带来负面效应。通常不建议启用。PARSE频繁执行SQL频繁的情况下不建议启用。
-------------------------------------------------------------------------------------------------------------------------------------------------
关于自动PGA管理的进一步探讨
作者：eygle English
伴随自动PGA调整新特性的引入,Oracle随之引入了一系列新的视图,V$PGASTAT就是其中的一个.
在V$PGASTAT中有这样一个条目: global memory bound ,该条目记录数据库允许的最高PGA内存使用量,我们可以从不同的PGA参数设置来观察一下Oracle运行的PGA上限.
SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 10m
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=10m
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:03:04.12
…….
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC
-------------------------------------------------- ------------------ -----
LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
-------------------------- -------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO
.548828125 206 PASSES 62914560
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 30M
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=30M
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:00:53.30
………..
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024
-------------------------------------------------- ------------------ ----- --------------------------
LAST_EXECUTION LAST_TEMPSEG_SIZE
-------------------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO 1.48046875
6 PASSES 57671680
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 30
global memory bound 1.5
Elapsed: 00:00:00.00
我们可以注意到,PGA的global memory bound会一直处在5%的PGA_AGGREGATE_TARGET参数设置,直到5% PGA_AGGREGATE_TARGET超过100M,然后global memory bound被限制为100M,也就是满足我们前文提到的:
对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
注意,修改PGA_AGGREGATE_TARGET参数可以使用如下命令:
alter system set pga_aggregate_target=4096M ;
修改参数后,通常需要之行操作才能看到视图信息的变化:
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 20
global memory bound 1
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 40
global memory bound 2
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 1024
global memory bound 51.1992188
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 100
实际上这个100M的上限是受到了另外一个隐含参数的控制,该参数为_pga_max_size,该参数的缺省值为200M,单进程串行操作PGA的上限不能超过该参数的1/2.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
Enter value for par: pga_max
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%pga_max%'
NAME VALUE DESCRIB
--------------------------------------------- ---------------- ---
_pga_max_size 209715200 Maximum size of the PGA memory for one process
如果我们修改该参数, global memory bound将可以突破100M的上限:
SQL> alter system set "_pga_max_size"=400M;
System altered.
……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 200
对于PGA的控制,还有一系列的内部参数,列举如下,仅供参考:
SQL> l
1 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6* AND x.ksppinm LIKE '%&par%'
SQL> /
Enter value for par: smm
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%smm%'
NAME VALUE DESCRIB
------------------------ ----- ----------------------------------------------------------------
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 128 minimum work area size in auto mode
_smm_max_size 2560 maximum work area size in auto mode (serial)
_smm_px_max_size 15360 maximum work area size in auto mode (global)
_smm_bound 0 overwrites memory manager automatically computed bound
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
11 rows selected.
历史上的今天...
>> 2016-04-09文章:
回顾学习展望：读书会分享我的Oracle数据库历程
>> 2013-04-09文章:
Oracle 2012年数据库市场份额48.3 - 再居第一
>> 2012-04-09文章:
数据安全防范 提升需从今日始 - 浅析数据安全
>> 2009-04-09文章:
使用DATAPUMP导致ORA-00600 17020错误
Oracle如何维护SMON_SCN_TIME表？
>> 2008-04-09文章:
数据字典表之：DBA_HIGH_WATER_MARK_STATISTICS
数据字典表之：DBA_TABLES
并行查询并行度Degree与instances 设置
By eygle on 2006-04-09 16:47 | Comments (12) | Internal | 736 |