ORA的资源限制的设置

前两天用了一下Oracle的PROFILE限制用户资源计划的功能，略有心得，简单总结一下。

资源限制包括一下几种：

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE = 'PRO_1' AND RESOURCE_TYPE = 'KERNEL';

PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- -------------------------------- -------- ------------------
PRO_1 COMPOSITE_LIMIT KERNEL DEFAULT
PRO_1 SESSIONS_PER_USER KERNEL DEFAULT
PRO_1 CPU_PER_SESSION KERNEL DEFAULT
PRO_1 CPU_PER_CALL KERNEL 1
PRO_1 LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PRO_1 LOGICAL_READS_PER_CALL KERNEL DEFAULT
PRO_1 IDLE_TIME KERNEL DEFAULT
PRO_1 CONNECT_TIME KERNEL DEFAULT
PRO_1 PRIVATE_SGA KERNEL DEFAULT

已选择9行。

其中IDLE_TIME和CONNECT_TIME没有什么可说的，需要注意的是，Oracle的检查最小时间间隔是5分钟左右，因此，用户的实际连接时间最多可能是设置值加5分钟。SESSIONS_PER_USER也很容易理解，这里就不讨论了。

对于CPU_PER_SESSION或CPU_PER_CALL限制，设置的值的单位是百分之一秒。一旦一次调用或会话累计CPU调用的时间超过设置值，则终止当前的操作，并返回错误信息。

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

系统已更改。

SQL> CONN YANGTK/YANGTK已连接。
SQL> SELECT COUNT(*) FROM T1;
SELECT COUNT(*) FROM T1
*第 1 行出现错误:
ORA-02393: 超出 CPU 使用的调用限制

SQL> SELECT COUNT(*) FROM T1;

COUNT(*)
----------
50308

SQL> SELECT COUNT(*) FROM T1, T2;
SELECT COUNT(*) FROM T1, T2
*第 1 行出现错误:
ORA-02393: 超出 CPU 使用的调用限制

SQL> SELECT COUNT(*) FROM T1, T2;
SELECT COUNT(*) FROM T1, T2
*第 1 行出现错误:
ORA-02393: 超出 CPU 使用的调用限制

对于消耗资源比较少的查询，第一次执行由于需要分析，而且可能存在物理读，导致查询会失败，再次执行时就可能会通过。对于消耗资源比较大的查询，由于查询操作本身已经超过了资源限制，即使重复执行多次也无法返回结果。

下面来看看LOGICAL_READS的限制。

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = FALSE;

系统已更改。

SQL> ALTER PROFILE PRO_1 LIMIT LOGICAL_READS_PER_CALL 10 CPU_PER_CALL UNLIMITED;

配置文件已更改

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

系统已更改。

SQL> CONN YANGTK/YANGTK
已连接。
SQL> SELECT COUNT(*) FROM T1;
SELECT COUNT(*) FROM T1
*
ERROR 位于第 1 行:
ORA-02395: 超出 IO 使用的调用限制

SQL> SELECT COUNT(*) FROM T2;
SELECT COUNT(*) FROM T2
*
ERROR 位于第 1 行:
ORA-02395: 超出 IO 使用的调用限制

SQL> SELECT * FROM DUAL;

D
-
X

根据上面的例子，可以看出，一旦超过资源限制，则终止当前的操作，并返回错误信息。使用这种方式限制用户对系统资源的不但十分方便，而且效率很高。

然而，上面给出的都是最简单的例子，超出资源限制后何时返回错误信息与很多因素有关，至少和SQL语句的执行计划是有关的。

下面将资源限制的条件放松一点，然后以不同执行计划执行相同的SQL语句：

SQL> ALTER PROFILE PRO_1 LIMIT LOGICAL_READS_PER_CALL 100;

配置文件已更改

SQL> CONN YANGTK/YANGTK
已连接。
SQL> SET TIMING ON
SQL> SELECT /*+ USE_NL(T1, T2) USE_NL(T3) */ COUNT(*) FROM T1, T2, T3 
2 WHERE T1.OWNER = T2.OWNER 
3 AND T1.OWNER = T3.OWNER;
SELECT /*+ USE_NL(T1, T2) USE_NL(T3) */ COUNT(*) FROM T1, T2, T3
*
ERROR 位于第 1 行:
ORA-02395: 超出 IO 使用的调用限制

已用时间: 00: 00: 00.00
SQL> SELECT /*+ USE_HASH(T1, T2) USE_HASH(T3) */ COUNT(*) FROM T1, T2, T3 
2 WHERE T1.OWNER = T2.OWNER 
3 AND T1.OWNER = T3.OWNER;
SELECT /*+ USE_HASH(T1, T2) USE_HASH(T3) */ COUNT(*) FROM T1, T2, T3
*
ERROR 位于第 1 行:
ORA-02395: 超出 IO 使用的调用限制

已用时间: 00: 01: 31.04

可以看到，仅仅是执行计划不同，且最后都由于超过了资源限制而返回了错误信息，但是对于NESTED LOOP执行计划，语句在瞬间就返回了结果，而对于HASH JOIN执行计划，语句执行了1分31秒后才返回结果。

因此，在正式环境中使用资源限制需要小心。资源限制是在运行中检查使用资源是否超过限制，也就是说SQL语句首先需要运行，这必然就会消耗一定的资源。而且如果资源限制的设置值相对较大，则资源限制受执行计划等其他因素影响较大，那么资源限制很可能无法像想象中的那样有效的工作。很可能用户的SQL语句所消耗的资源远远超过了资源限制的设置且运行了很长的时间，才返回错误信息。这时，使用资源限制就失去了原有的意义。

和资源限制具有类似功能的还有Oracle的资源管理器，其中有一项功能可以在SQL运行之前估计SQL执行的时间，如果估计执行时间超过设置的时间，则直接返回错误信息。