My query was fine last week and now it is slow. Why?
Submitted by admin on Sun, 2005-12-25 12:54.
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
- Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
- Has OPTIMIZER_MODE been changed in INIT
.ORA? - Has the DEGREE of parallelism been defined/changed on any table?
- Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
- Have the statistics changed?
- Has the INIT
.ORA parameter BB_FILE_MULTIBLOCK_READ_COUNT been changed? - Has the INIT
.ORA parameter SORT_AREA_SIZE been changed? - Have any other INIT
.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.
»
- Login to post comments
My query was fine last week and now it is slow. Why?
It can also happen because of a very high high water mark.
Typically when a table was big, but now only contains a couple of records. Oracle still needs to scan through all the blocks to see it they contain data.