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

  • From: Robert Freeman <robertgfreeman@xxxxxxxxx>
  • To: rgravens@xxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Aug 2008 15:18:47 -0700 (PDT)

Updated statistics?

 Robert G. Freeman
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: (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@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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 - 64bit 
PL/SQL Release - Production 
CORE Production 
TNS for Linux IA64: Version - Production 
NLSRTL Version - 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: