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

[20220422]為什麼執行不報錯.txt

相關文章