[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语句执行报错，也能生成执行计划。