What are some real-world examples of Bigfile Tablespaces being used instead of Smallfile Tablespaces in Oracle?

CodePudding user response：

We have data warehouses that are >100TB in size, with tablespaces around the 3TB mark. If they were smallfile, that would require 100 datafiles for just one tablespace. That can start to have performance impacts because every session needing to read from a segment crossing those datafiles is going to have to open and fstat each and every file and that adds OS overhead. Further, it's a maintenance headache: to "extend" you can't effectively use autoextend because of the 32G cap... you have to write a script that is regularly adding files and that's a headache.

A bigfile TS requires (and allows) only one, greatly easing administration. Set autoextend attributes to something sensible and you're done.

The only downsize to bigfile that I know of is that some backup software allocates one thread per file, so a really huge bigfile TS can lengthen your backup times. The solution there is to not let any TS get so big... divide them more liberally among your schemas so they stay smaller. There's also an older issue with contention on temp spaces on RAC if you have fewer tempfiles than nodes, but I think that's been mitigated in recent versions.