Forum Stats

  • 3,815,628 Users
  • 2,259,063 Discussions
  • 7,893,189 Comments

Discussions

Re-writing the below query for better performance and optimized cost.

Dayanand
Dayanand Member Posts: 33 Blue Ribbon
edited May 29, 2015 8:23AM in SQL & PL/SQL

Oracle 10g.

----------------------

Query

UPDATE FACETS_CUSTOM.MMR_DTL

SET

CAPITN_PRCS_IND = 2,

FIL_RUN_DT = Current_fil_run_dt,

ROW_UPDT_DT = dta_cltn_end_dttm

WHERE CAPITN_PRCS_IND = 5

AND HSPC_IND ='Y'

  AND EXISTS (SELECT 1

FROM FACETS_STAGE.CRME_FUND_DTL_STG STG_CRME

WHERE STG_CRME.MBR_CK = MMR_DTL.MBRSHP_CK

  AND MMR_DTL.PMT_MSA_STRT_DT BETWEEN STG_CRME.ERN_FROM_DT AND STG_CRME.ERN_THRU_DT

  AND STG_CRME.FUND_ID IN ('AAB1', '1AA2', '1BA2', 'AAB2', '1AA3', '1BA3', '1B80', '1A80') )

  AND EXISTS (SELECT 1

FROM FACETS_CUSTOM.FCTS_TMS_MBRID_XWLK XWLK

WHERE XWLK.MBR_CK = MMR_DTL.MBRSHP_CK

  AND MMR_DTL.PMT_MSA_STRT_DT BETWEEN XWLK.HSPC_EVNT_EFF_DT AND XWLK.HSPC_EVNT_TERM_DT);

Explain plan of the Query

-----------------------------------------------

Plan hash value: 3109991485

-------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT              |                       |     1 |   148 | 12431   (2)| 00:02:30 |

|   1 |  UPDATE                       | MMR_DTL               |       |       |            |          |

|   2 |   NESTED LOOPS SEMI           |                       |     1 |   148 | 12431   (2)| 00:02:30 |

|*  3 |    HASH JOIN RIGHT SEMI       |                       |    49 |  5488 | 12375   (2)| 00:02:29 |

|   4 |     TABLE ACCESS FULL         | FCTS_TMS_MBRID_XWLK   |  6494 | 64940 |    24   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL         | MMR_DTL               |   304K|    29M| 12347   (2)| 00:02:29 |

|*  6 |    TABLE ACCESS BY INDEX ROWID| CRME_FUND_DTL_STG     |     1 |    36 |     5   (0)| 00:00:01 |

|*  7 |     INDEX RANGE SCAN          | IE1_CRME_FUND_DTL_STG |     8 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("XWLK"."MBR_CK"="MMR_DTL"."MBRSHP_CK")

       filter("XWLK"."HSPC_EVNT_EFF_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND

              "XWLK"."HSPC_EVNT_TERM_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))

   5 - filter("CAPITN_PRCS_IND"=5 AND "HSPC_IND"='Y')

   6 - filter(("STG_CRME"."FUND_ID"='1A80' OR "STG_CRME"."FUND_ID"='1AA2' OR

              "STG_CRME"."FUND_ID"='1AA3' OR "STG_CRME"."FUND_ID"='1B80' OR "STG_CRME"."FUND_ID"='1BA2' OR

              "STG_CRME"."FUND_ID"='1BA3' OR "STG_CRME"."FUND_ID"='AAB1' OR "STG_CRME"."FUND_ID"='AAB2') AND

              "STG_CRME"."ERN_FROM_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND

              "STG_CRME"."ERN_THRU_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))

   7 - access("STG_CRME"."MBR_CK"="MMR_DTL"."MBRSHP_CK")

I couldnt optimize this query for better performance and optimized cost.. Can some one guide me on this.

Thanks,

DS

Ricky007

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,946 Blue Diamond
    edited May 6, 2015 3:14PM Answer ✓

    You think you're going to update 85K rows, Oracle thinks it's going to update one row.

    By the time the first existence test is run Oracle thinks it's already down to 49 rows, which is probably why it uses nested loop join for the second test. (In your version of Oracle the existence subquery introduces a very bad (small) guess about how much data will survive).

    It's possible that you will get better performance if you hint Oracle into using a hash join for both existence tests - and you may want to think about which test will eliminate most data and force that one to happen first.

    Having said that, though, note that the tablescan of MMR_DTL is a huge fraction of the cost of the query - and a tablescan is an easy thing for Oracle to cost correctly - so, despite your comments about updating a column with an index on it, you may find that the query can be more efficient if you use an index. This is more likely to be the case if the data "WHERE CAPITN_PRCS_IND = 5 AND HSPC_IND ='Y' " is well clustered (perhaps the most recent data added to the table).  You might then reduce the cost of maintaining this index by creating a function-based index that indexes only the row rows where these predicate are both true so that the update to 2 removes the entries from the index and allows the index to stay as small as possible.

    Regards

    Jonathan Lewis

    Dayanand
«1

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 5, 2015 2:13PM
  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited May 5, 2015 2:26PM

    Hi Dayanand,

    after quick look i spotted that MMR_DTL table is read fully regardless predicates CAPITN_PRCS_IND = 5 AND HSPC_IND = 'Y'

    You do not share any index or table/column information so i just guess that there is no index (MMR_DTL.CAPITN_PRCS_IND, MMR_DTL.HSPC_IND)

    Dayanand
  • Dayanand
    Dayanand Member Posts: 33 Blue Ribbon
    edited May 5, 2015 2:28PM

    Can  anybody give me a high level observations on the above mentioned query

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 5, 2015 2:31PM

    How? You didn't tell us anything about your tables, their sizes, any indexes that might be in place.

    That would be like me posting a picture of my car's accelerator pedal and asking you for high level observations about making it go faster.

  • Dayanand
    Dayanand Member Posts: 33 Blue Ribbon
    edited May 5, 2015 2:31PM

    Thanks Jarkko for replying.

    The table MMR_DTL doesnt have index on these columns CAPITN_PRCS_IND , HSPC_IND .. Since this is an update stmt which will update 85k records, In a dilema whether to index these columns or not .. And this table MMR_DTL is an ever growing table. Worried about the update performance.

    Thanks,

  • Dayanand
    Dayanand Member Posts: 33 Blue Ribbon
    edited May 5, 2015 2:33PM

    John, I will update my post with all the required information of the query.

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited May 5, 2015 2:35PM
    The table MMR_DTL doesnt have index on these columns CAPITN_PRCS_IND , HSPC_IND .. Since this is an update stmt which will update 85k records, In a dilema whether to index these columns or not .. And this table MMR_DTL is an ever growing table. Worried about the update performance. 
    

    well worrying too much is not good for health . Add index and observe what happens. I can't see any reason why you could not add index for column(s) that you filter.

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited May 5, 2015 3:46PM

    Guessing   (might even not be equivalent)

    merge into facets_custom.mmr_dtl md

    using (select d.mbrshp_ck

             from facets_custom.mmr_dtl d

                  left outer join

                  facets_stage.crme_fund_dtl_stg s

               on d.mbrshp_ck = s.mbr_ck

              and d.pmt_msa_strt_dt between s.ern_from_dt and s.ern_thru_dt

              and d.capitn_prcs_ind = 5

              and d.hspc_ind = 'y'

              and s.fund_id in ('AAB1','1AA2','1BA2','AAB2','1AA3','1BA3','1B80','1A80')

                  left outer join

                  facets_custom.fcts_tms_mbrid_xwlk x

               on d.mbrshp_ck = x.mbr_ck

              and d.pmt_msa_strt_dt between x.hspc_evnt_eff_dt and x.hspc_evnt_term_dt

            where s.mbr_ck is not null

              and x.mbr_ck is not null

          ) sx

       on md.mbrshp_ck = sx.mbr_ck

    when matched

    then update

             set md.capitn_prcs_ind = 2,

                 md.fil_run_dt = current_fil_run_dt,

                 md.row_updt_dt = dta_cltn_end_dttm

    Regards

    Etbin

    Ricky007Dayanand
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited May 5, 2015 3:52PM

    is the HASH JOIN in step 3 indeed returning just 49 rows (or something in that ballpark) as the optimizer expects? If the result is much bigger then the following NESTED LOOPS join may be a bad idea.

    Dayanand
  • user648773
    user648773 Member Posts: 59
    edited May 6, 2015 1:08PM

    Do you need to update again the register that you already have updated?

    Perhaps you could include a condition to filter historical register that you don't need to update again.

    For example adding the following condition:

         "and MMR_DTL.PMT_MSA_STRT_DT > sysdate - 90"

This discussion has been closed.