sql tunning

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 );
ShirleyTannerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

richard_cristCommented:
What indexes exist for each table in the query above?  Thanks.
0
ShirleyTannerAuthor Commented:
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
richard_cristCommented:
Thank you.  I will look and see if anything obvious appears as the problem.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DavidSenior Oracle Database AdministratorCommented:
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
DavidSenior Oracle Database AdministratorCommented:
Asker has not presented their own solution yet; only that she is working on one.
0
ShirleyTannerAuthor Commented:
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
richard_cristCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShirleyTannerAuthor Commented:
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
DavidSenior Oracle Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.