Same Query, same database, same application, new plan from one environment but not from another

  • From: "Rumpi Gravenstein" <rgravens@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Aug 2008 17:54:28 -0400

First here's what I'm running on:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux IA64: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

About a week or two ago a user started to complain that his Access 2002
Oracle queries started slowing down, not by a little, but by a lot.
Something that used to take a few minutes was now running into the hours.
In this first case, the query has just one bind variable, a date, all the
other predicate conditions are column joins or literals.

So we ran a trace on it and got that the Access query was suddenly using a
very bad explain plan.



Everyone claims that nothing has changed but clearly something has as the
Access run of the query now always runs slow and a cut and paste of the same
query through Toad always runs fast.



Also interesting, is that other users of Access databases that go against
different tables have started to report slow-downs as well, but
interestingly not all at the same time.  I checked and am told that there
have been no updates that should affect the Access 2002 to Oracle connection
environment in the last few weeks.  Having said that, about 1.5 months ago
there was a migration to the 10g client.



I'm going to run a 10053 trace against the Access version of the query to
see if that sheds any light on the issue.



In the mean time I thought I'd post here to see if anyone else has seen
something like this.



-- 
Rumpi Gravenstein

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2008