matheusdba Staff asked 3 years ago
Hi Matheus. My Insert is running every day but only one day it is taking long… How to fix?
matheusdba Staff answered 3 years ago
Hi!
This is classic. Welcome to the performance world.
Lots of things can be related. In general changes:
- Did any load ran yesterday on this table?
- Did any related table had a data load?
- Did any statistics gathering ran on this table or related objects (other tables or indexes)?
- Any new index or object? Any removed index or object?
- …
In general, you should check what changed. Something changed, 90% of the times.
I’m not saying 100% because since recently Oracle has some Adaptive features (Adaptive SQL Directives and Adaptive Statistics) that change the behavior of your select based on previous executions trying to improve it. Some times it messes it. Be aware of this. This can affect your plan stability. Things like cursor sharing and other parameters needs also to be checked.
ALSO, I’m assuming that’s the exact same query. Note that different values for binds can and should change the plan. Imagine a 1 billion entry “PEOPLE” table with only 1 woman. If you do join this for entries where sex=male, a loop need to be used, or most likely a FTS for the select. If you join the data for the female one, is a single row access. This is good and natural.
Anyway, there are some many items to check, I just gave a few shots and mentions above to check if I can help with anything… 🙂