进程全局区域（Process Global Area），通常称为程序全局区域（PGA：Program Global Area），位于服务器进程的进程私有内存。它包含全局变量、数据服务器进程的结构和控制信息。此类信息的示例是游标的运行时区域。每次执行游标时，都会在执行该游标的服务器进程的 PGA 内存区域中为该游标创建一个新的运行时区域。 # 版本说明 在 Oracle8i 中，管理员通过小心地调整一些初始化参数来调整 PGA 的大小，比如 SORT_AREA_SIZ、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、CREATE_BITMAP_AREA_SIZE 等。 从 Oracle 9i 开始，提供了一个选项来完全自动化 PGA 内存的管理。管理员只需要使用新引入的初始化参数 PGA_AGGREGATE_TARGET，指定一个实例可用的最大 PGA 内存量。 直至 9iR2，PGA_AGGREGATE_TARGET 参数控制所有专用服务器连接的工作区大小，但它对共享服务器（也就是MTS）连接没有影响，在这种情况下，*_AREA_SIZE 参数将优先。 在 10g 中，PGA_AGGREGATE_TARGET 控制由专用连接和共享连接分配的工作区。 从 11g 开始，自动内存管理（AMM）扩展到管理 SGA 和 PGA 内存。在 PGA 内存的内存压力下，SGA 内存将被重新分配，以供进程使用，以满足工作区的需要。另一方面，如果 PGA 内存被分配，内存可以被添加到 SGA 中的自动调优组件中，超出原来的 SGA 配置。 注意：对于 AMM，为 PGA_AGGREGATE_TARGET 设置一个显式值将作为一个最小设置，AMM 将不会缩小到这个值以下。请参阅注：443746.1了解更多信息。 设置 PGA_AGGREGATE_TARGET ================================== 我们设置这个参数时遇到的第一个问题是它的最佳值是多少? 为了确定 PGA_AGGREGATE_TARGET 参数的适当设置，建议遵循以下步骤 1. 根据以下规则对PGA_AGGREGATE_TARGET进行第一次估计 - OLTP 系统 PGA_AGGREGATE_TARGET = (物理内存* 80%)* 20% - DSS 系统 PGA_AGGREGATE_TARGET =(物理内存* 80%)* 50% 2. 调优 PGA_AGGREGATE_TARGET 的第二步是使用可用的 PGA 统计信息监视性能，看看 PGA_AGGREGATE_TARGET 的大小是否过大。 # UGA & CGA ### UGA (The User Global Area) 在专用服务（Dedicated Server）模式下，一个服务进程与一个用户进程相关，PGA 就包括了堆空间和 UGA。而UGA（User Global Area 用户全局区）由用户会话数据、游标状态和索引区组成。在共享服务（MTS）模式下，UGA 是 Shared Pool 或 Large Pool的一部分（依赖与配置）。 在专用服务模式下，进程和会话是一对一的；而在MTS模式下，进程和会话是一对多的关系。PGA 是服务于进程的，它包含的是进程的信息；而 UGA 是服务于会话的，它包含的是会话的信息。因此，MTS 模式下，PGA 和 UGA之间的关系也是一对多的。 UGA中包含了一个会话的信息，包括： 1. 打开游标的永久区和运行区； 2. 包的状态信息，特别是包的变量； 3. Java会话的信息； 4. 激活的角色； 5. 激活的跟踪事件（ALTER SESSION SET EVENT …）； 6. 起作用的 NLS 参数（SELECT * FROM NLS_SESSION_PARAMETERS;）； 7. 所有打开的db link； 8. 会话对于信任的Oracle的托管访问标记（mandatory access control (MAC） UGA 由两组区组成，固定UGA和可变UGA（或者说UGA堆）。固定 UGA 包含了大概 70 个原子变量、小的数据结构以及指向 UGA 堆的指针。 UGA heap 中的段可以通过表 X$KSMUP 查到（它的结构和 X$KSMSP 相同）。UGA 堆包含了存储一些固定表（X$表）的永久内存（依赖与特定参数的设置，如 OPEN_CURSORS，OPEN_LINKS 和 MAX_ENABLED_ROLES）。除此以外，大部分的 UGA 用于私有 SQL 区。UGA 内存的所在依赖于会话的设置。在专用服务模式下，会话和进程是一对一的关系，UGA 位于 PGA 中。固定 UGA 是 PGA 中的一段内存段，而 UGA堆是 PGA 的子堆。在 MTS 模式下，固定 UGA 是 shared pool 中的一段内存段，而 UGA 堆是 Large Pool 的子堆，如果从 large pool 分配失败，则从 shared pool 中分配。 MTS 模式下，可以通过 Profile 中的 PRIVATE_SGA 项（通过 dba_profiles 查看）来控制每个 UGA 占用的 SGA 的总的大小，但是不建议这样做。 ### CGA (The Call Global Area) 与其他的全局区不同，CGA（Call Global Area 调用全局区）的存在是瞬间的。它只存在于一个调用过程中。对于实例的一些低层次的调用需要 CGA，包括： 1. 解析一条 SQL 语句； 2. 执行一条 SQL 语句； 3. 取一条 SELECT 语句的输出值。 如果语句产生了递归调用，则需要为每个递归调用分配一个 CGA。如上所述，递归调用是在语句解析、优化器产生语句查询计划、DML 操作时需要查询或修改数据字典信息的调用。 无论 UGA 存在于 PGA 还是 SGA，CGA 都是 PGA 的 subheap。因为无论那种模式，会话在做调用时总需要一个进行进行处理。这一点很重要，特别是在 MTS 模式下时，如果发现一次调用很久没有响应，则可能需要增加 PGA 的大小。 当然，调用并不是只通过 CGA 中的数据结构来工作。实际上，调用所需要的大部分的重要数据结构都来自于 UGA。例如私有 SQL 取和排序区都存放在 UGA 中，因为调用结束后，它们是被保留的。CGA 中只包含了那些调用结束后可以被释放的数据。例如，CGA 中包含了直接 IO 缓存、关于递归调用的信息、用于表达式评估（产生查询计划时）的的堆空间和其他一些临时数据。 Java 调用内存也分配在 CGA中。它被分为三部分空间：堆空间、新空间和老空间。在调用期间（调用长短依赖于使用期长短和大小），在新空间和老空间中的内存段不再使用的内存段将被垃圾收集器回收。 ### realfree heap Oracle 9.2 以后，有一个新的隐含参数：_use_realfree_heap。当设置这个参数为 true 时，Oracle 会为 CGA、UGA 单独分配堆，而不从 PGA 中分配。它的默认值为 false，而当设置了 pga_aggregate_target 后，它的值自动被改为 true。 在 Oracle 12c 中，参数\_realfree_heap_pagesize_hint 被修改为 \_realfree_heap_pagesize，通过在 init/spfile 中设置以下参数来调整数据库内的 realfree 堆页面大小，并重新启动数据库。 ``` _use_realfree_heap = TRUE _realfree_heap_pagesize_hint = 262144 ``` # 性能视图 ### V$SYSSTAT 和 V$SESSTAT 以下统计数据将有助于监测和调优： - workarea memory allocated：PGA 内存的总数量专用于工作区域分配的 Kb。 - workarea executions - optimal：具有最优大小的工作区域的累计计数。例如，如果排序不需要溢出到磁盘，则定义最佳大小 - workarea executions - onepass：使用一次通过大小的工作区的累计计数。一次通道通常用于大的工作区域，在那里不能避免溢出到磁盘。 - workarea executions - multipass：在多个通道中运行的工作区的累计计数。这应该避免，这是系统调优不佳的症状。 工作区执行是最优的，一次执行或多次执行。 **使用 V$SYSTAT 监控私有内存** 下面查看当前系统中 optimal、onepass、multipass 几种不同排序下所占的比率，关于排序应当尽可能的为 optimal，避免过多的 multipass ```sql SELECT NAME profile, cnt, decode(total, 0, 0, round(cnt * 100 / total, 4)) percentage FROM (SELECT NAME, VALUE cnt, (SUM(VALUE) over()) total FROM V$SYSSTAT WHERE NAME LIKE 'workarea exec%'); PROFILE CNT PERCENTAGE ---------------------------------------- ---------- ---------- workarea executions - optimal 1113054 99.999 workarea executions - onepass 11 .001 workarea executions - multipass 0 0 ``` 下面查看内存排序占总排序的比率，此值越高越好。如此值较小，应结合 v$pga_target_advice 考虑调整 PGA ```sql SELECT round(m.value / (m.value + d.value), 4) * 100 || '%' memory_disk_sort_ratio FROM (SELECT value FROM v$sysstat WHERE NAME = 'sorts (memory)') m, (SELECT value FROM v$sysstat WHERE NAME = 'sorts (disk)') d; MEMORY_DISK_SORT_RATIO ----------------------------------------- 100% ``` ### V$PGASTAT 该视图提供了关于 PGA 内存使用情况和自动 PGA 内存管理器的实例级统计信息。例如： ```sql SELECT * FROM V$PGASTAT; NAME VALUE -------------------------------------------------- ------------------ aggregate PGA target parameter 524288000 bytes aggregate PGA auto target 463435776 bytes global memory bound 25600 bytes total PGA inuse 9353216 bytes total PGA allocated 73516032 bytes maximum PGA allocated 698371072 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 560744448 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes total bytes processed 4.0072E+10 bytes total extra bytes read/written 3.1517E+10 bytes cache hit percentage 55.97 percent ``` - aggregate PGA target parameter：当前PGA_AGGREGATE_TARGET的值 - aggregate PGA auto target：剩余的能被工作区（workarea）使用的内存（AUTO模式），aggregate PGA auto target 不应该比 PGA_AGGREGATE_TARGET 的值小。您必须确保为在自动模式下运行的工作区域留有足够的 PGA 内存。 - global memory bound：单条 SQL 串行操作的最高 PGA 内存使用量（AUTO模式） - total PGA inuse：正被耗用的PGA（包括 workarea pl/sql 等所有占用的 PGA） 这给出了使用中的 PGA 内存总数。这个的细节 value 可以在 v$process 视图的 PGA_USED_MEM 列中找到。 - total PGA allocated：当前实例已分配的 PGA 内存总量 - maximum PGA allocated：PGA 曾经扩张到的最大值 - total freeable PGA memory：PGA 的可释放的大小 - process count：当前 process - max processes count：最大时候的 process - PGA memory freed back to OS：PGA 返回给操作系统的累计值 - total PGA used for auto workareas：PGA 分配给 auto workareas 的总大小 用于自动工作区的总 PGA：这提供了系统使用的实际可调 PGA 内存。用于自动工作区的最大 PGA 给出了自实例启动以来之前的统计所达到的最大值。 - maximum PGA used for auto workareas：PGA 可以分配给 auto workareas 大小的峰值 - total PGA used for manual workareas：PGA 分配给 manual workareas 的总大小 - maximum PGA used for manual workareas：PGA 可以分配给 manual workareas 大小的峰值 - over allocation count：实例启动后，发生的分配次数，如果这个值大于 0，就要考虑增加 PGA 的值 - bytes processed：PGA 使用的字节 - cache hit percentage：命中率 $$ PGA 缓存命中率 = (处理的总字节数 * 100) / (处理的总字节数 + 读取/写入的总额外字节数) $$ ### V$SQL_WORKAREA_HISTOGRAM 该视图显示了自实例启动以来，使用最佳内存大小、单次内存大小和多次内存大小执行的工作区域数量。该视图中的统计信息被细分为多个 bucket，这些 bucket 由工作区域的最佳内存需求定义。每个 bucket 由列LOW_OPTIMAL_SIZE 和 HIGH_OPTIMAL_SIZE 的值指定的最优内存需求范围标识。 **例一** 以下查询显示所有非空存储桶的统计信息。 ```sql SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb, optimal_executions, onepass_executions, multipasses_executions FROM v$sql_workarea_histogram WHERE total_executions != 0; LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS ------ ------- ------------------ ------------------ ---------------------- 8 16 156255 0 0 16 32 150 0 0 32 64 89 0 0 64 128 13 0 0 128 256 60 0 0 256 512 8 0 0 512 1024 657 0 0 1024 2048 551 16 0 2048 4096 538 26 0 4096 8192 243 28 0 8192 16384 137 35 0 16384 32768 45 107 0 32768 65536 0 153 0 65536 131072 0 73 0 131072 262144 0 44 0 262144 524288 0 22 0 ``` - OPTIMAL_EXECUTIONS：是不需要通过临时表空间交换的操作的次数。 - ONEPASS_EXECUTIONS：是需要进行一次临时表空间交换的操作次数。 - MULTIPASSES_EXECUTIONS：是需要多次临时表空间交换的操作次数。 原则上，如果 100％ 的操作都是内存操作，是最佳的选择。如果内存不足，某些大型操作是 ONEPASS 的，也是可以接受的。 出现 MULTIPASSES 的操作会引起性能的急剧下降，此时应考虑调整 PGA target。 **例二** 您还可以使用 V$SQL_WORKAREA_HISTOGRAM 来查找自启动以来 OPTIMAL、ONEPASS 或 MULTIPASSES 的工作时间的百分比。 ```sql SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total, sum(OPTIMAL_EXECUTIONS) optimal_count, sum(ONEPASS_EXECUTIONS) onepass_count, sum(MULTIPASSES_EXECUTIONS) multipass_count FROM v$sql_workarea_histogram WHERE low_optimal_size > 64*1024); ---- for 64 K optimal size ``` ### V$SQL_WORKAREA_ACTIVE 该视图可用于显示实例中活动(或执行)的工作区域。较小的活动排序(小于64 KB)将从视图中排除。使用此视图可以精确地监视所有活动工作区的大小，并确定这些活动工作区是否溢出到临时段。 **例如** ```sql SELECT to_number(decode(SID, 65535, NULL, SID)) sid, operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE, trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM", NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2; SID OPERATION ESIZE MEM MAX MEM PASS TSIZE --- ----------------- --------- --------- --------- ----- ------- 8 GROUP BY (SORT) 315 280 904 0 8 HASH-JOIN 2995 2377 2430 1 20000 9 GROUP BY (SORT) 34300 22688 22688 0 11 HASH-JOIN 18044 54482 54482 0 12 HASH-JOIN 18044 11406 21406 1 120000 ``` ### V$PGA_TARGET_ADVICE ``` SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)", pga_target_factor, estd_pga_cache_hit_percentage, estd_overalloc_count FROM v$pga_target_advice; ``` - pga_target_for_estimate：预测的 pga_aggregate_target 参数的值。 - pga_target_factor：预测的 pga 的值与当前 pga 的值的比值。 - estd_pga_cache_hit_percentage：当 pga_aggregate_target 设置成某个 pga_target_for_estimate 的值时，估计的 cache hit 率。此列的值等于：bytes_processed / (bytes_processed + estd_extra_bytes_rw). - estd_overalloc_count：如果 pga_aggregate_target 的值设置成 pga_target_for_estimate 的值。估计会出现的超过分配的次数（over-allocations）。如果该值不是 0，表明 pga_target_for_estimate 还不够大，因此不应该把pga_target_for_estimate 设置成 pga_aggregate_target 的值，否则 oracle 将不会信任该值。超过分配说的是，实际分配给 PGA 的内存大小，超过 pga_aggregate_target 设定的值。 ### 有关工作区域监控的其他查询 ##### 查找需要最多缓存内存的前十个工作区： ```sql select * from (select workarea_address, operation_type, policy, estimated_optimal_size from v$sql_workarea order by estimated_optimal_size DESC) where ROWNUM <=10; ``` ##### 查找使用最大内存的工作区百分比 ```sql select operation_type, total_executions * 100 / optimal_executions "%cache" From v$sql_workarea Where policy='AUTO' And optimal_executions > 0 Order By operation_type; ``` ##### 查找系统中当前分配的前十大工作区域 ```sql select c.sql_text, w.operation_type, top_ten.wasize From (Select * From (Select workarea_address, actual_mem_used wasize from v$sql_workarea_active Order by actual_mem_used) Where ROWNUM <=10) top_ten, v$sql_workarea w, v$sql c Where w.workarea_address=top_ten.workarea_address And c.address=w.address And c.child_number = w.child_number And c.hash_value=w.hash_value; ``` ##### 查找分配过多和不足的内存百分比： ```sql select total_used, under*100/(total_used+1) percent_under_use, over*100/(total_used+1) percent_over_used From (Select sum(case when expected_size > actual_mem_used then actual_mem_used else 0 end) under, sum(case when expected_size < actual_mem_used then actual_mem_used else 0 end) over, sum(actual_mem_used) total_used From v$sql_workarea_active Where policy='AUTO') usage; ```