oracle pga建议值,Oracle PGA作用

专有模式下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频繁的情况下不建议启用。