[20211210]优化遇到的奇怪问题.txt

[20211210]优化遇到的奇怪问题.txt --//上午优化一条sql语句,遇到一些问题浪费许多时间,直到上午快下班才定位解决,在测试环境做一个小演示,避免在这些细节上犯错. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立测试: $ cat t1.txt alter session set statistics_level = all; set term off select * from dept where REGEXP_LIKE(dname, '00', '00'); set term on @ dpc '' '' --//简单说明:我们生产系统设置cursor_sharing=force,这样里面的常量变成:"SYS_B_03", :"SYS_B_04"之类的.如果写在where里面的, --//可以查看v$sql_bind_capture视图获得.而在select里面的值无法抓取，我一般选择'00'字符. --//我的测试REGEXP_LIKE出现在where中,而我调试的sql语句REGEXP_LIKE出现在select 里面. SCOTT@book> @ t1.txt Session altered. PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  fy92msh4xvx6u, child number 0 ------------------------------------- select * from dept where REGEXP_LIKE(dname, '00', '00') Plan hash value: 3383998547 ---------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | ---------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 | ---------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter( REGEXP_LIKE ("DNAME",'00','00',<not feasible>)    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//可以发现实际上居然错误的执行语句,可以看到执行计划.由于语句异常中止,我自己测试执行的sql语句非常快返回,而生产系统的语 --//句执行缓慢,非常不好理解. --//我开始注解一些where条件,最后发现问题在REGEXP_LIKE(dname, '00', '00'),我猜测第3个参数应该是i. select * from dept where REGEXP_LIKE(dname, '00', 'i'); SCOTT@book> @ t1.txt Session altered. PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  2qwr8pxwdp0qu, child number 0 ------------------------------------- select * from dept where REGEXP_LIKE(dname, '00', 'i') Plan hash value: 3383998547 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |       6 | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       6 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter( REGEXP_LIKE ("DNAME",'00','i',HEXTORAW('50B1FE7B0000000004D90402000000000000000000000000C883D               A060000000000000000000000000000000000000000120000000000000080B1FE7B0000000002000000000000000000000085000000'               ) )) --//oracle过滤条件很奇特，以前没注意还有第4个参数. --//再次测试,这样才能正常还原生产系统遇到的情况. --//实际上我当时还犯了几个错误,当时注解set term off,执行是没有仔细看前面的提示,实际上执行语句已经报错.我自己没有想到这样也 --//能看到执行计划.实际上如果当时执行set echo on,也许就很注意到问题在那里了. --//浪费差不多一个小时,做一个记录,避免以后在这些细节上犯错. --//如果当时我打开set echo on，关闭set term off，这个错误很快能发现： SCOTT@book> @ t1.txt SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> --set term off SCOTT@book> select * from dept where REGEXP_LIKE(dname, '00', '00'); select * from dept where REGEXP_LIKE(dname, '00', '00')                                             * ERROR at line 1: ORA-01760: illegal argument for function PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  fy92msh4xvx6u, child number 0 ------------------------------------- select * from dept where REGEXP_LIKE(dname, '00', '00') Plan hash value: 3383998547 ---------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | ---------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 | ---------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter( REGEXP_LIKE ("DNAME",'00','00',<not feasible>) --//主要问题在于我没想到oracle sql语句执行报错，也能生成执行计划。