求高手帮忙优化存储过程

Question

CREATE OR REPLACE PROCEDURE SP_DOC_FREIGHT_INQUIRY(OUTCURSOR      OUT IMS.PKG_CURSOR.T_CURSOR,
                                                   V_PAGESTART    IN NUMBER DEFAULT NULL,
                                                   V_PAGESIZE     IN NUMBER DEFAULT NULL,
                                                   V_PAGESORT     IN VARCHAR DEFAULT NULL,
                                                   V_AUDIT_STATUS IN CSC.BM_APP_FREIGHT.AUDIT_STATUS%TYPE,
                                                   V_DATE_FROM    IN VARCHAR2,
                                                   V_DATE_TO      IN VARCHAR2,
                                                   V_SERVICE      IN CSC.BM_APP_HEADER.SERVICE%TYPE,
                                                   V_VESSEL       IN CSC.BM_APP_HEADER.VESSEL%TYPE,
                                                   V_VOYAGE       IN CSC.BM_APP_HEADER.VOYAGE%TYPE) AS
  P_ROW_COUNT NUMBER DEFAULT 0;
BEGIN  DELETE DOC_FREIGHT_INQUIRY_TEMPORARY;  IF V_AUDIT_STATUS IS NULL THEN
  
    INSERT INTO DOC_FREIGHT_INQUIRY_TEMPORARY
      SELECT DISTINCT F.APP_ID       AS APP_ID,
                      F.FREIGHT_ID   AS FREIGHT_ID,
                      F.AUDIT_STATUS AS AUDIT_STATUS,
                      D.BL_NO        AS BL_NO,
                      F.FREIGHT      AS FREIGHT,
                      F.CURRENCY     AS CURRENCY,
                      F.REMARK       AS REMARK,
                      F.AUDIT_USER   AS AUDIT_USER,
                      F.AUDIT_TIME   AS AUDIT_TIME,
                      H.POR          AS POR,
                      H.POL          AS POL,
                      H.POD          AS POD,
                      H.DEL          AS DEL,
                      H.DEST         AS DEST,
                      F.POP          AS POP,
                      F.PAYER        AS PAYER,
                      F.FREIGHT_TYPE AS FREIGHT_TYPE,
                      F.LOCK_IND     AS LOCK_IND,
                      F.LOCK_USER    AS LOCK_USER,
                      F.LOCK_DATE    AS LOCK_DATE
        FROM CSC.BM_APP_FREIGHT F
        LEFT JOIN CSC.BM_APP_HEADER H ON F.APP_ID = H.APP_ID
        LEFT JOIN CSC.BM_APP_DETAIL D ON F.APP_ID = D.APP_ID
        LEFT JOIN CSC.BM_APP_ROUTING R ON F.APP_ID = R.APP_ID
       WHERE (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
         AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
         AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
         AND (F.AUDIT_STATUS IS NULL OR F.AUDIT_STATUS IN ('0', '1'))
         AND (V_DATE_FROM IS NULL OR
             R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
         AND (V_DATE_TO IS NULL OR
             R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
         AND (R.STATUS = 'F1')
         AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
  
  ELSIF V_AUDIT_STATUS = '1' THEN
  
    INSERT INTO DOC_FREIGHT_INQUIRY_TEMPORARY
      SELECT DISTINCT F.APP_ID       AS APP_ID,
                      F.FREIGHT_ID   AS FREIGHT_ID,
                      F.AUDIT_STATUS AS AUDIT_STATUS,
                      D.BL_NO        AS BL_NO,
                      F.FREIGHT      AS FREIGHT,
                      F.CURRENCY     AS CURRENCY,
                      F.REMARK       AS REMARK,
                      F.AUDIT_USER   AS AUDIT_USER,
                      F.AUDIT_TIME   AS AUDIT_TIME,
                      H.POR          AS POR,
                      H.POL          AS POL,
                      H.POD          AS POD,
                      H.DEL          AS DEL,
                      H.DEST         AS DEST,
                      F.POP          AS POP,
                      F.PAYER        AS PAYER,
                      F.FREIGHT_TYPE AS FREIGHT_TYPE,
                      F.LOCK_IND     AS LOCK_IND,
                      F.LOCK_USER    AS LOCK_USER,
                      F.LOCK_DATE    AS LOCK_DATE
        FROM CSC.BM_APP_FREIGHT F
        LEFT JOIN CSC.BM_APP_HEADER H ON F.APP_ID = H.APP_ID
        LEFT JOIN CSC.BM_APP_DETAIL D ON H.APP_ID = D.APP_ID
        LEFT JOIN CSC.BM_APP_ROUTING R ON H.APP_ID = R.APP_ID
       WHERE (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
         AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
         AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
         AND (F.AUDIT_STATUS = V_AUDIT_STATUS)
         AND (V_DATE_FROM IS NULL OR
             R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
         AND (V_DATE_TO IS NULL OR
             R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
         AND (R.STATUS = 'F1')
         AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
  
  ELSIF V_AUDIT_STATUS = '0' THEN
  
    INSERT INTO DOC_FREIGHT_INQUIRY_TEMPORARY
      SELECT DISTINCT F.APP_ID       AS APP_ID,
                      F.FREIGHT_ID   AS FREIGHT_ID,
                      F.AUDIT_STATUS AS AUDIT_STATUS,
                      D.BL_NO        AS BL_NO,
                      F.FREIGHT      AS FREIGHT,
                      F.CURRENCY     AS CURRENCY,
                      F.REMARK       AS REMARK,
                      F.AUDIT_USER   AS AUDIT_USER,
                      F.AUDIT_TIME   AS AUDIT_TIME,
                      H.POR          AS POR,
                      H.POL          AS POL,
                      H.POD          AS POD,
                      H.DEL          AS DEL,
                      H.DEST         AS DEST,
                      F.POP          AS POP,
                      F.PAYER        AS PAYER,
                      F.FREIGHT_TYPE AS FREIGHT_TYPE,
                      F.LOCK_IND     AS LOCK_IND,
                      F.LOCK_USER    AS LOCK_USER,
                      F.LOCK_DATE    AS LOCK_DATE
        FROM CSC.BM_APP_FREIGHT F
        LEFT JOIN CSC.BM_APP_HEADER H ON F.APP_ID = H.APP_ID
        LEFT JOIN CSC.BM_APP_DETAIL D ON H.APP_ID = D.APP_ID
        LEFT JOIN CSC.BM_APP_ROUTING R ON H.APP_ID = R.APP_ID
       WHERE (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
         AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
         AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
         AND (F.AUDIT_STATUS IS NULL OR F.AUDIT_STATUS = V_AUDIT_STATUS)
         AND (V_DATE_FROM IS NULL OR
             R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
         AND (V_DATE_TO IS NULL OR
             R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
         AND (R.STATUS = 'F1')
         AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
  
  END IF;  SELECT COUNT(1) INTO P_ROW_COUNT FROM DOC_FREIGHT_INQUIRY_TEMPORARY;  OPEN OUTCURSOR FOR
    SELECT W.*,
           sys_guid() as FREIGHT_INQUIRY_ID,
           null as CREATED_BY_USER,
           null as CREATED_OFFICE,
           sysdate as CREATED_DTM_LOC,
           sysdate as CREATED_TIME_ZONE,
           sysdate as UPDATED_BY_USER,
           null as UPDATED_OFFICE,
           sysdate as UPDATED_DTM_LOC,
           null as UPDATED_TIME_ZONE,
           0 as RECORD_VERSION,
           'CSCL' as PRINCIPAL_GROUP_CODE,
           P_ROW_COUNT AS MAX_ROW_NO
      FROM (SELECT N.*, ROWNUM AS ROW_NO
              FROM (SELECT L.*
                      FROM DOC_FREIGHT_INQUIRY_TEMPORARY L
                     ORDER BY L.APP_ID, L.FREIGHT_ID, L.BL_NO) N) W
     WHERE ROW_NO BETWEEN V_PAGESTART AND V_PAGESTART + V_PAGESIZE - 1;END;
查询速度比较慢，各位大大看有没有办法优化一下

Answer 1

把查询的SQL单独列出来，每个SQL对应执行计划列出来，总共查询返回多少行说清楚

Answer 2

不要动不动就给一大段代码，这个又不是其他语言
优化要涉及到数据库表、索引、数据量、Oracle优化模式，执行计划等
按照上面的提供线索！

Answer 3

数据库表：
CSC.BM_APP_FREIGHT，索引：
CSC.BM_APP_HEADER， 索引：
CSC.BM_APP_DETAIL，索引:
CSC.BM_APP_ROUTING， 索引:

Answer 4

CSC.BM_APP_FREIGHT，索引刚发错了，应该是：

Answer 5

CSC.BM_APP_FREIGHT,CSC.BM_APP_HEADER,CSC.BM_APP_DETAIL,CSC.BM_APP_ROUTING都有10W以上条记录