Re: Optimizer issue in 11g

From: Jonathan Lewis <>
Date: Wed, 3 Feb 2010 08:41:12 -0000
Message-ID: <>

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

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'));


Jonathan Lewis

"amy" <> wrote in message

> Hi,
> We have queries that have been completing in minutes for months in
> 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 - 02:41:12 CST

Original text of this message