This discussion is archived
5 Replies Latest reply: Jul 5, 2009 8:31 PM by 26741 RSS

Query taking time.

sidd - oracle Newbie
Currently Being Moderated
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.
    MohammedTaj Pro
    Currently Being Moderated
    Dear User,

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

    Thanks
  • 2. Re: Query taking time.
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.
    CharlesHooper Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points