ITPub博客

首页 > 数据库 > Oracle > 第89期 PGA 内存 —— 开发者悄悄吃光服务器内存的秘密武器 I

第89期 PGA 内存 —— 开发者悄悄吃光服务器内存的秘密武器 I

原创 Oracle 作者:pxbibm 时间:2026-11-17 16:54:17 0 删除 编辑

下面这个测试很有意思,你有机会通过下面几种方式之一把数据库服务器搞宕机

- 打满数据文件里仅剩的可用空间

- 让临时表空间疯狂扩容,直至达到它的最大上限

- 耗尽服务器上全部内存,哪怕已经配置了 PGA_AGGREGATE_TARGET 参数也无济于事

- 压垮磁盘 IO 子系统

我们先执行下面这段脚本,创建三张看起来平平无奇的表:

#CREATE TABLE T1 AS
#SELECT
#  ROWNUM C1,
#  RPAD('R'||TO_CHAR(ROWNUM),30,'B') C2,
#  RPAD('A',100,'A') C3
#FROM
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=10000) V1,
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=10000) V2;
#
#CREATE TABLE T2 AS
#SELECT
#  ROWNUM*10 C1,
#  RPAD('R'||TO_CHAR(ROWNUM*10),30,'B') C2,
#  RPAD('A',255,'A') C3
#FROM
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=10000) V1,
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=1000) V2;
#
#CREATE TABLE T3 AS
#SELECT
#  (ROWNUM*10)+2 C1,
#  RPAD('R'||TO_CHAR((ROWNUM*10)+2),30,'B') C2,
#  RPAD('A',255,'A') C3
#FROM
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=10000) V1,
#  (SELECT
#    ROWNUM C1
#  FROM
#    DUAL
#  CONNECT BY
#    LEVEL<=1000) V2;

收集这三个表的统计信息:

#EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
#EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')
#EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3')

我们可以通过下面这条 SQL 语句查看这三张表占用了多少磁盘空间:

#SELECT
#  SEGMENT_NAME SEGMENT,
#  SUM(BYTES/1048576) TOTAL_MB
#FROM
#  DBA_EXTENTS
#WHERE
#  OWNER=USER
#  AND SEGMENT_NAME IN ('T1','T2','T3')
#GROUP BY
#  SEGMENT_NAME
#ORDER BY
#  SEGMENT_NAME;
SEGMENT   TOTAL_MB
------- ----------
T1           15684
T2            3269
T3            3266

可以看到这三张表大约占用了 21.7GB 的空间。接下来,我们准备一个脚本,命名为 PGAMemoryFill.sql:

#DECLARE
#CURSOR C_MEMORY_FILL IS
#SELECT
#  T1.C1,
#  T1.C2,
#  T1.C3
#FROM
#  T1
#WHERE
#  T1.C1 NOT IN (
#    SELECT
#      C1
#    FROM
#      T2)
#  AND T1.C2 NOT IN (
#    SELECT
#      C2
#    FROM
#      T3)
#ORDER BY
#  T1.C2 DESC,
#  T1.C1 DESC;
#
#TYPE TYPE_MEMORY_FILL IS TABLE OF C_MEMORY_FILL%ROWTYPE
#INDEX BY BINARY_INTEGER;
#
#T_MEMORY_FILL  TYPE_MEMORY_FILL;
#
#BEGIN
#  OPEN C_MEMORY_FILL;
#  LOOP
#    FETCH C_MEMORY_FILL BULK COLLECT INTO  T_MEMORY_FILL  LIMIT 10000000;
#
#    EXIT WHEN T_MEMORY_FILL.COUNT = 0;
#
#    FOR I IN T_MEMORY_FILL.FIRST..T_MEMORY_FILL.LAST LOOP
#      NULL;
#    END LOOP;
#
#    DBMS_LOCK.SLEEP(20);
#  END LOOP;
#END;
#/

我们来查看一下 PGA_AGGREGATE_TARGET 参数:

SHOW PARAMETER PGA_AGGREGATE_TARGET
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
pga_aggregate_target                 big integer 1800M

好的,PGA_AGGREGATE_TARGET 设置值略小于 1.8GB,而这台服务器一共有 12GB 内存。

接下来开始测试,我们需要两个会话:会话 1 用来执行上面的脚本,会话 2 执行各类查询,观察数据库内部的变化。

会话 1:

#SELECT SID FROM V$MYSTAT WHERE ROWNUM<=1;
#
#  SID
#-----
#  335
#
#SET AUTOTRACE TRACEONLY EXPLAIN
#
#SELECT
#  T1.C1,
#  T1.C2,
#  T1.C3
#FROM
#  T1
#WHERE
#  T1.C1 NOT IN (
#    SELECT
#      C1
#    FROM
#      T2)
#  AND T1.C2 NOT IN (
#    SELECT
#      C2
#    FROM
#      T3)
#ORDER BY
#  T1.C2 DESC,
#  T1.C1 DESC;
#
#Plan hash value: 2719846691
#
#------------------------------------------------------------------------------------------
#| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|Time      |
#------------------------------------------------------------------------------------------
#|   0 | SELECT STATEMENT          |      |     1 |   174 |       |  2351K  (1)| 07:50:19 |
#|   1 |  SORT ORDER BY            |      |     1 |   174 |       |  2351K  (1)| 07:50:19 |
#|*  2 |   HASH JOIN RIGHT ANTI NA |      |     1 |   174 |   171M|  2351K  (1)| 07:50:19 |
#|   3 |    TABLE ACCESS FULL      | T2   |    10M|    57M|       |   113K  (1)| 00:22:39 |
#|*  4 |    HASH JOIN RIGHT ANTI NA|      |    99M|    15G|   410M|  1382K  (1)| 04:36:25 |
#|   5 |     TABLE ACCESS FULL     | T3   |    10M|   295M|       |   113K  (1)| 00:22:39 |
#|   6 |     TABLE ACCESS FULL     | T1   |   100M|    12G|       |   543K  (1)| 01:48:42 |
#------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
#   2 - access("T1"."C1"="C1")
#   4 - access("T1"."C2"="C2")
#
#SET AUTOTRACE OFF
#
#SELECT 1 FROM DUAL;

从上面的执行计划可以看到,Oracle 计划在 T1 表和 T3 表之间执行HASH JOIN RIGHT ANTI NA连接(预估会占用临时表空间 410MB…… 这真的是实际占用大小吗,请继续往下看);

随后再将这部分行源与 T2 表做HASH JOIN RIGHT ANTI NA连接。

这条关联 T1、T2、T3 三张表的 SQL,就是 PGAMemoryFill.sql 脚本里面要执行的语句。

我们在会话 2 中查看会话 1 最后执行的这条 SQL 当前生效的优化器参数:

#SET PAGESIZE 1000
#COLUMN CN FORMAT 99
#COLUMN NAME FORMAT A37
#COLUMN VALUE FORMAT A14
#COLUMN DEF FORMAT A3
#
#SELECT
#  CHILD_NUMBER CN,
#  NAME,
#  VALUE,
#  ISDEFAULT DEF
#FROM
#  V$SQL_OPTIMIZER_ENV SOE,
#  V$SESSION S
#WHERE
#  SOE.SQL_ID=S.SQL_ID
#  AND SOE.CHILD_NUMBER=S.SQL_CHILD_NUMBER
#  AND S.SID=335
#ORDER BY
#  NAME;
 CN NAME                                  VALUE          DEF
--- ------------------------------------- -------------- ---
  0 _pga_max_size                         368640 KB      NO
  0 active_instance_count                 1              YES
  0 bitmap_merge_area_size                1048576        YES
  0 cell_offload_compaction               ADAPTIVE       YES
  0 cell_offload_plan_display             AUTO           YES
  0 cell_offload_processing               true           YES
  0 cpu_count                             8              YES
  0 cursor_sharing                        exact          YES
  0 db_file_multiblock_read_count         128            YES
  0 hash_area_size                        131072         YES
  0 is_recur_flags                        0              YES
  0 optimizer_capture_sql_plan_baselines  false          YES
  0 optimizer_dynamic_sampling            2              YES
  0 optimizer_features_enable             11.1.0.7       YES
  0 optimizer_index_caching               0              YES
  0 optimizer_index_cost_adj              100            YES
  0 optimizer_mode                        all_rows       YES
  0 optimizer_secure_view_merging         true           YES
  0 optimizer_use_invisible_indexes       false          YES
  0 optimizer_use_pending_statistics      false          YES
  0 optimizer_use_sql_plan_baselines      true           YES
  0 parallel_ddl_mode                     enabled        YES
  0 parallel_degree                       0              YES
  0 parallel_dml_mode                     disabled       YES
  0 parallel_execution_enabled            true           YES
  0 parallel_query_default_dop            0              YES
  0 parallel_query_mode                   enabled        YES
  0 pga_aggregate_target                  1843200 KB     YES
  0 query_rewrite_enabled                 true           YES
  0 query_rewrite_integrity               enforced       YES
  0 result_cache_mode                     MANUAL         YES
  0 skip_unusable_indexes                 true           YES
  0 sort_area_retained_size               0              YES
  0 sort_area_size                        65536          YES
  0 star_transformation_enabled           false          YES
  0 statistics_level                      typical        YES
  0 transaction_isolation_level           read_commited  YES
  0 workarea_size_policy                  auto           YES

请注意上文,_pga_max_size参数被设置为 368640KB(即 360MB,为 PGA_AGGREGATE_TARGET 的 20%。

注意:当 PGA 内存大量占用时,即便强制执行硬解析,该值看起来也不会下降)。尽管 ISDEFAULT 列显示该值并非默认值,但它是依据PGA_AGGREGATE_TARGET的配置自动计算出来的。

在开始制造资源压力之前,我们先查阅 `V$SQL_WORKAREA_ACTIVE` 视图的官方文档:

给出了该视图各字段的定义,其中几条字段说明如下:

- OPERATION_TYPE:使用工作区的操作类型(排序 SORT、哈希连接 HASH JOIN、分组 GROUP BY、缓冲 BUFFERING、位图合并 BITMAP MERGE、位图创建 BITMAP CREATE)

- WORK_AREA_SIZE:当前该操作占用工作区的最大容量(字节)

- ACTUAL_MEM_USED:当前为该工作区分配的 PGA 内存大小(字节),取值范围应在 0 到 WORK_AREA_SIZE 之间

- NUMBER_PASSES:该工作区的读写轮次;0 代表以最优模式(OPTIMAL)运行,完全在内存中完成

- TEMPSEG_SIZE:该工作区用到的临时段大小(字节);如果工作区尚未溢出到磁盘,该字段为 NULL

当会话 1 正在执行 PGAMemoryFill.sql脚本时,我们在会话 2 周期性查询 V$SQL_WORKAREA_ACTIVE 视图,观察运行状态。

会话 1:

#ALTER SESSION SET STATISTICS_LEVEL=ALL;
@PGAMemoryFill.sql

会话 2:

在会话 2 中,间隔短暂延时后反复执行下面这条 SQL 语句

(注意:我本可以把 `OPERATION_ID` 字段也查出来,这样就能很方便地把内存占用和前面展示的执行计划里的某一步具体操作对应起来)。

#SELECT
#  SQL_ID,
#  OPERATION_TYPE,
#  WORK_AREA_SIZE,
#  ACTUAL_MEM_USED,
#  NUMBER_PASSES,
#  TEMPSEG_SIZE
#FROM
#  V$SQL_WORKAREA_ACTIVE;
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv HASH-JOIN                  29298688        20712448             0    121634816

由此可见,会话 1 的哈希连接大约占用了 19.75MB 的 PGA 内存。根据 `NUMBER_PASSES` 字段的定义,该哈希连接当前属于最优执行模式(optimal),但这似乎和 TEMPSEG_SIZE 字段的定义以及该字段的输出结果互相矛盾。

会话 2 会持续重复执行上面这条 SQL 语句,每次执行完成后短暂停顿:

—
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv HASH-JOIN                  40738816        33011712             0     45088768
0k5pr4rx072sv HASH-JOIN                 189427712        20740096             1    130023424

现在这条 SQL 语句有两个哈希连接处于活跃状态,PGA 内存总占用为 51.26MB。其中一个哈希连接仍然是最优执行模式,而第二个已经变成一遍溢出(1 pass)执行模式。

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  78364672        28554240             1   1293942784
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1    814743552
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1    470810624

现在两个哈希连接都显示为一遍溢出(1 pass)执行模式。新增了一个 V2 版本的排序操作,该排序同样是一遍溢出模式。根据该视图的输出,当前会话占用的 PGA 内存已经略超 274MB。

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  78304256        43396096             1   1749024768
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1    968884224
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1    591396864
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  80089088         5712896             1   5366611968
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1   2097152000
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   1509949440
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  73031680        67003392             1   8383365120
0k5pr4rx072sv HASH-JOIN                 147055616       148588544             1   3050307584
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2283798528

该会话的 PGA 内存占用已经涨到 310.77MB,同时 `TEMPSEG_SIZE` 字段的值还在持续增大。

--
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  67550208        37590016             1   9634316288
0k5pr4rx072sv HASH-JOIN                  23760896        13456384             1   3338665984
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2607808512
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  67550208        47077376             1   1.0252E+10
0k5pr4rx072sv HASH-JOIN                  23760896        13456384             1   3338665984
0k5pr4rx072sv HASH-JOIN                 129864704       110271488             1   2770337792
SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                 188743680       188285952             1   1.1250E+10
0k5pr4rx072sv HASH-JOIN                  18951168        17658880             1   2839543808

其中一个哈希连接操作已经执行完成,看样子快要结束了。

SQL_ID        OPERATION_TYPE       WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- -------------------- -------------- --------------- ------------- ------------
0k5pr4rx072sv SORT (v2)                  90914816        91714560             1   1.1966E+10

最后一个哈希连接也执行完毕了,此时 `TEMPSEG_SIZE` 为 1.1966E+10,代表临时表空间里的临时段大小约为 11.14GB。

这个体量相当大 —— 记住这个数值。当上面的查询只返回排序操作这一行时,会话 2 执行下面这条 SQL:

#COLUMN VALUE FORMAT 999,999,999,990
#
#SELECT
#  SN.NAME,
#  SS.VALUE
#FROM
#  V$STATNAME SN,
#  V$SESSTAT SS
#WHERE
#  SS.SID=335
#  AND SS.STATISTIC#=SN.STATISTIC#
#  AND SN.NAME LIKE '%pga%';
NAME                             VALUE
------------------------ -------------
session pga memory       3,391,500,272
session pga memory max   3,391,500,272

从上面结果可以看出,会话 1 并不是只占用约 90MB 的 PGA 内存,而是大约占用了 3234.39MB 的 PGA 内存。

希望负责这个数据库的 DBA 没有把 PGA_AGGREGATE_TARGET 设置的 1800MB 当成硬性上限,并且配置了其他参数,充分利用服务器上 12GB 内存。

脚本执行结束后,上面这条 SQL 语句返回如下结果:

NAME                             VALUE
------------------------ -------------
session pga memory          10,039,280
session pga memory max   3,391,500,272

该会话即便处于空闲状态,仍然占用着 9.57MB 的 PGA 内存 —— 记下这个数值。

接下来我们验证一下,V$SQL_WORKAREA_ACTIVE 视图查询出来的 0k5pr4rx072sv 确实是我们这条 SQL 的 SQL_ID:

#SELECT
#  SQL_TEXT
#FROM
#  V$SQL
#WHERE
#  SQL_ID='0k5pr4rx072sv';
#
#SQL_TEXT
#--------------------------------------------------------------------------------
#SELECT T1.C1, T1.C2, T1.C3 FROM T1 WHERE T1.C1 NOT IN ( SELECT C1 FROM T2) AND T
#1.C2 NOT IN ( SELECT C2 FROM T3) ORDER BY T1.C2 DESC, T1.C1 DESC

很好,接下来我们查看这条 SQL 语句的执行计划:

#SELECT
#  *
#FROM
#  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0k5pr4rx072sv',0,'ALLSTATS LAST'));
#
#SQL_ID  0k5pr4rx072sv, child number 0
#-------------------------------------
#SELECT T1.C1, T1.C2, T1.C3 FROM T1 WHERE T1.C1 NOT IN ( SELECT C1 FROM
#T2) AND T1.C2 NOT IN ( SELECT C2 FROM T3) ORDER BY T1.C2 DESC, T1.C1
#DESC
#
#Plan hash value: 2719846691
#
#---------------------------------------------------------------------------------------------------------------------------------------------------
#| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
#---------------------------------------------------------------------------------------------------------------------------------------------------
#|   0 | SELECT STATEMENT          |      |      1 |        |     80M|00:24:55.23 |    2833K|   5024K|   2190K|       |       |          |         |
#|   1 |  SORT ORDER BY            |      |      1 |      1 |     80M|00:24:55.23 |    2833K|   5024K|   2190K|    12G|    35M|  179M (1)|      11M|
#|*  2 |   HASH JOIN RIGHT ANTI NA |      |      1 |      1 |     80M|00:12:08.70 |    2833K|   3563K|    730K|   269M|    14M|  105M (1)|    2708K|
#|   3 |    TABLE ACCESS FULL      | T2   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|      0 |       |       |          |         |
#|*  4 |    HASH JOIN RIGHT ANTI NA|      |      1 |     99M|     90M|00:08:25.72 |    2416K|   2811K|    394K|   521M|    19M|  141M (1)|    3184K|
#|   5 |     TABLE ACCESS FULL     | T3   |      1 |     10M|     10M|00:00:20.12 |     416K|    416K|      0 |       |       |          |         |
#|   6 |     TABLE ACCESS FULL     | T1   |      1 |    100M|    100M|00:03:20.37 |    2000K|   1999K|      0 |       |       |          |         |
#---------------------------------------------------------------------------------------------------------------------------------------------------
#
#Predicate Information (identified by operation id):
#---------------------------------------------------
#   2 - access("T1"."C1"="C1")
#   4 - access("T1"."C2"="C2")

我们来看一下:这条 SQL 执行耗时将近 25 分钟,在一遍溢出(one pass)工作区执行过程中总共消耗了大约 425MB 内存(但从前面的输出可以看出,这些内存并不是同一时刻同时占用的),并且 SORT ORDER BY 操作使用了 11M 的临时表空间…… 但这里的 11M 到底是 11MB?还是 1100 万 KB?又或者是 11,534,336 KB(也就是 11 × 2²⁰ KB)?

还记得前面我们查到 “临时表空间里的临时段大小约为 11.14GB”,所以这里的 11M 代表的是 11,534,336 KB,大约 11GB。

好吧,这一点确实很容易让人混淆,而我们的测试还没有结束。

接下来我们玩个大的,把内存吃满(玩到系统出问题为止)。我们在 4 个会话里同时运行 `PGAMemoryFill.sql` 脚本,再开第 5 个会话用来监控进度(想玩得更极端的话,可以修改 T1 表上的 WHERE 条件,让所有工作区都以最优模式执行,而不是发生一遍 / 多遍溢出、写到磁盘)。

在 4 个会话中执行这个脚本:   

#@PGAMemoryFill.sql

短暂等待之后,第 5 个会话(监控会话)会周期性执行下面这条查询语句:

#SELECT
#  SN.NAME,
#  SUM(SS.VALUE) VALUE
#FROM
#  V$STATNAME SN,
#  V$SESSTAT SS
#WHERE
#  SS.STATISTIC#=SN.STATISTIC#
#  AND SN.NAME='session pga memory'
#GROUP BY
#  SN.NAME;
#
#NAME                            VALUE
#-------------------- ----------------
#session pga memory        144,351,120

PGA 内存占用大约 137.66MB。我很好奇,我们会不会达到 3,391,500,272 × 4 = 12.63GB 的 PGA 占用(也就是单会话观测值的 4 倍)?

好了,我们继续反复执行上面这条查询,每次执行之间短暂停顿:

NAME                            VALUE
-------------------- ----------------
session pga memory        144,351,120
NAME                            VALUE
-------------------- ----------------
session pga memory        285,902,000
NAME                            VALUE
-------------------- ----------------
session pga memory      1,191,920,144
NAME                            VALUE
-------------------- ----------------
session pga memory      1,296,843,280
NAME                            VALUE
-------------------- ----------------
session pga memory      1,379,306,720
NAME                            VALUE
-------------------- ----------------
session pga memory      1,401,504,272
NAME                            VALUE
-------------------- ----------------
session pga memory      1,465,467,408
NAME                            VALUE
-------------------- ----------------
session pga memory      1,473,207,536
NAME                            VALUE
-------------------- ----------------
session pga memory      1,484,283,120

我们挑其中一个会话,查看它的运行情况:

#SELECT
#  SN.NAME,
#  SS.VALUE
#FROM
#  V$STATNAME SN,
#  V$SESSTAT SS
#WHERE
#  SS.SID=335
#  AND SS.STATISTIC#=SN.STATISTIC#
#  AND SN.NAME LIKE '%pga%';
#
#NAME                              VALUE
#---------------------- ----------------
#session pga memory          357,904,368
#session pga memory maz      357,904,368

这一个会话大约占用 341.32MB 的 PGA 内存,现在我们切回另一条监控查询:

NAME                            VALUE
-------------------- ----------------
session pga memory      1,476,418,800
NAME                            VALUE
-------------------- ----------------
session pga memory      4,517,252,992
NAME                            VALUE
-------------------- ----------------
session pga memory      4,518,556,832

GA 内存占用看起来稳定在 4309.23MB(4.21GB)。我们虽然没有因为耗尽服务器 12GB 内存而把服务器搞宕机,

但这个数值已经是 PGA_AGGREGATE_TARGET 参数设置值的 2.4 倍。我们来看下这 4 个会话的运行进度:

#SELECT
#  SS.SID,
#  SN.NAME,
#  SS.VALUE
#FROM
#  V$STATNAME SN,
#  V$SESSTAT SS
#WHERE
#  SS.VALUE>=300*1024*1024
#  AND SS.STATISTIC#=SN.STATISTIC#
#  AND SN.NAME LIKE '%pga%'
#ORDER BY
#  SS.SID,
#  SN.NAME;
#
#SID NAME                              VALUE
#--- ---------------------- ----------------
#297 session pga memory        3,322,442,384
#297 session pga memory max    3,384,832,656
#304 session pga memory          368,734,864
#304 session pga memory max      373,518,992
#305 session pga memory          368,734,864
#305 session pga memory max      368,734,864
#335 session pga memory          357,904,368
#335 session pga memory max      357,904,368

上面的结果看起来显示,其中一个会话仍占用 3168.53MB 的 PGA 内存,

另外三个会话的内存占用都回落至大约 352MB。

我们查看一下这些会话的情况……4 个会话里有 3 个的脚本报错终止了,报错信息如下:

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

ORA-06512: at line 33

来快速算一笔账:在块大小为 8KB 的数据库中,11.14GB × 4 是否超过了小文件(SMALLFILE)数据文件的最大容量?

128 × 8KB = 1MB,这正是临时表空间的区(extent)大小。

好了,4 个会话里有 3 个脚本已经崩溃,那为什么这些会话只是静静等待下一条 SQL,却依旧各自占用大约 352MB 的 PGA 内存?

如果要排查开发人员吉米写的这段代码到底干了什么,这个现象绝对能把人逼疯。

那么,要怎么回收会话的这部分内存,让它返还给操作系统?你必须在每个会话里执行这条专门编写的 SQL 语句:

#SELECT
#  42
#FROM
#  DUAL
#WHERE
#  1=2;

当然,不一定非要执行上面那条 SQL 才行,但只要不执行新的 SQL,这 3 个会话各自占用的 352MB 内存就无法挪作他用。

朋友们,这就是开发者用来 “吃光服务器内存” 的秘密武器。

接下来可以试着修改 `PGAMemoryFill.sql` 脚本里的 SQL 语句,让三处工作区都以最优模式运行,看看执行这条 SQL 时内存占用最高能冲到多少。

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/12798004/viewspace-3189267/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
Oracle ACE。ACOUG核心成员,Oracle用户组年轻专家。熟悉Oracle的体系结构,擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    346
  • 访问量
    2479102