Defragmentation leads to severe performance degradation
We performed defragmentation on 2 schemas in one of oracle production databases over the weekend 09/08 using shrink space
Oracle version: 11.1.0.7
Alter table <table_name> shrink space cascade ;
Post defragmentation we gathered stats for both schemas using DMS_STATS.GATHER_SCHEMA_STATS package.
But after the activity, we are observing slowness in batch jobs and jobs are running much longer than expected time ( jobs taking 15 minutes against expected 1 minute).
We have to gather stats manually each day and run the cycle again to run them wihin time limit. Any reasons that would have caused SQLs to have a bad plan post table defragmentation.