![]() ![]() ![]() SQL> create index muse_i on muse(muse_date) SQL> create table muse (id number, muse_date date, name varchar2(10)) Ĩ insert into muse values (v_count, sysdate-i, 'MUSE') I’ll begin by creating a simple little table and populate it with approximately 5 years worth of data. It’s quite possible that because the statistics haven’t changed, the CBO is forced into makings changes in how it costs and determines an execution plan.Ī very simple example follows, a classic case of why not refreshing statistics has caused the CBO to suddenly change an execution plan for no apparent reason. So some things do change that are not in direct control of the DBA.īut if we don’t collect fresh statistics, even though the data might have changed, won’t those data changes be effectively invisible to the CBO? Won’t the statistics not reflect any possible data changes and if the CBO doesn’t think the data has changed, doesn’t that mean it can’t suddenly change how it determines an execution plan ? When things worked well, it was at a different point in time than now when things have suddenly gone wrong. People have tried but it’s very difficult to stop time. The other aspect that always changes is time. When pointing the finger at what might have caused an execution plan to change, many simply ignore the fact the data is constantly changing in the background. It might not be a database change as such but the fact the data changes within a database is a critical change that can directly influence CBO behaviour. Those damn users log on and keep adding new data and modifying data all the time. The first thing that does usually change within most databases is the actual data within the database. The second point is that when one goes through all the things that might have changed in the database, two important aspects are often overlooked. It’s the act of not refreshing statistics that can cause execution plans to suddenly change. One might need to collect fresh statistics to make sure vital execution plans don’t change. That by not collecting statistics, one somehow can ensure and guarantee the database will simply perform in the same manner and generate the same execution plans. The first is that there’s a common misperception that if no new statistics are gathered (and assuming nothing else is altered in the database), that execution plans must always remain the same. There are two key points I want to emphasise. The DBA hasn’t touched a thing and yet suddenly, for no apparent reason, execution plans suddenly change and (say) an inappropriate index is suddenly used and causes performance degradation. I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?īy no changes, it means that there have been no alterations to any segments, no new indexes have been added, no changes associated bind peeking (indeed, there may not even be any bind variables), no parameters changes, no new patches or upgrades, no new outlines or profiles, no new system stats and perhaps most prevalent of all, no changes to any CBO statistics. How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo) FebruPosted by Richard Foote in CBO, Index Access Path, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Myths. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |