Prev: Problem with 10G Physical Standby Database (archive gap). HELP :-)
Next: ytd calculation
From: Mladen Gogala on 3 Feb 2010 18:08
On Wed, 03 Feb 2010 22:02:36 +0100, Shakespeare wrote:

> Who mentioned 10g here?
>
> Shakespeare

Oh, I apologize. My understanding was that queries were running fine
under 10g and that the problem emerged with an upgrade to 11g. I should
be reading more carefully.



--
http://mgogala.byethost5.com
From: hpuxrac on 4 Feb 2010 21:05
On Feb 3, 1:48 am, amy <amykl...(a)gmail.com> wrote:

snip

> Hi,
> We have queries that have been completing in minutes for months in
> 11.1.0.7 that suddenly took hours to complete. Since the database
> table data are relatively static, we decided to disable the nightly
> auto stats gathering job, hoping for a more stable environment, but we
> are still having the same issue occasionally. Execution plan that used
> an index access path in the past suddenly used a Full table scan or a
> nested loop join in the past now becomes a hash join.
>
> We did verify that the statistics on the tables and indexes involved
> have not been reanalyzed and the stats remained the same since the
> last good run but yet the access path has changed. If everything
> remains the same, ie stats, init.ora parameters, could an access path
> changed? What else could influence the Optimizer?
>
> Thanks.

Did someone put on patches or perhaps the 11.1.0.7.1 and/or 11.1.0.7.2
patchset update?

That won't show from sqlplus etc output ( will still look like
11.1.0.7.0 ) ...
From: amy on 19 Feb 2010 08:43
Thanks everyone for your responses. The issue has not resurfaced after
I posted this question. I was hoping to check out some of the
suggestions posted here. We do not use SQL Baseline and our System
Stats have not been re-gathered. I did not realize that Dynamic
Sampling can happen even with Stats in place. Thanks Randolf for
pointing that out. That might have contributed to the plan changes.

Thanks.
First  |  Prev  | 
Pages: 1 2
Prev: Problem with 10G Physical Standby Database (archive gap). HELP :-)
Next: ytd calculation