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 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.

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: