对recursive calls的深刻理解

布满荆棘的人生 2024-04-17 06:06 66阅读 0赞

这篇文章《[这条SQL的索引，你会如何创建？][SQL]》发出后，不少朋友留言，包括一些前辈，指出了其中存在的问题，需要纠正和说明。

**问题1，部分截图中有递归调用，这样算一致性读，不准确？**

SQL执行计划中的统计信息部分，出现不为0的recursive calls，对结果的判断，究竟有什么影响？

![640?wx\_fmt=png][640_wx_fmt_png]

说到这，首先就要了解，什么是递归调用，recursive calls？

Oracle官方文档的解释，

Sometimes, to execute a SQL statement issued by a user, Oracle Database must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle Database makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.

Note: Recursive SQL statistics are not included for SQL-level operations.

大致意思是，有时，用户执行一条SQL语句的时候，Oracle必须调用其他的语句，这些额外调用的语句，就称为"recursive calls"，或者"recursive SQL statements"，Sometimes，有时会，有时不会，他举了两个例子，当插入记录的时候，没空间容纳这行，此时Oracle就会通过递归调用动态分配空间，另外当数据字典缓存中无法得到需要的数据字典信息时，必须从磁盘读取，此时就会执行递归调用。SQL级别的执行，不包括递归调用执行的SQL统计信息。

IBM的手册中，讲了递归调用的触发条件，

http://publib.boulder.ibm.com/tividd/td/ITMD/SC23-4724-00/en\_US/HTML/oraclepac510rg59.htm

*  An object requiring an additional extent for storage (dynamic extension)，动态扩展分配额外的空间存储对象
 *  Misses on the dictionary cache，数据字典缓存缺少需要的信息
 *  Firing of database triggers，数据库触发器
 *  DDL statements，DDL语句
 *  Execution of SQL statements within stored procedures, packages, functions, and anonymous PL/SQL blocks，在存储过程、包、函数和匿名PL/SQL块中执行SQL语句
 *  Enforcement of referential integrity constraints，执行外键完整性约束

针对我们的测试，不同的场景，每个语句首次执行的时候，都可能出现从磁盘读取数据字典信息的需求，但正如上面说的，这种recursive calls是Oracle为了满足用户检索的需求，额外调用的语句，如果比较的是不同SQL本身的性能消耗，公平起见，就需要忽略这些recursive calls。

因此，每个场景，都取第二次执行的统计信息，这种比较，才是更公平的。

重建测试场景，这是未建任何索引的时候，相关的成本消耗，此时recursive calls是0，

SQL> select max(object_id) from t where owner='SYS';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2966233522
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    30 |    13   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
    |*  2 |   TABLE ACCESS FULL| T    |  9165 |   268K|    13   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OWNER"='SYS')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             40  consistent gets
              0  physical reads
              0  redo size
            534  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

方案1，object\_id单键值索引，

方案2，owner单键值索引，

方案3，(object\_id, owner)复合索引，

SQL> select max(object_id) from t where owner='SYS';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2966233522
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    30 |    13   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
    |*  2 |   TABLE ACCESS FULL| T    |     1 |    30 |    13   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OWNER"='SYS')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             40  consistent gets
              0  physical reads
              0  redo size
            534  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

方案4，(owner, object\_id)复合索引，

SQL> select max(object_id) from t where owner='SYS';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2574007102
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |          |     1 |    30 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |          |     1 |    30 |            |          |
    |   2 |   FIRST ROW                  |          |     1 |    30 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX_T_01 |     1 |    30 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("OWNER"='SYS')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
            534  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

从上面的测试，很清楚地看出，(owner, object\_id)的成本消耗最低。

**问题2，既然问题1解决了，上次的结论，还正确？**

从测试结论看，上次的结论，是错误的，显然(owner, object\_id)复合索引的效率最高，object的单键值索引，条件owner不是索引的组成部分，自然是无法使用该索引，那什么情况下，(object\_id, owner)的效率可以？

这种索引的结构，owner重复值很少，

![640?wx\_fmt=png][640_wx_fmt_png 1]

就像这个测试中，owner='USER2'的记录只有几条，重复值很少，这条语句执行，用到的是INDEX FAST FULL SCAN，

SQL> select max(object_id) from t where owner='USER2';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2276624515
    
    ----------------------------------------------------------------------------------
    | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |          |     1 |    30 |    18   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |          |     1 |    30 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| IDX_T_01 |     1 |    30 |    18   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("OWNER"='USER2')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             58  consistent gets
              0  physical reads
              0  redo size
            534  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

但是，如果owner重复值很高，如下这种结构，就像这个测试中，owner='SYS'，或者一个不存在的值，owner='X'，此时几乎就要做一次完整的INDEX FAST FULL SCAN，相比(owner, object\_id)的INDEX RANGE SCAN，效率要低些，

![640?wx\_fmt=png][640_wx_fmt_png 2]

因此，在这个测试中，(object\_id, owner)效率的高低，取决于owner值的数据分布，而(owner, object\_id)效率高低，和数据分布，没任何关系，这个owner=的条件，消耗几个buffer就可以，再通过INDEX RANGE SCAN MIN/MAX，得到他的最值。

对recursive calls以及索引原理的准确理解，是判断这个问题的关键，很明显，上次的测试过程，自己犯了错误，给各位造成困惑，抱歉，多谢各位前辈、朋友的指教。

[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247489300&idx=1&sn=125b9b88f5f229387d2d04823d782aa5&chksm=ea8152e7ddf6dbf1f4b3a6b4c7b5738939818b19973078298cefa2b2bab4d214ba496f072a54&scene=21#wechat_redirect
[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanIP1npe64g0Qm2Kic60Aq7NlVxreGVVv5WuXJEoFaCRqPqJuKGXE9SCIUtzaCELMicEacqibmqCBTkw/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanIP1npe64g0Qm2Kic60Aq7NiacvvyM2CH2DvK3uCDDLMhm8agicAklns6G1QlTseYNKXJFfdUnMPczQ/640?wx_fmt=png
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanIP1npe64g0Qm2Kic60Aq7NsQ5MDtoXflztEgpjv3rNiaH5MjF2yjRfotClGlTdS6pSY73zw8Ro01A/640?wx_fmt=png