My query was fine last week and now it is slow. Why?
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 SPFILE/ INIT.ORA parameter DB_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.
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 if they contain data.
For detail, please contact hlei@webdemo.saksoft.com
