We upgraded our databases from 10g to 11g quite recently. After doing so, and after resolving a few resource manager issues, we thought we had everything covered. Soon thereafter, we started to notice that the tablespaces would automatically extend all datafiles by a substantial amount in one go when a single datafile reached the point at which it should autoextend. We checked through all of the autoextend values for our data files and they were set correctly. We had them set to extend by 2GB at a time.

What we noticed was that all of the datafiles in that tablespace would increase up to a much larger value, all at the same time. Strange, we thought, until we discovered an article about datafiles autoextending.

So, we took a look at Metalink note 743773.1 – SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable. This explanined the details exactly of what we were seeing.

Recap on Autoextend

The idea behind allowing your datafiles to autoextend is so that you have peace of mind. It means that when your segments (tables and indexes) are increasing in size and they hit the limit of the size allocated within your datafile the datafile will increase in size based on the autoextend value. Until 11g that is…

New Autoxtend Oracle Feature

As described in the Metalink note, there is a new background process called SMCO which is responsible for space management. One of the jobs for this process is to ensure that sessions do not have to wait for space to be allocated, so it attempts to pre-allocate space. The result of this is that if you have a tablespace of, say, 1,000GB and your datafile needs to autoextend the process will attempt to increase each of your datafiles by enough to add 10% of the total size of the tablespace….

So, that means for our 1,000GB tablespace that each datafile in it will be autoextended by enough to increase the total by 100GB. If you have larger tablespaces or perhaps 2TB or more then you need to have enough capacity for 200GB or more jumps in space usage.

Automatic Autoextend

Personally, I don’t really appreciate this new behaviour. We now see jumps of 200GB or more in one go, whereas before we would just see datafiles steadily increasing by 2gb at a time.

Note: If a datafile does not have AUTOEXTEND allowed it will not autoextend, so don’t worry about that happening. This only applies to those datafiles where AUTOEXTEND is set to ON.

Hopefully that has helped you learn about why your Oracle datafiles are autoextending more than you thought they should be…

Like it, share it...

Category: Articles

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *