sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確

平臺：Oracle 9.2.0.8.0 - 64bit

發現sql消耗I/O很大

SELECT B.WO_NO, B.NO, B.CUST_PART_NO,B.SKU_NO, D.NAMEE,A.NAMEE
        FROM IMB.STATION D,IMB.R_WIP B,
            (SELECT  A.WIP_ID ,E.NAMEE
               FROM IMB.R_WIP_LOG A ,IMB.STATION B,IMB.LINE E
                         WHERE  A.LINE_ID=E.ID
                           AND  A.STATION_TIME>=TO_DATE(:"SYS_B_00",:"SYS_B_01")
                           AND  A.STATION_TIME<=TO_DATE(:"SYS_B_02",:"SYS_B_03")
                           AND  A.STATION_ID=B.ID
                           AND  B.NAMEE  =:"SYS_B_04"
                           AND  A.LINE_ID=E.ID
                           AND  E.CODE =:"SYS_B_05"
                           and a.DEL_FLAG=:"SYS_B_06"
            )A,IMB.PRODUCT C
            WHERE D.CODE=B.CUR_STATION_CODE
            AND B.CUR_STATION_CODE= :"SYS_B_07"
            AND B.ID=A.WIP_ID
            AND B.DEL_FLAG=:"SYS_B_08"
            AND B.PRODUCT_ID=C.ID
            AND C.CATEGORY_KEY =:"SYS_B_09"
            AND EB_WEB.Get_Dsdnb_Configfast(B.CUST_PART_NO) /*函式*/= :"SYS_B_10"

透過explain plan for和set autotrace on得到的執行計劃均為下面：

------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                      | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |     1 |   158 |     7 |
|   1 |  FILTER                           |                            |       |       |       |
|   2 |   MERGE JOIN CARTESIAN            |                            |     1 |   158 |     7 |
|   3 |    NESTED LOOPS                   |                            |     1 |   141 |     6 |
|   4 |     NESTED LOOPS                  |                            |     1 |   123 |     5 |
|   5 |      NESTED LOOPS                 |                            |     1 |   111 |     4 |
|   6 |       NESTED LOOPS                |                            |     1 |    44 |     3 |
|   7 |        TABLE ACCESS BY INDEX ROWID| LINE                       |     1 |    20 |     1 |
|   8 |         INDEX RANGE SCAN          | IX_LINE_CODE               |     1 |       |     1 |
|   9 |        TABLE ACCESS BY INDEX ROWID| R_WIP_LOG                  |     1 |    24 |     2 |
|  10 |         INDEX RANGE SCAN          | IX_R_WIP_LOG_STATION_TIME  |     3 |       |     2 |
|  11 |       TABLE ACCESS BY INDEX ROWID | R_WIP                      |     1 |    67 |     1 |
|  12 |        INDEX UNIQUE SCAN          | IDX_R_WIP_ID               |     1 |       |     1 |
|  13 |      TABLE ACCESS BY INDEX ROWID  | PRODUCT                    |     1 |    12 |     1 |
|  14 |       INDEX UNIQUE SCAN           | PRODUCT_ID                 |     1 |       |       |
|  15 |     TABLE ACCESS BY INDEX ROWID   | STATION                    |     1 |    18 |     1 |
|  16 |      INDEX UNIQUE SCAN            | STATION_ID                 |     1 |       |       |
|  17 |    BUFFER SORT                    |                            |     1 |    17 |     6 |
|  18 |     TABLE ACCESS BY INDEX ROWID   | STATION                    |     1 |    17 |     1 |
|  19 |      INDEX RANGE SCAN             | IX_STATION_CODE            |     1 |       |       |
------------------------------------------------------------------------------------------------

按此執行計劃應該不至於產生高I/O消耗，做了次10046 trace(alter session set events '10046 trace name context forever';)發現執行計劃迥異，懷疑是SQL中用到函式EB_WEB.Get_Dsdnb_Configfast所導致:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.25       9.49     126822     135314          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.25       9.50     126822     135314          0          27

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  FILTER 
     27   NESTED LOOPS 
     27    NESTED LOOPS 
     75     HASH JOIN 
      1      TABLE ACCESS FULL STATION
   1275      TABLE ACCESS BY INDEX ROWID R_WIP_LOG
   1359       NESTED LOOPS 
     79        MERGE JOIN CARTESIAN
      1         TABLE ACCESS BY INDEX ROWID STATION
      1          INDEX RANGE SCAN IX_STATION_CODE (object id 31557)
     79         BUFFER SORT
     79          TABLE ACCESS FULL R_WIP
   1279        INDEX RANGE SCAN IX_WIP_LOG_WIP_ID (object id 31496)
     27     TABLE ACCESS BY INDEX ROWID LINE
     75      INDEX UNIQUE SCAN LINE_ID (object id 31239)
     27    TABLE ACCESS BY INDEX ROWID PRODUCT
     27     INDEX UNIQUE SCAN PRODUCT_ID (object id 31308)