Re: Optimizer issue in 11g
Date: Thu, 4 Feb 2010 18:05:53 -0800 (PST)
Message-ID: <a51127d3-fc29-4520-9843-1f8b13a83bd9_at_o28g2000yqh.googlegroups.com>
On Feb 3, 1:48 am, amy <amykl..._at_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 ) ... Received on Thu Feb 04 2010 - 20:05:53 CST