How can I measure the performance hit of tablespace autoextensions?

Question

I'm not a DBA, nor is there a DBA on the project I'm currently working, so we have tablespaces with autoextend on next 6m. I have no idea if this is low, although I believe so.

I want to measure what's going on with the current configuration and, if possible, tune the autoextension of my tablespaces so that a low value does not cause this to happen very frequently.

Answer 1

If you know the original size, it is easy to calculate the number of extends that have occurred. The metadata for the datafile will include its age.

The overhead for an auto extend is low, but datafile fragmentation may cause problems.

I generally try to extend by 10 to 25 percent of the original size, and aim for as few extend operations as possible. Try to allocate enough space for at least a year's worth of data.

For development databases, I tend to size smaller, and extend more often.

You can find out how full a tablespace by querying dba_tablespace_usage_metrics. The Orafaq Tablespace page will give you additional information.