Poor database performace due to Segment Fragmentation
Posted on 2011-04-25
We have a database where we consistently see high cpu/ io wait times. e.g.
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 12,596 61.6
db file sequential read 475,458 4,322 9 21.1 User I/O
db file scattered read 243,545 2,618 11 12.8 User I/O
log file sync 354,624 1,064 3 5.2 Commit
log file parallel write 432,639 1,039 2 5.1 System I/O
Also we see a bunch of sql statements with slow performance, but I don't see anything in the explain plans (missing indexes/high cost etc..)
The one thing that I do see in our database is the tables are fragmented. EG. we have a table that is using 16 gigs of space but the reclaimable space is 8+GB.
Would this be causing I/O and memory issues?
Please let me know if more info is required to diagnose this problem.