Re: Optimizer issue in 11g
From: amy <amykline_at_gmail.com>
Date: Wed, 3 Feb 2010 09:12:42 -0800 (PST)
Message-ID: <488c1380-0126-4846-aa5a-d1bd8303ebdf_at_d34g2000vbl.googlegroups.com>
On Feb 3, 3:41 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> 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 Lewishttp://jonathanlewis.wordpress.com
>
> "amy" <amykl..._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.
Date: Wed, 3 Feb 2010 09:12:42 -0800 (PST)
Message-ID: <488c1380-0126-4846-aa5a-d1bd8303ebdf_at_d34g2000vbl.googlegroups.com>
On Feb 3, 3:41 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> 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 Lewishttp://jonathanlewis.wordpress.com
>
> "amy" <amykl..._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.
Jonathan, thanks for your response. There was only one query that used bind variables.The rest of the queries used hardcoded values. It's puzzling how the execution plan for a static query could change given that the table/index stats were frozen and the table data remains pretty much unchanged. I'll check out the bind values as suggested by you if the query runs slow again.
Thanks. Received on Wed Feb 03 2010 - 11:12:42 CST