Database Administration (MOSC)

MOSC Banner

Defragmentation leads to severe performance degradation

edited Sep 12, 2013 7:59PM in Database Administration (MOSC) 8 commentsAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center