Optimizer issue in 11g
From: amy <amykline_at_gmail.com>
Date: Tue, 2 Feb 2010 22:48:11 -0800 (PST)
Message-ID: <d687e1b6-7758-47c4-af7b-4f7040c2ccdb_at_m31g2000yqd.googlegroups.com>
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.
Date: Tue, 2 Feb 2010 22:48:11 -0800 (PST)
Message-ID: <d687e1b6-7758-47c4-af7b-4f7040c2ccdb_at_m31g2000yqd.googlegroups.com>
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. Received on Wed Feb 03 2010 - 00:48:11 CST