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

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Wed, 27 Aug 2008 17:54:28 -0400
Message-ID: <9c9b9dc90808271454i2c0e1a3bsd2aaa338d9dc2c32@mail.gmail.com>


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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 27 2008 - 16:54:28 CDT

Original text of this message