Dear DBA Friends,
DB Version - 11.2.0.4.
I'm trying to understand an INSERT SQL and the way Optimizer is choosing it's execution plan. This insert runs once in every 4 hours prior to the purge process. (Purge deletes are based on selection from this insert statement). Stats are gathered for entire schema prior to the insert/purge is invoked
On most runs, insert takes 5-10 min to complete which is normal. On some occasions, this insert is running very long (90 min, 3 hours etc....) causing delays with purge process.
When I looked at the execution plan of a good run vs bad run, I found good runs always doing FTS and bad runs always doing an Index skip scan.
Below is the INSERT SQL and good, bad execution plans. Is there a way I can avoid optimizer using skip scan and make it always go for FTS?
This is Oracle provided INSERT that is part of the purge process.