We had a fair number of problems related to bind variable peeking in our 10.2.0.3 and 10.2.0.4 dbs. In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files.
Flash forward to Oracle 11g (11.2.0.2) and the optimizer has been enhanced to use bind variable peeking much better. After converting to 11g, one of my queries was running slower than before. I ran the slow query with hard-coded values and it finished in less than a second. I then tried the same thing with bind variables and it ran in 2400 seconds, with a different execution plan.
These were the exact symptoms that originally brought me to set _optim_peek_user_binds in the first place. My first thought was that I left the underscore parameters commented out after the upgrade. Nope, it was set "correctly", or at least how I thought it should be set.
I then set optimizer_features_enable back to 10.2.0.4 for my session and re-executed the query. Sure enough, it was back to sub-second response time. I switched the optimizer_features_enable back to 11.2.0.2 and it was slow again. Aha, something changed!
More research yeilded some documentation to digest:
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
http://www.pythian.com/news/820/bind-peeking-ad-hoc-queries-stable-performance-on-10g-you-can-only-pick-any-two/
After much reading, thought, and testing, I decided that _optim_peek_user_binds=false was not needed in 11g for my environment.
Subscribe to:
Post Comments (Atom)
2 comments:
What was the solution to obtain the same performance as 10.2?
Thanks.
I took _optim_peek_user_binds=false out of the init.ora
Post a Comment