Slow Table Creation

Why is it taking so long to create empty tables?

A user creates a new tablespace and starts building a copy of the current schema on it. The new tablespace is on separate disk so that the copy process will be as quick as possible - reading from one set of spindles and writing to another set. However, the timings are starting to look disastrous as the table creates are taking minutes. The user tries again - but using the existing tablespaces and the job takes seconds. How can this be? Finally, she drops the user on the new tablespaces and tries once more; this time it is much faster. Relieved, the team goes on with the rest of the tests. Unfortunately, there are problems down the road and they have to go right back to the beginning and try again.

Having rebuilt the whole database from backup, the test begins, creating the new tablespace and building an empty schema. Once again, this process is terribly slow.

The existing tables are hundreds to thousands of megabytes, so the new tablespace has been built with a uniform extent size of 100M. Each create table is allocating a single 100M extent. The buffer cache is over 2G - and at this point we aren't actually loading any data into the tables in any case. And yet ...

The first point of investigation was to check that the new disks are as fast as the old ones. The System Administrator can quickly assure us that the striping layout is identical - except that the new mountpoint is on the same spindles as the redo logs. Aha! We run some disk monitoring and can see that there are continuous writes to the datafile mountpoint and the redolog mountpoint. When the redolog fills up, the response time on the disks slows as the archiver kicks in, reading the redo and writing it out to archive.

So, the redologs are moved onto different disks - an easy enough operation - and we try again. There is only a very small improvement. Time to stop and think. What else could be causing such a problem? Have a look at the create tablespace statement -

SQL> CREATE TABLESPACE DATA_100M_NEW
2    DATAFILE '/oradata/data_100m_new.dbf' SIZE 500M
3    REUSE AUTOEXTEND ON NEXT 8K
4    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
5    SEGMENT SPACE MANAGEMENT AUTO;

Can you see it? Think about what our disks are doing. Why is a create table statement causing significant writes to the datafiles with a large Buffer Cache and no other activity on the system? The redolog is normally key to efficient I/O - but we are pounding our data disk with writes.

The problem is the autoextend size. At 8K, the database is having to do 12800 space request operations for each 100M extent that the create requests. Increasing the autoextend amount to a sensible value immediately makes a significant difference to the time taken to create the empty tables.

Even setting autoextend to 100M would slow down the create table statements, as this would force a space request from the operating system at each table create. With a sensibly sized tablespace, the only necessarily performant I/O in this process is to the redo writes. If Oracle knows it has room to manouvre, the writes to datafiles are far less constraining to performance. The db writer can tootle along at it's own pace for quite a while.