[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不大
--//。影响也不大。