sql query tuning - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: sql query tuning

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    AND (tptfbdr_a.codptf = 'CROSB')
    AND (tptfbdr_b.codptf = 'WARTS')

    What are the cardinalities of these 2 columns?
    Do you analyze tables and indexes?

    Try this:
    ANALYZE TABLE xxxx estimate statistics sample 10000 rows
    for table
    for all indexes
    for all indexed columns;

    Then run the explain plan. Post the plans here.

  2. #12
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by abhaysk
    Order of table may not matter ... not sure

    Order of columns in where clause wud matter some times...

    it wud be best if most restrictive column is preceded before any columns...(though it wud go for toss if not indexed col)

    The order in which columns are indexes...if used in where clause in same order wud have beter performance ( i blv )..

    if i am wrong please bark at me...

    Abhay.
    With the CBO, the order of the entries in the FROM and WHERE clause do not matter 99% of the time. Having said that, the best way to structure a statement, IMHO, is to bring one table at a time into the WHERE clause and join it to everything that's already there. Think like the compiler and bring the tables into the WHERE clause in the same order you want the optimizer to. Then change the FROM clause to match this order. This has several benefits:
    - It is more readable
    - It gets you thinking about how the query will/should be solved
    - It makes sure that you don't miss a join. Remember that joining to everything that's valid and restricting by everything you can gives the optimizer more choices and will solve the majority of your performance problems.
    - Things are now set up for an ORDERED hint if you need it
    - Things are now set up for that 1% where the order actually does matter.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #13
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by tamilselvan
    AND (tptfbdr_a.codptf = 'CROSB')
    AND (tptfbdr_b.codptf = 'WARTS')

    What are the cardinalities of these 2 columns?
    Do you analyze tables and indexes?

    Try this:
    ANALYZE TABLE xxxx estimate statistics sample 10000 rows
    for table
    for all indexes
    for all indexed columns;

    Then run the explain plan. Post the plans here.

    Hi I cannot analyze the tables because we are using RBO..that would break our application.

  4. #14
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I was just wundering how come by creating a index on ttralnktix.NUMTRAFRTLNK

    how come the plan changed so much..increasing the response time to fraction of a second..

    regards
    Hrishy

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi

    I was just wundering how come by creating a index on ttralnktix.NUMTRAFRTLNK

    how come the plan changed so much..increasing the response time to fraction of a second..

    regards
    Hrishy
    Hrishy :

    Its jus my thinking.. i may be wrong..

    check the bold part of the where clause.

    Code:
    SQL> SELECT 
    ttralnktix.CODAPP_OLD, 
    2 ttralnktix.NUMTRAFRT, 
    3 TPTFBDR_B.CODPTF codptfold , 
    4 ttralnktix.NUMTRAFRTLNK, 
    5 ttralnktix.CODAPP_NEW, 
    6 TPTFBDR_A.CODPTF codptfnew, 
    7 ttralnktix.CURDATE 
    8 FROM ttralnktix, 
    9 TPTFBDR TPTFBDR_A, 
    10 TPARCPTTIX TPARCPTTIX_A, 
    11 TPARGESTIX TPARGESTIX_A, 
    12 TPTFBDR TPTFBDR_B, 
    13 TPARCPTTIX TPARCPTTIX_B, 
    14 TPARGESTIX TPARGESTIX_B 
    15 WHERE ( ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and 
    16 ( TPARCPTTIX_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and 
    17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND 
    18 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND 
    19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND 
    20 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND 
    21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND 
    22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT ) 
    23 AND 
    24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ;
    Has this column ttralnktix.NUMTRAFRT index?

    If yes, then ur first query went smoothly coz u restricted the table TPTFBDR with _A alias and the access order seems good.

    If no, i am compeltely wrong.

    Now column ttralnktix.NUMTRAFRTLNK was not indexed and with most restrictive statement on table TPTFBDR was with _B alias joined to this indirectly..

    so its joining table also went for toss..for FTS.


    Mind this is my feeling..may be wrong..just trying to analyze query :-)

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by abhaysk
    Hrishy :

    Its jus my thinking.. i may be wrong..

    check the bold part of the where clause.

    Code:
    SQL> SELECT 
    ttralnktix.CODAPP_OLD, 
    2 ttralnktix.NUMTRAFRT, 
    3 TPTFBDR_B.CODPTF codptfold , 
    4 ttralnktix.NUMTRAFRTLNK, 
    5 ttralnktix.CODAPP_NEW, 
    6 TPTFBDR_A.CODPTF codptfnew, 
    7 ttralnktix.CURDATE 
    8 FROM ttralnktix, 
    9 TPTFBDR TPTFBDR_A, 
    10 TPARCPTTIX TPARCPTTIX_A, 
    11 TPARGESTIX TPARGESTIX_A, 
    12 TPTFBDR TPTFBDR_B, 
    13 TPARCPTTIX TPARCPTTIX_B, 
    14 TPARGESTIX TPARGESTIX_B 
    15 WHERE ( ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and 
    16 ( TPARCPTTIX_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and 
    17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND 
    18 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND 
    19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND 
    20 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND 
    21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND 
    22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT ) 
    23 AND 
    24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ;
    Has this column ttralnktix.NUMTRAFRT index?

    If yes, then ur first query went smoothly coz u restricted the table TPTFBDR with _A alias and the access order seems good.

    If no, i am compeltely wrong.

    Now column ttralnktix.NUMTRAFRTLNK was not indexed and with most restrictive statement on table TPTFBDR was with _B alias joined to this indirectly..

    so its joining table also went for toss..for FTS.


    Mind this is my feeling..may be wrong..just trying to analyze query :-)

    Abhay.
    Hi abhay

    Thanks for the response..all the columns of the table ttralnktix form a composite primary key and henece they were indexed.when i created one more index on ttralnktix.NUMTRAFRTLNK the query plan changed and the response was just a fraction of a second...any ideas why the plan changed so much

    regards
    Hrishy

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