Performance issue

[cdias]

Here is my query:
SELECT a, b
FROM (
SELECT tab1.a, tab1.b,
RANK() OVER (PARTITION BY tab1.d
ORDER BY tab1.a DESC NULLS LAST) rnk
FROM tab1, tab2
WHERE tab2.x = xpto
AND tab1.d = xyz
AND tab1.c = tab2.a
) WHERE rnk < 7

I have a primary key on tab1, that includes column c, d and others (the order is c,d,e,f).
Have a primary key in tab2.
The response time is about 0.6s to 1.3s. The point is that I have to make this query for 40000 times, if
it takes 1s the procedure time is about 11hours!!!

Explain plan
SELECT STATEMENT Optimizer Mode=CHOOSE 1 96
VIEW 1 39 96
WINDOW SORT PUSHED RANK 1 85 96
TABLE ACCESS BY LOCAL INDEX ROWID TAB1 3 117 2
NESTED LOOPS 1 85 94
TABLE ACCESS FULL TAB2 1 46 92
PARTITION RANGE ITERATOR KEY KEY
INDEX RANGE SCAN PK_TAB1 3 1 KEY KEY


Table tab1 has 115200000 records and 70800.

I try to run statistics, but without success in performance.

What can I do?

Thanks in advance,
Carlos Dias

[LKBrwn_DBA]

What do you mean by "I have to make this query for 40000 times,"?
Is it: I have to execute this query 40,000 times? -- If this is the case, please provide the requirements and/or reason.

PS: Do you have indexes on tab2 that include columns x, c and d?

[tamilselvan]

WHERE tab2.x = xpto
AND tab1.d = xyz
AND tab1.c = tab2.a

I would like to try with an index on tab1 (d,c) or tab2(x,a).

Why don't you post the trace output.

Tamil

[malay_biswal]

Since you'll be executing it 40K times and that sums up your problem. There is one thing missing bind variable as you are using hard coded variable. That will save the parsing time. You may need to look at your buffer cahe size and if it's sort may think of increasing it.