Performance impact when Tablespace Datafile Autoextend is enabled

Performance impact when Tablespace Datafile Autoextend is enabled

The performance impact would be depending on the moment of the day (number of current transactions, IO activity in the storage system, etc.) and the simple answer is ‘yes, auto-extending a datafile could impact concurrent transactional activity’. The real answer is a lot more complex.

The extent to which extending a datafile might cause concurrency issues depends upon:

 How long it takes to extend the datafile

  What transactions are occurring at the same time as (1)

When a datafile is extended, the session performing the operation will acquire a number of locks. Generally these locks will not affect concurrent transactions, but some locks may do. For example, we have to update the controlfile with the updated datafile information. This will involve acquiring the CF (controlfile) enqueue in exclusive mode. If for some reason this enqueue is held for too long, it could possibly cause checkpoints to hang (by blocking the CKPT) process. This may then block sessions doing DML because we may be unable to write redo to the online log files. Note that this is not an usual scenario, but it could possibly happen.

It is not possible to list every potential concurrency blocking scenario. If such a hang did occur we would need to have the customer dump HANGANALYZE dumps to investigate the reason.

The best way to minimize the risk of concurrency issues would be to:

 Size datafiles correctly to minimise auto-extension activity

  Monitor datafiles’ free space and manually resize files before auto-extension is required

  Do manual resizes during off-peak periods