Home » SQL & PL/SQL » SQL & PL/SQL » CURSOR taking more time (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit)
CURSOR taking more time [message #661171] Fri, 10 March 2017 02:27 Go to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

Dear Experts,

I have sql file with main cursor and sub-queries. Below is the cursor which i suspect that taking more time. And query reruns around 1500 - 2000 records which currently running more than 2 hours,

Kindly help or any suggestion to reduce the cursor execution time.

Below is the Main Cursor,

SELECT NVL(A.PRDEM,0),'PRDEM',NVL(B.INDEM,0),'INDEM',GAM.FORACID,GAM.ACID,GAM.ACCT_CRNCY_CODE
FROM
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,ACID AS AACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='PRDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) A ,
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,ACID AS BACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='INDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) B,
DBSCUST.C_MPAMT D,TBAADM.LRCT E,TBAADM.view_GAM GAM,TBAADM.LAM LAM
WHERE GAM.FORACID = D.FORACID
AND GAM.SCHM_CODE = E.REF_CODE
AND GAM.ACID = LAM.ACID
AND (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND A.AACID(+) = GAM.ACID
AND B.BACID(+) = GAM.ACID
AND E.REF_REC_TYPE ='CBGSC'
AND E.DEL_FLG='N'
AND E.SCHM_REF_TYPE='CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL
AND (A.PRDEM >0 OR B.INDEM > 0)
AND ((D.LSA_COMM_DATE IS NULL) OR (D.LSA_COMM_DATE <= sysdate));


and AUTOTRACE for above cursor,


1521 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2592180231

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN OUTER | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 3 | HASH JOIN OUTER | | 10802 | 1434K| | 30374 (1)| 00:06:05 |
| 4 | NESTED LOOPS | | 10802 | 1044K| | 5626 (1)| 00:01:08 |
| 5 | NESTED LOOPS | | 10803 | 1044K| | 5626 (1)| 00:01:08 |
|* 6 | HASH JOIN | | 10803 | 907K| | 2384 (1)| 00:00:29 |
|* 7 | HASH JOIN | | 10803 | 632K| | 1234 (1)| 00:00:15 |
|* 8 | TABLE ACCESS BY INDEX ROWID| LARGE_REFERENCE_CODE_TABLE | 31 | 558 | | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_LARGE_REFERENCE_CODE_TABLE | 63 | | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE | 31233 | 1281K| | 1230 (1)| 00:00:15 |
|* 11 | INDEX SKIP SCAN | IDX_GAM_ALT1_SHORT_NAME | 62465 | | | 22 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | CUST_MPAMT_TBL | 45886 | 1165K| | 1149 (1)| 00:00:14 |
|* 13 | INDEX RANGE SCAN | IDX_LA_ACCT_MAST_TABLE | 1 | | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | LA_ACCT_MAST_TABLE | 1 | 13 | | 1 (0)| 00:00:01 |
| 15 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 16 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 17 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
| 18 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 19 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 20 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."PRDEM">0 OR "B"."INDEM">0)
2 - access("B"."BACID"(+)="GAM"."ACID")
3 - access("A"."AACID"(+)="GAM"."ACID")
6 - access("GAM"."FORACID"="D"."FORACID")
7 - access("GAM"."SCHM_CODE"="E"."REF_CODE")
8 - filter("E"."DEL_FLG"='N')
9 - access("E"."REF_REC_TYPE"='CBGSC' AND "E"."SCHM_REF_TYPE"='CJ')
filter("E"."SCHM_REF_TYPE"='CJ')
10 - filter("GAM"."ACCT_CLS_FLG"='N' AND "GAM"."BANK_ID"='SG')
11 - access("GAM"."SOL_ID"='367')
filter("GAM"."SOL_ID"='367')
12 - filter("D"."ACCNT_TYPE" IS NOT NULL AND "D"."MODE_OF_PAYMNT"='ACT' AND ("D"."LSA_COMM_DATE" IS NULL OR
"D"."LSA_COMM_DATE"<=SYSDATE@!))
13 - access("GAM"."ACID"="LAM"."ACID")
14 - filter(("LAM"."CHRGE_OFF_FLG" IS NULL OR "LAM"."CHRGE_OFF_FLG"<>'Y') AND ("LAM"."PAYOFF_FLG"<>'Y' OR
"LAM"."PAYOFF_FLG" IS NULL))
17 - filter("DMD_FLOW_ID"='PRDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
20 - filter("DMD_FLOW_ID"='INDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')


Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
262477 consistent gets
152573 physical reads
0 redo size
72481 bytes sent via SQL*Net to client
1631 bytes received via SQL*Net from client
103 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1521 rows processed


Re: CURSOR taking more time [message #661172 is a reply to message #661171] Fri, 10 March 2017 02:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your code and exec plan are unreadable. Please use the [code] tags. You have been asked to do this before.
Re: CURSOR taking more time [message #661173 is a reply to message #661172] Fri, 10 March 2017 02:44 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

Sorry for the inconvenience,

SELECT NVL(A.PRDEM,0),'PRDEM',NVL(B.INDEM,0),'INDEM',GAM.FORACID,GAM.ACID,GAM.ACCT_CRNCY_CODE
FROM
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,ACID AS AACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='PRDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) A ,
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,ACID AS BACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='INDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) B,
DBSCUST.C_MPAMT D,TBAADM.LRCT E,TBAADM.view_GAM GAM,TBAADM.LAM LAM
WHERE GAM.FORACID = D.FORACID
AND GAM.SCHM_CODE = E.REF_CODE
AND GAM.ACID = LAM.ACID
AND (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND A.AACID(+) = GAM.ACID
AND B.BACID(+) = GAM.ACID
AND E.REF_REC_TYPE ='CBGSC'
AND E.DEL_FLG='N'
AND E.SCHM_REF_TYPE='CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL  
AND (A.PRDEM >0 OR B.INDEM > 0)
AND ((D.LSA_COMM_DATE IS NULL) OR (D.LSA_COMM_DATE <= sysdate));

Explain plan for above cursor query,
1521 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2592180231

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                | 10802 |  1824K|       | 55121   (1)| 00:11:02 |
|*  1 |  FILTER                            |                                |       |       |       |            |          |
|*  2 |   HASH JOIN OUTER                  |                                | 10802 |  1824K|       | 55121   (1)| 00:11:02 |
|*  3 |    HASH JOIN OUTER                 |                                | 10802 |  1434K|       | 30374   (1)| 00:06:05 |
|   4 |     NESTED LOOPS                   |                                | 10802 |  1044K|       |  5626   (1)| 00:01:08 |
|   5 |      NESTED LOOPS                  |                                | 10803 |  1044K|       |  5626   (1)| 00:01:08 |
|*  6 |       HASH JOIN                    |                                | 10803 |   907K|       |  2384   (1)| 00:00:29 |
|*  7 |        HASH JOIN                   |                                | 10803 |   632K|       |  1234   (1)| 00:00:15 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| LARGE_REFERENCE_CODE_TABLE     |    31 |   558 |       |     4   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_LARGE_REFERENCE_CODE_TABLE |    63 |       |       |     1   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE        | 31233 |  1281K|       |  1230   (1)| 00:00:15 |
|* 11 |          INDEX SKIP SCAN           | IDX_GAM_ALT1_SHORT_NAME        | 62465 |       |       |    22   (0)| 00:00:01 |
|* 12 |        TABLE ACCESS FULL           | CUST_MPAMT_TBL                 | 45886 |  1165K|       |  1149   (1)| 00:00:14 |
|* 13 |       INDEX RANGE SCAN             | IDX_LA_ACCT_MAST_TABLE         |     1 |       |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID   | LA_ACCT_MAST_TABLE             |     1 |    13 |       |     1   (0)| 00:00:01 |
|  15 |     VIEW                           |                                | 85054 |  3073K|       | 24747   (2)| 00:04:57 |
|  16 |      HASH GROUP BY                 |                                | 85054 |  2491K|    61M| 24747   (2)| 00:04:57 |
|* 17 |       TABLE ACCESS FULL            | LA_DMD_TABLE                   |  1345K|    38M|       | 20391   (1)| 00:04:05 |
|  18 |    VIEW                            |                                | 85054 |  3073K|       | 24747   (2)| 00:04:57 |
|  19 |     HASH GROUP BY                  |                                | 85054 |  2491K|    61M| 24747   (2)| 00:04:57 |
|* 20 |      TABLE ACCESS FULL             | LA_DMD_TABLE                   |  1345K|    38M|       | 20391   (1)| 00:04:05 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."PRDEM">0 OR "B"."INDEM">0)
   2 - access("B"."BACID"(+)="GAM"."ACID")
   3 - access("A"."AACID"(+)="GAM"."ACID")
   6 - access("GAM"."FORACID"="D"."FORACID")
   7 - access("GAM"."SCHM_CODE"="E"."REF_CODE")
   8 - filter("E"."DEL_FLG"='N')
   9 - access("E"."REF_REC_TYPE"='CBGSC' AND "E"."SCHM_REF_TYPE"='CJ')
       filter("E"."SCHM_REF_TYPE"='CJ')
  10 - filter("GAM"."ACCT_CLS_FLG"='N' AND "GAM"."BANK_ID"='SG')
  11 - access("GAM"."SOL_ID"='367')
       filter("GAM"."SOL_ID"='367')
  12 - filter("D"."ACCNT_TYPE" IS NOT NULL AND "D"."MODE_OF_PAYMNT"='ACT' AND ("D"."LSA_COMM_DATE" IS NULL OR
              "D"."LSA_COMM_DATE"<=SYSDATE@!))
  13 - access("GAM"."ACID"="LAM"."ACID")
  14 - filter(("LAM"."CHRGE_OFF_FLG" IS NULL OR "LAM"."CHRGE_OFF_FLG"<>'Y') AND ("LAM"."PAYOFF_FLG"<>'Y' OR
              "LAM"."PAYOFF_FLG" IS NULL))
  17 - filter("DMD_FLOW_ID"='PRDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
  20 - filter("DMD_FLOW_ID"='INDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')


Statistics
----------------------------------------------------------
         80  recursive calls
          0  db block gets
     262477  consistent gets
     152573  physical reads
          0  redo size
      72481  bytes sent via SQL*Net to client
       1631  bytes received via SQL*Net from client
        103  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       1521  rows processed

Re: CURSOR taking more time [message #661174 is a reply to message #661172] Fri, 10 March 2017 02:51 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Any chance to reverse the logic of DEL_FLG != 'Y' to DEL_FLG = 'N' (by example)
It's been a while to read the old join syntax.
There is also an INDEX SKIP SCAN | IDX_GAM_ALT1_SHORT_NAME that might need investigation
Post a testcase
Re: CURSOR taking more time [message #661175 is a reply to message #661173] Fri, 10 March 2017 02:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What is the code for the TBAADM.LDT view? It isn't merging.
And have you tried indexing LA_DMD_TABLE on DMD_FLOW_ID,BANK_ID,DEL_FLG ?

--update sorry, I misread the plan: it is the two subqueries that don't merge, because they have aggregations. Can you not re-write them into one query, grouping by ACID and DMD_FLOW_ID, so that you need only one pass through the table?

[Updated on: Fri, 10 March 2017 03:22]

Report message to a moderator

Re: CURSOR taking more time [message #661176 is a reply to message #661175] Fri, 10 March 2017 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does this give the same result?
SELECT NVL(SUM(CASE WHEN a.DMD_FLOW_ID = 'PRDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END),0),
       'PRDEM',
       NVL(SUM(CASE WHEN a.DMD_FLOW_ID = 'INDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END),0),
       'INDEM',
       GAM.FORACID,
       GAM.ACID,
       GAM.ACCT_CRNCY_CODE
FROM TBAADM.view_GAM GAM 
JOIN DBSCUST.C_MPAMT D ON GAM.FORACID = D.FORACID
JOIN TBAADM.LRCT E ON GAM.SCHM_CODE = E.REF_CODE
JOIN TBAADM.LAM LAM ON GAM.ACID = LAM.ACID
LEFT JOIN TBAADM.LDT A ON A.ACID = GAM.ACID 
                       AND A.BANK_ID = GAM.BANK_ID
                       AND A.DMD_FLOW_ID IN ('PRDEM', 'INDEM') 
                       AND A.DEL_FLG != 'Y' 
WHERE (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND E.REF_REC_TYPE = 'CBGSC'
AND E.DEL_FLG = 'N'
AND E.SCHM_REF_TYPE = 'CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL  
AND (D.LSA_COMM_DATE IS NULL OR D.LSA_COMM_DATE <= sysdate)
GROUP BY GAM.FORACID,
         GAM.ACID,
         GAM.ACCT_CRNCY_CODE
HAVING SUM(CASE WHEN a.DMD_FLOW_ID = 'PRDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END) > 0
OR SUM(CASE WHEN a.DMD_FLOW_ID = 'INDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END) > 0;

[Updated on: Fri, 10 March 2017 03:59]

Report message to a moderator

Re: CURSOR taking more time [message #661177 is a reply to message #661176] Fri, 10 March 2017 03:33 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
     262477  consistent gets
     152573  physical reads

That seems a low number for 2 hours. Have you traced this to check it is not waiting elsewhere?
Re: CURSOR taking more time [message #661178 is a reply to message #661177] Fri, 10 March 2017 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thinking about it, that LEFT JOIN should just be JOIN.

[Updated on: Fri, 10 March 2017 03:59]

Report message to a moderator

Re: CURSOR taking more time [message #661179 is a reply to message #661178] Fri, 10 March 2017 04:47 Go to previous messageGo to next message
amarjadhav
Messages: 40
Registered: April 2011
Location: bangalore
Member

Thanks all for your valuable suggestion, will apply same in program and share the outcome with you,

@cookiemonster --> The new query is giving same result with less time in sql developer. I will do through tesing and update you.

@Flyby,@John Watson,@Roachcoach i will check the possibilities whether i can apply index/code rewrite.

Thanks
Re: CURSOR taking more time [message #661181 is a reply to message #661179] Fri, 10 March 2017 05:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
That wasn't my point. My point was are you sure you're waiting on the query 100% of the time?
Re: CURSOR taking more time [message #661196 is a reply to message #661173] Fri, 10 March 2017 06:27 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
[quote title=amarjadhav wrote on Fri, 10 March 2017 02:44]Sorry for the inconvenience,

Well, you used code tags, but that doesn't help the lack of readability due to lack of formatting to begin with. For your own sanity, you should learn to format your code with logical indention.
Like this: (took me longer to write this sentence than it did to reformat your code in SQL Developer!):

SELECT NVL(A.PRDEM,0),
  'PRDEM',
  NVL(B.INDEM,0),
  'INDEM',
  GAM.FORACID,
  GAM.ACID,
  GAM.ACCT_CRNCY_CODE
FROM
  (SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,
    ACID                           AS AACID
  FROM TBAADM.LDT
  WHERE DMD_FLOW_ID ='PRDEM'
  AND DEL_FLG      != 'Y'
  AND BANK_ID       = 'SG'
  GROUP BY ACID
  ) A ,
  (SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,
    ACID                           AS BACID
  FROM TBAADM.LDT
  WHERE DMD_FLOW_ID ='INDEM'
  AND DEL_FLG      != 'Y'
  AND BANK_ID       = 'SG'
  GROUP BY ACID
  ) B,
  DBSCUST.C_MPAMT D,
  TBAADM.LRCT E,
  TBAADM.view_GAM GAM,
  TBAADM.LAM LAM
WHERE GAM.FORACID       = D.FORACID
AND GAM.SCHM_CODE       = E.REF_CODE
AND GAM.ACID            = LAM.ACID
AND (LAM.PAYOFF_FLG    != 'Y'
OR LAM.PAYOFF_FLG      IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y'
OR LAM.CHRGE_OFF_FLG   IS NULL)
AND A.AACID(+)          = GAM.ACID
AND B.BACID(+)          = GAM.ACID
AND E.REF_REC_TYPE      ='CBGSC'
AND E.DEL_FLG           ='N'
AND E.SCHM_REF_TYPE     ='CJ'
AND GAM.SOL_ID          = '367'
AND GAM.ACCT_CLS_FLG    = 'N'
AND GAM.BANK_ID         = 'SG'
AND D.MODE_OF_PAYMNT    = 'ACT'
AND D.ACCNT_TYPE       IS NOT NULL
AND (A.PRDEM            >0
OR B.INDEM              > 0)
AND ((D.LSA_COMM_DATE  IS NULL)
OR (D.LSA_COMM_DATE    <= sysdate));
Re: CURSOR taking more time [message #661562 is a reply to message #661171] Thu, 23 March 2017 14:30 Go to previous message
mikek
Messages: 29
Registered: January 2017
Junior Member
It is a very complex SQL Statement so I encourage
further review/testing/modification of the included
suggestions.


Some Analysis Notes:

Just an FYI - not sure, but the "gam" usage is possibly
using a View "tbaadm.view_gam". Explain Plan does not appear
to show any issues.


Uses Synonyms referencing Tables(View?)

Synonym     Table Name                  Script Table Aliases
-------     --------------------------  --------------------
ldt         LA_DMD_TABLE                 a and b
c_mpamt     CUST_MPAMT_TBL               d
lrct        LARGE_REFERENCE_CODE_TABLE   e
gam         GENERAL_ACCT_MAST_TABLE	 gam
            (View? "view_gam")
lam         LA_ACCT_MAST_TABLE           lam


Indexing Suggestions:

1. CUST_MPAMT_TBL(c_mpamt)

Check Table CUST_MPAMT_TBL for an index including the Column "foracid".
In the Explain Plan the Table is showing "TABLE ACCESS FULL".

Note: Index Name and Tablespace will need modification. If no index
exist I would suggest the following Index. Research Needed.

CREATE INDEX ak_cust_mpamt_tbl
  ON cust_mpamt_tbl(foracid)
TABLESPACE ???;



2. LA_DMD_TABLE (ldt)

Check LA_DMD_TABLE for an index including the Column "acid".
In the Explain Plan the Table is showing "TABLE ACCESS FULL" twice
to satisfy a Sum of a specific type.
As suggested this should be looked at for a rewrite.

Note: Index Name and Tablespace will need modification. If no index
exist I would suggest the following Composite Index. Research Needed.

CREATE INDEX ak_la_dmd_table
  ON la_dmd_table(acid, dmd_flow_id)
TABLESPACE ???;



Formatted SQL Based on Original Post:
(Some Reformatting was Done, Formatted SQL is Easier to Analyze)

SELECT NVL(a.prdem, 0),
       'PRDEM',
       NVL(b.indem, 0),
       'INDEM',
       gam.foracid,
       gam.acid,
       gam.acct_crncy_code
FROM   ( SELECT SUM(dmd_amt - tot_adj_amt) AS PRDEM,
                acid                       AS AACID
         FROM   tbaadm.ldt
         WHERE  dmd_flow_id =  'PRDEM'
         AND    del_flg     != 'Y'
         AND    bank_id     =  'SG'
         GROUP  BY acid
        )                                                  a,
       ( SELECT SUM(dmd_amt - tot_adj_amt) AS indem,
                acid                       AS BACID
         FROM   tbaadm.ldt
         WHERE  dmd_flow_id =  'INDEM'
         AND    del_flg     != 'Y'
         AND    bank_id     =  'SG'
         GROUP  BY acid
       )                                                   b,
       dbscust.c_mpamt                                     d,
       tbaadm.lrct                                         e,
       tbaadm.view_gam                                     gam,
       tbaadm.lam                                          lam
WHERE  gam.acid            = a.aacid(+)
AND    gam.acid            = b.bacid(+) 
AND    gam.foracid         = d.foracid
AND      d.mode_of_paymnt       = 'ACT'
AND      d.accnt_type           IS NOT NULL
AND      (   ( d.lsa_comm_date  IS NULL )
          OR ( d.lsa_comm_date  <= SYSDATE )
         )
AND    gam.schm_code       = e.ref_code
AND      e.ref_rec_type         = 'CBGSC'
AND      e.del_flg              = 'N'
AND      e.schm_ref_type        = 'CJ'
AND    gam.acid            = lam.acid
AND      (   lam.payoff_flg     != 'Y'
          OR lam.payoff_flg     IS NULL )
AND      (   lam.chrge_off_flg  != 'Y'
          OR lam.chrge_off_flg  IS NULL )
AND    gam.sol_id             = '367'
AND    gam.acct_cls_flg       = 'N'
AND    gam.bank_id            = 'SG'
AND    (   a.prdem > 0
        OR b.indem > 0
       ); 


Possible Solution:
(Unable to Test.)

SELECT *
FROM  (
          SELECT   ( SELECT NVL(SELECT SUM(dmd_amt - tot_adj_amt), 0)
                     FROM   tbaadm.ldt
                     WHERE  acid        = gam.acid
                     AND    dmd_flow_id =  'PRDEM'
                     AND    del_flg     != 'Y'
                     AND    bank_id     =  'SG'
                   )                                                 prdem
                 ,'PRDEM'
                 , ( SELECT NVL(SELECT SUM(dmd_amt - tot_adj_amt), 0)
                     FROM   tbaadm.ldt
                     WHERE  acid        = gam.acid
                     AND    dmd_flow_id =  'INDEM'
                     AND    del_flg     != 'Y'
                     AND    bank_id     =  'SG'
                   )                                                 indem
                 ,'INDEM'
                 ,gam.foracid
                 ,gam.acid
                 ,gam.acct_crncy_code
          FROM   tbaadm.view_gam                                     gam
          JOIN   dbscust.c_mpamt                                     d
            ON        gam.foracid            = d.foracid
                 AND  d.mode_of_paymnt       = 'ACT'
                 AND  d.accnt_type           IS NOT NULL
                 AND  (    ( d.lsa_comm_date IS NULL )
                        OR ( d.lsa_comm_date <= SYSDATE )
                      )
          JOIN   tbaadm.lrct                                         e
            ON        gam.schm_code          = e.ref_code
                 AND  e.ref_rec_type         = 'CBGSC'
                 AND  e.del_flg              = 'N'
                 AND  e.schm_ref_type        = 'CJ'
          JOIN tbaadm.lam                                            lam
            ON        gam.acid               = lam.acid
                 AND  (    lam.payoff_flg    != 'Y'
                        OR lam.payoff_flg    IS NULL )
                 AND  (    lam.chrge_off_flg != 'Y'
                        OR lam.chrge_off_flg IS NULL )
          WHERE  gam.sol_id                  = '367'
          AND    gam.acct_cls_flg            = 'N'
          AND    gam.bank_id                 = 'SG'
      )  
WHERE (    a.prdem > 0
        OR b.indem > 0
       ); 

Previous Topic: Orcale: Missing Group By expression working
Next Topic: Query performance
Goto Forum:
  


Current Time: Fri Mar 29 05:35:54 CDT 2024