sql query tuning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: sql query tuning

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    sql query tuning

    Hi All

    I have interesting query

    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_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and
    16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and
    17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND
    18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND
    19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND
    20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND
    21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND
    22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT )
    23 AND
    24 ( TPTFBDR_A.CODPTF = 'CROSB') )
    25 ;

    C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE
    - ---------- -------- ------------ - -------- ---------
    A 16553901 WARTS 11199599 A CROSB 10-APR-03
    A 19059202 WARTS 11199599 A CROSB 11-APR-03
    A 19059202 WARTS 11898559 A CROSB 10-APR-03

    Elapsed: 00:00:00.13

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 NESTED LOOPS
    2 1 NESTED LOOPS
    3 2 NESTED LOOPS
    4 3 NESTED LOOPS
    5 4 NESTED LOOPS
    6 5 NESTED LOOPS
    7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE)
    9 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    10 9 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNI
    QUE)

    11 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
    12 11 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE)
    13 4 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE)
    14 3 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
    15 14 INDEX (RANGE SCAN) OF 'IPARGESTIX2' (NON-UNIQUE)
    16 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    17 16 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE)
    18 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    19 18 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE)




    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    508 consistent gets
    0 physical reads
    0 redo size
    1070 bytes sent via SQL*Net to client
    554 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed

    when i change the query to

    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_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and
    16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and
    17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND
    18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND
    19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND
    20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND
    21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND
    22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT )
    23 AND
    24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ;


    C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE
    - ---------- -------- ------------ - -------- ---------
    A 16553901 WARTS 11199599 A CROSB 10-APR-03
    A 16868993 WARTS 11199599 A ACTEU 10-APR-03
    A 17778227 WARTS 11898558 A CBWAR 10-APR-03
    A 19059202 WARTS 11199599 A CROSB 11-APR-03
    A 19059202 WARTS 11898559 A CROSB 10-APR-03

    Elapsed: 00:01:07.80

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 NESTED LOOPS
    2 1 NESTED LOOPS
    3 2 NESTED LOOPS
    4 3 NESTED LOOPS
    5 4 NESTED LOOPS
    6 5 NESTED LOOPS
    7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE)
    9 6 TABLE ACCESS (FULL) OF 'TPARGESTIX'
    10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    11 10 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNIQU
    E)

    12 4 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
    13 12 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE)
    14 3 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE)
    15 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    16 15 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE)
    17 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    18 17 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE)




    Statistics
    ----------------------------------------------------------
    0 recursive calls
    5 db block gets
    11400369 consistent gets
    0 physical reads
    0 redo size
    1292 bytes sent via SQL*Net to client
    554 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    5 rows processed

    How can i tune this query ?

    why is the plan getting changed ?

    regards
    Hrishy

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Hrishy ;

    Try this....i am not sure....coz never touched the performance manual yet...

    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') ) ;
    25 ;

    Abhay
    Last edited by abhaysk; 04-11-2003 at 12:43 AM.
    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"

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: sql query tuning

    Originally posted by hrishy
    why is the plan getting changed ?
    regards
    Hrishy
    The plan is changing because in the first querry you have used
    Code:
    AND (tptfbdr_a.codptf = 'CROSB')
    and in the next
    Code:
    AND (tptfbdr_b.codptf = 'WARTS')
    And because of this the access order of the table has changes as the table tptfbdr_b is some where at the bottom and tptfbdr_a is at the top in the from clause.

    Try using
    Code:
    AND (tptfbdr_a.codptf = 'WARTS')
    and see whats the plan.
    Last edited by adewri; 04-10-2003 at 01:05 PM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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

    Amar and Abhay..well with two geniuses at work..that query tuning problem seemed very very easy..if you are in bangalore india..then i would like to host a treat in honour of you guys :-)

    regards
    Hrishy

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

    Amar and Abhay..well with two geniuses at work..that query tuning problem seemed very very easy..if you are in bangalore india..then i would like to host a treat in honour of you guys :-)

    regards
    Hrishy
    am waiting for ur treat....coz am in bangalore india :-)
    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. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: sql query tuning

    Originally posted by adewri
    The plan is changing because in the first querry you have used
    Code:
    AND (tptfbdr_a.codptf = 'CROSB')
    and in the next
    Code:
    AND (tptfbdr_b.codptf = 'WARTS')
    Not Necessarly it should change.
    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"

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Can someone please verify the 'best' order for tables in the FROM clause?

    Is it tables that would return LEAST number of rows (given the inidividual query) that should be last.
    Or is it smallest tables (whatever the query) that should be last?

    Also - In the WHERE clause should the most restrictive statement be last?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by JMac
    Can someone please verify the 'best' order for tables in the FROM clause?

    Is it tables that would return LEAST number of rows (given the inidividual query) that should be last.
    Or is it smallest tables (whatever the query) that should be last?

    Also - In the WHERE clause should the most restrictive statement be last?
    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.
    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"

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Solved thsi by creating a index on ttralnktix.NUMTRAFRTLNK

    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 NESTED LOOPS
    2 1 NESTED LOOPS
    3 2 NESTED LOOPS
    4 3 NESTED LOOPS
    5 4 NESTED LOOPS
    6 5 NESTED LOOPS
    7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE)
    9 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    10 9 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNI
    QUE)

    11 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
    12 11 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE)
    13 4 TABLE ACCESS (BY INDEX ROWID) OF 'TTRALNKTIX'
    14 13 INDEX (RANGE SCAN) OF 'I_NUMTRAFRTLNK' (NON-UNIQ
    UE)

    15 3 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
    16 15 INDEX (RANGE SCAN) OF 'IPARGESTIX2' (NON-UNIQUE)
    17 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
    18 17 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE)
    19 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
    20 19 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE)




    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    1050 consistent gets
    0 physical reads
    136 redo size
    1398 bytes sent via SQL*Net to client
    554 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    6 rows processed

    so abhay you still win the treat i guess :-)

    regards
    Hrishy
    Last edited by hrishy; 04-11-2003 at 07:21 AM.

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: Re: Re: sql query tuning

    Originally posted by abhaysk
    Not Necessarly it should change.
    In most of the cases the plan won't change, but if you are using ordered hint it would.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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