We are using Oracle 12c. We have a nightly jobs that is pretty complicated but has been run OK for years.
Recently it suddenly takes a couple hours or 3 hours longer than its normal run (3 hours).
We did not change any code about the procedures, and the data are increased little by little each day, so not sure what made the slowness. And the slowness does not always happen every day.
for example in the past 2 weeks, it runs 3 hours in most of days, but 5 hours in the other 2 days. And a couple of times failed, with the error of using a lot of temp table spaces and time out. There is also more disk I/o happened during that time.
We have extended temp table space to pretty big, but still happens some day.
We have a monitor tool, I noticed since this happened, it started to use a different execution plan, that has some table scan instead of previously used index scan.
But the tables are not very big, about 8000 rows.
What could be wrong and how should we troubleshoot it?
Thanks