sql query tuning

[hrishy]

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

[abhaysk]

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

[adewri]

Originally posted by hrishy
why is the plan getting changed ?
regards
Hrishy

The plan is changing because in the first querry you have used

AND (tptfbdr_a.codptf = 'CROSB')

and in the next

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

AND (tptfbdr_a.codptf = 'WARTS')

and see whats the plan.

[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

[abhaysk]

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 :-)

[abhaysk]

Originally posted by adewri
The plan is changing because in the first querry you have used

AND (tptfbdr_a.codptf = 'CROSB')

and in the next

AND (tptfbdr_b.codptf = 'WARTS')

Not Necessarly it should change.

[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?

[abhaysk]

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.

[hrishy]

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

[adewri]

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.