Re: Optimizer issue in 11g

The obvious guess is that there is some variation in bind variables used to run the query, and someone uses a value that produces a plan that's good for them but bad for everyone else.

Since you have execution plans that take hours to complete you should have time to query v$sql_plan to find the bind variables used to generate the plan. If you can do the same when a good plan is running, this may give you a clue about the problem.

You'll need to find the SQL_ID of the statement then make a call the appropriate call to dbms_xplan

http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html

select * from table(dbms_xplan.display_cursor({your SQL_ID},null,'advanced'));
or simply
select * from table(dbms_xplan.display_cursor({your SQL_ID},null,'peeked_binds'));

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"amy" <amykline_at_gmail.com> wrote in message 
news: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.

>