Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql tunning

Posted on 2008-11-18
11
Medium Priority
?
470 Views
Last Modified: 2013-12-19
I am trying to tune the following query.. Can you make any recommendations.... I am getting 2 full table scans on the payment table and the claim_form table. Indexes do exist, but are not being used...

SELECT  A.PMT_CHEQUE_NO,
       A.PMT_EFT_NO,
       A.BA_LETTER_CD,
       A.ROW_EFF_DT,
       A.STATUS_TYPE_CD,
       A.PMT_LUPD_OPER_CD,
       A.REASON_CD,
       B.CF_CLAIM_FORM_ID,
       C.CODE_VALUE_DESC,
       D.BA_DESC
FROM  PROD.PAYMENT A,
      PROD.PAYMENT_DETAIL B,
      PROD.APP_CODE_VALUE_DESC C,
      PROD.BANK_ACCOUNT D
WHERE  A.STATUS_TYPE_CD = 'SP'
      AND A.ROW_EXP_DT IS NULL
      AND A.DELETE_DT  IS NULL
      and a.BA_BANK_ID = d.BA_BANK_ID
      and d.ROW_EXP_DT is null
      and d.DELETE_DT is null
      AND C.CODE_VALUE_CD = A.REASON_CD
      AND C.CODE_TYPE_CD = 'REASON_CD'
      AND A.PMT_PAYMENT_ID = B.PMT_PAYMENT_ID
      AND B.Delete_Dt   IS NULL
      AND A.BA_BANK_ID = D.BA_BANK_ID
      AND C.CODE_VALUE_LANG_CD = 'ENG'
      AND NOT EXISTS (SELECT 1 FROM PROD.CLAIM_FORM e
                       WHERE B.CF_CLAIM_FORM_ID = e.CF_CLAIM_FORM_ID
                           AND e.REVISION_NO > b.CLAIM_FORM_REVISION_NO )
     AND NOT EXISTS (SELECT 1 FROM PROD.CLAIM_FORM f, prod.payment_detail pd2
                       WHERE a.pmt_payment_id = pd2.pmt_payment_id
                                                  AND f.revision_no > pd2.CLAIM_FORM_REVISION_NO
                                        AND f.cf_claim_form_id = pd2.cf_claim_form_id
                                                  AND f.CLAIM_TRANS_TYPE_CD = 'AJ'
                                        AND f.delete_dt IS NULL
                                        AND f.row_exp_dt IS NULL );
0
Comment
Question by:ShirleyTanner
  • 3
  • 3
  • 3
9 Comments
 
LVL 3

Expert Comment

by:richard_crist
ID: 22986485
What indexes exist for each table in the query above?  Thanks.
0
 

Author Comment

by:ShirleyTanner
ID: 22987092
The following indexes exist

       Index Name      Unique?      Logging      Degree      Column Name      Order      Position      Index Owner

PLAIN      X02CLAIM_FORM      N      YES      1      PAYEE_TYPE_CD      Asc      1      PROD
PLAIN      X03CLAIM_FORM      N      YES      1      CF_RECEIVED_DT      Asc      1      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      SU_SUBS_ID      Asc      1      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      PA_DEP_NO      Asc      2      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      CLAIM_TYPE_CD      Asc      3      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      STATUS_TYPE_CD      Asc      4      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      ROW_EFF_DT      Asc      5      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      ROW_EXP_DT      Asc      6      PROD
PLAIN      X04CLAIM_FORM      N      YES      1      DELETE_DT      Asc      7      PROD
PLAIN      X05CLAIM_FORM      N      YES      1      CF_ENTERED_DT      Asc      1      PROD
PLAIN      X05CLAIM_FORM      N      YES      1      CF_PRVDR_ACCOUNT_NO      Asc      2      PROD
PLAIN      X06CLAIM_FORM      N      YES      1      CLAIM_TYPE_CD      Asc      1      PROD
PLAIN      X06CLAIM_FORM      N      YES      1      STATUS_TYPE_CD      Asc      2      PROD
PLAIN      X06CLAIM_FORM      N      YES      1      BNFT_TYPE_CD      Asc      3      PROD
PLAIN      X07CLAIM_FORM      N      YES      1      CF_POLICY_NO      Asc      1      PROD
PLAIN      X08CLAIM_FORM      N      YES      1      CF_PARTICIPANT_ID      Asc      1      PROD
PLAIN      X08CLAIM_FORM      N      YES      1      CF_POLICY_NO      Asc      2      PROD
PLAIN      X09CLAIM_FORM      N      YES      1      CF_WAC_CLAIM_NO      Asc      1      PROD
PLAIN      X10CLAIM_FORM      N      YES      1      CF_EXTERNAL_CLAIM_NO      Asc      1      PROD
PLAIN      X11CLAIM_FORM      N      YES      1      REVISION_NO      Asc      1      PROD
PLAIN      X11CLAIM_FORM      N      YES      1      CF_CLAIM_FORM_ID      Asc      2      PROD
PLAIN      X11CLAIM_FORM      N      YES      1      CLAIM_TRANS_TYPE_CD      Asc      3      PROD
PLAIN      X11CLAIM_FORM      N      YES      1      DELETE_DT      Asc      4      PROD
PLAIN      X11CLAIM_FORM      N      YES      1      ROW_EXP_DT      Asc      5      PROD
PLAIN      XPKCLAIM_FORM      Y      YES      1      CF_CLAIM_FORM_ID      Asc      1      PROD
PLAIN      XPKCLAIM_FORM      Y      YES      1      REVISION_NO      Asc      2      PROD

       Index Name      Unique?      Logging      Degree      Column Name      Order      Position      Index Owner

PLAIN      X01PAYMENT      N      YES      1      ROW_EFF_DT      Asc      1      PROD
PLAIN      X02PAYMENT      N      YES      1      PMT_CASHED_DT      Asc      1      PROD
PLAIN      X02PAYMENT      N      YES      1      STATUS_TYPE_CD      Asc      2      PROD
PLAIN      X03PAYMENT      N      YES      1      PMT_CHEQUE_NO      Asc      1      PROD
PLAIN      X03PAYMENT      N      YES      1      PAYMENT_TYPE_CD      Asc      2      PROD
PLAIN      X04PAYMENT      N      YES      1      PAYMENT_TYPE_CD      Asc      1      PROD
PLAIN      X04PAYMENT      N      YES      1      PAYMENT_CURRENCY_CD      Asc      2      PROD
PLAIN      X04PAYMENT      N      YES      1      PMT_CHEQUE_NO      Asc      3      PROD
PLAIN      X04PAYMENT      N      YES      1      ROW_EFF_DT      Asc      4      PROD
PLAIN      X04PAYMENT      N      YES      1      ROW_EXP_DT      Asc      5      PROD
PLAIN      X04PAYMENT      N      YES      1      DELETE_DT      Asc      6      PROD
PLAIN      X05PAYMENT      N      YES      1      PMT_ISSUE_DT      Asc      1      PROD
PLAIN      X05PAYMENT      N      YES      1      PMT_CASHED_DT      Asc      2      PROD
PLAIN      X05PAYMENT      N      YES      1      STATUS_TYPE_CD      Asc      3      PROD
PLAIN      X05PAYMENT      N      YES      1      ROW_EFF_DT      Asc      4      PROD
PLAIN      X05PAYMENT      N      YES      1      ROW_EXP_DT      Asc      5      PROD
PLAIN      X05PAYMENT      N      YES      1      DELETE_DT      Asc      6      PROD
PLAIN      X06PAYMENT      N      YES      1      STATUS_TYPE_CD      Asc      1      PROD
PLAIN      XPKPAYMENT      Y      YES      1      PMT_PAYMENT_ID      Asc      1      PROD
PLAIN      XPKPAYMENT      Y      YES      1      REVISION_NO      Asc      2      PROD

       Index Name      Unique?      Logging      Degree      Column Name      Order      Position      Index Owner

PLAIN      X02PAYMENT_DETAIL      N      YES      1      CF_CLAIM_FORM_ID      Asc      1      PROD
PLAIN      X02PAYMENT_DETAIL      N      YES      1      CLAIM_FORM_REVISION_NO      Asc      2      PROD
PLAIN      X02PAYMENT_DETAIL      N      YES      1      CL_CLAIM_ID      Asc      3      PROD
PLAIN      X02PAYMENT_DETAIL      N      YES      1      CLAIM_REVISION_NO      Asc      4      PROD
PLAIN      X02PAYMENT_DETAIL      N      YES      1      CD_DETAIL_ID      Asc      5      PROD
PLAIN      X02PAYMENT_DETAIL      N      YES      1      PMT_PAYMENT_ID      Asc      6      PROD
PLAIN      X03PAYMENT_DETAIL      N      YES      1      PMT_PAYMENT_ID      Asc      1      PROD
PLAIN      X03PAYMENT_DETAIL      N      YES      1      CL_CLAIM_ID      Asc      2      PROD
PLAIN      X03PAYMENT_DETAIL      N      YES      1      CLAIM_REVISION_NO      Asc      3      PROD
PLAIN      X03PAYMENT_DETAIL      N      YES      1      ROW_EXP_DT      Asc      4      PROD
PLAIN      X03PAYMENT_DETAIL      N      YES      1      DELETE_DT      Asc      5      PROD
PLAIN      XPKPAYMENT_DETAIL      Y      YES      1      PMT_PAYMENT_ID      Asc      1      PROD
PLAIN      XPKPAYMENT_DETAIL      Y      YES      1      PMD_DETAIL_ID      Asc      2      PROD
PLAIN      XPKPAYMENT_DETAIL      Y      YES      1      REVISION_NO      Asc      3      PROD
       Index Name      Unique?      Logging      Degree      Column Name      Order      Position      Index Owner

PLAIN      XPKAPP_CODE_VALUE      Y      YES      1      CODE_TYPE_CD      Asc      1      PROD
PLAIN      XPKAPP_CODE_VALUE      Y      YES      1      CODE_VALUE_CD      Asc      2      PROD
PLAIN      XPKAPP_CODE_VALUE      Y      YES      1      CODE_VALUE_EFF_DT      Asc      3      PROD

       Index Name      Unique?      Logging      Degree      Column Name      Order      Position      Index Owner

PLAIN      XPKBANK_ACCOUNT      Y      YES      1      BA_BANK_ID      Asc      1      PROD
PLAIN      XPKBANK_ACCOUNT      Y      YES      1      REVISION_NO      Asc      2      PROD
0
 
LVL 3

Expert Comment

by:richard_crist
ID: 22987223
Thank you.  I will look and see if anything obvious appears as the problem.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 23

Expert Comment

by:David
ID: 22987378
Do you know how to execute an EXPLAIN_PLAN on the statement?  If not it's certainly available on many previous questions here on EE.  What do your customers need the statement to do that it's not doing now -- how is the customer going to be satisfied by any changes?
0
 
LVL 23

Expert Comment

by:David
ID: 22994387
Asker has not presented their own solution yet; only that she is working on one.
0
 

Author Comment

by:ShirleyTanner
ID: 22997219
Yes I can run an explain plan... with the indexes show above I am able to prevent the full tablescans... but performance for the query is terrible... Please see the information below...

SELECT STATEMENT
18  NESTED LOOPS [ANTI]
16  NESTED LOOPS
13  NESTED LOOPS
10  NESTED LOOPS
7  HASH JOIN [ANTI]
2  PROD.PAYMENT TABLE ACCESS [BY INDEX ROWID]
1  PROD.X06PAYMENT INDEX [RANGE SCAN]
6  SYS.VW_SQ_1 VIEW
5  HASH JOIN
3  PROD.X11CLAIM_FORM INDEX [FAST FULL SCAN]
4  PROD.X02PAYMENT_DETAIL INDEX [FAST FULL SCAN]
9  PROD.BANK_ACCOUNT TABLE ACCESS [BY INDEX ROWID]
8  PROD.XPKBANK_ACCOUNT INDEX [RANGE SCAN]
12  PROD.APP_CODE_VALUE_DESC TABLE ACCESS [BY INDEX ROWID]
11  PROD.XPK_APP_CODE_VALUE_DESC INDEX [RANGE SCAN]
15  PROD.PAYMENT_DETAIL TABLE ACCESS [BY INDEX ROWID]
14  PROD.XPKPAYMENT_DETAIL INDEX [RANGE SCAN]
17  PROD.XPKCLAIM_FORM INDEX [RANGE SCAN]

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
  1    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index X06PAYMENT. 1,371 729,587 --
  2    This plan step retrieves rows from table PAYMENT through ROWID(s) returned by an index. 20,533 161,303 8,663.735
  3    This plan step retrieves all of the ROWIDs of B*-tree index X11CLAIM_FORM by sequentially scanning the leaf nodes. 18,523 5,822,858 90,982.156
  4    This plan step retrieves all of the ROWIDs of B*-tree index X02PAYMENT_DETAIL by sequentially scanning the leaf nodes. 37,377 14,036,440 233,026.836
  5    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 96,242 478,228 15,411.645
  6    This plan step represents the execution plan for the subquery defined by the view VW_SQ_1. 96,242 478,228 6,071.254
  7    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which do not satisfy the right side of a NOT IN condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 117,872 1 0.066
  8    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XPKBANK_ACCOUNT. 1 5 --
  9    This plan step retrieves rows from table BANK_ACCOUNT through ROWID(s) returned by an index. 2 2 0.059
  10    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 117,874 1 0.096
  11    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XPK_APP_CODE_VALUE_DESC. 1 1 --
  12    This plan step retrieves rows from table APP_CODE_VALUE_DESC through ROWID(s) returned by an index. 2 1 0.053
  13    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 117,876 1 0.148
  14    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XPKPAYMENT_DETAIL. 2 4 --
  15    This plan step retrieves rows from table PAYMENT_DETAIL through ROWID(s) returned by an index. 3 4 0.074
  16    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 117,879 1 0.167
  17    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XPKCLAIM_FORM. 2 929,093 9,073.174
  18    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 117,881 1 0.177
  19    This plan step designates this statement as a SELECT statement. 117,881 1 0.177
 
0
 
LVL 3

Accepted Solution

by:
richard_crist earned 750 total points
ID: 23007345
This is what I have noticed so far:
1)  Your query has the where clause term "A.BA_BANK_ID = D.BA_BANK_ID" listed twice.  I am not sure how this affects the query.
2)  The second NOT EXISTS clause contains WHERE clause condition "F.CLAIM_TRANS_TYPE_CD = 'AJ'".  The only index containing CLAIM_TRANS_TYPE_CD is X11CLAIM_FORM.  In that index CLAIM_TRANS_TYPE_CD is the third element out of five.  Since it is in the middle of the index the entire index must be scanned as indicated in explain plan step 3.
3)  Your query joins the table CLAIM_FORM to the table PAYMENT_DETAIL using PAYMENT_DETAIL.CLAIM_FORM_REVISION_NO.  The only index containing CLAIM_FORM_REVISION_NO is X02PAYMENT_DETAIL.  In that index CLAIM_FORM_REVISION_NO is the second element out of six.  Since it is in the middle of the index the entire index must be scanned as indicated in explain plan step 4.

There may be other information that can be taken from the explain plan, but those 3 are what I have noticed so far.
0
 

Author Comment

by:ShirleyTanner
ID: 23037659
I added some index based on your feedback... however, this did not help much.. I am still getting a full tablescan on claim_form... I replaced the X11CLAIM_FORM index with the single field of CLAIM_TRANS_TYPE_CD. I also added the following indexes...

CREATE INDEX "PROD"."X06PAYMENT"
    ON "PROD"."PAYMENT"  ("STATUS_TYPE_CD") ...

CREATE INDEX "PROD"."X04PAYMENT_DETAIL"
    ON "PROD"."PAYMENT_DETAIL"  ("CLAIM_FORM_REVISION_NO") ...

CREATE INDEX "PROD"."X11CLAIM_FORM"
    ON "PROD"."CLAIM_FORM"  ("CLAIM_TRANS_TYPE_CD") ...

If I just focus on a subset of the query...

SELECT 1 FROM PROD.CLAIM_FORM f, prod.payment_detail pd2,PROD.PAYMENT A
                       WHERE a.pmt_payment_id = pd2.pmt_payment_id
                                        AND f.revision_no > pd2.CLAIM_FORM_REVISION_NO
                                        AND f.cf_claim_form_id = pd2.cf_claim_form_id
                                        AND f.CLAIM_TRANS_TYPE_CD = 'AJ'
                                       AND f.delete_dt IS NULL
                                       AND f.row_exp_dt IS NULL

The explain plan looks like...
 SELECT STATEMENT
5  HASH JOIN
3  HASH JOIN
1  PROD.CLAIM_FORM TABLE ACCESS [FULL]
2  PROD.X02PAYMENT_DETAIL INDEX [FAST FULL SCAN]
4  PROD.XPKPAYMENT INDEX [FAST FULL SCAN]

What can you suggest....
0
 
LVL 23

Assisted Solution

by:David
David earned 750 total points
ID: 23043044
Your table and index statistics are fresh, correct?  
This is the tricky part of tuning where I always forget whether or not the driving table (claim_form) should come last in the predicate instead of first....
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question