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

Updated statistics?

 Robert G. Freeman
Author:
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
Blog: http://robertgfreeman.blogspot.com (Oracle Press) The LDS Church is looking for DBA's. You must be LDS to apply (please don't write to me and tell me I'm breaking the law. A church can choose to hire members of it's own faith. Look it up if you don't believe me).

• Original Message ---- From: Rumpi Gravenstein <rgravens_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, August 27, 2008 3:54:28 PM Subject: Same Query, same database, same application, new plan from one environment but not from another

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 - 17:18:47 CDT