Home » RDBMS Server » Performance Tuning » improve performance for the query (Oracle 11g)
improve performance for the query [message #625412] Wed, 08 October 2014 01:00 Go to next message
sumanh
Messages: 2
Registered: February 2011
Location: Mumbai
Junior Member
The below query is taking very long.

Can anybody suggest of tuning it -
WITH cte AS 
( 
       SELECT 
              /*+ INDEX (ct CLMTRANS_CLAIM_X) INDEX (cgc CLMGLCDTXN_CLMACCTTXN_X) USE_NL(c, ct, cat, cgc) */
              cat.lgl_entity_id legalid, 
              pr.identifier     centernumber, 
              cgc.gl_acct_no    glcode, 
              cm.short_descr    gldescription, 
              CASE 
                     WHEN cgc.credit_debit_ind = ''d'' THEN cat.amt 
              END entereddebits, 
              CASE 
                     WHEN cgc.credit_debit_ind = ''c'' THEN cat.amt 
              END enteredcredits 
       FROM   claims c, 
              claim_transactions ct, 
              claim_acct_transactions cat, 
              claim_gl_credit_debit_txns cgc, 
              transaction_types tt, 
              transaction_sub_types tst, 
              code_masters cm, 
              party_roles pr 
       WHERE  c.clm_seq_no = ct.clm_seq_no 
       AND    cat.clm_txn_seq_no = ct.clm_txn_seq_no 
       AND    cat.clm_acct_txn_seq_no = cgc.clm_acct_txn_seq_no 
       AND    cm.cd_mstr_seq_no = cgc.gl_acct_cd_mstr_seq_no 
       AND    ct.txn_type_cd = tt.txn_type_cd 
       AND    ct.txn_sub_type_cd = tst.txn_sub_type_cd 
       AND    pr.pr_seq_no = c.fac_pr_seq_no 
       AND    pr.pr_type_cd = ''elie_fac'' 
       AND    cat.pstng_date_time BETWEEN to_date(''' ||                FINANCIALPERIOD ||                ''', ''mon-yyyy'') 
       AND    last_day(to_date(''' ||                FINANCIALPERIOD || ''',''mon-yyyy'')) 
       AND    (( 
                            ''ALL'' IN (' || CNTRID ||                ')) 
              OR     ( 
                            pr.identifier IN (' || CNTRID || '))) 
       AND    (( 
                            ''ALL'' IN (' || GLCD ||                ')) 
              OR     ( 
                            cgc.gl_acct_no IN (' || GLCD || '))) 
       AND    (( 
                            ''ALL'' IN (' || SUBMITGL ||                ')) 
              OR     ( 
                            cgc.submit_to_gl_ind IN (' || SUBMITGL || '))) 
       UNION ALL 
       SELECT 
              /*+ INDEX (cit CLMITMTRNS_CLM_X) INDEX (cigc CLMITMGLCDTXN_CLMITMACTCDTX_X) USE_NL(c, cit, ciat, cigc) */
              ciat.lgl_entity_id legalid, 
              pr.identifier      centernumber, 
              cigc.gl_acct_no    glcode, 
              cm.short_descr     gldescription, 
              CASE 
                     WHEN cigc.credit_debit_ind = ''d'' THEN ciat.amt 
              END entereddebits, 
              CASE 
                     WHEN cigc.credit_debit_ind = ''c'' THEN ciat.amt 
              END enteredcredits 
       FROM   claims c, 
              claim_item_transactions cit, 
              claim_item_acct_transactions ciat, 
              claim_itm_gl_credit_debit_txns cigc, 
              transaction_types tt, 
              transaction_sub_types tst, 
              code_masters cm, 
              party_roles pr 
       WHERE  c.clm_seq_no = cit.clm_seq_no 
       AND    ciat.clm_itm_txn_seq_no = cit.clm_itm_txn_seq_no 
       AND    ciat.clm_itm_acct_txn_seq_no = cigc.clm_itm_acct_txn_seq_no 
       AND    cm.cd_mstr_seq_no = cigc.gl_acct_cd_mstr_seq_no 
       AND    cit.txn_type_cd = tt.txn_type_cd 
       AND    cit.txn_sub_type_cd = tst.txn_sub_type_cd 
       AND    pr.pr_seq_no = c.fac_pr_seq_no 
       AND    pr.pr_type_cd = ''elie_fac'' 
       AND    ciat.pstng_date_time BETWEEN to_date(''' ||                FINANCIALPERIOD ||                ''', ''mon-yyyy'') 
       AND    last_day(to_date(''' ||                FINANCIALPERIOD || ''',''mon-yyyy'')) 
       AND    (( 
                            ''ALL'' IN (' || GLCD ||                ')) 
              OR     ( 
                            cigc.gl_acct_no IN (' || GLCD || '))) 
       AND    (( 
                            ''ALL'' IN (' || SUBMITGL ||                ')) 
              OR     ( 
                            cigc.submit_to_gl_ind IN (' || SUBMITGL || '))) 
       AND    (( 
                            ''ALL'' IN (' || CNTRID ||                ')) 
              OR     ( 
                            pr.identifier IN (' || CNTRID ||                ')))) 
SELECT   legalid, 
         centernumber, 
         glcode, 
         gldescription, 
         SUM(entereddebits)  debits, 
         SUM(enteredcredits) credits 
FROM     cte 
GROUP BY legalid, 
         centernumber, 
         glcode, 
         gldescription



Edited by Lalit : Formatted code using http://www.dpriver.com/pp/sqlformat.htm and added code tags

[Updated on: Wed, 08 October 2014 03:54] by Moderator

Report message to a moderator

Re: improve performance for the query [message #625414 is a reply to message #625412] Wed, 08 October 2014 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Then read How to Identify Performance Problem and Bottleneck and provide the requested information.

[Updated on: Wed, 08 October 2014 01:33]

Report message to a moderator

Re: improve performance for the query [message #625417 is a reply to message #625412] Wed, 08 October 2014 02:12 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
As your query is invalid without putting between single quotes and making it dynamic, the most obvious way of tuning is to make it static. Also consider whether all the hints make sense under all conditions.

According to the variables in the IN clauses - it depends what is their origin.
If they are taken from collections, use them directly.
If they are comma separated strings, convert them to collections. You may use any technique from this article: http://tkyte.blogspot.com/2006/06/varying-in-lists.html
Previous Topic: code taking long amount of time to execute
Next Topic: Query chooses bitmap index and runs slower
Goto Forum:
  


Current Time: Fri Mar 29 06:04:27 CDT 2024