Results 1 to 4 of 4

Thread: sql tuning

  1. #1
    Join Date
    Mar 2002
    Posts
    1

    Unanswered: sql tuning

    How can I improve performance for below query and explian plan is also given.

    SELECT BGRP.bndl_iosc_cd
    FROM REF_I.REF_SBTP SBTP,REF_I.REF_IDES IDES,
    REF_I.REF_INCL INCL,REF_I.REF_BGRP BGRP
    WHERE BGRP.company_code = 'GTCA'
    AND BGRP.res_state_cd = 'CA'
    AND (BGRP.pkg_iosc_cd LIKE ' %' OR
    BGRP.pkg_iosc_cd IS NULL)
    AND (BGRP.grpg_cat_id = ' ' OR
    BGRP.grpg_cat_id IS NULL)
    AND BGRP.tab_ent_exp_dt > to_date('28-FEB-02','DD-MON-YY')
    AND INCL.company_code = 'GTCA'
    AND INCL.res_state_cd = 'CA'
    AND INCL.bndl_iosc_cd = BGRP.bndl_iosc_cd
    AND INCL.pkg_iosc_cd LIKE ' %'
    AND INCL.comp_iosc_cd LIKE ' %'
    AND (INCL.grpg_cat_id = ' ' OR
    INCL.grpg_cat_id IS NULL)
    AND SBTP.sbtp_cmpy_cd = 'GTCA'
    AND SBTP.sbtp_state = 'CA'
    AND SBTP.sbtp_svc_type_cd IN ('R','RI','R N','R C','R R','R V','R G','R E','RG','B C','B1C')
    AND NOT EXISTS (SELECT cocp_iosc_cd FROM REF_I.ref_cocp COCP
    where COCP.cocp_area_ofc_cd = '411'
    AND SBTP.sbtp_iosc_cd = COCP.cocp_iosc_cd)
    AND SBTP.sbtp_iosc_cd = BGRP.bndl_iosc_cd
    AND SBTP.sbtp_iosc_cd = IDES.ides_iosc_Cd

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.21 0.19 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 5 26.80 27.31 0 1104420 0 4
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 7 27.01 27.50 0 1104420 0 4

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 20 (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    4 NESTED LOOPS
    9633 NESTED LOOPS
    77729 MERGE JOIN CARTESIAN
    9 TABLE ACCESS BY INDEX ROWID REF_INCL
    9 INDEX RANGE SCAN (object id 35204)
    77736 SORT JOIN
    9716 INDEX FULL SCAN (object id 35202)
    87360 TABLE ACCESS BY INDEX ROWID REF_SBTP
    1052176 INDEX RANGE SCAN (object id 35269)
    4 TABLE ACCESS BY INDEX ROWID REF_BGRP
    9645 INDEX RANGE SCAN (object id 35152)
    34344 INDEX RANGE SCAN (object id 35164)

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    With all those LIKE clauses, there's probably not a lot you can do.

    If your Oracle version supports it, you could add a function based index on a substring of the columns that start with a leading space.

    You could also try to eliminate the NOT EXISTS clause by using either a correlated query or an outer join.

  3. #3
    Join Date
    Jun 2002
    Posts
    3

    Re: sql tuning

    Use LECCO DB Expert product from LECCOTECH. You can get an evaluation copy from: http://www.leccotech.com

    This tools has a SQL optimizer that uses AI to transform a SQL statement in every possible SQL alternative, with unique execution plan and guarantees semantically equivalence.

    Just copy and paste your SQL into LECCO's SQL Optimizer module.
    One click gives you every possible transformation like different table join orders, IN/Exists, and any other SQL transformation rules applicable to your SQL statement. This is the only product that offeres complete and extensive SQL transformation. One more click to bechmark the SQL alternatives and you'll find the solution for your SQL statement.
    This product can find the fastest possible SQL alternative to your SQL statement.


    Originally posted by nadeem0374
    How can I improve performance for below query and explian plan is also given.

    SELECT BGRP.bndl_iosc_cd
    FROM REF_I.REF_SBTP SBTP,REF_I.REF_IDES IDES,
    REF_I.REF_INCL INCL,REF_I.REF_BGRP BGRP
    WHERE BGRP.company_code = 'GTCA'
    AND BGRP.res_state_cd = 'CA'
    AND (BGRP.pkg_iosc_cd LIKE ' %' OR
    BGRP.pkg_iosc_cd IS NULL)
    AND (BGRP.grpg_cat_id = ' ' OR
    BGRP.grpg_cat_id IS NULL)
    AND BGRP.tab_ent_exp_dt > to_date('28-FEB-02','DD-MON-YY')
    AND INCL.company_code = 'GTCA'
    AND INCL.res_state_cd = 'CA'
    AND INCL.bndl_iosc_cd = BGRP.bndl_iosc_cd
    AND INCL.pkg_iosc_cd LIKE ' %'
    AND INCL.comp_iosc_cd LIKE ' %'
    AND (INCL.grpg_cat_id = ' ' OR
    INCL.grpg_cat_id IS NULL)
    AND SBTP.sbtp_cmpy_cd = 'GTCA'
    AND SBTP.sbtp_state = 'CA'
    AND SBTP.sbtp_svc_type_cd IN ('R','RI','R N','R C','R R','R V','R G','R E','RG','B C','B1C')
    AND NOT EXISTS (SELECT cocp_iosc_cd FROM REF_I.ref_cocp COCP
    where COCP.cocp_area_ofc_cd = '411'
    AND SBTP.sbtp_iosc_cd = COCP.cocp_iosc_cd)
    AND SBTP.sbtp_iosc_cd = BGRP.bndl_iosc_cd
    AND SBTP.sbtp_iosc_cd = IDES.ides_iosc_Cd

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.21 0.19 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 5 26.80 27.31 0 1104420 0 4
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 7 27.01 27.50 0 1104420 0 4

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 20 (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    4 NESTED LOOPS
    9633 NESTED LOOPS
    77729 MERGE JOIN CARTESIAN
    9 TABLE ACCESS BY INDEX ROWID REF_INCL
    9 INDEX RANGE SCAN (object id 35204)
    77736 SORT JOIN
    9716 INDEX FULL SCAN (object id 35202)
    87360 TABLE ACCESS BY INDEX ROWID REF_SBTP
    1052176 INDEX RANGE SCAN (object id 35269)
    4 TABLE ACCESS BY INDEX ROWID REF_BGRP
    9645 INDEX RANGE SCAN (object id 35152)
    34344 INDEX RANGE SCAN (object id 35164)

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This query is very strange as it has lots of "LIKE '%'" which will match anything except null (should be replaced with IS NOT NULL).

    Also "(BGRP.pkg_iosc_cd LIKE ' %' OR BGRP.pkg_iosc_cd IS NULL)" will match all rows so why have it?

    Also both tests are identical here so replace with a single test "(INCL.grpg_cat_id = ' ' OR INCL.grpg_cat_id IS NULL) "

    Cleaning up the query may help Oracle to put together an improved query plan. Also make sure the tables and indexes have been analysed recently.

    Alan

Posting Permissions

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