1、全面分析 PGA全面分析 PGA个人分类：oracle 1.2. PGA (The Process Global Area)PGA（Program Global Area 程序全局区）是一块包含一个服务进程的数据和控制信息的内存区域。它是 Oracle 在一个服务进程启动是创建的，是非共享的。一个 Oracle 进程拥有一个 PGA 内存区。一个PGA 也只能被拥有它的那个服务进程所访问，只有这个进程中的Oracle 代码才能读写它。因此，PGA 中的结构是不需要 Latch 保护的。我们可以设置所有服务进程的 PGA 内存总数受到实例分配的总体PGA（Aggregated PGA）限制。在专
2、有服务器（Dedicated Server）模式下，Oracle 会为每个会话启动一个 Oracle 进程；而在多线程服务（Multi-Thread Server MTS）模式下，由多个会话共享通一个 Oracle 服务进程。PGA 中包含了关于进程使用到的操作系统资源的信息，以及一些关于进程状态的信息。而关于进程使用的 Oracle 共享资源的信息则是在 SGA 中。这样做可以使在进程以外中止时，能够及时释放和清除这些资源。1.2.1. PGA 的组成PGA 由两组区域组成：固定 PGA 和可变 PGA（或者叫 PGA 堆，PGA Heap【堆Heap 就是一个受管理的内存区】 ） 。固定
3、PGA 和固定SGA 类似，它的大小时固定的，包含了大量原子变量、小的数据结构和指向可变 PGA 的指针。可变 PGA 时一个内存堆。它的内存段可以通过视图 X$KSMPP（另外一个视图 X$KSMSP 可以查到可变 SGA 的内存段信息，他们的结构相同）查到。PGA 堆包含用于存放 X$表的的内存（依赖与参数设置，包括 DB_FILES、CONTROL_FILES） 。总的来说，PGA 的可变区中主要分为以下三部分内容：o 私有 SQL 区；o 游标和 SQL 区o 会话内存1.2.1.1. 私有 SQL 区（Private SQL Area）前面已经说过，私有 SQL 区包含了绑定变量值和
4、运行时期内存结构信息等数据。每一个运行 SQL 语句的会话都有一个块私有 SQL 区。所有提交了相同 SQL 语句的用户都有各自的私有 SQL 区，并且他们共享一个共享 SQL 区。因此，一个共享 SQL 区可能和多个私有共享区相关联。一个游标的私有 SQL 区又分为两个生命周期不同的区：o 永久区。包含绑定变量信息。当游标关闭时被释放。o 运行区。当执行结束时释放。创建运行区是一次执行请求的第一步。对于 INSERT、UPDATE 和DELETE 语句，Oracle 在语句运行结束时释放运行区。对于查询操作，Oracle 只有在所有记录被 fetch 到或者查询被取消时释放运行区。1.2.1
5、.2. 游标和 SQL 区（Cursors and SQL Areas）一个 Oracle 预编译程序或 OCI 程序的应用开发人员能够很明确的打开一个游标，或者控制一块特定的私有 SQL 区，将他们作为程序运行的命名资源。另外，oracle 隐含的为一些 SQL 语句产生的递归调用（前面有介绍，读取数据字典信息）也使用共享 SQL 区。私有 SQL 区是由用户进程管理的。如何分配和释放私有 SQL 区极大的依赖与你所使用的应用工具。而用户进程可以分配的私有 SQL 区的数量是由参数 OPEN_CURSORS 控制的，它的默认值是 50。在游标关闭前或者语句句柄被释放前，私有 SQL 区将一直
6、存在（但其中的运行区是在语句执行结束时被释放，只有永久区一直存在）下去。应用开发人员可以通过将所有打开的不再使用的游标都关闭来释放永久区，以减少用户程序所占用的内存。1.2.1.3. 会话内存（Session Memory）会话内存是一段用于保存会话变量（如登录信息）和其他预会话相关信息的内存。对于共享服务器模式下，会话内存是共享的，而不是私有的。对于复杂的查询（如决策支持系统中的查询） ，运行区的很大一部分被那些内存需求很大的操作分配给 SQL 工作区（SQL Work Area） 。这些操作包括：o 基于排序的操作（ORDER BY、GROUP BY、ROLLUP、窗口函数） ；o Has
7、h Joino Bitmap mergeo Bitmap create例如，一个排序操作使用工作区（这时也可叫排序区 Sort Area）来将一部分数据行在内存排序；而一个 Hash Join 操作则使用工作区（这时也可以叫做 Hash 区 Hash Area）来建立 Hash 表。如果这两种操作所处理的数据量比工作区大，那就会将输入的数据分成一些更小的数据片，使一些数据片能够在内存中处理，而其他的就在临时表空间的磁盘上稍后处理。尽管工作区太小时，Bitmap 操作不会将数据放到磁盘上处理，但是他们的复杂性是和工作区大小成反比的。因此，总的来说，工作区越大，这些操作就运行越快。工作区的大小是可
8、以调整的。一般来说，大的工作区能让一些特定的操作性能更佳，但也会消耗更多的内存。工作区的大小足够适应输入的数据和相关的 SQL 操作所需的辅助的内存就是最优的。如果不满足，因为需要将一部分数据放到临时表空间磁盘上处理，操作的响应时间会增长。1.2.2. PGA 内存自动管理SQL 工作区可以是自动的、全局的管理。DBA 只要设置参数PGA_AGGREGATE_TARGET 给一个实例的 PGA 内存指定总的大小。设置这个参数后，Oracle 将它作为一个总的全局限制值，尽量使所有Oracle 服务进程的 PGA 内存总数不超过这个值。在这个参数出现之前，DBA 要调整参数 SORT_AREA_
9、SIZE、 HASH_AREA_SIZE,、BITMAP_MERGE_AREA_SIZE 和CREATE_BITMAP_AREA_SIZE（关于这些参数，我们会在后面介绍） ，使性能和 PGA 内存消耗最佳。对这些参数的调整是非常麻烦的，因为即要考虑所有相关的操作，使工作区适合它们输入数据大小，又要使 PGA 内存不消耗过大导致系统整体性能下降。9i 以后，通过设置了参数 PGA_AGGREGATE_TARGET，使所有会话的工作区的大小都是自动分配。同时，所有*_AREA_SIZE 参数都会失效。在任何时候，实例中可用于工作区的 PGA 内存总数都是基于参数PGA_AGGREGATE_TAR
10、GET 的。工作区内存总数等于参数PGA_AGGREGATE_TARGET 的值减去系统其他组件（如分配给会话的PGA 内存）的内存消耗。分配给 Oracle 进程的 PGA 内存大小是根据它们对内存的需求情况来的。参数 WORKAREA_SIZE_POLICY 决定是否使用 PGA_AGGREGATE_TARGET来管理 PGA 内存。它有两个值：AUTO 和 MANUAL。默认是 AUTO，即使用 PGA_AGGREGATE_TARGET 来管理 PGA 内存。其实，从参数WORKAREA_SIZE_POLICY 的名字上可以看出，Oracle 的 PGA 内存自动管理只会调整工作区部分，
11、而非工作区部分（固定 PGA 区）则不会受影响。还有注意一点就是：10g 之前，PGA_AGGREGATE_TARGET 只在专用服务模式下生效。而 10g 以后，PGA 内存自动管理在专有服务模式（Dedicated Server）和 MTS 下都有效。另外，9i 在 OpenVMS 系统上还不支持 PGA 内存自动管理，但 10g 支持。设置了 PGA_AGGREGATE_TARGET 以后，每个进程 PGA 内存的大小也是受限制的：o 串行操作时，每个进程可用的 PGA 内存为MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隐含参数_p
12、ga_max_size 的默认值是 200M，同样不建议修改它。o 并行操作时，并行语句可用的 PGA 内存为PGA_AGGREGATE_TARGET * 30% / DOP （Degree Of Parallelism 并行度） 。1.2.3. 专有服务（Dedicated Server）和共享服务（Shared Server）对 PGA 内存的管理和分配，很大程度上依赖与服务模式。下面这张表显示了在不同模式下，PGA 内存不同部分的分配的异同：内存区专有服务共享服务会话内存 私有的 共享的永久区所在区域 PGA SGASELECT 语句的运行区所在区域 PGA PGADML/DDL 语句的
13、运行区所在区域 PGA PGA1.2.4. 重要参数PGA 的管理和分配是由多个系统参数控制的，下面介绍一下这些参数：1.2.4.1. PGA_AGGREGATE_TARGET这个参数前面介绍了。它控制了所有进程 PGA 内存的总的大小。在专有服务模式下，推荐使用 PGA_AGGREGATE_TARGET。PGA_AGGREGATE_TARGET 的取值范围是 10M（4096G - 1 ）bytes。对于 PGA_AGGREGATE_TARGET 大小的设置，Oracle 提供了一个以下建议方案（参见 Metalink Note: 223730.1）：o 对于 OLTP 系统，PGA_AGG
14、REGATE_TARGET = (物理内存大小 * 80%) * 20%o 对于 DSS 系统，PGA_AGGREGATE_TARGET = (物理内存大小 * 80%) * 50%例如，你的系统是一个 OLTP 系统，物理内存为 8G，那么推荐PGA_AGGREGATE_TARGET 设置为 (8 * 80%) * 20% = 1.28G。1.2.4.2. WORKAREA_SIZE_POLICY参数 WORKAREA_SIZE_POLICY 决定是否使用 PGA_AGGREGATE_TARGET来管理 PGA 内存。它有两个值：AUTO 和 MANUAL。默认是 AUTO，即使用 PGA_
15、AGGREGATE_TARGET 来管理 PGA 内存。1.2.4.3. sort_area_sizeOracle 在做排序操作（ORDER BY、GROUP BY、ROLLUP、窗口函数）时，需要从工作区中分配一定内存区域对数据记录做内存排序。在排序完成后，数据返回之前，Oracle 会释放这部分内存，。SORT_AREA_SIZE 指定了这部分内存的大小。设置了PGA_AGGREGATE_TARGET 后，该参数无效。除非在共享服务模式下，一般不推荐设置这个参数，而推荐使用PGA_AGGREGATE_TARGET 进行 PGA 内存自动管理。如果需要设置此参数，可以考虑设置在 1M3M。O
16、racle 也许会为一个查询分配多个排序区。通常情况下，一条语句只有 1、2 个排序操作，但是对于复杂语句，可能存在多个排序操作，每个排序操作都有自己的排序区。因此，语句的复杂性也影响到每个进程 PGA 内存的大小。1.2.4.4. sort_area_retained_size这个参数与 SORT_AREA_SIZE 配合使用。它指定了在排序操作完成后，继续保留用户全局区（User Global Area UGA，关于 UGA 与PGA、SGA 关系在 UGA 部分介绍）内存的最大大小，以维护内存中的排序，直到所有数据行被返回后才释放（上面提到，SORT_AREA_SIZE 的内存在排序完成
17、、数据行返回之前被释放）回UGA（注意：是释放回 UGA，而不会被操作系统回收） 。SORT_AREA_RETAINED_SIZE 在共享服务中是从 SGA 中分配的（因为此时 UGA 从 SGA 中分配） ，在专有服务模式中是从 PGA 中分配的。而SORT_AREA_SIZE 无论在那种模式下都从 PGA 中分配。同样，设置了 PGA_AGGREGATE_TARGET 后，该参数无效。1.2.4.5. hash_area_sizeHASH_AREA_SIZE 设置了在做 Hash Join 时，hash 内存表可占用的内存空间。同样，设置了 PGA_AGGREGATE_TARGET 后，该
18、参数无效。它的默认值大小是 sort_area_size 的 1.5 倍。此外，由于 Hash Join 只有在优化器为 CBO（Cost-Base Optimizer）模式下才有效，因此这个参数也只有 CBO 模式下才有意义。1.2.4.6. hash_join_enable这个参数决定是否启用 Hash Join。默认为 TRUE。由于 Hash Join 只有在优化器为 CBO（Cost-Base Optimizer）模式下才有效，因此这个参数也只有 CBO 模式下才有意义。10g 中，这个参数是隐含参数。1.2.4.7. bitmap_merge_area_size在使用位图索引（Bi
19、tmap Index）时，oracle 为索引位图段建立一张位图。在进行位图索引扫描时，需要将扫描到的位图索引排序后与位图合并（Merge） ，Oracle 会在 PGA 中开辟一片区域用于排序和合并。参数 BITMAP_MERGE_AREA_SIZE 指定了这篇区域的大小。默认值是 1M。同样，设置了 PGA_AGGREGATE_TARGET 后，该参数无效。1.2.4.8. create_bitmap_area_size在字段的集的势（Cardinality 参照记录行数，字段的不同值的一个因子。记录数越多，不同值越少，则集的势越小）很小，并且表的数据变化不大时，可以考虑为字段建立位图索引
20、以提高对该字段的检索效率。这个参数指定可在创建位图索引时的内存空间占用大小。它的默认大小是 8M。同样，设置了 PGA_AGGREGATE_TARGET 后，该参数无效。1.2.4.9. open_cursors这个参数设置一个会话可以同时打开的游标数。由于每打开一个游标，都需要一部分 PGA 内存分配出来作为私有 SQL 区。因此这个参数也影响了每个进程的 PGA 内存的占用大小。1.2.4.10. _pga_max_size这是一个隐含参数。它规定了一个 PGA 的最大大小。可参见1.2.2。1.2.5. 重要视图1.2.5.1. V$PGASTAV$PGASTAT 提供了 PGA 内存使
21、用情况的统计信息和当自动 PGA 内存管理启动时的统计信息。视图里面的累加数据是自从实例启动后开始累加的。字段数据类型说明NAMEVARCHAR2(64)统计的名称，包括：aggregate PGA target parameter 当前参数PGA_AGGREGATE_TARGET 的值。如果参数没有设置，则值为 0 并且PGA 内存自动管理被关闭。aggregate PGA auto target 在自动管理模式下，可用于工作区的总的 PGA 内存数。这个数值是动态的，和 PGA_AGGREGATE_TARGET 的值以及当前工作区的负载有关，Oracle 会动态调整它。这个值相对与 PGA
22、_AGGREGATE_TARGET 来说很小。其他很大一部分的PGA 内存都被用于系统的其他组件（如 PLSQL 和 Java 的内存） 。DBA必须保证在自动模式下有足够的 PGA 内存用于工作区。global memory bound 自动模式下可用的工作区的最大大小。Oracle 根据当前工作区的负载动态调整这个值。当系统中活动的工作区数量增加时，global memory bound 一般会下降。如果 global bound 降到低于 1M，则要考虑增加 PGA_AGGREGATE_TARGET 了。total PGA allocated 当前实例分配的总的 PGA 内存大小。Ora
23、cle 会试图保持这个值在 PGA_AGGREGATE_TARGET 以内。然而，当工作区负载增加得非常快或者 PGA_AGGREGATE_TARGET 被设置得很小时，这个值也许会在一段时间内超过 PGA_AGGREGATE_TARGET。total PGA used 当前被工作区消耗得 PGA 内存。这个数值也可以用于计算有多少 PGA 内存被其他组件（如 PLSQL 或 Java）所消耗。total PGA used for auto workareas 自动模式下，当前多少PGA 内存被工作区所消耗。这个数值也可以用于计算有多少 PGA 内存被其他组件（如 PLSQL 或 Java）所
24、消耗。total PGA used for manual workareas 手动模式下，当前多少PGA 内存被工作区所消耗。这个数值也可以用于计算有多少 PGA 内存被其他组件（如 PLSQL 或 Java）所消耗。over allocation count 这个数值是自从实例启动后累加的。当PGA_AGGREGATE_TARGET 设置非常小或工作区负载增长很快时，会超额分配 PGA 内存（分配的值大于 PGA_AGGREGATE_TARGET） 。这种情况发生时，Oracle 不能限制 PGA 内存小于 PGA_AGGREGATE_TARGET，只能分配实际需要的 PGA 内存。此时，建
25、议通过建议器视图V$PGA_TARGET_ADVICE 来增加 PGA_AGGREGATE_TARGET 的大小。bytes processed 自从实例启动后，被内存 SQL 操作处理的字节数。extra bytes read/written 自从实例启动后，需要额外输入数据所处理的字节数。当工作区无法在最佳状态下运行时，就需要进行这个额外处理。cache hit percentage Oracle 计算出来的一个与 PGA 内存组件性能相关的数据，是自从实例启动后累加的。如果这个值是 100%，则表示实例启动后，所有系统使用到的工作区都分配了最佳的 PGA内存。当工作区无法在最佳状态下运行
26、，就需要进行额外的数据输入处理，这将会降低 cache hit percentage。VALUENUMBER统计数据UNITSVARCHAR2(12)数据的单位 (microseconds, bytes, or percent)1.2.5.2. V$PGA_TARGET_ADVICE这个视图是可以显示 PGA 优化建议器的估算预测结果，它显示了在各种 PGA_AGGREGATE_TARGET 值时，V$PGASTAT 可能会显示的 PGA 性能统计数据。选取所用来预测的 PGA_AGGREGATE_TARGET 值是当前PGA_AGGREGATE_TARGET 左右的的值。而估算出的统计值是根
27、据实例启动后的负载模拟出来的。只有当建议器打开（隐含参数_smm_advice_enabled 为 TRUE） ，并且参数 STATISTICS_LEVEL 值不是 BASIC 时，视图中才会有内容。实例重启后，所有预测数据都会被重写。字段数据类型说明PGA_TARGET_FOR_ESTIMATENUMBER用于预测的 PGA_AGGREGATE_TARGET 值。PGA_TARGET_FACTORNUMBER预测的 PGA_AGGREGATE_TARGET 与当前 PGA_AGGREGATE_TARGET 的比。ADVICE_STATUSVARCHAR2(3)建议器状态（ON 或 OFF）B
28、YTES_PROCESSEDNUMBER预测的被所有工作区处理的字节数。ESTD_EXTRA_BYTES_RWNUMBER当 PGA_AGGREGATE_TARGET 设置为预测值时，需要额外读写的字节数。ESTD_PGA_CACHE_HIT_PERCENTAGENUMBER当 PGA_AGGREGATE_TARGET 设置为预测值时，缓存命中率。这个值等于 BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)ESTD_OVERALLOC_COUNTNUMBER当 PGA_AGGREGATE_TARGET 设置为预测值时，需要超额分
29、配的 PGA内存。如果非 0 则说明 PGA_AGGREGATE_TARGET 设置得太小。1.2.5.3. V$SYSSTAT 、V$SESSTAT这两个视图显示了系统（会话）的统计数据。他们的统计项目基本相同，但不同之处在于一个是系统级的、一个是会话级的。通过这两个视图我们可以查出像 sort 这样操作对工作区的使用情况：SQL select * from V$SYSSTAT 2 where name like %sort%; STATISTIC# NAME CLASS VALUE- - - - 245 sorts (memory) 64 2876455 246 sorts (disk)
30、64 483 247 sorts (rows) 64 116554720 SQL1.2.5.4. V$SQL_WORKAREA这个视图显示了被 SQL 游标使用的工作区的信息。存储在 Shared Pool 中的每条 SQL 语句都有一个或多个子游标，它们能被 V$SQL 显示。而 V$SQL_WORKAREA 显示需要被这些游标所使用的工作区信息。可以将它与 V$SQL 进行 join 查询。通过这个视图可以解决以下一些问题：1、 请求最多的工作区；2、 在自动模式下，占用内存最多的工作区。字段数据类型说明ADDRESSRAW(4 | 8)游标句柄的地址。HASH_VALUENUMBER游标
31、句柄的 Hash 值。这个字段和 ADDRESS 字段 join V$SQLAREA 可以定位出相关语句。CHILD_NUMBERNUMBER使用此工作区的子游标数。WORKAREA_ADDRESSRAW(4 | 8)工作区句柄的地址。唯一定位了一条记录OPERATION_TYPEVARCHAR2(20)工作区的操作类型(SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE)OPERATION_IDNUMBER唯一定位查询计划中的一个操作的值，可以和视图 V$SQL_PLAN join。POLICYVARCHA
32、R2(10)工作区的模式(MANUAL 或 AUTO)ESTIMATED_OPTIMAL_SIZENUMBER估计需要此工作区来执行内存中操作的所需的大小。ESTIMATED_ONEPASS_SIZENUMBER估计需要此工作区来一次执行内存中操作的所需的大小。LAST_MEMORY_USEDNUMBER最后一次执行游标所使用的工作区大小。LAST_EXECUTIONVARCHAR2(10)最后一次执行游标，工作区请求内存的方式，OPTIMAL, ONE PASS, ONE PASS 或 MULTI-PASS。LAST_DEGREENUMBER最后一次执行并行操作的并行度（Degree of
33、parallelism DOP） 。TOTAL_EXECUTIONSNUMBER此工作区激活的次数。OPTIMAL_EXECUTIONSNUMBER此工作区运行于 optimal 模式的次数ONEPASS_EXECUTIONSNUMBER此工作区运行于 one-pass 模式的次数MULTIPASSES_EXECUTIONSNUMBER此工作区运行于 one-pass 内存请求情况下的次数ACTIVE_TIMENUMBER此工作区激活的评价时间数。MAX_TEMPSEG_SIZENUMBER实例化此工作区所创建的临时段的最大大小。LAST_TEMPSEG_SIZENUMBER最后一次实例化此工
34、作区所创建的临时段的大小。1.2.5.5. V$SQL_WORKAREA_ACTIVE这个视图包含了系统当前分配的工作区的瞬间信息。可以通过字段WORKAREA_ADDRESS join V$SQL_WORKAREA 来查询工作区信息。如果工作区溢出到磁盘，则这个视图就包含了这个工作区所溢出的临时段的信息。通过与视图 V$TEMPSEG_USAGE join，可以得到更多的临时段信息。这个视图可以解决以下问题：1、 当前系统分配最大的工作区；2、 超额分配内存的百分比（EXPECTED_SIZE ACTUAL_MEM_USED） ；3、 哪个活动的工作区使用了超出内存管理预期的内存大小；4、
35、那个活动的工作区溢出到磁盘了。字段数据类型说明WORKAREA_ADDRESSRAW(4 | 8)工作区句柄的地址。唯一定位了一条记录。OPERATION_TYPEVARCHAR2(20)使用此工作区的操作 (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, 或 BITMAP CREATE)OPERATION_IDNUMBER唯一定位查询计划中的一个操作的值，可以和视图 V$SQL_PLAN join。POLICYVARCHAR2(6)工作区的模式(MANUAL 或 AUTO)SIDNUMBER会话 IDQCINST_IDNUMBER查询协
36、调（查询协调在并行查询中出现）者实例 ID。QCSIDNUMBER查询协调者的会话 ID。ACTIVE_TIMENUMBER此工作区激活的平均时间（厘秒为单位）WORK_AREA_SIZENUMBER被当前操作使用的最大工作区大小。EXPECTED_SIZENUMBER工作区的预期大小。预期大小是内存管理器设置的。当WORK_AREA_SIZE 大于 EXPECTED_SIZE 时，内存会超额分配。ACTUAL_MEM_USEDNUMBER当前分配个工作区的 PGA 内存大小（KB） 。这个值在 0 和 WORK_AREA_SIZE 之间。MAX_MEM_USEDNUMBER这个工作区使用的最
37、大大小（KB） 。NUMBER_PASSESNUMBER这个工作区的通道数(如果在 OPTIMAL 模式下为 0)TEMPSEG_SIZENUMBER用于此工作区的临时段大小。TABLESPACEVARCHAR2(31)创建临时段给这个工作区的表空间名字。SEGRFNO#NUMBER创建临时段的表空间的文件 ID。SEGBLK#NUMBER给工作区创建临时段的 block 数。1.2.5.6. V$PROCESS这个视图显示了所有 Oracle 进程的信息。其中以下几个字段则说明了进程 PGA 内存的使用情况。PGA_USED_MEM：进程使用的 PGA 内存PGA_ALLOCATED_MEM：分配给进程的 PGA 内存PGA_MAX_MEM：进程使用的最大的 PGA 内存本篇文章来源于 Linux 公社网站() 原文链接：http:/
