Query Performance

[malay_biswal]

I've one query which is executing in 2 seconds when i'm using hard coded value. But when i'm using bind variables the same query is taking 180 seconds. Could not understood what could cause the problem.

Explain plan of both of the queries same.
Thought of histograms and executed this query.
select column_name from dba_tab_col_statistics where table_name = '&Table_name' and owner='&Owner' and NUM_BUCKETS>1 ;

But this did not return any record. So i think none of the tables has histogram statistics and histograms is not the issue here.

Any suggestions.

[davey23uk]

post the explain plans, prove they are the same

[slimdave]

Did you supply uppercase names for the table and owner?

[malay_biswal]

I found the proble. It was with the cardinality. Here is the sample query.
select T23.CONFLICT_ID,
T23.LAST_UPD,
T23.CREATED,
T23.LAST_UPD_BY,
T23.CREATED_BY,
T23.MODIFICATION_NUM,
T23.ROW_ID
from s_opty t23,S_OPTY_POSTN T1
where T1.POSITION_ID = :1 AND
T23.ROW_ID = T1.OPTY_ID
and T23.TEMPLATE_FLG = :2

TEMPLATE_FLG column has cardinality 1 and for that it's taking time.

Now my question if i'll gather stat with histograms will that solve the problem as we can not modify the query so the bind varibale will be there for sure.

[Scorby]

I think the answer is no, because Oracle decides on the execution plan before the bind variable assignment takes place. If you can't modify the query, you might be able to force the execution plan using stored outlines.

http://download-uk.oracle.com/docs/c...3/outlines.htm

[tamilselvan]

I found the proble. It was with the cardinality. Here is the sample query.
select T23.CONFLICT_ID,
T23.LAST_UPD,
T23.CREATED,
T23.LAST_UPD_BY,
T23.CREATED_BY,
T23.MODIFICATION_NUM,
T23.ROW_ID
from s_opty t23,S_OPTY_POSTN T1
where T1.POSITION_ID = :1 AND
T23.ROW_ID = T1.OPTY_ID
and T23.TEMPLATE_FLG = :2

TEMPLATE_FLG column has cardinality 1 and for that it's taking time.

Now my question if i'll gather stat with histograms will that solve the problem as we can not modify the query so the bind varibale will be there for sure.

This query comes from Siebel appln. Do you use CBO or RBO?
What happened to the table T2 to T22?

Tamil

[malay_biswal]

Obviously we use CBO. For simplicity i posted small version of the query. Do n't worry about the T2 and T22.

[tamilselvan]

Are both execution plans same?

Tamil

[malay_biswal]

Problem here was with the explain plan, While executing it was picking different explain plan. In the tkprof o/p also it was not showing correctly. When i checked v$sql_plan , it was showing it was using index on template_flg column whose cardinality was 1. Drop that index, now all set. This issue was resolved long back, but just wanted to keep all of u updated.

[PAVB]

so... original posting about "Explain plan of both of the queries same" was wrong and, you never bother to post explain plans as Dave asked for. bad, bad, bad.