各位看官，现有一个View如下：

SELECT REQUEST_ID,
    SO_NUM,
    JOB_NUM,
    MAIN_JOB_NUM,
    SO_TYPE,
    IBAS_EVENT_ID,
    END_CUST_NAME,
    MO_SHIP_SET_NAME,
    CASE
      WHEN BILLING_MILESTONE='PARTS SHIPMENT'
      THEN UPPER(DECODE(SHIP_SET_STATUS,'BOX RELEASE',UPPER(FS_APP_133_GET_CURRENT_STEP(N_PROCESS_INST)),UPPER(SHIP_SET_STATUS)))
      WHEN BILLING_MILESTONE IN ('ADVANCE PAYMENT','POST SHIPMENT')
      THEN UPPER(DECODE(SHIP_SET_STATUS,NULL,FS_APP_133_GET_CURRENT_STEP(N_PROCESS_INST),SHIP_SET_STATUS))
    END SHIP_SET_STATUS,
    LINES_COUNT,
    AMT_USD,
    LOT_PROMISED_DATE,
    CWD,
    BILLING_MILESTONE,
    N_PROCESS_INST,
    --FS_APP_069_GET_PROCESS_INST(HAND_OFF_REQUEST_ID) HAND_OFF_REQUEST_ID,
    HAND_OFF_REQUEST_ID,
    CWD_FLAG
  FROM
    (SELECT REQUEST_ID,
      SO_NUM,
      FS_APP_101_HAND_OFF_REQ(PROCESS_INST,'JOB_NUM') JOB_NUM,
      FS_APP_101_HAND_OFF_REQ(PROCESS_INST,'MAIN_JOB_NUM') MAIN_JOB_NUM,
      FS_APP_101_HAND_OFF_SUB_TYPE(PROCESS_INST) SO_TYPE,
      FS_APP_111_GET_IBAS_EVNT_ID(PROCESS_INST) IBAS_EVENT_ID,
      END_CUST_NAME,
      MO_SHIP_SET_NAME,
      DECODE(LENGTH(SHIP_SET_STATUS),1,NULL,SHIP_SET_STATUS) SHIP_SET_STATUS,
      SO_LINES_COUNT LINES_COUNT,
      CASE
        WHEN ((REQUEST_TYPE LIKE 'COMMERCIAL%'
        AND BILLING_MILESTONE='PARTS SHIPMENT')
        OR (REQUEST_TYPE     ='PROFORMA'))
        THEN TO_CHAR(MO_SHIP_SET_AMOUNT)
        ELSE DECODE(NEW_INV_AMOUNT,NULL,TO_CHAR(FS_APP_110_GET_EVNT_AMT(PROCESS_INST)),NEW_INV_AMOUNT)
      END AMT_USD,
      LOT_PROMISED_DATE,
      CWD,
      BILLING_MILESTONE,
      PROCESS_INST N_PROCESS_INST,
      --FS_APP_069_GET_PROCESS_INST(HAND_OFF_REQUEST_ID) HAND_OFF_REQUEST_ID,
      HAND_OFF_REQUEST_ID,
      (
      CASE
        WHEN TO_DATE(CWD) BETWEEN SYSDATE AND (SYSDATE+30)
        THEN 'Y'
        WHEN SYSDATE>TO_DATE(CWD)
        THEN 'R'
        ELSE 'N'
      END) AS CWD_FLAG
    FROM
      (SELECT N_PROCESS_INST PROCESS_INST,
        D_INS,
        D_UPD,
        MAX(DECODE(MY_ATT,'REQUEST_ID', MY_ATT_VAL)) REQUEST_ID,
        MAX(DECODE(MY_ATT,'SO_NUM', MY_ATT_VAL)) SO_NUM,
        MAX(DECODE(MY_ATT,'SHIP_SET_STATUS', MY_ATT_VAL)) SHIP_SET_STATUS,
        MAX(DECODE(MY_ATT,'TOTAL_AMOUNT', MY_ATT_VAL)) MO_SHIP_SET_AMOUNT,
        MAX(DECODE(MY_ATT,'SO_LINES_COUNT', MY_ATT_VAL)) SO_LINES_COUNT,
        MAX(DECODE(MY_ATT,'END_CUST_NAME', MY_ATT_VAL)) END_CUST_NAME,
        MAX(DECODE(MY_ATT,'MO_SHIP_SET_NAME', MY_ATT_VAL)) MO_SHIP_SET_NAME,
        MAX(DECODE(MY_ATT,'SHIP_SET_PROMISED_DATE', MY_ATT_VAL)) LOT_PROMISED_DATE,
        MAX(DECODE(MY_ATT,'CWD', MY_ATT_VAL)) CWD,
        MAX(DECODE(MY_ATT,'BILLING_MILESTONE', MY_ATT_VAL)) BILLING_MILESTONE,
        MAX(DECODE(MY_ATT,'HAND_OFF_REQUEST_ID', MY_ATT_VAL)) HAND_OFF_REQUEST_ID,
        MAX(DECODE(MY_ATT,'REQUEST_TYPE',MY_ATT_VAL)) REQUEST_TYPE,
        MAX(DECODE(MY_ATT,'NEW_INV_AMOUNT',MY_ATT_VAL)) NEW_INV_AMOUNT
      FROM
        (SELECT TAI.N_PROCESS_INST,
          TA.C_ATTRIBUTE MY_ATT,
          TAI.C_ATTRIBUTE_VALUE MY_ATT_VAL,
          TPI.D_INS D_INS,
          TPI.D_UPD D_UPD
        FROM Table_AAA TAI
        INNER JOIN Table_BBB ATTRIBUTE TA
        ON TA.C_ATTRIBUTE = TAI.C_ATTRIBUTE
        INNER JOIN Table_CCC TSP
        ON TSP.C_SUB_PROCESS = TA.C_SUB_PROCESS
        INNER JOIN Table_DDD TPI
        ON TPI.N_PROCESS_INST = TAI.N_PROCESS_INST
        WHERE TPI.C_CURRENT_STATUS NOT LIKE 'otr.shp.Close'
        AND TA.C_SUB_PROCESS  IN ('SH')
        AND TPI.C_SUB_PROCESS IN ('SH')
        )
      GROUP BY N_PROCESS_INST,
        D_UPD
      HAVING FS_APP_137_IS_SPLIT_REQ(N_PROCESS_INST)='N'
      )
    ORDER BY D_UPD DESC
    )

该查询的执行计划如下：
Plan hash value: 25602094

------------------------------------------------------------------------
| Id  | Operation                          | Name                      |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |
|   1 |  VIEW                              |                           |
|*  2 |   FILTER                           |                           |
|   3 |    SORT GROUP BY                   |                           |
|   4 |     MERGE JOIN                     |                           |
|   5 |      SORT JOIN                     |                           |
|   6 |       NESTED LOOPS                 |                           |
|   7 |        NESTED LOOPS                |                           |
|*  8 |         TABLE ACCESS FULL          | Table_DDD   |
|   9 |         TABLE ACCESS BY INDEX ROWID| Table_AAA |
|* 10 |          INDEX RANGE SCAN          | TS_APP_006_IDX1           |
|* 11 |        INDEX UNIQUE SCAN           | TS_APP_PK                 |
|* 12 |      SORT JOIN                     |                           |
|  13 |       TABLE ACCESS FULL            | Table_CCC    |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FS_APP_137_IS_SPLIT_REQ"("TAI"."N_PROCESS_INST")='N')
   8 - filter("TPI"."C_SUB_PROCESS"='SH' AND "TPI"."C_CURRENT_STATUS"
              NOT LIKE 'otr.shp.Close')
  10 - access("TPI"."N_PROCESS_INST"="TAI"."N_PROCESS_INST")
  11 - access("TA"."C_ATTRIBUTE"="TAI"."C_ATTRIBUTE" AND
              "TA"."C_SUB_PROCESS"='SH')
  12 - access("TSP"."C_SUB_PROCESS"="TA"."C_SUB_PROCESS")
       filter("TSP"."C_SUB_PROCESS"="TA"."C_SUB_PROCESS")

Note
-----
   - rule based optimizer used (consider using cbo)

老板认为这个View的执行效率太低了，要求我去优化：
现有如下问题：
1). 这个View可以查询出来11000多条记录，但是需要耗费120到150秒中的时间，请问这个正常吗？
2). 从执行计划上看，该查询的效率低的瓶颈在哪里？
3). 如果你认为有调优的空间，请给出你的思路