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.

Report message to a moderator

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: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE
& the use TKPROF on the trace file

Report message to a moderator

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: 621
Registered: July 2006
Senior Member
Post both EXPLAIN plans and your query.

Table definitions may also help.

Report message to a moderator

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

Report message to a moderator

Previous Topic: Enable Row Movement in Partitioning and Overhead
Next Topic: Analyze before insert - does it help
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 13:31:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.