DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: tuning sql

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    I have the following sql to be tuned. The buffer gets should not be greater than 2000. I put 'ORDERED' hint and index hints. The buffer_gets is 7975. It was 3337346 without the hints.

    How can I reduce the buffer gets to 2000?

    Please help.





    SELECT /*+ORDERED index(LE LEGAL_ENTITY_NDX_1) index(PR PK_PARTY_ROLE) */

    P.PTY_ID,LE.PTY_Id,PR.PTY_ID,P.PTY_STA_TYP,LE.PTY_STA_TYP,PR.PTY_STA_TYP,

    PR.PTY_ROLE_CATG_TYP,PR.PTY_ROLE_TYP,

    P.PTY_TYP,P.PTY_NAM,P.PTY_SHRT_NAM,P.PTY_DES,P.ADR_LNE_1,P.ADR_LNE_2,P.ADR_LNE_3,P.ADR_LNE_4

    ,P.ISO_CTRY_COD,LE.SIC_COD,P.VERSION_NO,LE.CUST_REF_ID,LE.CUST_REF_TYP,LE.LGL_NAM
    FROM b19991112qc31.LEGAL_ENTITY LE,b19991112qc31.PARTY_ROLE PR,b19991112qc31.PARTY P
    WHERE ( LE.PTY_ID = P.PTY_ID AND
    LE.PTY_STA_TYP = P.PTY_STA_TYP
    AND PR.PTY_ID = P.PTY_ID
    AND PR.PTY_STA_TYP = P.PTY_STA_TYP
    AND PR.PTY_ROLE_CATG_TYP = 'CUST'
    AND PR.PTY_ROLE_TYP = 'LGLBU'
    AND P.PTY_TYP = 'LGLU'
    AND LE.CUST_REF_TYP = 'LIABNO' )

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sorry, but as much as I love tuning SQL, I would need a whole lot more information to be of any use here. It looks like you've done a great job of tuning so far. You have reduced the buffer get count to 1/418th of what it was - nicely done. Why torture yourself over another 1/4? Where did this target number come from anyway?

    - Chris

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    Well, is it possible to reduce it more? The target number is set by the Lead DBA.

    Please help.

    Thanks.



  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    My disclaimer from my first reply still holds. 'Gross' tuning of SQL is not so hard. In order to generally tune a statement, one needs to know the
    - statement
    - plan
    - table sizes
    - abailable indexes
    ... and that is just to generally tune the statement, which you have already done.

    To *fine* tune the statement, I need the database. There's no other way around it.

    So, unfortunately, as I said the first time, there's precious little I can do for you.

    Again, however, I re-state that you have done a fine job. Your lead dba needs to have his head checked. :) Setting arbitrary goals is kinda pointless.

    However, if he/she is trying to 'teach' you how to optimize, then that is a different story. I do the same thing with the people I teach. I figure out the best path and then give them a goal to shoot for. If this is the case, good luck.

    If your lead dba has *not* already determined how to optimize this statement, the he/she may be pulling numbers out of thin air and this may *not* actually be an attainable goal.

    In any case, I doubt that I can help you. However, I am willing to give it a shot if you want to take the time to provide me with *every* piece of info you have - indexes, table sizes, distribution, histograms, plans - *anything* that could help. Simply providing the statement is wholly insufficient.

    - Chris

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, this has been tugging at me brain, so here are a few thoughts...

    First of all, always join everything that can be joined. Here is your statement with some minor changes:

    SELECT /*+ORDERED index(LE LEGAL_ENTITY_NDX_1) index(PR PK_PARTY_ROLE) */
    ---P.PTY_ID,LE.PTY_Id,PR.PTY_ID,P.PTY_STA_TYP,LE.PTY_STA_TYP,PR.PTY_STA_TYP,
    ---PR.PTY_ROLE_CATG_TYP,PR.PTY_ROLE_TYP,
    ---P.PTY_TYP,P.PTY_NAM,P.PTY_SHRT_NAM,P.PTY_DES,P.ADR_LNE_1,P.ADR_LNE_2,P.ADR_LNE_3,P.ADR_LNE_4,
    ---P.ISO_CTRY_COD,LE.SIC_COD,P.VERSION_NO,LE.CUST_REF_ID,LE.CUST_REF_TYP,LE.LGL_NAM
    FROM
    ---b19991112qc31.LEGAL_ENTITY LE,
    ---b19991112qc31.PARTY_ROLE---PR,
    ---b19991112qc31.PARTY---------P
    WHERE
    ---LE.CUST_REF_TYP------=---'LIABNO'---------AND
    ---PR.PTY_ID------------=---LE.PTY_ID------AND----- Added
    ---PR.PTY_STA_TYP---------=---LE.PTY_STA_TYP---AND ----- Added
    ---PR.PTY_ROLE_CATG_TYP---=---'CUST'---------AND
    ---PR.PTY_ROLE_TYP------=---'LGLBU'---------AND
    ---P.PTY_ID---------------=---LE.PTY_ID------AND
    ---P.PTY_STA_TYP---------=---LE.PTY_STA_TYP---AND---
    ---P.PTY_ID---------------=---PR.PTY_ID------AND
    ---P.PTY_STA_TYP---------=---PR.PTY_STA_TYP---AND
    ---P.PTY_TYP------------=---'LGLU'

    Okay, your best options are most likely:

    1- LE accessed via index on CUST_REF_TYP
    2- Nested Loop join to PR with index on PTY_ID, PTY_STA_TYP, PTY_ROLE_CATG_TYP, PTY_ROLE_TYP
    3- Nested Loop join to P with index on PTY_ID, PTY_STA_TYP, PTY_TYP

    This assumes
    ---- Step 1 is the most restrictive step and is very restrictive
    ---- The predicate restrictions on PR do not cut the result set down as much as joing to LE would
    ------- Same with P
    ---- All the specified indexes exist

    Another option is
    1- LE accessed via index on CUST_REF_TYP
    2- PR accessed via index on PTY_ROLE_CATG_TYP, PTY_ROLE_TYP
    3- These results are hash-joined together via common fields: PTY_ID, PTY_STA_TYP
    4- P accessed via index on PTY_TYP
    5- These results are has-joined together via common fields: PTY_ID, PTY_STA_TYP

    This assumes
    ---- Step 1 and 2 are simliarly-restrictive but not highly restrictive
    ---- The indexes mentioned in the first solution do not exist
    ---- The indexes mentioned in the second solution *do* exist :)

    Any combination of NLs and Hash joins are possible. Also, the order can change.

    Personally, I would look into the hash join option.
    It is worrisome that you had to hint the statement to use the indexes.
    Why didn't the optimizer pick the indexes?
    Is your data severely skewed?
    Do you have histograms?
    Should you have histograms?
    Are all your stats current?
    Can you use better indexes?

    Anyway, things to think about.

    Hope this helps,

    - Chris

  6. #6
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    I really appreciate the effort taken by you. Here is the info:


    The following are the indexes on table Legal_entity :

    1. LEGAL_ENTITY_NDX_1 :
    CUST_REF_ID
    CUST_REF_TYP

    2. LEGAL_ENT_LEGAL_ENT_FK :
    INC_PTY_ID


    3. PK_LEGAL_ENTITY (unique):
    PTY_ID
    PTY_STA_TYP

    4. SIC_TBL_LEGAL_ENTITY_FK
    SIC_COD

    The foll. are the indexes on table Party:
    1. COUNTRY_PARTY_FK
    ISO_CTRY_COD

    2. CURRENCY_PARTY_FK
    ISO_CCY_COD

    3. PARTY_NDX_3
    PTY_NAM
    PTY_STA_TYP
    PTY_TYP

    4. PARTY_NDX_4
    PTY_ID
    PTY_STA_TYP
    PTY_TYP

    5. PARTY_NDX_5
    REF_KEY

    6. PARTY_NDX_6
    PTY_TYP

    7. PK_PARTY(unique)
    PTY_ID
    PTY_STA_TYP

    8. PTY_JUNK
    PTY_SHRT_NAM

    The foll. are the indexes on Party_role table:
    1. FK_PARTY_PARTY_ROLE
    PTY_ID
    PTY_STA_TYP

    2. PARTY_ROLE_NDX_1
    WL_CUST_NO

    3. PARTY_ROLE_NDX_3
    PTY_ROLE_CATG_TYP

    4. PK_PARTY_ROLE (unique)
    PTY_ID
    PTY_ROLE_TYP
    PTY_STA_TYP

    If you need any more info, please let me know. Also, the sql was given to me wihtout the hint and the ORDERED clause. So, if you want to start from scratch, ie. start tuning the sql from the beginning, it's okay.

    If i need to create nay indexes, let me know. The only things is that I will not be able to test it in the same environment if a new index is created as the new index will be created in the DEvelopment environment and this is QC envt.



    Thanks a lot.





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