sql query tuning

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

[chrisrlong]

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

[hrishy]

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.

[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

[abhaysk]

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.

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.

[hrishy]

Originally posted by abhaysk
Hrishy :

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

check the bold part of the where clause.

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