Oracle 性能问题。

我们正在进行性能调整，下面的程序需要时间，请指导我们进一步调整或者我们可以编写任何其他方法来提高性能，

CREATE OR REPLACE TYPE "RECO_FE" FORCE AS OBJECT
(
    o_o_id                    VARCHAR2(18 CHAR),
    o_gl_no                VARCHAR2(35 CHAR),
    o_p_key               VARCHAR2(5 CHAR),
    o_p_name              VARCHAR2(35 CHAR),
    o_c_b_sum        NUMBER(18,3),
    o_d_b_sum         NUMBER(18,3),
    o_o_b_sum     NUMBER(18,3),
    o_c_sum       NUMBER(18,3),
    o_d_sum        NUMBER(18,3),
    o_de_sum         NUMBER(18,3)
);
/



CREATE OR REPLACE TYPE "RECO_FI_COL" FORCE AS TABLE OF RECO_FE;
/

CREATE OR REPLACE PROCEDURE TA_RE_FILE 
(
    pi_id IN VARCHAR2, 
    pi_no IN NUMBER,
    pi_reco_coll OUT RECO_FI_COL
)
IS 
    V_OBJ_RECONCI           RECO_FI_COL;
    V_OBJ_RECO_COLL       RECO_FI_COL:= RECO_FI_COL();
    
BEGIN


    SELECT RECO_FE(EX_TA_O_ID,TA_g_AC_CRDT, TA_pr_key, TA_pr_na, SUM(TA_CR_BA_LOC),NULL, NULL, NULL, NULL, NULL)BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TA_g_AC_CRDT IS NOT NULL
    GROUP BY EX_TA_O_ID, TA_g_AC_CRDT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;


    SELECT RECO_FE(EX_TA_O_ID,TANA_GL_ACCT_DBT, TA_pr_key, TA_pr_na,NULL, SUM(TA_DE_BA_LOC), NULL, NULL, NULL, NULL) BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TANA_GL_ACCT_DBT IS NOT NULL
    GROUP BY EX_TA_O_ID, TANA_GL_ACCT_DBT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;


    SELECT RECO_FE(EX_TA_O_ID, TANA_GL_ACCT_OVERDRFT,TA_pr_key, TA_pr_na, NULL,NULL, SUM(TA_OVE_BAL_LOC), NULL, NULL, NULL)BULK COLLECT INTO V_OBJ_RECONCI
    FROM TA_no_AC
    WHERE FK_TARB_BANK_REPORT_ID=pi_no
        AND EX_TA_O_ID=pi_id
        AND TANA_GL_ACCT_OVERDRFT IS NOT NULL
    GROUP BY EX_TA_O_ID, TANA_GL_ACCT_OVERDRFT, TA_pr_key, TA_pr_na;
    
    V_OBJ_RECO_COLL := V_OBJ_RECO_COLL MULTISET UNION V_OBJ_RECONCI;

 
    pi_reco_coll := V_OBJ_RECO_COLL;
    
END;

你已经几乎相同的查询运行了三次。然后在PL/SQL中统一结果。

你不能有一个SQL语句将这些合并在一起吗？

为了让我们提供更有意义的帮助，我们需要查看具体发生了什么以及需要多长时间的详细信息。即执行 (不解释!) 您的SQL计划，PL/SQL的运行时间等。

要获取计划，请跟踪您的会话并运行您的代码。然后解析跟踪文件。您可以在以下位置找到有关如何执行此操作的说明:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

可能还值得对PL/SQL进行分析，以查看这些多集联合是否需要花费时间。请在以下位置阅读:

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1