[20220422]为什么执行不报错.txt

[20220422]为什么执行不报错.txt --//生产系统1条sql语句，明显存在隐式转换，而且这个转换无法进行的，但是我奇怪的是执行不报错。 1.环境: > @ 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.分析: > @ sql_id 0818yxqmw7n43 --SQL_ID = 0818yxqmw7n43 select c.brch,        c.brxm,        a.tmbh,        a.examinaim,        null cause,        a.requesttime,        null register_time,        null report_person,                  :"SYS_B_0" clyj,                  a.doctrequestno         from l_lis_sqd a , zy_brry c         where a.patientid = c.zyhm    and a.qrht is null         and c.brbq = :al_brbq and c.brxm =:"SYS_B_1"; > @ bind_cap 0818yxqmw7n43 '' SQL_ID        CHILD_NUMBER WAS NAME     POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------- -------- ---------- ------------------- --------------- ------------ 0818yxqmw7n43            0 YES :AL_BRBQ        2         22 2022-04-08 09:05:43 NUMBER          1                            YES :SYS_B_1        3         22 2022-04-08 09:05:43 NUMBER          1 --//brxm表示病人姓名，直接带入数字1，存在隐式转换，变成to_number(c.brxm)=:"SYS_B_1". > @ d_buffer 0818yxqmw7n43 60 1     EXECUTIONS1    BUFFER_GETS1   ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets    每次执行时间  平均处理记录数         INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------          105124       189651724      1193685786               0 1804.0763669571 11355.026311784               0               1 ... sleep 60 , waiting ....     EXECUTIONS2    BUFFER_GETS2   ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets    每次执行时间  平均处理记录数         INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------          105126       189655482      1193717300               0 1804.0777923634 11355.110058406               0               1        执行次数   总buffer_gets      总执行时间    总处理记录数 每次buffer_gets    每次执行时间  平均处理记录数 --------------- --------------- --------------- --------------- --------------- --------------- ---------------               2            3758           31514               0            1879           15757               0 --//至少说明这条语句可以执行的。每次逻辑读1879，但是每次记录数都是0。 > select * from pppppp_hhh.zy_brry where brxm=1; select * from pppppp_hhh.zy_brry where brxm=1                                        * ERROR at line 1: ORA-01722: invalid number > @ sl all alter session set statistics_level = all; Session altered. > variable  a number; > exec :a := 1; PL/SQL procedure successfully completed. > select * from pppppp_hhh.zy_brry where brxm=:a; select * from pppppp_hhh.zy_brry where brxm=:a                                        * ERROR at line 1: ORA-01722: invalid number > @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  9fqpamj5sc661, child number 0 ------------------------------------- select * from pppppp_hhh.zy_brry where brxm=:a Plan hash value: 1081078487 ------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |      1 |        |       |   509 (100)|          |      0 |00:00:00.01 | |*  1 |  TABLE ACCESS FULL| ZY_BRRY |      1 |      1 |   568 |   509   (1)| 00:00:07 |      0 |00:00:00.01 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / ZY_BRRY@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - (NUMBER): 1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_NUMBER("BRXM")=:A) --//即使语句不能正常执行，执行计划还是存在的。 > @ tpt/sqlid 9fqpamj5sc661 % Show SQL text, child cursors and execution stats for SQLID 9fqpamj5sc661 child %   CH#    PLAN_HASH       PARSES     H_PARSES EXECUTIONS      FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC_EXEC ELA_SEC/EXEC LIOS_PER_EXEC PIOS_PER_EXEC TOTAL_CPU_SEC TOTAL_ELA_SEC TOTAL_IOWAIT_SEC   TOTAL_LIOS   TOTAL_PIOS        SORTS USERS_EXECUTING LAST_ACTIVE_TIME    PARENT_HANDLE    OBJECT_HANDLE ----- ------------ ------------ ------------ ---------- ------------ -------------- -------------- ------------ ------------ ------------- ------------- ------------- ------------- ---------------- ------------ ------------ ------------ --------------- ------------------- ---------------- ----------------     0   1081078487            3            1          3            3              0              0         .001         .002             3             0          .004          .005                0            9            0            0               0 2022-04-22 10:26:55 00000005E77DF028 00000005E77DEEB8 --//仅仅执行3次，逻辑读每次也就是3个。看看上面的执行计划： Plan hash value: 2161839103 -------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name                   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                        |        |       |   514 (100)|          | |   1 |  NESTED LOOPS                |                        |      1 |    91 |   514   (1)| 00:00:07 | |   2 |   NESTED LOOPS               |                        |      7 |    91 |   514   (1)| 00:00:07 | |*  3 |    TABLE ACCESS FULL         | ZY_BRRY                |      1 |    24 |   509   (1)| 00:00:07 | |*  4 |    INDEX RANGE SCAN          | I_L_LIS_SQD_PATIENT_ID |      7 |       |     2   (0)| 00:00:01 | |*  5 |   TABLE ACCESS BY INDEX ROWID| L_LIS_SQD              |      7 |   469 |     5   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    3 - SEL$1 / C@SEL$1    4 - SEL$1 / A@SEL$1    5 - SEL$1 / A@SEL$1 Peeked Binds (identified by position): --------------------------------------    2 - (NUMBER): 1 Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter(("C"."BRBQ"=:AL_BRBQ AND TO_NUMBER("C"."BRXM")=:SYS_B_1))    4 - access("A"."PATIENTID"="C"."ZYHM")    5 - filter("A"."QRHT" IS NULL) --//难道过滤是先执行"C"."BRBQ"=:AL_BRBQ，如果不满足就没有执行TO_NUMBER("C"."BRXM")=:SYS_B_1)。 > select * from pppppp_hhh.zy_brry where brbq=1; no rows selected --//确实没有这样的记录。 > select * from pppppp_hhh.zy_brry where brbq=1 and brxm=1; no rows selected --//发现可以正常执行，这样就验证前面生产系统遇到的情况。 > @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  0sxwy69ac6p7d, child number 0 ------------------------------------- select * from pppppp_hhh.zy_brry where brbq=1 and brxm=1 Plan hash value: 1081078487 ----------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |      1 |        |       |   509 (100)|          |      0 |00:00:00.01 |    1879 | |*  1 |  TABLE ACCESS FULL| ZY_BRRY |      1 |      1 |   568 |   509   (1)| 00:00:07 |      0 |00:00:00.01 |    1879 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / ZY_BRRY@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(("BRBQ"=1 AND TO_NUMBER("BRXM")=1)) --//逻辑读=1879.反过来写看看： > select * from pppppp_hhh.zy_brry where brxm=1 and brbq=1; no rows selected --//依旧可以正常执行。 > @ descz pppppp_hhh.zy_brry "column_name  in ('BRXM','BRBQ')" eXtended describe of pppppp_hhh.zy_brry DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name            SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density    NUM_NULLS HISTOGRAM        NUM_BUCKETS Low_value      High_value ---------- -------------------- ------------ ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ------------ --------------- ------------ -------------- ----------- pppppp_hhh ZY_BRRY                     25117 2022-04-21 09:24:39    8 BRXM                 NOT NULL   VARCHAR2(40)                18390   .00005437738            0                            1 LUC THI THA0毛 瞿凤连                                        24996 2022-04-21 09:24:39   35 BRBQ                            NUMBER(18,0)                   25   .04000000000          121                            1 251            1219 --//我估计因为隐式转换的函数，导致oracle最后做brxm=1的判断，与字段在数据段的位置没有关系。换一个字段brxz在brxm前面。 > select * from pppppp_hhh.zy_brry where brxz=100000 and brxm=1; no rows selected > select * from pppppp_hhh.zy_brry where brxz=2 and brxm=1; select * from pppppp_hhh.zy_brry where brxz=2 and brxm=1                                                   * ERROR at line 1: ORA-01722: invalid number 3.总结： --//我不知道为什么这样的代码出现在生产系统中，而且在不断的执行，这样的代码说不定那一天就是一个杀手程序，目前zy_brry不大 --//。影响也不大。