Hi,
We have a scenario, we have to drop lots of unused legacy columns from almost all tables (around 300 small-medium tables, 4/5 columns in each table). I have executed a batch with all drop commands at a time in test database. it took around 6 hours but problem is after dropping all of these columns database performance decreased a lot.
In ADDM reports of that time, I see one recommendation for below message-
"Cursors were getting invalidated due to DDL operations. This resulted in additional hard parses which were consuming significant database time"
But in second report (current report, after complete the commands) , I see “Hard parses due to cursor environment mismatch were not consuming significant database time”
Is there any immediate solution for this slowness. I tried gather stats of tables and indexes.
could you please advice on this?
Note: I didn't set unused columns at first. I just dropped the columns physically. DB version- 12.1.0.2.0 - 64bit .
Now I am planning to modify the columns as invisible instead of physically drop. since our main target is not to reclaim space. is there any performance affect ? Please advice.
Regards