5 Replies Latest reply: Jul 5, 2009 10:31 PM by 26741 RSS

    Query taking time.

    SiddS
      Hi All,

      I am new to this forum ,but i hope that i will get some valuable info abt the query i am going to post.

      We have a query in our system(Banking Software Oracle Flexcube) which calculates the penalty on late payments of Loan installmenst.

      which is as follows :

      -------------------------------------------------------------------------------------------------------------

      BEGIN
      INSERT INTO ln_ioa_base_calc_gtt(cod_acct_no,
      cod_drcr,
      amt_txn_acy,
      cod_arrear_type,
      ref_billno_srl,
      dat_value,
      amt_arrears_due,
      dat_last_payment,
      dat_ioa_grace_expiry,
      dat_arrears_due)
      SELECT b.cod_acct_no,
      b.cod_drcr,
      b.amt_txn_acy,
      b.cod_arrear_type,
      b.ref_billno_srl,
      b.dat_value,
      c.amt_arrears_due,
      c.dat_last_payment,
      c.dat_ioa_grace_expiry,
      c.dat_arrears_due
      FROM ln_arrear_txn_hist B, ln_arrears_table C
      WHERE b.cod_acct_no = c.cod_acct_no
      AND c.cod_acct_no = pi_cod_acct_no
      AND b.ref_billno_srl = c.ref_billno_srl
      AND c.dat_arrear_cancelled = LN_GLOBAL.BLANK_DATE;
      EXCEPTION
      WHEN others THEN ln_utl.raise_err(SQLCODE,' Failed in insert for ln_ioa_base_calc_gtt',LineNo);
      END;

      SELECT LN_INT_BASE_REC
      (
      pi_cod_acct_no,
      INT_TYP_PENALTY,
      dat_baschg_eff,
      l_amt_face_value,
      l_amt_disbursed,
      l_amt_princ_balance,

      ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
      FROM ln_ioa_base_calc_gtt
      WHERE cod_acct_no = pi_cod_acct_no
      AND cod_arrear_type = ARREAR_TYPE.PRINCIPAL
      AND dat_value <= A.dat_baschg_eff
      AND NOT (
      /* arrears not paid within the grace period */
      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
      OR
      /* arrears still within the grace period */
      ( dat_ioa_grace_expiry > pi_dat_to )
      )
      ),

      ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
      FROM ln_ioa_base_calc_gtt
      WHERE cod_acct_no = pi_cod_acct_no
      AND cod_arrear_type IN( ARREAR_TYPE.INTEREST,
      ARREAR_TYPE.PMI,
      ARREAR_TYPE.INTEREST_SUSP,
      ARREAR_TYPE.PMI_NONACC )
      AND dat_value <= A.dat_baschg_eff
      AND NOT (
      /* arrears paid within the grace period */
      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
      OR
      /* arrears still within the grace period */
      ( dat_ioa_grace_expiry > pi_dat_to )
      )
      ),


      ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
      FROM ln_ioa_base_calc_gtt
      WHERE cod_acct_no = pi_cod_acct_no
      AND cod_arrear_type IN ( ARREAR_TYPE.FEES,
      ARREAR_TYPE.PREMIUM,
      ARREAR_TYPE.LEGAL_FEES,
      ARREAR_TYPE.OUTGOINGS,
      ARREAR_TYPE.FEES_SUSP,
      ARREAR_TYPE.PREMIUM_SUSP,
      ARREAR_TYPE.LEGAL_FEES_SUSP,
      ARREAR_TYPE.OUTGOINGS_SUSP )
      AND dat_value <= A.dat_baschg_eff
      AND NOT (
      /* arrears paid within the grace period */
      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
      OR
      /* arrears still within the grace period */
      ( dat_ioa_grace_expiry > pi_dat_to )
      )
      ),


      ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
      FROM ln_ioa_base_calc_gtt
      WHERE cod_acct_no = pi_cod_acct_no
      AND cod_arrear_type IN ( ARREAR_TYPE.PENALTY,
      ARREAR_TYPE.PENALTY_SUSP )
      AND dat_value <= A.dat_baschg_eff
      AND NOT (
      /* arrears paid within the grace period */
      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
      OR
      /* arrears still within the grace period */
      ( dat_ioa_grace_expiry > pi_dat_to )
      )
      ),

      l_amt_advances,
      l_amt_int_comp
      )

      BULK COLLECT INTO l_ln_int_base_tab

      FROM ( SELECT DISTINCT dat_value dat_baschg_eff
      FROM ln_arrear_txn_hist
      WHERE cod_acct_no = pi_cod_acct_no
      AND dat_value >= pi_dat_from
      AND dat_value <= pi_dat_to
      UNION
      /* to return the interest base for the day of calculation
      even if there are no transactions for the from date */
      SELECT pi_dat_from
      FROM dual ) A
      ORDER BY dat_baschg_eff DESC;
      ------------------------------------------------------------------------------------------------------------------------------

      First we insert data into ln_ioa_base_calc_gtt which is a GTT table for all the arrears(Principal + interest + Penalty etc...) and then we filter the data based on penalty condition.



      Trace for this query is


      ---------------------------------------------------------------------------------------
      INSERT INTO LN_IOA_BASE_CALC_GTT(COD_ACCT_NO, COD_DRCR, AMT_TXN_ACY,
      COD_ARREAR_TYPE, REF_BILLNO_SRL, DAT_VALUE, AMT_ARREARS_DUE,
      DAT_LAST_PAYMENT, DAT_IOA_GRACE_EXPIRY, DAT_ARREARS_DUE) SELECT
      B.COD_ACCT_NO, B.COD_DRCR, B.AMT_TXN_ACY, B.COD_ARREAR_TYPE,
      B.REF_BILLNO_SRL, B.DAT_VALUE, C.AMT_ARREARS_DUE, C.DAT_LAST_PAYMENT,
      C.DAT_IOA_GRACE_EXPIRY, C.DAT_ARREARS_DUE FROM LN_ARREAR_TXN_HIST B,
      LN_ARREARS_TABLE C WHERE B.COD_ACCT_NO = C.COD_ACCT_NO AND C.COD_ACCT_NO =
      :B1 AND B.REF_BILLNO_SRL = C.REF_BILLNO_SRL AND C.DAT_ARREAR_CANCELLED =
      LN_GLOBAL.BLANK_DATE


      call count cpu elapsed disk query current rows
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      Parse 1563 0.13 0.07 0 0 0 0
      Execute 1563 25.97 267.84 37790 676858 73122 249000
      Fetch 0 0.00 0.00 0 0 0 0
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 3126 26.10 267.92 37790 676858 73122 249000

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 61 (recursive depth: 1)

      Rows Row Source Operation
      ------- ---------------------------------------------------
      305 TABLE ACCESS BY INDEX ROWID LN_ARREAR_TXN_HIST (cr=824 pr=37 pw=0 time=768689 us)
      445 NESTED LOOPS (cr=602 pr=8 pw=0 time=54636 us)
      139 TABLE ACCESS BY INDEX ROWID LN_ARREARS_TABLE (cr=44 pr=6 pw=0 time=19143 us)
      139 INDEX RANGE SCAN IN_LN_ARREARS_TABLE_2 (cr=4 pr=0 pw=0 time=210 us)(object id 242756)
      305 INDEX RANGE SCAN IN_LN_ARREAR_TXN_HIST_5 (cr=558 pr=2 pw=0 time=3722 us)(object id 380710)


      Elapsed times include waiting on following events:
      Event waited on Times Max. Wait Total Waited
      ---------------------------------------- Waited ---------- ------------
      library cache lock 1 0.01 0.01
      row cache lock 10 0.00 0.00
      db file sequential read 37785 0.97 229.45
      gc current block 2-way 17995 0.08 13.89
      gc cr grant 2-way 2163 0.08 1.31
      latch: cache buffers chains 9 0.00 0.00
      latch: KCL gc element parent latch 7 0.00 0.00
      gc cr block 2-way 255 0.02 0.22
      read by other session 10 0.02 0.05
      gc current grant busy 2 0.00 0.00
      gc cr grant congested 1 0.00 0.00
      latch: enqueue hash chains 1 0.00 0.00
      log file switch completion 2 0.12 0.22
      gc current block congested 2 0.08 0.08
      gc cr block busy 5 0.01 0.02
      gc current grant 2-way 3 0.00 0.00
      latch: gcs resource hash 1 0.00 0.00
      ********************************************************************************

      SELECT LN_INT_BASE_REC ( :B3 , :B9 , DAT_BASCHG_EFF, :B8 , :B7 , :B6 , (
      SELECT NVL(SUM(DECODE(COD_DRCR,'D',1,-1) * AMT_TXN_ACY), 0)
      FROM
      LN_IOA_BASE_CALC_GTT WHERE COD_ACCT_NO = :B3 AND COD_ARREAR_TYPE = :B10 AND
      DAT_VALUE <= A.DAT_BASCHG_EFF AND NOT ( ( AMT_ARREARS_DUE = 0 AND
      DAT_LAST_PAYMENT < DAT_IOA_GRACE_EXPIRY ) OR ( DAT_IOA_GRACE_EXPIRY > :B1 )
      ) ), ( SELECT NVL(SUM(DECODE(COD_DRCR,'D',1,-1) * AMT_TXN_ACY), 0) FROM
      LN_IOA_BASE_CALC_GTT WHERE COD_ACCT_NO = :B3 AND COD_ARREAR_TYPE IN( :B14 ,
      :B13 , :B12 , :B11 ) AND DAT_VALUE <= A.DAT_BASCHG_EFF AND NOT ( (
      AMT_ARREARS_DUE = 0 AND DAT_LAST_PAYMENT < DAT_IOA_GRACE_EXPIRY ) OR (
      DAT_IOA_GRACE_EXPIRY > :B1 ) ) ), ( SELECT NVL(SUM(DECODE(COD_DRCR,'D',1,-1)
      * AMT_TXN_ACY), 0) FROM LN_IOA_BASE_CALC_GTT WHERE COD_ACCT_NO = :B3 AND
      COD_ARREAR_TYPE IN ( :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 )
      AND DAT_VALUE <= A.DAT_BASCHG_EFF AND NOT ( ( AMT_ARREARS_DUE = 0 AND
      DAT_LAST_PAYMENT < DAT_IOA_GRACE_EXPIRY ) OR ( DAT_IOA_GRACE_EXPIRY > :B1 )
      ) ), ( SELECT NVL(SUM(DECODE(COD_DRCR,'D',1,-1) * AMT_TXN_ACY), 0) FROM
      LN_IOA_BASE_CALC_GTT WHERE COD_ACCT_NO = :B3 AND COD_ARREAR_TYPE IN ( :B24 ,
      :B23 ) AND DAT_VALUE <= A.DAT_BASCHG_EFF AND NOT ( ( AMT_ARREARS_DUE = 0
      AND DAT_LAST_PAYMENT < DAT_IOA_GRACE_EXPIRY ) OR ( DAT_IOA_GRACE_EXPIRY >
      :B1 ) ) ), :B5 , :B4 ) FROM ( SELECT DISTINCT DAT_VALUE DAT_BASCHG_EFF FROM
      LN_ARREAR_TXN_HIST WHERE COD_ACCT_NO = :B3 AND DAT_VALUE >= :B2 AND
      DAT_VALUE <= :B1 UNION SELECT :B2 FROM DUAL ) A ORDER BY DAT_BASCHG_EFF
      DESC


      call count cpu elapsed disk query current rows
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      Parse 1563 0.14 0.10 0 0 0 0
      Execute 1563 0.22 0.28 3 74 0 0
      Fetch 1563 4.83 19.71 4486 204000 0 1563
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 4689 5.19 20.10 4489 204074 0 1563

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 61 (recursive depth: 1)

      Rows Row Source Operation
      ------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=364 us)
      43 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=163 us)
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=363 us)
      50 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=116 us)
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=293 us)
      0 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=283 us)
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=530 us)
      212 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=236 us)
      1 SORT ORDER BY (cr=100 pr=4 pw=0 time=10224 us)
      1 VIEW (cr=80 pr=4 pw=0 time=8553 us)
      1 SORT UNIQUE (cr=80 pr=4 pw=0 time=8549 us)
      4 UNION-ALL (cr=80 pr=4 pw=0 time=8479 us)
      3 FILTER (cr=80 pr=4 pw=0 time=8462 us)
      3 TABLE ACCESS BY INDEX ROWID LN_ARREAR_TXN_HIST (cr=80 pr=4 pw=0 time=8457 us)
      305 INDEX RANGE SCAN IN_LN_ARREAR_TXN_HIST_1 (cr=8 pr=4 pw=0 time=2595 us)(object id 242396)
      1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us)


      Elapsed times include waiting on following events:
      Event waited on Times Max. Wait Total Waited
      ---------------------------------------- Waited ---------- ------------
      library cache lock 1 0.00 0.00
      db file sequential read 4486 0.40 14.48
      gc current block 2-way 414 0.08 0.47
      gc cr block 2-way 72 0.04 0.08
      KJC: Wait for msg sends to complete 1 0.00 0.00
      ********************************************************************************

      SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
      NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0),
      NVL(SUM(C3),0)
      FROM
      (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("LN_IOA_BASE_CALC_GTT")
      FULL("LN_IOA_BASE_CALC_GTT") NO_PARALLEL_INDEX("LN_IOA_BASE_CALC_GTT") */ 1
      AS C1, CASE WHEN "LN_IOA_BASE_CALC_GTT"."COD_ACCT_NO"=:B1 AND
      "LN_IOA_BASE_CALC_GTT"."COD_ARREAR_TYPE"=:B2 AND
      ("LN_IOA_BASE_CALC_GTT"."AMT_ARREARS_DUE"<>0 OR
      "LN_IOA_BASE_CALC_GTT"."DAT_LAST_PAYMENT">=
      "LN_IOA_BASE_CALC_GTT"."DAT_IOA_GRACE_EXPIRY") AND
      "LN_IOA_BASE_CALC_GTT"."DAT_IOA_GRACE_EXPIRY"<=:B3 THEN 1 ELSE 0 END AS C2,
      CASE WHEN "LN_IOA_BASE_CALC_GTT"."COD_ACCT_NO"=:B1 THEN 1 ELSE 0 END AS C3
      FROM "LN_IOA_BASE_CALC_GTT" "LN_IOA_BASE_CALC_GTT") SAMPLESUB


      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 1 0.00 0.00 0 5 0 1
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 3 0.00 0.00 0 5 0 1

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 61 (recursive depth: 2)

      Rows Row Source Operation
      ------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=495 us)
      305 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=98 us)

      ********************************************************************************

      SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
      NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0),
      NVL(SUM(C3),0)
      FROM
      (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("LN_IOA_BASE_CALC_GTT")
      FULL("LN_IOA_BASE_CALC_GTT") NO_PARALLEL_INDEX("LN_IOA_BASE_CALC_GTT") */ 1
      AS C1, CASE WHEN "LN_IOA_BASE_CALC_GTT"."COD_ACCT_NO"=:B1 AND
      ("LN_IOA_BASE_CALC_GTT"."COD_ARREAR_TYPE"=:B2 OR
      "LN_IOA_BASE_CALC_GTT"."COD_ARREAR_TYPE"=:B3 OR
      "LN_IOA_BASE_CALC_GTT"."COD_ARREAR_TYPE"=:B4 OR
      "LN_IOA_BASE_CALC_GTT"."COD_ARREAR_TYPE"=:B5) AND
      ("LN_IOA_BASE_CALC_GTT"."AMT_ARREARS_DUE"<>0 OR
      "LN_IOA_BASE_CALC_GTT"."DAT_LAST_PAYMENT">=
      "LN_IOA_BASE_CALC_GTT"."DAT_IOA_GRACE_EXPIRY") AND
      "LN_IOA_BASE_CALC_GTT"."DAT_IOA_GRACE_EXPIRY"<=:B6 THEN 1 ELSE 0 END AS C2,
      CASE WHEN "LN_IOA_BASE_CALC_GTT"."COD_ACCT_NO"=:B1 THEN 1 ELSE 0 END AS C3
      FROM "LN_IOA_BASE_CALC_GTT" "LN_IOA_BASE_CALC_GTT") SAMPLESUB


      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 1 0.01 0.00 0 5 0 1
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 3 0.01 0.00 0 5 0 1

      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 61 (recursive depth: 2)

      Rows Row Source Operation
      ------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=470 us)
      305 TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=17 us)

      ********************************************************************************
      -----------------------------------------------------------------------------------------------------------------------------

      Seeking your help on the same ....
        • 1. Re: Query taking time.
          Mohammed Taj
          Dear User,

          if you format the query then it will easy for understand for us.

          Thanks
          • 2. Re: Query taking time.
            Charles Hooper
            Reposting portions of what you provided using the { code } tags (without spaces) so that others may more easily help you:
                     INSERT INTO ln_ioa_base_calc_gtt(cod_acct_no,
                                                      cod_drcr,
                                                      amt_txn_acy,
                                                      cod_arrear_type,
                                                      ref_billno_srl,
                                                      dat_value,
                                                      amt_arrears_due,
                                                      dat_last_payment,
                                                      dat_ioa_grace_expiry,
                                                      dat_arrears_due)
                                               SELECT b.cod_acct_no,
                                                      b.cod_drcr,
                                                      b.amt_txn_acy,
                                                      b.cod_arrear_type,
                                                      b.ref_billno_srl,
                                                      b.dat_value,
                                                      c.amt_arrears_due,
                                                      c.dat_last_payment,
                                                      c.dat_ioa_grace_expiry,
                                                      c.dat_arrears_due
                                                 FROM ln_arrear_txn_hist B, ln_arrears_table C
                                                WHERE b.cod_acct_no = c.cod_acct_no
                                                  AND c.cod_acct_no = pi_cod_acct_no
                                                  AND b.ref_billno_srl = c.ref_billno_srl
                                                  AND c.dat_arrear_cancelled = LN_GLOBAL.BLANK_DATE;
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse     1563      0.13       0.07          0          0          0           0
            Execute   1563     25.97     267.84      37790     676858      73122      249000
            Fetch        0      0.00       0.00          0          0          0           0
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total     3126     26.10     267.92      37790     676858      73122      249000
            
            Misses in library cache during parse: 1
            Misses in library cache during execute: 1
            Optimizer mode: ALL_ROWS
            Parsing user id: 61     (recursive depth: 1)
            
            Rows     Row Source Operation
            -------  ---------------------------------------------------
                305  TABLE ACCESS BY INDEX ROWID LN_ARREAR_TXN_HIST (cr=824 pr=37 pw=0 time=768689 us)
                445   NESTED LOOPS  (cr=602 pr=8 pw=0 time=54636 us)
                139    TABLE ACCESS BY INDEX ROWID LN_ARREARS_TABLE (cr=44 pr=6 pw=0 time=19143 us)
                139     INDEX RANGE SCAN IN_LN_ARREARS_TABLE_2 (cr=4 pr=0 pw=0 time=210 us)(object id 242756)
                305    INDEX RANGE SCAN IN_LN_ARREAR_TXN_HIST_5 (cr=558 pr=2 pw=0 time=3722 us)(object id 380710)
            
            
            Elapsed times include waiting on following events:
              Event waited on                             Times   Max. Wait  Total Waited
              ----------------------------------------   Waited  ----------  ------------
              library cache lock                              1        0.01          0.01
              row cache lock                                 10        0.00          0.00
              db file sequential read                     37785        0.97        229.45
              gc current block 2-way                      17995        0.08         13.89
              gc cr grant 2-way                            2163        0.08          1.31
              latch: cache buffers chains                     9        0.00          0.00
              latch: KCL gc element parent latch              7        0.00          0.00
              gc cr block 2-way                             255        0.02          0.22
              read by other session                          10        0.02          0.05
              gc current grant busy                           2        0.00          0.00
              gc cr grant congested                           1        0.00          0.00
              latch: enqueue hash chains                      1        0.00          0.00
              log file switch completion                      2        0.12          0.22
              gc current block congested                      2        0.08          0.08
              gc cr block busy                                5        0.01          0.02
              gc current grant 2-way                          3        0.00          0.00
              latch: gcs resource hash                        1        0.00          0.00
            
            ------------------------------------------------------------------------------------------------------------------------------
            
                  SELECT LN_INT_BASE_REC
                         (
                           pi_cod_acct_no,
                           INT_TYP_PENALTY,
                           dat_baschg_eff,
                           l_amt_face_value,
                           l_amt_disbursed,
                           l_amt_princ_balance,
            
                           ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
                               FROM ln_ioa_base_calc_gtt
                              WHERE cod_acct_no = pi_cod_acct_no
                                AND cod_arrear_type = ARREAR_TYPE.PRINCIPAL
                                AND dat_value <= A.dat_baschg_eff
                                AND NOT (
                                      /* arrears not paid within the grace period */
                                      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
                                      OR
                                      /* arrears still within the grace period */
                                      ( dat_ioa_grace_expiry pi_dat_to )
                                    )
                           ),
            
                           ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
                               FROM ln_ioa_base_calc_gtt
                              WHERE cod_acct_no = pi_cod_acct_no
                                AND cod_arrear_type IN( ARREAR_TYPE.INTEREST,
                                                        ARREAR_TYPE.PMI,
                                                        ARREAR_TYPE.INTEREST_SUSP,
                                                        ARREAR_TYPE.PMI_NONACC )
                                AND dat_value <= A.dat_baschg_eff
                                AND NOT (
                                      /* arrears paid within the grace period */
                                      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
                                      OR
                                      /* arrears still within the grace period */
                                      ( dat_ioa_grace_expiry > pi_dat_to )
                                    )
                           ),
            
            
                           ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
                               FROM ln_ioa_base_calc_gtt
                              WHERE cod_acct_no = pi_cod_acct_no
                                AND cod_arrear_type IN ( ARREAR_TYPE.FEES,
                                                         ARREAR_TYPE.PREMIUM,
                                                         ARREAR_TYPE.LEGAL_FEES,
                                                         ARREAR_TYPE.OUTGOINGS,
                                                         ARREAR_TYPE.FEES_SUSP,
                                                         ARREAR_TYPE.PREMIUM_SUSP,
                                                         ARREAR_TYPE.LEGAL_FEES_SUSP,
                                                         ARREAR_TYPE.OUTGOINGS_SUSP )
                                AND dat_value <= A.dat_baschg_eff
                                AND NOT (
                                      /* arrears paid within the grace period */
                                      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
                                      OR
                                      /* arrears still within the grace period */
                                      ( dat_ioa_grace_expiry > pi_dat_to )
                                    )
                           ),
            
            
                           ( SELECT NVL(SUM(DECODE(cod_drcr,'D',1,-1) * amt_txn_acy), 0)
                               FROM ln_ioa_base_calc_gtt
                              WHERE cod_acct_no = pi_cod_acct_no
                                AND cod_arrear_type IN ( ARREAR_TYPE.PENALTY,
                                                         ARREAR_TYPE.PENALTY_SUSP )
                                AND dat_value <= A.dat_baschg_eff
                                AND NOT (
                                      /* arrears paid within the grace period */
                                      ( amt_arrears_due = 0 AND dat_last_payment < dat_ioa_grace_expiry )
                                      OR
                                      /* arrears still within the grace period */
                                      ( dat_ioa_grace_expiry > pi_dat_to )
                                    )
                           ),
            
                           l_amt_advances,
                           l_amt_int_comp
                         )
            
                  BULK COLLECT INTO l_ln_int_base_tab
            
                  FROM ( SELECT DISTINCT dat_value dat_baschg_eff
                           FROM ln_arrear_txn_hist
                          WHERE cod_acct_no = pi_cod_acct_no
                            AND dat_value >= pi_dat_from
                            AND dat_value <= pi_dat_to
                          UNION
                         /* to return the interest base for the day of calculation
                            even if there are no transactions for the from date */
                         SELECT pi_dat_from
                           FROM dual ) A
                 ORDER BY dat_baschg_eff DESC;
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse     1563      0.14       0.10          0          0          0           0
            Execute   1563      0.22       0.28          3         74          0           0
            Fetch     1563      4.83      19.71       4486     204000          0        1563
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total     4689      5.19      20.10       4489     204074          0        1563
            
            Misses in library cache during parse: 1
            Misses in library cache during execute: 1
            Optimizer mode: ALL_ROWS
            Parsing user id: 61     (recursive depth: 1)
            
            Rows     Row Source Operation
            -------  ---------------------------------------------------
                  1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=364 us)
                 43   TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=163 us)
                  1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=363 us)
                 50   TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=116 us)
                  1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=293 us)
                  0   TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=283 us)
                  1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=530 us)
                212   TABLE ACCESS FULL LN_IOA_BASE_CALC_GTT (cr=5 pr=0 pw=0 time=236 us)
                  1  SORT ORDER BY (cr=100 pr=4 pw=0 time=10224 us)
                  1   VIEW  (cr=80 pr=4 pw=0 time=8553 us)
                  1    SORT UNIQUE (cr=80 pr=4 pw=0 time=8549 us)
                  4     UNION-ALL  (cr=80 pr=4 pw=0 time=8479 us)
                  3      FILTER  (cr=80 pr=4 pw=0 time=8462 us)
                  3       TABLE ACCESS BY INDEX ROWID LN_ARREAR_TXN_HIST (cr=80 pr=4 pw=0 time=8457 us)
                305        INDEX RANGE SCAN IN_LN_ARREAR_TXN_HIST_1 (cr=8 pr=4 pw=0 time=2595 us)(object id 242396)
                  1      FAST DUAL  (cr=0 pr=0 pw=0 time=2 us)
            
            
            Elapsed times include waiting on following events:
              Event waited on                             Times   Max. Wait  Total Waited
              ----------------------------------------   Waited  ----------  ------------
              library cache lock                              1        0.00          0.00
              db file sequential read                      4486        0.40         14.48
              gc current block 2-way                        414        0.08          0.47
              gc cr block 2-way                              72        0.04          0.08
              KJC: Wait for msg sends to complete             1        0.00          0.00
            
            ------------------------------------------------------------------------------------------------------------------------------
            Observations:
            Insert statement is consuming the most amount of CPU time and experiencing the greatest amount of wait time. There were 1,563 executes of this procedure. Just looking at the insert statement, there were 37,790 blocks read from disk with 37,785 blocks read one block at a time in 229.45 seconds = 6ms per single block read (this is reasonable for a single block read from disk). There were 24 blocks read from disk per execution with 249,000 rows inserted = 159.3 rows inserted per execution. The average execution time of the insert statement is 0.17 seconds.

            On average, the procedure appears to be reasonably efficient, although I am not a fan of the way the SELECT statement is set up with selects in each column position. I wonder if this is a case where over-modularization - essentially doing the same task over and over again, revisiting some of the same blocks multiple times searching for different values for ln_arrears_table.cod_acct_no and ln_arrears_table.dat_arrear_cancelled is contributing to the performance problem. Most of the time is spent performing single block reads from disk. Would it be possible to batch more of the records so that more than one value of ln_arrears_table.cod_acct_no and ln_arrears_table.dat_arrear_cancelled are retrieved at a time and inserted into the global temporary table? Then you might not have 1,563 executions of this procedure. If you modify the SELECT statement, you might even be able to eliminate the need for the global temporary table.

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: Query taking time.
              701909
              This convoluted query is a perfect example of how SQL is used instead of a procedural language.

              If I were you I would rewrite this in PL/SQL and make each query separate.

              http://www.alibris.com/search/books/qwork/9936404/used/Oracle%20PL%20SQL%20Tuning%3A%20Expert%20Secrets%20for%20High%20Performance%20Programming

              With tools for gaining as many as 30 times the execution speed, this handbook educates database administrators on how to rewrite SQL into PL/SQL and how to use advanced Oracle bulk array processing techniques to achieve high performance.
              • 4. Re: Query taking time.
                Charles Hooper
                Absorbine Jr. wrote:
                This convoluted query is a perfect example of how SQL is used instead of a procedural language.

                If I were you I would rewrite this in PL/SQL and make each query separate.

                (snip) Oracle%20PL%20SQL%20Tuning%3A%20Expert%20Secrets%20for%20High%20Performance%20Programming

                With tools for gaining as many as 30 times the execution speed, this handbook educates database administrators on how to rewrite SQL into PL/SQL and how to use advanced Oracle bulk array processing techniques to achieve high performance.
                Mr. Burleson,

                Did you read what was posted above in this thread? If so, how much did you read? That is generous of you to suggest that the user buy and read a book published by Rampant, which I believe is a company you own, to rewrite the contents of the OP's original post so that it is not using a "convoluted query", but instead a PL/SQL procedure. Look closer at the OP's post, what do you see?

                A PL/SQL procedure which was executed 1,563 times, and each query is separate. At the start of the PL/SQL procedure is an INSERT statement, which inserts rows into an apparent global temp table - it is this insert statement which has the greatest CPU utilization and greatest share of wait events (primarily single block reads). The SELECT statement which selects from this global temporary table is a minor contributor to the performance problem.

                The OP's starting point is already accomplishing what you are suggesting. Now he needs to completely change course to fix a problem caused by over utilization of PL/SQL modules - essentially over-modularization of a process.

                Any other suggestions for the OP?

                Charles Hooper
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.
                • 5. Re: Query taking time.
                  26741
                  The first insert into the GTT didn't seem to spend much time on the select from ln_arrear_txn_hist and ln_arrears_table.

                  Is the GTT indexed ? What are the indexes ? I tend to think that it spend much time on updating the indexes on this table. How big is the table and it's index ?