从隐式转换案例，来挖掘开发人员的技能提升

深藏阁楼爱情的钟 2023-07-10 14:22 6阅读 0赞

前几天在墨天轮的论坛上，碰到个问题(https://www.modb.pro/issue/1440)，有点意思，值得在这说下。

问题说明：

TBL\_TallyABC存在联合主键，

WorkDate，ReceiveDate，StatDate，BillCycle，SettlementOrgID，StatType

SQL的执行计划是全表扫描，

update TBL_TallyABC set Count = Count + 1,Amount = Amount + :1
    where WorkDate = :2 and ReceiveDate = :3 and StatDate = :4 and
    BillCycle = :5 and SettlementOrgID = :6 and StatType = :7

![format_png][]

发现这个SQL造成数据库高峰时段75%的enq: TX - row lock contention等待事件，且锁的模型是6是什么原因呢，怎样减少enq: TX - row lock contention？

从上面的信息，可以知道这几点，

1. update语句的where条件字段都是复合索引的字段。

2. 但是update语句没用上索引，为全表扫描。

3. 执行update语句期间，数据库出现enq: TX - row lock contention争用。

基于以上信息，推测之所以出现行锁争用，是这样的逻辑，

![format_png 1][]

当对表行进行DML操作的时候，需要获取相应锁，enq: TX - row lock contention就是行锁争用，之所以出现这个争用，就是因为UPDATE用了全表扫描，导致一条SQL的执行时间比以前更久，大量UPDATE操作，雪崩效应，就会让行锁争用更明显。

朋友虽然没给出这个问题中具体的表结构、绑定变量值，但是，我们是可以反推出实验的，如下所示，表t\_001，含三个字段，分别是timestamp、date和number类型，此时，创建(timestamp, date, number)复合索引，

SQL> create table t_001(a_ts timestamp, a_date date, id number);
    Table created.
    
    SQL> alter table t_001 add constraint pk_t_001 primary key(a_ts, a_date, id);
    Table altered.

执行select，where条件涉及时间的，统一使用to\_date转成date类型，左值a\_ts字段是timestamp类型，右值date类型，因为timestamp优先级高于date，因此在这左值不用隐式转换，从使用的索引唯一扫描就能证明这点，复合索引的三个字段都用上了，

SQL> select * from t_001 where id = 1 and a_ts  = to_date('2020-02-15','yyyy-mm-dd') and 
      2  a_date = to_date('2020-02-15','yyyy-mm-dd');
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 76550962
    
    ------------------------------------------------------------------------------
    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |          |     1 |    35 |     0   (0)| 00:00:01 |
    |*  1 |  INDEX UNIQUE SCAN| PK_T_001 |     1 |    35 |     0   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:00:00' AND
                  "A_DATE"=TO_DATE(' 2020-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                  "ID"=1)

但是，当使用下面的SQL，where条件涉及时间的统一使用to\_timestamp转成timestamp类型，左值a\_date是date类型，右值转成timestamp类型，因为timestamp优先级高于date，此时左值会进行隐式转换，需要将date转成timestamp，而且从谓词信息，可以看到，Oracle对a\_date使用了INTERNAL\_FUNCTION函数，对字段使用了函数操作，就会导致字段上的索引失效，谓词从access改为了filter，这个复合索引，仅可用到其中一部分字段，其他字段只能作为过滤条件，从使用的索引范围扫描就能证明这个推测，

SQL> select * from t_001 where id = 1 and a_ts  = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss')
      2  and a_date = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss');
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 491049371
    
    -----------------------------------------------------------------------------
    | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |          |     1 |    35 |     0   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| PK_T_001 |     1 |    35 |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:01:00.000000000' AND
                  "ID"=1)
           filter("ID"=1 AND INTERNAL_FUNCTION("A_DATE")=TIMESTAMP'
                  2020-02-15 00:01:00.000000000')

隐式转换，说白了，就是当where条件“=”号右值的优先级高于“=”号左值的优先级，此时就需要对左值进行函数操作，借此转换成和右值相同的类型，就像Oracle中nvarchar2、varchar2、char以及timestamp和date。

以下是和隐式转换相关的历史文章，

《[浅谈显式转换和隐式转换][Link 1]》

《[隐式转换的案例场景][Link 2]》

《[一次有意思的错选执行计划问题定位][Link 3]》

对这个问题，如果各位有什么其他的见解，欢迎在文末留言，我们一同探讨。

然而，实际情况是，开发人员很可能因为对字段类型的错误理解，导致写出来的程序出现隐式转换，而且在测试环境，数据量很小，这种性能问题是不能暴露出来的，可到了生产环境，他就会对系统的稳定运行产生影响。

因此，作为开发人员，除了需要了解数据库的CRUD，对可能产生性能隐患的一些数据库基础知识同样需要了解，不能做code的搬运工，而是要做创造者，才可能提升自己的个人价值。

当你要确定自己写的SQL代码在性能上是否存在隐患的时候，就可能会用到执行计划，你要知道怎么得到真实的执行计划，判断执行计划的正确，根据执行计划纠正自己的SQL。

当你要对表结构做调整，例如增加字段、删除字段，你可能需要了解在执行过程中他会持有什么级别的锁，知道这个操作对数据库有什么影响。

当你要删除大表数据，或者更新大表数据的时候，你可能需要根据实际情况，以及对数据库原理的理解，来判断选择合适的方案，方案选择的对，可能秒级完成，否则，几个小时都未必能完成。

其实像这样的例子还很多，开发人员对数据库原理了解的越多，就会越让你在关键时刻做出正确的判断。

当然，这些都取决于你在日常的积累。我这个杂货铺就是了解这些知识的渠道之一，这张图是杂货铺中和Oracle相关的文章分类，其中一些是站在开发人员的角度讲解的知识，或是碰到的实际问题，欢迎各位品读、尝试和讨论，

![format_png 2][]

**近期热文：**

《[公众号600篇文章分类和索引][600]》

《[Oracle ACE，一段不可思议的旅程][Oracle ACE]》

《[Oracle 19c之RPM安装][Oracle 19c_RPM]》

《[应用执行慢的问题排查路径][Link 4]》

《[ACOUG年会感想][ACOUG]》

《[千万级表数据更新的需求][Link 5]》

《[探寻大表删除字段慢的原因][Link 6]》

《[一次Oracle bug的故障排查过程思考][Oracle bug]》

《[新增字段的一点一滴技巧][Link 7]》

《[对recursive calls的深刻理解][recursive calls]》

《[《Oracle Concept》第三章 - 12][Oracle Concept_ - 12]》

《[幂等性][Link 8]》

《[一次惊心动魄的问题排查][Link 9]》

《[Java日期中“y”和“Y”的区别][Java_y_Y]》

《[英超梦幻之行][Link 10]》

《[藤子不二雄博物馆之行][Link 11]》

《[传控Tiki-Taka战术解惑][Tiki-Taka]》

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWw2MUU0dlpmYkhJbURkTUIzWkw2clNZUVJ3Tnd1ZE9xT3BCdnE3bFRJM2h6TTY1WEZLRzl6NlNucXVwWWdySGRUbW9rSVJ2amR1amcvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWw2MUU0dlpmYkhJbURkTUIzWkw2clNOc2dJODVjbWFYdDdWeXNjZklJRmljZjNzdDE1bFZNbm5LWk43TGljbXFmNjF0MGF2bWtWcHo1US82NDA?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814a02ddf6c31494565b7add85e0ab7cd6497c06920c7f13b338363660a74ca7f2218c34cc&idx=1&mid=2247487473&scene=21&sn=3a1c9fee7d73e5fda1612d65166b7511#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81485dddf6c14b7af06e84eb4115815828aa958a7b3c42283a1b8feb1cff3e25d7ea5837f1&idx=1&mid=2247486894&scene=21&sn=96754f2cdbd73086dc7a3126ec46fb69#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/46137895
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW5NSmlhVlpSSE9OU09CUTFiaGZhaWE1SUdMSW1Vc3RNVmVucU5FenB6VkR2VW5EdDdLcENqcTdpYlNvT1hpY09HemJEOWZKYTM1NmxUZzJBLzY0MA?x-oss-process=image/format,png
[600]: https://blog.csdn.net/bisal/article/details/104548761
[Oracle ACE]: https://blog.csdn.net/bisal/article/details/94386471
[Oracle 19c_RPM]: https://blog.csdn.net/bisal/article/details/100909708
[Link 4]: https://blog.csdn.net/bisal/article/details/102480420
[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 6]: https://blog.csdn.net/bisal/article/details/86635285
[Oracle bug]: https://blog.csdn.net/bisal/article/details/101729688
[Link 7]: https://blog.csdn.net/bisal/article/details/103626212
[recursive calls]: https://blog.csdn.net/bisal/article/details/100074863
[Oracle Concept_ - 12]: https://blog.csdn.net/bisal/article/details/100788405
[Link 8]: https://blog.csdn.net/bisal/article/details/92802477
[Link 9]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8149e8ddf6c0fea92181efe658b69c6acd52b691cd562e830f0e63a565215a136105272d27&idx=1&mid=2247486491&scene=21&sn=584c4ac132b69a1e319b2a2bedfd49e2#wechat_redirect
[Java_y_Y]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815467ddf6dd717251b9f1b6ccd1ec838908ff11d6981159db79c56a2fa554ea5f483c969f&idx=1&mid=2247487892&scene=21&sn=7aa998ef83b3ba3308615808dc3ceba2#wechat_redirect
[Link 10]: https://blog.csdn.net/bisal/article/details/103044518
[Link 11]: https://blog.csdn.net/bisal/article/details/103379370
[Tiki-Taka]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814e5cddf6c74a763ac9fac98f94e538bca009fdcff3795533026269686983ceae190fe883&idx=1&mid=2247486383&scene=21&sn=d4c54d7033fc9a21d622e5937cddf64e#wechat_redirect