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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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.
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.