Home » RDBMS Server » Performance Tuning » Need help on explain plan
Need help on explain plan [message #186955] Thu, 10 August 2006 05:18 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Hi,
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 while executing from sqlplus.

How can we find out what is the reason for this discrepancy? and how can we fix this problem.

Thanks.
Re: Need help on explain plan [message #186989 is a reply to message #186955] Thu, 10 August 2006 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE
& the use TKPROF on the trace file
Re: Need help on explain plan [message #187247 is a reply to message #186955] Fri, 11 August 2006 08:51 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post both EXPLAIN plans and your query.

Table definitions may also help.
Re: Need help on explain plan [message #187727 is a reply to message #186955] Tue, 15 August 2006 03:40 Go to previous message
orafaqer
Messages: 48
Registered: July 2006
Member
I guess it's side effect of bind variables peeking
Previous Topic: Enable Row Movement in Partitioning and Overhead
Next Topic: Analyze before insert - does it help
Goto Forum:
  


Current Time: Thu Dec 08 10:22:55 CST 2016

Total time taken to generate the page: 0.06402 seconds