What could make your query suddenly run slow .

Many times I had been asked from Developer Team "Why this query become slow when it used to run quickly?". I think Jonathan Lewis well answered this question as follows:

1. A change in execution plan due to unlucky bind variable peeking with histograms
2. A change in execution plan because the statistics have been slowly going out of sync with the data/query
3. A change in execution plan because new (and unlucky) statistics have been generated since the last time query ran.
4. A change in execution plan because a few new, empty, partitions have been added to a critical table
5. An unlucky change in execution plan because a small change in actual data volume (with correct stats in place) can have a dramatic impact on the shape of the plan
6. A change in execution plan because you had an incomplete set of hints in your SQL and your luck just ran out
7. An unlucky change in data order that has a big impact on the success of subquery caching
8. A small slippage in timing that results in the SQL suffering a direct data collision (locking / read-consistency) with some other process.
9. A small slippage in timing that leaves the SQL running concurrently with something that is processing unrelated data but competing for the raw (disc / CPU / PX Slave) resources.
10. A small slippage in timing that means some other process cleared everything you wanted from the buffer cache before your SQL started running.
11. A small slippage in timing that means a process that normally follows your SQL preceded it, and you’re spending all your time on delayed block cleanout.
12. Someone dropped some indexes (or made them invalid)
13. Someone rebuilt one or more indexes since the last time the SQL executed
14. Someone has done a “shrink space” on a critical table since the last time you ran the SQL

Reference:

http://jonathanlewis.wordpress.com/2013/12/23/plan-changes/