Re: Need assistance for tuning

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Thu, 10 Aug 2006 12:58:39 +0100
Message-ID: <44db1f70$0$18081$ed2619ec@ptn-nntp-reader02.plus.net>

Giridhar wrote:
> we have a query which takes 31 seconds when called through JDBC.
> The same sql statement, when executed from sqlplus takes only 1 second.
>
> When we took help of our dba to find out why it takes 31 seconds in
> production,
> our dba found that one table is undergoing full table scan when called
> from JDBC.
> This was also found from V$SQL_PLAN.
>
> However, explain plan shows index access and we get the output in just
> 1 second.
>
> How can we find out what is the reason for this discrepancy?

Bet you it's a bound variable.

If the optimiser knows the value of a variable it can do "clever stuff", whereas a plan for a query with deferred (bound) variable is a generic plan.

BugBear Received on Thu Aug 10 2006 - 06:58:39 CDT