下面这个测试很有意思,你有机会通过下面几种方式之一把数据库服务器搞宕机
- 打满数据文件里仅剩的可用空间
- 让临时表空间疯狂扩容,直至达到它的最大上限
- 耗尽服务器上全部内存,哪怕已经配置了 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/ ,如需转载,请注明出处,否则将追究法律责任。