DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: query tuning

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    query tuning

    Hi DBAs,

    The following is a query thrown to my desk by a developer.
    He claimed it took much longer time than it should be.
    I did something but no significant changes.

    all yr input is greatly appreciated.

    select vs.act_site_code, vs.site_name, vs.pn, vs.test_date,
    vs.load_date, vs.match_date, vs.billing_code, vs.hours24_flag, vc.code, vc.name, decode(vc.client_type,'E', 'Education', 'B', 'Business') client_type,
    vf.litho_code, vf.lname, vf.ssn_id,
    vr.test_type rfi,
    vm.test_type am,
    vl.test_type l,
    vw.test_type w,
    vi.test_type li,
    vt.test_type tw,
    va.test_type at,
    vb.test_type ob
    from wkbus.v_pn_site vs,
    wkbus.v_pn_client vc,
    wkbus.v_pn_folder_detail vf,
    wkbus.v_pn_rfi_detail vr,
    wkbus.v_pn_am_detail vm,
    wkbus.v_pn_l_detail vl,
    wkbus.v_pn_w_detail vw,
    wkbus.v_pn_li_detail vi,
    wkbus.v_pn_tw_detail vt,
    wkbus.v_pn_at_detail va,
    wkbus.v_pn_ob_detail vb
    where vs.act_site_code = decode(upper(: p_servicecenter), 'ALL', vs.act_site_code, : p_servicecenter) and
    ((to_char(vs.load_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) or
    (to_char(vs.match_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) ) and
    vs.pn = vc.pn and
    vs.pn = vf.pn(+) and
    vf.pn = vr.pn(+) and vf.litho_code = vr.litho_code(+) and vf.doc_id = vr.doc_id(+) and
    vf.pn = vm.pn(+) and vf.litho_code = vm.litho_code(+) and vf.doc_id = vm.doc_id(+) and
    vf.pn = vl.pn(+) and vf.litho_code = vl.litho_code(+) and vf.doc_id = vl.doc_id(+) and
    vf.pn = vw.pn(+) and vf.litho_code = vw.litho_code(+) and vf.doc_id = vw.doc_id(+) and
    vf.pn = vi.pn(+) and vf.litho_code = vi.litho_code(+) and vf.doc_id = vi.doc_id(+) and
    vf.pn = vt.pn(+) and vf.litho_code = vt.litho_code(+) and vf.doc_id = vt.doc_id(+) and
    vf.pn = va.pn(+) and vf.litho_code = va.litho_code(+) and vf.doc_id = va.doc_id(+) and
    vf.pn = vb.pn(+) and vf.litho_code = vb.litho_code(+) and vf.doc_id = vb.doc_id(+)
    order by vs.test_date
    Last edited by Lily_Liu_2004; 08-02-2004 at 04:33 PM.

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134


    This is why I like Lecco


    Is a valid charachter?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Applying the to_char functions to the dates in the predicate is unnecessary, and ought to be coded as ...
    Code:
    ...
    vs.load_date between :p_begindate and :p_enddate or
    ...
    ... if the dates contain no time portion. If they do, then use ...
    Code:
    ...
    (vs.load_date >= :p_begindate and
    vs.load_date <  :p_enddate+1) or
    ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    SQL Navigator isn't so bad either.
    Are you sure that you aren't getting a cartesian product?
    What does the explain plan look like?

    Code:
    SELECT   vs.act_site_code, vs.site_name, vs.pn, vs.test_date, 
             vs.load_date, vs.match_date, vs.billing_code, vs.hours24_flag,
             vc.code, vc.NAME, 
             DECODE( vc.client_type, 'E', 'Education', 'B', 'Business' ) client_type, 
             vf.litho_code, vf.lname, vf.ssn_id, vr.test_type rfi, vm.test_type am,
             vl.test_type l, vw.test_type w, vi.test_type li, vt.test_type tw, 
             va.test_type AT, vb.test_type ob
        FROM wkbus.v_pn_site vs,
             wkbus.v_pn_client vc,
             wkbus.v_pn_folder_detail vf,
             wkbus.v_pn_rfi_detail vr,
             wkbus.v_pn_am_detail vm,
             wkbus.v_pn_l_detail vl,
             wkbus.v_pn_w_detail vw,
             wkbus.v_pn_li_detail vi,
             wkbus.v_pn_tw_detail vt,
             wkbus.v_pn_at_detail va,
             wkbus.v_pn_ob_detail vb
       WHERE vs.act_site_code = 
             DECODE( UPPER( :p_servicecenter ),'ALL', vs.act_site_code,:p_servicecenter ) AND
             ((TO_CHAR( vs.load_date, 'yymmdd' )  
                  BETWEEN TO_CHAR( :p_begindate, 'yymmdd' ) AND 
                          TO_CHAR( :p_enddate, 'yymmdd'  ))  OR
              (TO_CHAR( vs.match_date, 'yymmdd' ) 
                  BETWEEN TO_CHAR( :p_begindate, 'yymmdd' ) AND 
                          TO_CHAR( :p_enddate, 'yymmdd' )))                                                                                                AND
             vs.pn = vc.pn                                  AND
             vs.pn = vf.pn(+)                               AND
             vf.pn = vr.pn(+)                               AND
             vf.litho_code = vr.litho_code(+)               AND
             vf.doc_id = vr.doc_id(+)                       AND
             vf.pn = vm.pn(+)                               AND
             vf.litho_code = vm.litho_code(+)               AND
             vf.doc_id = vm.doc_id(+)                       AND
             vf.pn = vl.pn(+)                               AND
             vf.litho_code = vl.litho_code(+)               AND
             vf.doc_id = vl.doc_id(+)                       AND
             vf.pn = vw.pn(+)                               AND
             vf.litho_code = vw.litho_code(+)               AND
             vf.doc_id = vw.doc_id(+)                       AND
             vf.pn = vi.pn(+)                               AND
             vf.litho_code = vi.litho_code(+)               AND
             vf.doc_id = vi.doc_id(+)                       AND
             vf.pn = vt.pn(+)                               AND
             vf.litho_code = vt.litho_code(+)               AND
             vf.doc_id = vt.doc_id(+)                       AND
             vf.pn = va.pn(+)                               AND
             vf.litho_code = va.litho_code(+)               AND
             vf.doc_id = va.doc_id(+)                       AND
             vf.pn = vb.pn(+)                               AND
             vf.litho_code = vb.litho_code(+)               AND
             vf.doc_id = vb.doc_id(+)
    ORDER BY vs.test_date
    Last edited by marist89; 08-03-2004 at 09:02 AM.

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...

    Any way good luck on outer joins
    Raghu

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I wonder also whether ...
    Code:
    vs.act_site_code = DECODE( UPPER( :p_servicecenter ),'ALL', vs.act_site_code,:p_servicecenter )
    ... might not be better as ...
    Code:
    ((   vs.act_site_code = :p_servicecenter And Upper(:p_servicecenter) != 'ALL') Or
    Upper(:p_servicecenter) = 'ALL')
    It would be worth benchmarking, for values of :p_servicecenter equal to and not equal to ALL.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by raghud
    This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...

    Any way good luck on outer joins
    I never said it was a catesian product. With so many tables being joined and no information about the key structure. It could be that he doesn't have a cartesian product, and that a few simple tweaks will make all the difference. But without an explain plan, and with the query having so many outer joins, how can you say what it will do?

  8. #8
    Join Date
    Aug 2001
    Posts
    267
    Yaa.. It is true , he need to post query plan. Since the original person did not showned up again. We hope he solved the problem himself.
    Raghu

  9. #9
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Sorry for the late response. Too busy to get a chance.

    The attached is the execution plan.
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    Plan                                                         Rows
    ------------------------------------------------------------ -----
    SELECT STATEMENT                                             1
     NESTED LOOPS OUTER                                          1
      NESTED LOOPS OUTER                                         1
       NESTED LOOPS OUTER                                        1
        NESTED LOOPS OUTER                                       1
         NESTED LOOPS OUTER                                      1
          NESTED LOOPS OUTER                                     1
           NESTED LOOPS OUTER                                    1
            NESTED LOOPS OUTER                                   1
             NESTED LOOPS OUTER                                  1
              NESTED LOOPS                                       1
               VIEW  V_PN_SITE                                   17
                SORT UNIQUE                                      17
                 FILTER
                  NESTED LOOPS OUTER
                   NESTED LOOPS                                  1
                    NESTED LOOPS                                 1
                     TABLE ACCESS BY INDEX R PN_INFORMA          4
                      INDEX RANGE SCAN PN_INFORMA                4
                     TABLE ACCESS BY INDEX R SITE_HEADE          1
                      INDEX UNIQUE SCA SITE_HEADE                1
                    TABLE ACCESS BY INDEX R EXPRESS_SI           432
                     INDEX UNIQUE SCA EXPRESS_SI                 432
                   TABLE ACCESS BY INDEX R ESSAY_TEST            38098
                    INDEX RANGE SCAN ESSAY_TEST                  38098
               TABLE ACCESS BY INDEX R LEVEL2_HEA                1607
                INDEX RANGE SCAN LEVEL2_HEA                      1607
              TABLE ACCESS BY INDEX R EXAMINEE                   27718
               INDEX RANGE SCAN EXAMINEE_L                       27718
             VIEW  V_PN_L_DET                                    3067
              INDEX FAST FULL  IDX_ESSAY_                        3067
            VIEW  V_PN_W_DET                                     3466
             INDEX FAST FULL  IDX_ESSAY_                         3466
           VIEW  V_PN_RFI_D                                      9676
            NESTED LOOPS                                         9676
             TABLE ACCESS FULL FORMS                             79
             INDEX RANGE SCAN IDX_MC_TES                         72019
          VIEW  V_PN_AM_DE                                       9676
           NESTED LOOPS                                          9676
            TABLE ACCESS FULL FORMS                              79
            INDEX RANGE SCAN IDX_MC_TES                          72019
         VIEW  V_PN_LI_DE                                        9676
          NESTED LOOPS                                           9676
           TABLE ACCESS FULL FORMS                               79
           INDEX RANGE SCAN IDX_MC_TES                           72019
        VIEW  V_PN_TW_DE                                         9676
         NESTED LOOPS                                            9676
          TABLE ACCESS FULL FORMS                                79
          INDEX RANGE SCAN IDX_MC_TES                            72019
       VIEW  V_PN_AT_DE                                          9676
        NESTED LOOPS                                             9676
         TABLE ACCESS FULL FORMS                                 79
         INDEX RANGE SCAN IDX_MC_TES                             72019
      VIEW  V_PN_OB_DE                                           9676
       NESTED LOOPS                                              9676
        TABLE ACCESS FULL FORMS                                  79
        INDEX RANGE SCAN IDX_MC_TES                              72019
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width