SQE存储系列之三 —— 建立SQL临时存储限制（译文）

制止失控的查询

原文作者： Tim Clark

疯狂的列车正在脱轨

在 2004 年的电影《蜘蛛侠 2 》中，有一个戏剧性的场景，主人公彼得 · 帕克发现自己身处一辆载满乘客的失控火车上，后者正向着轨道的尽头疾驰而去。他动用了所有蜘蛛侠的超能力，在火车即将坠入毁灭之时，终于把它刹住。

您不会经常碰到试图将 IBM i 系统推入过载境地的超级恶棍。但有时似乎确实如此： Ad-hoc 查询、糟糕的应用程序逻辑，或者一个沮丧的用户反复地按着 “ 提交 ” ，这些都可能会耗尽系统的临时存储空间，从而使其过载而失去响应。就像失控的火车一样，最好是在作业失控之前停止它们。 IBM 对临时存储控制支持的改进，使得该工作比以往任何时候都容易。所以，戴上你最喜欢的超级英雄面具，继续阅读，看看如何提高你自己的驯服查询超能力。

使用新的 PTF 增强控制

多年来， IBM i 提供了防止单个作业分配过多临时存储的能力。现在，通过 IBM i 7.4 和下面列出的在 IBM i 7.2 和 7.3 的程序临时修复程序（ PTF ），该控制已经扩展到 SQL 查询引擎（ SQE ）所使用的临时存储。

SQE 会让超过 MAXTMPSTG 的作业暂停

IBM i 7.2: MF64627, MF64628, MF64629, MF64630, SI66859, SI66860

IBM i 7.3: MF64447, MF64448, MF64449, MF64450, SI66499, SI66500

如果与作业相关的临时存储限制已经成为了系统管理策略的一部分，请注意：当应用这些 PTF 时，您可能会看到那些先前平稳运行的作业会被系统 暂停 。这是有原因的，你需要理解为什么和什么时候会发生这种情况。

另一方面，如果您从未使用过这些限制，那么请利用这个机会考虑是否建立临时存储最大值来保护 IBM i 系统。让它成为你系统的超级英雄！

知晓并设定你的限制

SQL 临时存储的使用问题可能是由那些编写得很差的 Ad-hoc 查询引起的，也可能只是由于大数据变得更大了的缘故。不管原因是什么，没有人希望看到他们的系统因为一个（或几个）查询而变得疯狂以至于消耗掉了所有可用的存储空间而变得慢如蜗牛。查询引擎总是包含一些简单的安全措施，以预留少量（大约 1% ）的系统存储空间。但是，这些限制是避免系统崩溃的最后手段，对于希望对系统进行微调的系统管理员和数据库工程师来说，它们没什么用。新近的这些 PTF ，其目的就是为了有效地避免查询存储分配失控问题的。

临时存储限制可以通过 Change Job (CHGJOB) 命令为单个作业设置最大临时存储（ MAXTMPSTG ）参数。要对多个作业应用限制，您可以使用 Create Class （ CRTCLS ）或 Change Class （ CHGCLS ）命令为与 Subsystem Description 里的 Rounting Entry 相关联的类设置 MAXTMPSTG 参数。当在 IBM Navigator for i 中创建或修改类时，涉及的是 Maximum Temporary Storage 字段。分配给该参数的值（以 MB 为单位）将限制作业可以使用的临时存储空间。如果达到了限制，则作业将被暂停，并且一条 CPI112E 消息 —— “ 作业 … 被系统暂停，原因是超过了 MAXTMPSTG 限制。 ” —— 将被发送到 QSYSOPR 消息队列和作业日志当中。该作业将一直被暂停，直到有人通过终止该工作或着通过提高（或删除）该作业的 MAXTMPSTG 限制进而释放该作业这些办法来加以干预。 *NOMAX 的取值表明作业的临时存储不受限制；这些作业在行为上将不会有任何改变。

通过将 SQL 引擎置于 MAXTMPSTG 参数的范围内，您现在可以有效地控制失控的 SQL 查询以免它们消耗过多的存储空间。这对于管理您的系统来说是个好消息，但是在 SQE 决定是否暂停作业的方式和时间方面还是存在着一些差异。

在本文的其余部分中，我们将查询引擎用来运行查询的存储称作为 SQE 临时存储。就 Global Storage Bucket 而言， SQE 临时存储可以从 DATABASE Segment Cache ， DATABASE SQE Heap ， DATABASE DSI SQE MIT ，和 DATABASE DS SQE LOB 这些 Buckets 中获知其分配。而那些总是受到 MAXTMPSTG 限制而被分配的存储则被称为非 SQE 临时存储。现在，让我们看看 MAXTMPSTG 如何独特地应用于 SQE 临时存储。

区别 1 ：有条件激活

这第一个区别仅适用于 IBM i 7.2 和 7.3 的。它不适用于 7.4 和更高版本。

有条件激活意味着，只有当系统辅助存储池（ ASP ）（也称为、SYSBAS ）中的空闲空间低于辅助存储下限（ QSTGLOWLMT ）系统值时，才会触发针对 SQE 临时存储的限制。 SQE 使用这种方法，可以将因行为改变而导致的对现有工作负载所造成的中断降至最低。默认情况下， QSTGLOWLMT 为 5% ，即 SYSBAS 中未使用（可用）的空间量。这意味着当系统的 ASP 使用率超过 95% （在默认配置中）时，新的行为模式就会发生。要了解你的系统到底使用了多少系统存储，运行 Work with system Status （ WRKSYSSTS ）命令，并关注 “% system ASP used” 字段，如图 1 所示。该值可以与 DSPSYSVAL SYSVAL(QSTGLOWLMT) 命令的输出进行比较。

图 1 ： WRKSYSSTS 命令显示系统存储池的使用率是 39%

您还可以通过 SQL 查询获得这些信息：

代码清单 1 ：查询系统存储的使用情况和限制

WITH SYSVAL(LOW_LIMIT) AS (
SELECT CURRENT_NUMERIC_VALUE/10000.0 AS QSTGLOWLMT
FROM QSYS2.SYSTEM_VALUE_INFO
WHERE SYSTEM_VALUE_NAME = 'QSTGLOWLMT'
)
SELECT SYSTEM_ASP_USED,
   DEC((100.00 - LOW_LIMIT),4,2) AS SYSTEM_ASP_LIMIT
FROM SYSVAL, QSYS2.SYSTEM_STATUS_INFO ;

当 QSTGLOWLMT 设置为 5% 时，图 1 中的系统不会遇到这个限制。这意味着 MAXTMPSTG 限制将和应用 PTF 之前的操作一样 —— 只有过多的非 SQE 存储才会触发作业的暂停。而一旦使用的存储超过 95% ，新的行为模式将被激活。此时，系统将会针对每个定义了 MAXTMPSTG 的作业，将其中的那些 SQE 存储和非 SQE 临时存储加在一起的总和超过了 MAXTMPSTG 限制的作业暂停。

区别 2 ：只计算活动查询

第二个区别是 SQE 临时存储只计算由当前活动游标分配的临时存储。这很重要，因为它可能会带来一些令人惊讶的结果。

与为作业分配的其它临时存储不同， SQE 分配的存储可以被其它作业重用。正如 本系列的第一篇文章 所解释的，即使在游标关闭后，查询引擎也会经常在内存中保留某些对象。这些对象可以包括临时结果和临时维持索引（ MTI ）。其它作业可以自由地访问和使用这些对象来处理自己的查询。这有利于提高性能，但很难准确地为底层存储分配所有权。

为了适应这种复杂性， SQE 的 MAXTMPSTG 实现只关注为当前运行的查询新分配的那些存储空间。如果缓存的对象被重用，则这些对象将不会包括在对 MAXTMPSTG 的计算当中。类似地，一旦查询结束，它的缓存对象便可以为其它作业所用，分配给这些对象的存储也将不再计入作业的 MAXTMPSTG 数值当中。即使存储空间处于被分配的状态，情况也是一样。

让我们看一个例子。作业的 MAXTMPSTG 限制被设置为 1 GB 。当作业运行时，它将消耗 100 MB 的非 SQE 临时存储空间。该作业还依次运行了三个查询，每个查询都需要 400 MB 新分配的 SQE 临时存储。查询引擎缓存这些对象以便重用。因此，该作业增加了 1300 MB 的系统临时存储空间，大于 1 GB 的 MAXTMPSTG 限制。大家可能估计，第三个查询的运行会导致作业被暂停。但是，因为只计算活动游标的存储空间，所以在作业运行的任何时刻，都不超过 100 MB 加 400 MB 。总计对临时存储的使用从来没有超过 500 MB ，因而从来没有触发 MAXSTGLMT 限制。

另一方面，如果作业运行第四个查询，需要超过 900 MB 的临时存储，此时将超过 1 GB 的限制。该作业将运行查询，直到达到存储限制，那时它将被暂停。该作业将保持被暂停的状态，直到它被终止，或者直到存储限制额度得以增加从而该作业可被释放。注意，取消查询并不会释放作业。取消操作可以在作业被暂停时发起，但处理它必须等到该作业被释放之后。

在这一点上，我们的情况看上去似乎并没有比应用 PTF 之前好多少。毕竟，该作业总不能为了继续运行那 400 MB 的查询，而直接将系统的存储空间耗尽吧？重要的是要记住，还有其它内部限制控制着临时查询对象的缓存。尽管详细信息超出了本文的范围，但只要注意到 SQL 计划缓存是不会无限制地增长的这一点就足够了。最终，一些已分配的对象会将被清除，从而为较新的查询腾出空间。

在大多数情况下，困扰系统管理员和数据库工程师的查询是那些会在没有任何警告的情况下消耗数 GB 的存储空间的查询。增强的 MAXTMPSTG 支持正是为了防止这些庞大的查询而来的。

拿来使用

考虑了 SQE 临时存储的计算方式后，我们可以为使用新的 MAXTMPSTG 支持来开发一个通用策略。如果您以前从未使用过 MAXTMPSTG （即使使用过），您将需要考虑要限制的作业以及多少存储空间的需求会被认为是过多的。使用预料中的查询、行为良好的应用程序可能不需要任何限制。但是，允许 Ad-hoc 查询的应用程序和接口容易受到存储分配失控的影响，值得进一步评估。考虑将多少内存分配给运行作业的内存池。 SYSBAS 有多少可用的存储空间？有多少易受影响的作业会同时运行？记住，这些 PTF 的预期目的就是为了抓住那些失控的作业。你不希望将限制设得太高，以至于作业（或多个作业合并在一起）可以填满 SYSBAS 而不触及相关限制，但你也不希望把限制设得太低，以至于你必须不断地进行干预和释放作业。

如果您确实停止了一个正在失控的查询，那么就把它看作为一个进行深入研究的机会。避免结束作业然后接着继续的诱惑。相反，试着找到为什么查询会消耗如此多存储空间的原因。是否这个查询在试图做太多的事情？查询的环境改变了吗？正确的索引是否使优化器能够选择使用较少查询运行时对象的计划？如果需要帮助，请参阅本页右侧的参考资料，了解关于提高 SQL 性能的更多信息。

提前计划

如果您目前为任何作业使用 MAXTMPSTG 参数，并且您使用的是 IBM i 7.4 之前的版本，那么 IBM 建议您在应用表 1 中的 PTF 之前评估一下系统存储的状态。花些时间看看“ % system ASP used ”和每个系统的 QSTGLOWLMT 。（请参见上面的代码清单 1 。）收集一个设置了 MAXTMPSTG 的所有作业和类的列表。如果系统看起来正在接近 QSTGLOWLMT ，您必须准备好快速响应任何被暂停的工作。了解这些工作是什么，并备有一个适当的计划，以解决他们可能被暂停的问题，是成功应用这些 PTF 的关键。

为了帮助您进行准备工作，请看下面的代码示例。它们结合了来自 IBM Db2 for i 的几个 IBM I Services ，并为查询系统上的作业状态提供了一个良好的开端。

使用代码清单 2 ，您可以找到当前在系统中正在运行的那些设置了 MAXTMPSTG 并且使用了 SQL 的作业。其输出会显示当前使用了多少非 SQE 临时存储以及当前的 MAXTMPSTG 设置。它还会显示作业当前所使用的非 SQE 存储与存储限制之间存在多大的“净空”。 这是查询引擎允许使用的空间量。注意，即便使用表 1 中的 PTF ， TEMP_STORAGE_CONSUMED 值也只包含非 SQE 存储。即使 SQE 临时存储可以从 MAXTMPSTG 中计算出来，但它在每个作业级别上还是无法显示的。

代码清单 2 ：查找使用 MAXTMPSTG 限制运行的作业

SELECT AJ.JOB_NAME,-- THE NAME OF THE JOB THAT IS HELD
   AJ.TEMPORARY_STORAGE, -- THE NON SQE TEMPORARY STORAGE THAT IS ALLOCATED TO THE JOB
   JI.MAXIMUM_TEMPORARY_STORAGE_ALLOWED, -- THE CURRENT VALUE OF MAXTMPSTG FOR THE JOB
   (SELECT INFO.V_SQL_STATEMENT_TEXT -- THE CURRENLY RUNNING SQL STATEMENT.
    FROM TABLE(QSYS2.GET_JOB_INFO(AJ.JOB_NAME)) AS INFO
 WHERE INFO.V_SQL_STMT_STATUS='ACTIVE') AS ACTIVE_SQL_STATEMENT
FROM
   TABLE(QSYS2.ACTIVE_JOB_INFO()) AJ
 INNER JOIN
   TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER=>'*ACTIVE', JOB_USER_FILTER=>'*ALL')) JI
 ON AJ.JOB_NAME=JI.JOB_NAME
WHERE AJ.JOB_STATUS = 'HLD' -- ONLY SELECT JOBS THAT ARE HELD
 AND AJ. JOB_TYPE <> 'SYS' -- AND ARE NOT SYSTEM JOBS
 AND EXISTS (-- AND THAT HAVE A CPI112E (MAXTMPSTG EXCEEDED) WITHIN THE
 SELECT '*' -- LAST 5 MESSAGES IN THE JOB LOG.
 FROM TABLE(QSYS2.JOBLOG_INFO(AJ.JOB_NAME)) AS JLI1
 WHERE MESSAGE_ID = 'CPI112E'
 AND ORDINAL_POSITION + 5 >  
 (SELECT MAX (ORDINAL_POSITION)
 FROM TABLE(QSYS2.JOBLOG_INFO(AJ.JOB_NAME)) AS JLI2)
);

在应用 PTF 之后，您可能会发现它们对监视系统以确定是否有任何作业由于 MAXTMPSTG 的限制而被暂停很有帮助。这就是代码清单 3 中给出的查询的目的。这个查询会返回当前所有被暂停、并且在日志末尾有一条 CPI112E 消息的作业列表。 CPI112E 表示被暂停的原因是 MAXTMPSTG 限制。

代码清单 3 ：查找当前由于 MAXTMPSTG 而被暂停的工作

SELECT AJ.JOB_NAME,-- THE NAME OF THE JOB THAT IS HELD
   AJ.TEMPORARY_STORAGE, -- THE NON SQE TEMPORARY STORAGE THAT IS ALLOCATED TO THE JOB
   JI.MAXIMUM_TEMPORARY_STORAGE_ALLOWED, -- THE CURRENT VALUE OF MAXTMPSTG FOR THE JOB
   (SELECT INFO.V_SQL_STATEMENT_TEXT -- THE CURRENLY RUNNING SQL STATEMENT.
    FROM TABLE(QSYS2.GET_JOB_INFO(AJ.JOB_NAME)) AS INFO
 WHERE INFO.V_SQL_STMT_STATUS='ACTIVE') AS ACTIVE_SQL_STATEMENT
FROM
   TABLE(QSYS2.ACTIVE_JOB_INFO()) AJ
 INNER JOIN
   TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER=>'*ACTIVE', JOB_USER_FILTER=>'*ALL')) JI
 ON AJ.JOB_NAME=JI.JOB_NAME
WHERE AJ.JOB_STATUS = 'HLD' -- ONLY SELECT JOBS THAT ARE HELD
 AND AJ. JOB_TYPE <> 'SYS' -- AND ARE NOT SYSTEM JOBS
 AND EXISTS (-- AND THAT HAVE A CPI112E (MAXTMPSTG EXCEEDED) WITHIN THE
 SELECT '*' -- LAST 5 MESSAGES IN THE JOB LOG.
 FROM TABLE(QSYS2.JOBLOG_INFO(AJ.JOB_NAME)) AS JLI1
 WHERE MESSAGE_ID = 'CPI112E'
 AND ORDINAL_POSITION + 5 >  
 (SELECT MAX (ORDINAL_POSITION)
 FROM TABLE(QSYS2.JOBLOG_INFO(AJ.JOB_NAME)) AS JLI2)
);

或者你可能想从历史的角度看问题。代码清单 4 显示了如何查询 QSYSOPR ，以找出今天因为达到 MAXTMPSTG 限制而被暂停的所有作业。注意，代码清单 3 和代码清单 4 都返回由于使用临时存储过多而被暂停的所有作业；它们并不区分哪些是由于早期 MAXTMPSTG 行为而被暂停的作业，哪些是因为新的 SQE 临时存储保护而被暂停的作业。

代码清单 4 。找到今天因 MAXTMPSTG 保护而被暂停的工作

SELECT SUBSTR(MESSAGE_TEXT, 5, 23) AS HELD_JOB,
        A.* FROM QSYS2.MESSAGE_QUEUE_INFO A
      WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
            MESSAGE_QUEUE_LIBRARY = 'QSYS' AND
            MESSAGE_ID = 'CPI112E' AND
            DATE(MESSAGE_TIMESTAMP) = CURRENT DATE
      ORDER BY MESSAGE_TIMESTAMP DESC;

如果您使用的是 IBM i 7.4 之前的版本，请记住，升级到 IBM i 7.4 或更高版本时，系统将不再使用 QSTGLOWLMT 作为强制执行 MAXTMPSTG 的条件。这可能意味着您现有的限制会在以前没有执行的情况下被执行。因而在升级时，请确保重新评估并考虑调整临时存储的限制。

全速前进

敏捷的思维、可发射蜘蛛网的高手，以及超人的力量让蜘蛛侠拯救了一列满载惊恐乘客的火车。你的 ” 超能力 ” 可能不那么迷人，但它们对保持你系统的正常运行至关重要。通过 Db2 for i 团队最近发布的增强功能以及本系列文章中的信息，您可以使用更多的工具来诊断、解决 —— 以及最重要的 —— 阻止严重的系统紧急情况的发生。

原文网址： Establish SQL temporary storage limits – IBM Developer