Is using defrag good enough?

[Rahul Pednekar]

Hello All,

We use defrag every month on our Oracle datamart server, but nowadays its taking up to 4 hours to finish defrag. I dont really see performance change after defrag but just the tightning of the data. would it be ok if I stop using defrag? or choose another way to defrag? seggestions please

Thanks

Rahul.

[TTr · Accepted solution]

How busy is your database? You have to be deleting and adding a huge number of records in your database to need defragmenting every month. Is this the case? Normally you don't need to defrag this often.

[Bill Hassell]

What makes you think that a defrag is even necessary? Oracle does not normally create and remove lots of files every day. Most Oracle designs create large files for the data storage that are mostly empty. Even if you have a bunch of applications using the same mount points as Oracle and those apps create and remove lots of files, you won't see any performance change at all for the Oracle files. Are you running fsadm to defrag because the -E and -D options report large fragmentation, or because PCs require it so Unix must need it too?

A badly fragmented lvol in VxFS filesystems is quite tricky to accomplish. With static files like Oracle, they don't change size so open, close, seek and read/write will see no change in performance. If you have performance issues, they are very likely not due to disk fragmentation but instead are caused by bad SQL code, missing or unbalanced index files, not enough (properly used) SGA, and lack of RAM.

[Rahul Pednekar]

Bill

Thanks a lot for that explaination,

Rahul.