问题[oracle](dba)

我有一个 Oracle 存储过程，它接受一堆参数并且大量执行左外连接，如下所示：

create or replace PROCEDURE "SOME_PROCEDURE" (
    cRECORDSET OUT SYS_REFCURSOR,
    iPHY_YEAR IN SOME_PROCEDURE_TYPE.PHYSICAL_YEAR % TYPE,
    iPHYSICAL_DOC_TYPE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE % TYPE,
    iPHYSICAL_DOC_TYPE_NONSAP IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE_NONSAP % TYPE,
    sREFERENCE_NO IN SOME_PROCEDURE_TYPE.REFERENCE_NO % TYPE,
    sPHYSICAL_NO IN SOME_PROCEDURE_TYPE.PHYSICAL_NO % TYPE,
    iLOCATION_ID IN SOME_PROCEDURE_TYPE.LOCATION_ID % TYPE,
    iCONTAINER_ID IN SOME_PROCEDURE_TYPE.CONTAINER_ID % TYPE,
    sCONTAINER_NUMBER IN SOME_PROCEDURE_TYPE.CONTAINER_NUMBER % TYPE,
    dREGISTERED_DATE_FROM IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_FROM % TYPE,
    dREGISTERED_DATE_TO IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_TO % TYPE,
    iPHYSICAL_STATUS_ID IN SOME_PROCEDURE_TYPE.PHYSICAL_STATUS_ID % TYPE,
    sDRAFT_FLAG IN SOME_PROCEDURE_TYPE.DRAFT_FLAG % TYPE,
    sCANCEL_FLAG IN SOME_PROCEDURE_TYPE.CANCEL_FLAG % TYPE,
    sPHYSICAL_REMARK IN SOME_PROCEDURE_TYPE.PHYSICAL_REMARK % TYPE,
    sPHYSICAL_DOC_TITLE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TITLE % TYPE,
    sFI_DOC_NO_FROM IN SOME_PROCEDURE_TYPE.FI_DOC_NO_FROM % TYPE,
    sFI_DOC_NO_TO IN SOME_PROCEDURE_TYPE.FI_DOC_NO_TO % TYPE,
    iCOMPANY_ID IN SOME_PROCEDURE_TYPE.COMPANY_ID % TYPE,
    iFI_DOC_YEAR IN SOME_PROCEDURE_TYPE.FI_DOC_YEAR % TYPE,
    sFI_DOC_TYPE IN SOME_PROCEDURE_TYPE.FI_DOC_TYPE % TYPE,
    sIS_ASSET IN SOME_PROCEDURE_TYPE.IS_ASSET % TYPE,
    sWORKFLOW_STATUS_CODE IN SOME_PROCEDURE_TYPE.WORKFLOW_STATUS_CODE % TYPE,
    sPO_NO IN SOME_PROCEDURE_TYPE.PO_NO % TYPE,
    sVENDOR_NAME IN SOME_PROCEDURE_TYPE.VENDOR_NAME % TYPE,
    sCUSTOMER_NAME IN SOME_PROCEDURE_TYPE.CUSTOMER_NAME % TYPE,
    dPOSTING_DATE_FROM IN SOME_PROCEDURE_TYPE.POSTING_DATE_FROM % TYPE,
    dPOSTING_DATE_TO IN SOME_PROCEDURE_TYPE.POSTING_DATE_TO % TYPE,
    sCLEARING_DOC_NO IN SOME_PROCEDURE_TYPE.CLEARING_DOC_NO % TYPE,
    iCLEARING_DOC_YEAR IN SOME_PROCEDURE_TYPE.CLEARING_DOC_YEAR % TYPE,
    dCLEARING_DOC_DATE IN SOME_PROCEDURE_TYPE.CLEARING_DOC_DATE % TYPE,
    sPAYMENT_BLOCK IN SOME_PROCEDURE_TYPE.PAYMENT_BLOCK % TYPE,
    iAMOUNT_INC_VAT IN SOME_PROCEDURE_TYPE.AMOUNT_INC_VAT % TYPE,
    sFI_DOC_REFNO IN SOME_PROCEDURE_TYPE.FI_DOC_REFNO % TYPE,
    sSEND_TO IN SOME_PROCEDURE_TYPE.SEND_TO % TYPE,
    sSAP_REVERSE_FLAG IN SOME_PROCEDURE_TYPE.SAP_REVERSE_FLAG % TYPE,
    sCLEARING_FLAG IN SOME_PROCEDURE_TYPE.CLEARING_FLAG % TYPE,
    iPAGE_SIZE IN SOME_PROCEDURE_TYPE.PAGE_SIZE % TYPE,
    iPAGE_INDEX IN SOME_PROCEDURE_TYPE.PAGE_INDEX % TYPE,
    iUSERID IN USR_DETAIL.USER_ID % TYPE
) IS
LAST_INDEX NUMBER;
USR_PERM_TYPE GET_USR_PERM_TYPE;
FIRST_INDEX NUMBER;
BEGIN
    LAST_INDEX := iPAGE_SIZE * iPAGE_INDEX;
    FIRST_INDEX := LAST_INDEX - iPAGE_SIZE + 1;
    SELECT FN_GET_USR_PERM(iUSERID) INTO USR_PERM_TYPE FROM DUAL;
    OPEN cRECORDSET FOR 
    
    WITH CTE_PHD_DETAIL AS (
    SELECT
        *
    FROM
        PHD_DETAIL
    WHERE
        USR_PERM_TYPE.VIS_MODE = 'ALL'
        OR (
            USR_PERM_TYPE.VIS_MODE = 'UNT'
            AND (UNIT_ID = USR_PERM_TYPE.USUB_ID)
        )
        OR (
            USR_PERM_TYPE.VIS_MODE = 'SUB'
            AND SUB_UNIT_ID = USR_PERM_TYPE.USUB_ID
        )
)
    
    SELECT
    PHD_ITEMS.RECORD_NUMBER,
    PHD.PHYSICAL_ID,
    PHD.PHYSICAL_DOC_TYPE,
    PHD.PHYSICAL_NO,
    PHD.REGISTERED_DATE,
    PHD.CANCEL_FLAG,
    PHD.DRAFT_FLAG,
    CASE
            WHEN PHD.REQUESTED_NO IS NOT NULL THEN
            PHD.REQUESTED_NO ELSE PHD.TEXT_FILE_NAME
        END REFERENCE_NO,
    PHDS.PHYSICAL_STATUS_ID,
    PHDS.PHYSICAL_STATUS_NAME,
    CNT.CONTAINER_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2 THEN
    NULL ELSE CNT.CONTAINER_NUMBER
    END CONTAINER_NUMBER,
    CNT.CONTAINER_TYPE,
    CNTU.IS_DOC_HOUSE CONTAINER_IS_DOC_HOUSE,
    CNTU.UNIT_ABBR CONTAINER_UNIT_ABBR,
    LOC.LOCATION_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2
    OR PHD.PHYSICAL_STATUS_ID = 12
    OR PHD.PHYSICAL_STATUS_ID = 14 THEN
    (
    SELECT
        SEND_TO
    FROM
        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC )
    WHERE
        ROWNUM = 1
        AND PHYSICAL_ID = PHD_ITEMS.PHYSICAL_ID
    ) --DECODE( PHDLG.SENT_TO,NULL , '',PHDLG.SENT_TO )
    WHEN CNT.CONTAINER_TYPE = 1 THEN
CASE
        WHEN CNT.CONTAINER_STATUS_ID = 2 THEN
    CASE
            WHEN CNT.CURRENT_UNIT_ID IS NOT NULL THEN
            CNTU.UNIT_FULL_NAME ELSE CCOM.COMPANY_CODE || ' - ' || CCOM.COMPANY_NAME
        END ELSE LOC.LOCATION_NAME
    END ELSE LOC.LOCATION_NAME -- LOC.LOCATION_NAME
    END LOCATION_NAME,
    FIDS.WORKFLOW_STATUS_NAME,
    FID.FI_DOC_ID,
    FID.FI_DOC_NO,
    FID.FI_DOC_YEAR,
CASE
    WHEN COM_FID.COMPANY_ID IS NOT NULL THEN
    COM_FID.COMPANY_CODE || ' - ' || COM_FID.COMPANY_NAME ELSE
CASE
        WHEN COM_PHD.COMPANY_ID IS NOT NULL THEN
        COM_PHD.COMPANY_CODE || ' - ' || COM_PHD.COMPANY_NAME ELSE ''
    END
    END AS COMPANY_FULL_NAME,
    FID.FI_DOC_REFNO,
CASE
        WHEN FID.FI_DOC_ID IS NULL THEN
        PTEMP.AMOUNT_INC_VAT ELSE FID.AMOUNT_INC_VAT
    END AMOUNT_INC_VAT,
CASE
    WHEN FID.FI_DOC_ID IS NULL THEN
    PTEMP.AMOUNT_CURRENCY ELSE FID.AMOUNT_CURRENCY
    END AMOUNT_CURRENCY,
    FID.IS_ASSET,
CASE
    WHEN PHD_ITEMS.FI_DOC_ID IS NULL THEN
CASE
        WHEN PTEMP.CUSTOMER_CODE IS NOT NULL THEN
        PTCUS.CUSTOMER_FULL_NAME ELSE PTVEN.VENDOR_FULL_NAME
END ELSE
CASE
        WHEN FID.FIRST_ITEMS = 'CUSTOMER' THEN
        FID.CUSTOMER_NAME ELSE FID.VENDOR_NAME
    END
    END AS CUSTOMER_VENDOR_NAME,
    PHD.PHYSICAL_REMARK
FROM
    (
    SELECT
        ROW_NUMBER ( ) over ( ORDER BY PHD.PHYSICAL_NO DESC, FID_ID.FI_DOC_NO, PHDTEMP.PHD_TEMP_DATA_ID DESC ) RECORD_NUMBER,
        PHD.PHYSICAL_ID,
        PHD.PHYSICAL_NO,
        FID_ID.FI_DOC_ID,
        FID_ID.FI_DOC_NO,
        PHDTEMP.PHD_TEMP_DATA_ID
    FROM
        (
        SELECT DISTINCT
            PHD.PHYSICAL_ID,
            PHD.PHYSICAL_NO,
            FID_ID.FI_DOC_ID,
            PHDTEMP.PHD_TEMP_DATA_ID
        FROM
            CTE_PHD_DETAIL PHD
            LEFT JOIN (
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                PHD_FID_RELATE_DOC t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID UNION ALL
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                FID_SAP_PHYSICAL_HISTORY t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID
            ) FID_ID ON FID_ID.PHYSICAL_ID = PHD.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHYSICAL_ID = PHDTEMP.PHYSICAL_ID
            AND FID_ID.FI_DOC_ID
            IS NULL LEFT JOIN PHD_STATUS_LOGS PHDSL ON PHD.PHYSICAL_ID = PHDSL.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DOCTITLE PHDT ON PHD.PHYSICAL_ID = PHDT.PHYSICAL_ID
            LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
            LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
            LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
            LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
            LEFT JOIN FID_SAP_DETAIL FID ON FID_ID.FI_DOC_ID = FID.FI_DOC_ID
            LEFT JOIN COM_DETAIL COM ON FID.COMPANY_CODE = COM.COMPANY_CODE
            LEFT JOIN FID_SAP_CUSTOMER FCUS ON FID.FI_DOC_ID = FCUS.FI_DOC_ID
            LEFT JOIN FID_SAP_VENDOR FVEN ON FID.FI_DOC_ID = FVEN.FI_DOC_ID
            LEFT JOIN FID_SAP_CLEARING_DOC CLR ON FID.FI_DOC_ID = CLR.FI_DOC_ID
            LEFT JOIN FID_SAP_PO PO ON FID.FI_DOC_ID = PO.FI_DOC_ID
            LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
            LEFT JOIN MAS_VENDOR PTVEN ON PHDTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
            AND COM_PHD.SERVER_ID = COM_PHD.SERVER_ID
            LEFT JOIN MAS_CUSTOMER PTCUS ON PHDTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
            AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
        WHERE
            PHD.PHYSICAL_NO IS NOT NULL
            AND (
                (
                    (
                        ( PHD.PHYSICAL_DOC_TYPE = 1 AND iPHYSICAL_DOC_TYPE = 1 )
                        OR ( ( PHD.PHYSICAL_DOC_TYPE = 2 OR PHD.PHYSICAL_DOC_TYPE = 4 ) AND iPHYSICAL_DOC_TYPE = 2 )
                    )
                    AND iPHYSICAL_DOC_TYPE_NONSAP IS NULL
                )
                OR ( PHD.PHYSICAL_DOC_TYPE = iPHYSICAL_DOC_TYPE_NONSAP AND iPHYSICAL_DOC_TYPE_NONSAP IS NOT NULL )
            )
            AND (
                sREFERENCE_NO IS NULL
                OR (
                    UPPER( PHD.REQUESTED_NO ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                    OR UPPER( PHD.TEXT_FILE_NAME ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                )
            )
            AND ( sPHYSICAL_NO IS NULL OR ( PHD.PHYSICAL_NO = sPHYSICAL_NO ) )
            AND ( iLOCATION_ID IS NULL OR ( LOC.LOCATION_ID = iLOCATION_ID ) )
            AND ( iPHYSICAL_STATUS_ID IS NULL OR ( PHD.PHYSICAL_STATUS_ID = iPHYSICAL_STATUS_ID ) )
            AND ( iCONTAINER_ID IS NULL OR ( CNT.CONTAINER_ID = iCONTAINER_ID AND CNT.CONTAINER_TYPE = 2 ) )
            AND (
                sCONTAINER_NUMBER IS NULL
                OR ( UPPER( CNT.CONTAINER_NUMBER ) LIKE '%' || UPPER( sCONTAINER_NUMBER ) || '%' AND CNT.CONTAINER_TYPE = 1 )
            )
            AND ( TRUNC( dREGISTERED_DATE_FROM ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) >= TRUNC( dREGISTERED_DATE_FROM ) ) )
            AND ( TRUNC( dREGISTERED_DATE_TO ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) <= TRUNC( dREGISTERED_DATE_TO ) ) )
            AND ( ( PHD.DRAFT_FLAG IS NULL AND sDRAFT_FLAG IS NULL ) OR ( PHD.DRAFT_FLAG = sDRAFT_FLAG ) ) --AND (PHD.CANCEL_FLAG              IS NULL
--OR (PHD.CANCEL_FLAG                = sCANCEL_FLAG))
            AND ( sPHYSICAL_REMARK IS NULL OR ( UPPER( PHD.PHYSICAL_REMARK ) LIKE '%' || UPPER( sPHYSICAL_REMARK ) || '%' ) )
            AND ( sPHYSICAL_DOC_TITLE IS NULL OR ( UPPER( PHDT.PHYSICAL_DOC_TITLE ) LIKE '%' || UPPER( sPHYSICAL_DOC_TITLE ) || '%' ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iPHY_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iPHY_YEAR ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( FID_ID.FI_DOC_YEAR = iFI_DOC_YEAR OR FID_ID.FI_DOC_ID IS NULL ) ) /*    AND (
                sSEND_TO IS NULL
                OR ( PHDSL.SEND_TO LIKE '%' || sSEND_TO || '%' OR LOWER( PHDSL.SEND_TO ) LIKE '%' || sSEND_TO || '%' )
            )*/
            AND (
                sSEND_TO IS NULL
                OR (
                    iPHYSICAL_STATUS_ID = 2
                    AND EXISTS (
                    SELECT
                        PDSENT.SEND_TO
                    FROM
                        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC ) PDSENT
                    WHERE
                        ROWNUM = 1
                        AND PDSENT.PHYSICAL_ID = PHD.PHYSICAL_ID
                        AND LOWER( PDSENT.SEND_TO ) LIKE '%' || LOWER( sSEND_TO ) || '%'
                    )
                )
            )
            AND ( sFI_DOC_NO_FROM IS NULL OR ( FID.FI_DOC_NO >= sFI_DOC_NO_FROM ) )
            AND ( sFI_DOC_NO_TO IS NULL OR ( FID.FI_DOC_NO <= sFI_DOC_NO_TO ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iFI_DOC_YEAR OR FID.FI_DOC_YEAR = iFI_DOC_YEAR ) ) --AND ( sFI_DOC_TYPE              IS NULL
--OR ( UPPER(T1.FI_DOC_TYPE)       = UPPER(sFI_DOC_TYPE) ) )
            AND ( sIS_ASSET IS NULL OR ( sIS_ASSET IS NOT NULL AND FID.IS_ASSET IS NOT NULL ) )
            AND ( sWORKFLOW_STATUS_CODE IS NULL OR ( FID.WORKFLOW_STATUS_CODE = sWORKFLOW_STATUS_CODE ) )
            AND ( sPO_NO IS NULL OR ( UPPER( PO.PO_NO ) = UPPER( sPO_NO ) ) )
            AND (
                TRUNC( dPOSTING_DATE_FROM ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) OR TRUNC( PHDTEMP.CREATE_DATE ) <= TRUNC( dPOSTING_DATE_TO ) )
            )
            AND (
                TRUNC( dPOSTING_DATE_TO ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) <= TRUNC( dPOSTING_DATE_TO ) OR TRUNC( PHDTEMP.CREATE_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) )
            )
            AND ( sCLEARING_DOC_NO IS NULL OR ( UPPER( CLR.CLEARING_DOC_NO ) LIKE '%' || UPPER( sCLEARING_DOC_NO ) || '%' ) )
            AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )
            AND ( dCLEARING_DOC_DATE IS NULL OR ( CLR.CLEARING_DATE = dCLEARING_DOC_DATE ) ) --AND ( sPAYMENT_BLOCK      IS NULL
-- OR ( UPPER(FID.PAYMENT_BLOCK) LIKE '%'
--   || UPPER(sPAYMENT_BLOCK)
--  || '%' ) )
            AND ( sFI_DOC_REFNO IS NULL OR ( UPPER( FID.FI_DOC_REFNO ) LIKE '%' || UPPER( sFI_DOC_REFNO ) || '%' ) )
            AND ( ( sSAP_REVERSE_FLAG IS NULL AND ( FID.SAP_REVERSE_FLAG IS NULL OR FID.FI_DOC_ID IS NULL ) ) OR ( sSAP_REVERSE_FLAG IS NOT NULL ) )
            AND ( iAMOUNT_INC_VAT IS NULL OR ( FID.AMOUNT_INC_VAT = iAMOUNT_INC_VAT OR PHDTEMP.AMOUNT_INC_VAT = iAMOUNT_INC_VAT ) )
            AND (
                sCUSTOMER_NAME IS NULL
                OR UPPER( PTCUS.CUSTOMER_FULL_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
                OR UPPER( FCUS.CUSTOMER_CODE || ' ' || FCUS.CUSTOMER_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
            )
            AND (
                sVENDOR_NAME IS NULL
                OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
                OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
            ) --AND FID_ID.FI_DOC_ID IS NULL AND PHDTEMP.PHD_TEMP_DATA_ID IS NOT NULL
        ) PHD
        LEFT JOIN FID_SAP_DETAIL FID_ID ON FID_ID.FI_DOC_ID = PHD.FI_DOC_ID
        LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHD_TEMP_DATA_ID = PHDTEMP.PHD_TEMP_DATA_ID
        AND PHD.FI_DOC_ID IS NULL
    ) PHD_ITEMS
    INNER JOIN CTE_PHD_DETAIL PHD ON PHD_ITEMS.PHYSICAL_ID = PHD.PHYSICAL_ID
    LEFT JOIN PHD_STATUS PHDS ON PHD.PHYSICAL_STATUS_ID = PHDS.PHYSICAL_STATUS_ID
    LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
    LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
    LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
    LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
    LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
    LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
    LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
    LEFT JOIN COM_DETAIL COM_FID ON COM_FID.COMPANY_CODE = FID.COMPANY_CODE
    LEFT JOIN PHD_TEMP_DATA PTEMP ON PHD_ITEMS.PHD_TEMP_DATA_ID = PTEMP.PHD_TEMP_DATA_ID
    LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTVEN.SERVER_ID = COM_PHD.SERVER_ID
    LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
    PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX
    AND LAST_INDEX
ORDER BY
    RECORD_NUMBER;
END;

此查询大约需要 1 分 30 秒，然后才会发出约 4000 条记录的查询结果。很难提取此长查询的执行计划。有什么建议或策略，或者我应该做些什么来获取计划？

我认为，我目前有 3 个选择：

动态查询

第一个选项是将过程升级为 ORM 语句，使用一些 if 语句来组成正确的语句，使用内连接而不是左外连接（如果参数为空，则不连接）。乍一看，我觉得这个选项很聪明。但是，我可能花了很长时间才完成这项任务。

物化视图

我想到的一个选项是将左连接子句重写为Materialized View。不幸的是，此存储过程涉及应用程序，因此当与ON COMMIT或结合时，物化视图将频繁更新ON STATEMENT。此选项是否被认为对死锁或性能不佳很危险？

删除一些搜索条件（存储过程参数）

假设我可以删除一些条件，这意味着我可以删除一些左外连接子句。但是，在我与队友讨论后，这个选项不可行。

我该如何解决性能问题，或者我有什么其他策略或选择？欢迎提出任何建议。

我正在尝试导出一个包含 long raw 的表。表大小为 800GB。数据泵未导出 long raw。我做了一些研究，似乎唯一的方法是将它们传输到 LOB，然后导出/导入。然后我必须在另一个数据库中将它们再次传输到 long raw。还有其他简单的方法吗？

我做了以下操作。请注意，大小很小。对表格进行求和后，dba_table结果约为 800GB。我知道这种数据类型已被弃用，但目前我无法更改。

Export: Release 19.0.0.0.0 - Production on Tue Dec 17 23:53:38 2024
Version 19.11.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "db1"."SYS_EXPORT_TABLE_01":  db1/********@sv1 directory=backup dumpfile=tablle1_d.dmp LOGFILE=log_tablle1_d.log TABLES=tablle1
 EXCLUDE=STATISTICS,INDEX parallel=10
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "db1"."tablle1"              12.54 MB      77 rows
Master table "db1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for db1.SYS_EXPORT_TABLE_01 is:
  /backup/tablle1_d.dmp
Job "db1"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 17 23:55:21 2024 elapsed 0 00:01:26

我已经执行了以下命令，并且可以在目录中找到它。但是查询“select * from dba_tablespaces”时我找不到它。我试图在这个表空间上创建一个新的模式，但抛出了错误tablessoace不存在。我的错误是什么？

CREATE BIGFILE TABLESPACE ESC_SCHEMA_DATA DATAFILE  '/datafiles/CDB1/O19/ESC_SCHEMA_DATA.DBF' SIZE 536870912000 AUTOEXTEND ON NEXT 1073741824 MAXSIZE 33554431M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

我需要唯一值的数量，并尝试使用 Oracle 的COUNTDISTINCT()函数来获取：

select COUNTDISTINCT(a.m_label)
        from user_rep a, user_group_rep b, trn_grp_rep c
        where b.m_user_id = a.m_reference
...

这导致ORA-00904: "COUNTDISTINCT": invalid identifier...使用普通的COUNT()作品，但没有返回正确的结果。

我做错了什么？有什么有效的解决方法吗？

考虑以下代码：

CREATE OR REPLACE PACKAGE the_package AS
   PROCEDURE procA ( pval1 IN VARCHAR2 );
   PROCEDURE procA ( pval1 IN NUMBER );
END;
/

创建了一个具有重载程序的PLSQL包procA。

现在我们创建调用这些的过程：

CREATE OR REPLACE testProc1 AS
  l_val VARCHAR2 := 'Boncho';
BEGIN
   the_package.procA( l_val );
END;
/

CREATE OR REPLACE testProc21 AS
  l_val NUMBER := 88;
BEGIN
   the_package.procA( l_val );
END;
/

我想要做的是能够识别哪些变体testProc1并testProc2进行调用。

我无法使用，DBA_DEPENDENCIES因为它只是告诉我testProc1和testProc2依赖于包the_package。

我无法使用DBA_PROCEDURES，因为它只告诉我可以调用哪些程序。

似乎唯一的选择是以某种方式使用DBA_PROCEDURES和的组合DBA_ARGUMENTS并DBA_SOURCE进行一些类似编译器的分析来获得所需的输出：

第 2 行的包的过程testProc1调用。 第 3 行的包的过程调用。procAthe_packagetestProc2procAthe_package

但是谁想编写 PLSQL 编译器？

但是我认为 Oracle 在编译时会进行验证，即在编译过程时会检查所调用的过程是否存在。因此，我想也许在元数据的某个地方存储了有关所调用过程的确切信息。

是否有人知道在元数据视图中这些信息被保存在哪里？

几周以来，当我尝试调用外部过程/dll 时，我不断看到间歇性 oracle 错误。收到的消息是：

ORA-28576: 丢失与外部过程代理的 RPC 连接

还有人遇到过这种情况吗？如果是这样，采取了哪些步骤来解决这个问题？我正在使用带有最新补丁 19.24.0 的 Oracle 标准版 19c，并且我在 Windows 主机上。

我删除了不再需要的表空间，并且不小心遗漏了删除文件的 SQL 查询。表空间已被删除，但 DFB 仍留在操作系统路径中。

我想知道删除它的最佳方法是什么，因为它占用了不必要的空间。

我不敢执行

rm -f file.dbf

lsof 并未表明有任何进程正在使用它，但我认为如果可能的话最好从 DB 本身来执行此操作。

系统：RHEL4 Oracle数据库：10.2

谢谢！

我有一个名为“ADMINX”的非常老的用户，该用户几乎可以控制 Oracle 19c 数据库中的所有表空间。它被赋予了“DBA”角色，还有一个名为“APP_ADMIN_ROLE”的自定义角色，其中包含 DBA 角色。

如果我从 ADMINX 本身撤销 DBA 角色，但保留 APP_ADMIN_ROLE，表空间配额会被重置吗？我认为它应该保持无限配额，因为 APP_ADMIN_ROLE 仍然具有 DBA 角色，但我不确定 Oracle 在这种情况下会如何表现。遗憾的是，DBA_TS_QUOTAS 不显示 DBA 角色占用的无限配额。

我有一个自定义 oracle 用户（假设为 monitor_user），它有很多程序和作业，用于发送查询结果的电子邮件。但出于安全原因，我想禁用它从外部客户端登录数据库。我该怎么做？我认为删除“CREATE SESSION”对我没有帮助。

我曾经发现一个 Oracle 系统视图SYS.EXU8CON，它的数据似乎与所有数据库对象的信息相关，如对象 ID、所有者等。它还有一些我不知道它们是什么的列，如 CONDITION、CONDLENGTH 和 SQLVER。我甚至在互联网上找不到任何信息，这太奇怪了。所以，有人可以解释一下吗？

研究了视图中的数据并在互联网上进行了搜索。希望获得表格注释。