前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >对recursive calls的深刻理解

对recursive calls的深刻理解

作者头像
bisal
发布于 2019-08-29 11:17:59
9460
发布于 2019-08-29 11:17:59
举报
文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

问题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，

代码语言：javascript
复制
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单键值索引，

代码语言：javascript
复制
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

方案2，owner单键值索引，

代码语言：javascript
复制
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

方案3，(object_id, owner)复合索引，

代码语言：javascript
复制
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)复合索引，

代码语言：javascript
复制
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

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

代码语言：javascript
复制
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

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

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

本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019年08月26日，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
oracle
编程算法
腾讯云测试服务
缓存

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

sql
oracle
编程算法
腾讯云测试服务
缓存
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论