All about oracle smallfile and bigfile tablespace

All about oracle smallfile and bigfile tablespace Oracle Tablespace tablespace is the highest level of the logical structure, and others correspond to segment object segment, partition extent, and block respectively. The Oracle database on the physical layer is composed of a series of files. The data file datafile is the object corresponding to Tablespace. The relationship between Tablespace and Datafile is one-to-multiple. One Tablespace can correspond to multiple datafiles, while Datafile can only belong to one Tablespace. In the traditional concept of Oracle management, multiple data files are created in a tablespace, especially on multiple storage disks, in order to distribute I/O. However, the BigFile Tablespace big file Tablespace introduced by Oracle10g has changed this concept. 1. Bigfile tablespace introduces the concept of Bigfile tablespace in Oracle10g. Tablespace is divided into two types after Oracle10g: smallfile tablespace and bigfile tablespace. In the past, a Tablespace corresponds to multiple data files and we became Smallfile Tablespace. When a 10 Gb database is created, the default tablespace type is specified. If this parameter is not specified, the table space of the SMALLFILE type is used by default. ========================================================== ========================================================== ====== SYS @ orcl> select * from database_properties where property_name = 'default _ TBS_TYPE '; PROPERTY_NAME PROPERTY_VALU DESCRIPTION ------------------------------ ---------------------------------- DEFAULT_TBS_TYPE SMALLFILE Default tablespace type # You can set the Default tablespace type. SQL> ALTER DATABASE SET DEFAULT bigfile TABLESPACE; ========================================================== ========================================================== ===== the most significant difference between the so-called Bigfile Tablespace is that a Tablespace can only correspond to one data file. Although Bigfile Tablespace only corresponds to one data file, the maximum volume of the data file is greatly increased. In traditional small datafile, each file contains a maximum of 4 MB data blocks. The size of each data block is 8 KB, and the maximum file size is 32 GB. Each Small Tablespace can theoretically contain 1024 data files, so that the maximum theoretical value is 32 TB. Bigfile Datafile has more powerful data block capacity, and can contain up to 4 GB Data blocks. The size of Bigfile Datafile is also 32 KG = 32 TB Based on 8 K of data blocks. Theoretically, the total capacity of small tablespace is the same as that of big tablespace. Next we will observe Bigfile Tablespace through a series of experiments. Here, we use the create bigfile tablespace statement to create a Bigfile Tablespace. Note that Bigfile Tablespace must be Managed using local extent, and DMT (Dictionary Managed Tablespace) is not allowed ). On the other hand, the segment space uses auto mode instead of manual. However, these two conditions allow exceptions in the Undo or temporary Bigfile tablespace. Small Tablespace and Bigfile Tablespace can coexist. ========================================================== ====================== SYS @ orcl> select tablespace_name, bigfile from dba_tablespaces; TABLESPACE_NAME BIG ---------------------------- --- system nosysaux NOUNDOTBS1 notemp nousers nobttest yes ====================== ==================================================== when bigfile tablespace is used, the data file has been specified. If we try to add a new data file at this time, the Oracle system will report an error. ========================================================== ========================================================== ==================== SYS @ orcl> alter tablespace bttest add datafile '/oradata/orcl/bttest2.dbf' size 10 M autoextend off; alter tablespace bttest add datafile '/oradata/orcl/bttest2.dbf' size 10 M autoextend off * ERROR at line 1: ORA-32771: cannot add file to bigfile tablespace ==================================== ====================== ========================================================== ======= 2. Relative file number of Bigfile here we will look at some features of the Bigfile Tablespace system. ========================================================== ============================== SYS @ orcl> select file_name, file_id, relative_fno from dba_data_files; FILE_NAME FILE_ID RELATIVE_FNO certificate ---------- ------------/oradata/orcl/kernel 4 4/oradata/orcl/kernel 3 3/oradata/orcl/kernel 2 2/oradata/orcl/system01.dbf 1/oradata /orcl/bttest. dbf 5 1024 =============================== ========================================================== ======= Each file contains two numbers, one is the absolute file number file_id, and the other is the relative file number relative_fno. When the number of files is small or the number of files to be deleted is small, the file_id and relative_fno are the same in the database. Relative_fno is a cycle, with 1024 as a cycle. After file_id increases sequentially to an integer multiple of 1024, file_id continues to increase, while relative_fno forms an internal loop. This mechanism reminds us of the data file ceiling limit of Oracle Small Tablespace, which is also 1023 files. We imagine an extreme situation where the number of data files in a tablespace exceeds 1024, that is, the same relative_fno exists in the data files in a tablespace. This is not allowed first. Setting relative_fno in 1024 is required for Oracle Data addressing. Here we need to pay attention to the rowid information of the Data row located in Oracle. rowid is the address that uniquely marks a row record in Oracle. Rowid consists of four parts: object number + file number + block number + slot number. The Rowid length is fixed, so the length of the four parts is fixed. The file number fno here is the relative file number. The search order here is that the object number gets the number of the tablespace in which it is located, and the relative file number is used inside the tablespace to locate the file. If the same relative_fno file exists in a tablespace, locating is impossible. Therefore, the maximum number of internal small tablepsace files must be 1023. However, Bigfile Tablespace has some differences. If a tablespace has only one data file and only one data file, relative_fno loses its meaning. Therefore, in the relative_fno column of dba_data_files, The datafile corresponding to bigfile tablespace is 1024. ========================================================== ========================================================== ========== SYS @ orcl> create table t_big tablespace bttest as select * from dba_tables; table created. SYS @ orcl> select dbms_rowid.rowid_object (t. rowid) obj_id, dbms_rowid.rowid_relative_fno (t. rowid, 'bigfile') relative_fno, 2 dbms_rowid.rowid_block_number (t. rowid) block_num, dbms_rowid.rowid_row_number (t. rowid) rownums, 3 dbms_rowid.rowid_to_absolute_fno (t. rowid, 'sys ','t _ BIG') abso_fno 4 from t_BIG T where rownum <4; OBJ_ID RELATIVE_FNO BLOCK_NUM ROWNUMS ABSO_FNO ---------- ------------ ---------- 74519 1024 131 74519 1024 131 74519 1 5 1024 131 2 5 ============== ========================================================== ================================ method, we can also see rowi on bigfile tablespace. The relative_fno information of Row d. 3. Bigfile Datafile size: Explore the maximum size of a data file. There are two factors to control in Oracle. One is the maximum number of blocks that can be found in Oracle internal addressing, the other is the size of each block in the database. Let's first look at the situation of Small Datafile. Rowid is an 18-bit length class string in hexadecimal notation. The first six digits represent object_id, the last three digits represent the file number, the last six digits represent the block number, and the last three digits represent the row slot number. -- Statement from MOS [ID 1057891.6] SQL> select substr (rowid,) "object", substr (rowid,) "file", substr (rowid) "block", substr (rowid, 16,3) "row" from test; object fil block row ------ --- aaabpw aaf AAAAv1 AAA in small datafile, the data block is represented in binary format of 6x4 = 24 bits. Oracle uses 22 digits in the middle for block address storage. In this way, the 22-bit binary data block number can be expressed in the range of (2 ^ 22-1) = 4 m data blocks. If the general size of each data block is 8 K, the maximum size of each small datafile is 32 GB. If the maximum size of each data block is 32 KB, the maximum size of each small datafile is 128 GB. In bigfile datafile, things are different. Because there is no relative_fno problem, in this way, the maximum value of relative_fno is 1024 in rowid. In this way, 10 binary bits are saved to locate the data block. The rowid with the same length can be used to address the data block with the 32-bit binary length. Each bigfile datafile can contain up to (2 ^ 32-1) = 4 GB Data blocks. In an 8 K data block environment, the maximum size is 32 TB. If the data block size is 32 KB, the file size can be up to TB. 4. Conclusion and discussion in the first part of this article, we discuss the theoretical analysis of Bigfile Tablespace and Small Tablespace. The maximum capacity values of the two are the same theoretically. It's just that the size of a single Bigfile is too large. What do we think of this new type? First, in the actual production environment, Bigfile Tablespace has a slight advantage in dealing with massive data storage. Because Small Tablespace consumes a lot of file management overhead to implement the same storage size. At the same time, it takes a lot of effort to control files to maintain files in multiple locations, such as collaborative check points and file header SCN. Second, the storage of Small Tablespace is just a theory, and few systems really create thousands of files in a Tablespace. From this perspective, Bigfile is quite attractive. Finally, Oracle provides comprehensive support for Bigfile Tablespace. At the storage layer, ASM, Logical Volumn Manager/RAID, Dynamically Extensible Logical Volumes, and OMF provide comprehensive support. However, Bigfile Tablespace is not a panacea. Bigfile is an Oracle-level Bigfile and does not involve OS-level support. When using Bigfile, You must select an appropriate underlying storage system to provide support. In addition, the concept of Bigfile violates the I/O dispersion theory we previously declared. Therefore, it is best to use Bigfile with powerful underlying storage support (such as ASM) to achieve the optimal performance balance. According to the official Oracle documents, the values of DB_FILES and MAXDATAFILES reduce the pressure on SGA. The maximum number of data files in the database is limited (64 K files). The emergence of BFT indeed has positive significance for massive databases. To a certain extent, BFT simplifies the complexity of managing multiple data files.