oracle tablespace extending

Question

While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of minutes), with all sessions blocked by "data file init write". Is there way to configure Oracle to extend the file proactively so that things can keep moving while it is doing it?

• Mat: Tens of minutes sounds incredibly long. Why don't you preallocate?
• MK01: @Mat well, yeah, we are going to try to preallocate, but I'd like to know 1) is it normal for this to take 30 minutes to extend by 1Tb 2) is it normal that all writing stops? Can it pre-extend before it really needs it to avoid stopping?
• Mat: Yes, extending by 1T can take a while, depending on your I/O system. Not sure how the locking works there though - 1T extent is pretty huge, it's usually done in smaller chunks (meaning disruptions are shorter, but total time spent extending probably a bit longer).

Answer 1

30min for 1TB is quite normal. That all writing stops is also normal if your tablespace completely run out of space. When writes has nowhere to go they have to wait for the RESIZE operation to complete. If one extends datafile while there is still space in it database I/O does not stop.

Just why you extended for such huge amount? Now those +1TB will add to your RMAN backup. Of course they will compress well but still not 100% and RMAN will need time to read all those blocks.

I would setup autoextend on those tablespaces:

ALTER DATABASE DATAFILE '+DG/sid/datafile/datafile_01.dbf' AUTOEXTEND ON NEXT 10G MAXSIZE 30G

Then you would not get wasted space in tablespace and also even in case your data grows fast and catches up with autoextend operation - there will be only small delay in writes because 10GB will not take a long time to format.

And Oracle will autoextend tablespace proactively. It will not wait for the last blocks to fill up.

• MK01: So how do I tell it to autoextend before it runs out of tablespace?
• Mindaugas Riauba: Did not find such information. Basically if you expect such bursts of data load and cannot have writes delayed for some time - monitor and preextend tablespace. Otherwise I never had problems while autoextending in 1-2-10GB chunks.

Answer 2

We solved this at my previous company by scheduling a cron job for out of trading hours to grow the tablespace when it fell to within a couple of standard deviations of average daily growth free. I researched it extensively at the time, and there is no way to tune the threshold for when Oracle does it (and you probably wouldn't want to anyway, since you still couldn't guarantee it wouldn't hammer you at the wrong time). We could have used this mechanism exclusively and turned autoextend off completely, but we thought that pausing to autoextend during the day was at least better than running out of space altogether. It can be hard to make the judgement objectively tho' since Oracle doesn't log autoextending events anywhere (at least not as of 10g which we were on then).

Another job checked the max sizes of all the autoextending DBFs, and compared it to the space actually free on the disk, to make sure we weren't overcommitting.

• MK01: you would think that Oracle could handle such a basic thing, eh? 11g still doesn't log extends!!