Understanding Bigfile Tablespace Defaults in Oracle Database 23ai: Impact and Benefits

Oracle Database 23ai introduces a significant but subtle shift in its default storage architecture, the adoption of bigfile tablespaces as the default for most tablespaces, including core ones like SYSTEM and SYSAUX. This change reflects Oracle’s continued evolution towards simplifying storage management and scaling capabilities. As database professionals with years of experience managing complex environments, understanding this new default and its impact on performance and administration is critical.

Background: What Are Bigfile Tablespaces?

Bigfile tablespaces were originally introduced in Oracle 10g as a means to address scalability challenges in very large databases. Unlike traditional small-file tablespaces, which consist of multiple datafiles each limited in size (typically up to 32 GB or 128 GB, depending on block size and OS), a bigfile tablespace consists of a single large datafile that can grow up to 128 terabytes (TB) or more, depending on the platform and block size.

This architecture reduces the overhead of managing multiple datafiles, simplifies tablespace management, and supports extremely large datasets without the complexity of juggling numerous datafiles.

The New Default in Oracle Database 23ai

With Oracle Database 23ai, bigfile tablespaces become the default when creating new tablespaces, including the critical Oracle-supplied ones:

• SYSTEM
• SYSAUX
• UNDOTBS1
• USERS (for the root container)

For example, when connecting to the root container in a 23ai database and querying the tablespaces, you’ll find that all Oracle-provided tablespaces except TEMP are bigfile tablespaces:

SQL> select tablespace_name, bigfile from dba_tablespaces order by 1;

TABLESPACE_NAME  BIG
---------------  ---
SYSAUX           YES
SYSTEM           YES
TEMP             NO
UNDOTBS1         YES
USERS            YES

However, when switching to a pluggable database (PDB) in the same release, the USERS tablespace remains a smallfile tablespace by default, reflecting a transitional behavior in multi-tenant environments:

SQL> alter session set container=freepdb1;

SQL> select tablespace_name, bigfile from dba_tablespaces order by 1;

TABLESPACE_NAME  BIG
---------------  ---
SYSAUX           YES
SYSTEM           YES
TEMP             NO
UNDOTBS1         YES
USERS            NO

Furthermore, when creating user-defined tablespaces without explicitly specifying the BIGFILE option, the default is now bigfile:

SQL> create tablespace new_ts datafile size 2g;

SQL> select tablespace_name, bigfile from dba_tablespaces order by 1;

TABLESPACE_NAME  BIG
---------------  ---
NEW_TS           YES

Why Did Oracle Make This Change?

Several strategic reasons underpin this shift:

1. Simplified storage management: Bigfile tablespaces simplify the management of multiple datafiles. With just one large file, DBAs spend less time monitoring and resizing individual files.
2. Scalability: As data volumes grow exponentially, bigfile tablespaces enable Oracle databases to handle much larger datasets seamlessly without the limitations smallfile tablespaces impose.
3. Improved cloud readiness: Oracle Autonomous Databases and cloud-based services benefit from the bigfile architecture, which aligns better with elastic storage and automated management.
4. Performance gains: While not a silver bullet, bigfile tablespaces can improve I/O performance by reducing the metadata overhead related to multiple datafiles and simplifying storage allocation.

Impact on Database Performance

From a performance standpoint, the shift to bigfile tablespaces can have several subtle but meaningful effects:

• Reduced datafile management overhead: With fewer datafiles to manage, the system spends less time updating control files and system metadata during space allocation or resizing operations.

• Faster tablespace operations: Operations like tablespace backup, recovery, and resizing can be simpler and potentially faster, since fewer files are involved.

• Better support for very large objects: For workloads involving huge tables or indexes (multi-terabyte scale), bigfile tablespaces avoid fragmentation and allow large segments to be allocated contiguously.

• Potential Risks: There is a trade-off; having a single very large datafile means that if the datafile becomes corrupted or unavailable, the impact is more significant compared to multiple smaller files. However, robust backup and recovery strategies mitigate this risk.

Considerations for DBAs and Users

• Backward compatibility: Existing smallfile tablespaces remain unaffected by this change, so upgrades will not retroactively convert old tablespaces to bigfile.
• Multi-tenant environment nuances: As observed, PDBs may still have small-file tablespaces by default for some system tablespaces. Future releases are expected to unify this behavior.
• TEMP tablespaces: The TEMP tablespace remains a small-file tablespace by default since temporary segments benefit from multiple files for parallel operations and workload distribution.
• Storage infrastructure: Administrators should verify that their underlying storage systems (ASM, filesystem, cloud block storage) efficiently handle very large files and provide the necessary performance and reliability.
• Monitoring: Adjust monitoring tools and alerts to track bigfile tablespaces specifically, especially when dealing with auto-extension and space usage.

Practical Advice Based on Experience

Having worked with Oracle databases through multiple versions, including cloud and on-premises deployments, I recommend:

• When deploying new Oracle 23ai databases or upgrading, plan for bigfile tablespaces as the default. Review your backup, recovery, and storage policies accordingly.
• For new tablespaces, embrace bigfile by default, but always specify tablespace attributes explicitly if your use case demands smallfile.
• Keep an eye on your pluggable databases and their tablespaces since defaults can differ, especially in mixed-version or hybrid environments.
• Use Oracle Managed Files (OMF) to simplify datafile management, which complements the bigfile default nicely by automating file naming and storage.
• Monitor tablespace growth patterns and adjust auto-extend parameters carefully. Large data files can grow quickly and consume storage unexpectedly if unchecked.

Conclusion

The shift to bigfile tablespaces as the default in Oracle Database 23ai marks an important step in Oracle’s strategy to simplify database storage management while enhancing scalability and cloud readiness. While this change requires some adjustments to operational practices, it ultimately benefits DBAs and users by enabling more efficient handling of large datasets and reducing the complexity of multiple data files.

By understanding the implications and adapting storage strategies, database professionals can leverage these improvements to optimize performance, streamline management, and prepare their environments for future growth.