Re: Optimizer issue in 11g
Date: Thu, 4 Feb 2010 18:05:53 -0800 (PST)
On Feb 3, 1:48 am, amy <amykl..._at_gmail.com> wrote:
> We have queries that have been completing in minutes for months in
> 220.127.116.11 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?
Did someone put on patches or perhaps the 18.104.22.168.1 and/or 22.214.171.124.2 patchset update?
That won't show from sqlplus etc output ( will still look like 126.96.36.199.0 ) ... Received on Thu Feb 04 2010 - 20:05:53 CST