原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/10/07/resumable/
-Jonathan Lewis 2019年10月7日上午10:31
有两个关于临时表空间的问题在许多论坛上出现得相当频繁。
一种形式是这样：
我的临时表空间不时地大幅增长（而且必须缩小），我如何才能找到导致这种情况发生的原因？
另一种遵循更基本的模式：
我的进程有时会因Oracle错误而崩溃：“ORA-01652: unable to extend temp segment by %n in tablespace %s”我该如何阻止这种情况发生？
在讨论博客话题之前，有必要指出第二个问题的两点：
首先，很容易陷入temp这个词，然后得出结论，问题在于临时表空间，而没有注意到错误消息包含引起问题的特定表空间。例如，如果在指定的表空间中重建索引，Oracle首先将该索引创建为该表空间中的临时段（其名称类似于 {starting_file_number}.{starting_block_number}），然后在重建完成后将其重命名为与原始索引名称相同，并删除旧索引。
其次，引发ORA-01652的进程不一定是有问题的一方-它可能是一个其它在不该占用所有可用空间的时候占用了所有可用空间的进程的牺牲品。此外，当你开始寻找问题时，其他进程可能已经完成并释放了它的空间，这会造成额外的混乱，因为你的进程看起来无缘无故地崩溃了。以上面的索引重建为例，你的索引重建可能会失败，因为其他人正在同一个表空间中同时重建另一个索引；但是，当你检查表空间时，它们的原始索引空间现在都是空闲的，因为它们的重建在此期间已完成了。
因此，在你开始寻找你认为有问题的代码之前，请暂停片刻以再次检查错误消息，并考虑你是否可能是某个现在已经完成，并重新活动的进程的牺牲品。
我将这两个问题列为同一主题的变体，因为其中一个问题的解决方法会带来另一个问题的风险—如果你想避免ORA-01652，可以将所有数据文件和临时文件设为“自动扩展”，但是，在某些情况下，它们可能会扩展得太多，你需要再次缩小它们（如果不是临时表空间，那么这并不一定容易）。相反，如果你认为你的数据或临时文件随机暴涨到荒唐的大小，你可以设定你的文件的最大大小并禁用自动扩展-然后在用户报告ora-01652时处理投诉。
当然，有多种方法可以近乎实时地监视系统以发现潜在的威胁；还有多种方法可以在事件发生后识别潜在的错误SQL。你可以关注各种v$ 动态性能视图或 dba_ 管理视图，以尝试发现问题；你可以设置1652事件，以转储用于崩溃后分析的errorstack（甚至systemstate），以查看报告的内容。两者都不是一个理想的解决方案，一个需要你对系统给予过多的关注，另一个是为了让问题发生，然后让你事后清理。然而，有一种策略可以在不需要持续监控的情况下阻止问题的出现。策略是（选择性地）启用resumable 操作。
如果可恢复操作需要分配空间，但无法这样做（即它通常将要引发ORA-01652），它将暂停自身一段时间，进入等待状态“statement suspended, wait error to be cleared”，这将在vsession_wait中显示为事件。超时后每两秒一次，会话还将在视图vresumable或dba_resumable中报告其当前操作。当会话挂起时，它也将向告警日志写入一条消息，但你也可以创建一个“挂起后”数据库触发器，以提醒你发生了问题。
如果将可恢复超时设置为适当的值，则可能会发现：
问题自行消失，会话在到达超时之前恢复
或
你将收到一个警告，并有一段时间来确定问题的根源，并采取所需的最小操作以允许会话恢复。
实施
参数resumable_timeout是可恢复会话的常规控件，如果你不在比系统更细粒度的级别处理该功能。
默认情况下，不启用可恢复默认为0，当可恢复时默认值为7200秒，但除非会话声明自己可恢复，否则该默认值（7200s）不会生效。如果将参数设置为非零值，则所有会话都将自动作为可恢复会话运行，而且你很快就会听到不希望这样做的原因。
可恢复会话的第二个启用功能是可恢复权限-除非已授予schema可恢复权限（可通过role授予），否则会话无法控制其自身的可恢复性。如果会话具有此权限，则它可以设置自己的可恢复超时，即使系统值为零。
假设我们通过实例参数文件将resumable_timeout设置为10（秒），然后重新启动实例。如果我们现在执行（例如）以下“create table”语句：
create table t1 (n1, v1 )
pctfree 90 pctused 10
tablespace tiny
as
select
rownum, cast(lpad('x',800) as varchar2(1000))
from all_objects
where rownum <= 20000
/
这将尝试为20000个块为每个块分配一行数据（加上位图空间管理块约1.5%），表空间tiny就像它的名字一样，由一个只有10,000个Oracle块的文件组成。执行不久，会话将遇到Oracle错误“ORA-01652: unable to extend temp segment by 128 in tablespace TINY”，但它不会报告该错误；相反，它将暂停自身10秒，然后失败并报告该错误。无论会话是否具有可恢复权限，都会发生这种情况-在这种情况下，行为由我们设置的系统参数决定。如果在会话最终出错后查看警报日志，你将发现如下文本：
2019-10-04T14:01:11.847943+01:00 ORCL(3):ORA-1652: unable to extend temp segment by 128 in tablespace TINY [ORCL] ORCL(3):statement in resumable session 'User TEST_USER(138), Session 373, Instance 1' was suspended due to ORCL(3): ORA-01652: unable to extend temp segment by 128 in tablespace TINY 2019-10-04T14:01:23.957586+01:00 ORCL(3):statement in resumable session 'User TEST_USER(138), Session 373, Instance 1' was timed out
请注意，在会话报告它正在挂起自己的地方和它失败并超时的地方之间有一个10(加上两个)秒的时间差。额外的两秒之所以出现，是因为会话每两秒轮询一次，以查看问题是否仍然存在，或者问题是否自动消失，从而允许会话继续。
让我们稍微改变一下游戏规则;让我们再次尝试创建表，但这一次先执行以下语句:
alter session enable resumable timeout 60 name 'Help I''m stuck';
对此的初始响应将是Oracle错误“ORA-01031: insufficient privileges”，因为会话没有可恢复的权限，但是在将可恢复的权限授予用户(或相关角色)之后，我们再次尝试，并发现在CTAS超时之前允许我们花费的时间多了一点。我们的会话现在覆盖了系统超时时间，并将在失败之前等待60秒(外加一点)。“timeout”子句是可选的，如果我们省略它，会话将使用系统值，类似地，“name”子句是可选的，尽管它没有默认值，它只是一个将进入各种视图和报告的信息。
在这60秒的时间里，你可以检查几件事。session wait history会记录你的会话超时，每两秒一次(如果你有权使用，那么active session history也会记录):
v$session_wait_history where sid = 373
SEQ# EVENT WAIT_TIME
---------- ---------------------------------------------------------------- ----------
1 statement suspended, wait error to be cleared 204
2 statement suspended, wait error to be cleared 201
3 statement suspended, wait error to be cleared 201
4 statement suspended, wait error to be cleared 201
5 statement suspended, wait error to be cleared 200
6 statement suspended, wait error to be cleared 200
7 statement suspended, wait error to be cleared 202
8 statement suspended, wait error to be cleared 200
9 statement suspended, wait error to be cleared 200
10 statement suspended, wait error to be cleared 200
然后是一个特殊的动态性能视图，v$resumable，我在下面使用了一个print_table()存储过程，这个过程是Tom Kyte在很多年前编写的，用于行列转换展示数据:
SQL> set serveroutput on
SQL> execute print_table('select * from v$resumable where sid = 373')
ADDR : 0000000074515B10
SID : 373
ENABLED : YES
STATUS : SUSPENDED
TIMEOUT : 60
SUSPEND_TIME : 10/04/19 14:26:20
RESUME_TIME :
NAME : Help I'm stuck
ERROR_NUMBER : 1652
ERROR_PARAMETER1 : 128
ERROR_PARAMETER2 : TINY
ERROR_PARAMETER3 :
ERROR_PARAMETER4 :
ERROR_PARAMETER5 :
ERROR_MSG : ORA-01652: unable to extend temp segment by 128 in tablespace TINY
CON_ID : 0
-----------------
1 rows selected
注意，name列如何展示我在启用可恢复会话时写的name的。视图还告诉我们关键语句何时被暂停，以及准备等待多长时间(总共)-让我们从当前时间计算出我们还有多少时间来处理这个问题。
还有一个视图dba_resumable，它提供的信息稍微多一点(尽管下面的示例与上面的示例不一致，因为我执行了几次CTAS，同时编辑了博客):
SQL> execute print_table('select * from dba_resumable where session_id = 373')
USER_ID : 138
SESSION_ID : 373
INSTANCE_ID : 1
COORD_INSTANCE_ID :
COORD_SESSION_ID :
STATUS : SUSPENDED
TIMEOUT : 60
START_TIME : 10/04/19 14:21:14
SUSPEND_TIME : 10/04/19 14:21:16
RESUME_TIME :
NAME : Help I'm stuck
SQL_TEXT : create table t1 (n1, v1 ) pctfree 90 pctused 10 tablespace tiny as select rownum,
cast(lpad('x',800) as varchar2(1000)) from all_objects where rownum <= 20000
ERROR_NUMBER : 1652
ERROR_PARAMETER1 : 128
ERROR_PARAMETER2 : TINY
ERROR_PARAMETER3 :
ERROR_PARAMETER4 :
ERROR_PARAMETER5 :
ERROR_MSG : ORA-01652: unable to extend temp segment by 128 in tablespace TINY
-----------------
1 rows selected
这个视图包含已经挂起的语句的文本，并向我们显示它何时开始运行(以便我们可以决定是否真正想要拯救它，或者是否乐意杀掉它以允许其他挂起的会话恢复)。
如果你在本例中查看警报日志，你会发现name已经用于汇报用户名、会话名和实例名-这意味着你可能需要仔细考虑如何使用name选项:
2019-10-04T14:21:16.151839+01:00
ORCL(3):statement in resumable session 'Help I'm stuck' was suspended due to
ORCL(3): ORA-01652: unable to extend temp segment by 128 in tablespace TINY
2019-10-04T14:22:18.655808+01:00
ORCL(3):statement in resumable session 'Help I'm stuck' was timed out
一旦你的可恢复任务完成(或超时和失败)，你可以停止会话重用，用如下命令:
alter session disable resumable;
重要的是，每次启用可恢复性时，应该在不再需要该功能时立即禁用它。另外，在启用它时要小心，不要试图使每个会话都可恢复。只在真正重要的情况下使用。一旦一个会话是可恢复的，实际上在那个会话中发生的所有事情都被认为是可恢复的，这有副作用。
可能出现的第一个副作用是视图 v$resumable -它是SGA中的一个内存结构，因此每个人都可以看到它，并且所有可恢复的会话都可以填充和更新它。这意味着必须有一些latch(或mutex)在起保护作用—如果你查看v$latch，你会发现只有一个(child)latch在起作用，所以可恢复性可能会引入一个争用点。下面是一个简单的脚本(使用我的“start_XXX”策略来“select 1 from dual;”一千次)，调用来检查latch活动:
set termout off
set serveroutput off
execute snap_latch.start_snap
@start_1000
set termout on
set serveroutput on
execute snap_latch.end_snap(750)
下面是运行脚本的结果-报告收集间隔访问超过750的latch-先不是可恢复的结果，然后是可恢复的会话的结果:
---------------------------------
Latch waits:- 04-Oct 15:04:31
Lower limit:- 750
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
session idle bit 6,011 0 0 0 0 0 0 0 .0
enqueue hash chains 2,453 0 0 0 0 0 0 0 .0
enqueue freelist latch 1 0 0 0 2,420 0 0 0 .0
JS queue state obj latch 1,176 0 0 0 0 0 0 0 .0
SQL> alter session enable resumable;
SQL> @test
---------------------------------
Latch waits:- 04-Oct 15:04:46
Lower limit:- 750
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
session idle bit 6,011 0 0 0 0 0 0 0 .0
enqueue hash chains 2,623 0 0 0 0 0 0 0 .0
enqueue freelist latch 1 0 0 0 2,588 0 0 0 .0
resumable state object 3,005 0 0 0 0 0 0 0 .0
JS queue state obj latch 1,260 0 0 0 0 0 0 0 .0
PL/SQL procedure successfully completed.
SQL> alter session disable resumable;
这是1000次 select from dual- 在一个单独的子latch上获取了3000次。看起来对数据库的每次调用都会导致latch get和对内存结构的更新。(注意:如果在匿名PL/SQL块中运行循环，则不会看到相同的效果，因为这样是一个数据库调用来访问该块)。
对于可恢复性的其他副作用，请考虑在你的会话周围还发生了什么。如果你允许一个会话挂起(比方说)3600秒，并且它设法及时恢复以避免超时。那么它需要构造一个读取一致的结果，这需要3600秒的数据库更改去回溯;所以你不仅要允许增加undo tablespace和增加undo retention time，而且你必须接受进程恢复时它可能会比往常慢得多的事实，因为它花费更多的时间来查看会话挂起之前的数据，这可能需要对undo表空间进行更多的单块读取，而会话可能会因为Oracle错误ORA-01555而崩溃(这是众所周知的，在这我不会引用该类文档了)。
同理-如果一个进程在(特别是)临时表空间中获得了大量的空间，并且由于无法获得更多的空间而立即失败，那么它通常会崩溃并释放空间。如果你允许该进程挂起一个小时，那么它将占用这个空间-这意味着以前安全运行的其他进程现在可能会崩溃，因为它们发现在临时表空间中没有多余的空间留给它们。
在引入可恢复的会话时要非常谨慎-你需要了解全局影响，而不仅仅是对会话的潜在好处。
需要注意
除了告诉你会话已经挂起的(被动)视图之外，还可以在事件发生时获得某种形式的(主动)警报。有一个“挂起后”事件，你可以用来创建一个数据库触发器，采取一些防御行动，例如:
create or replace trigger call_for_help
after suspend
on test_user.schema
begin
if sysdate between trunc(sysdate) and trunc(sysdate) + 3/24 then
null;
-- use utl_mail， utl_smtp et. al. to page the DBA
end if;
end;
/
该触发器仅限于test_user schema，并且(不包括代码)仅在午夜到凌晨3点之间向DBA的寻呼机发送消息。除了dbms_standard中常见的返回错误代码、对象名称等的函数之外，你可能还想了解一下dbms_resumable包，它提供了函数和过程的使用指南。
关于可恢复会话的更多信息，这里有一个指向12.2手册的链接，可以帮助你学习。
