Problem With a Query

[tvrdileep]

Hai All.
I have a Query that is showing about 456 consistent gets, using SQLTRACE, when I'm executing as a single user. But, when we are doing a Load Test, we are seeing lot of Logical Reads, about 95,233.0 Buffer Gets per single execution from Statspack. So, I would like to have your advices. The following are the details of the Query. Please help me.

[pando]

do they share same execution plan?

[tvrdileep]

Ofourse they should be using the same execution plan as it the same query which is being used. Ofcourse we don't get the execution plan in the Statspack Report. But, how do we know if it's the same execution plan being used or not as the Query is being fired from our Web Application. Is there any way by whcih we could improve the Query.

Ofcourse inspite of that I can always see atleast 850MB free memory in our Buffer Cache afetr the test. But, this Query probably is increasing the response times of all others also in our test.

regards,
Dileep T.V.R.

[tamilselvan]

I may guess that the explain plan in sqlplus differs from the tkprof which tells correct execution plan.
You can post the tkprof output here.

Also, post the statspack report.

Tamil