Oracle High-level knowledge

First, Prepare knowledge: Oracle's logical storage management.

Oracle is divided into 4 granularity on logical storage: tablespaces, segments, extents, and blocks.

(1) Block: Is the smallest granularity of storage units, now the standard block size is 8k,oracle every I/O operation is also done by block, that is, when Oracle reads data from the data file, is read how many blocks, not how many rows. Each block can contain multiple row.

(2) Area: composed of a series of adjacent blocks, which is also the basic unit of Oracle space allocation, for example, when we create a table Dave, first Oracle assigns a space to the table, with constant insert data to Dave, When the original area cannot accommodate the inserted data, Oracle expands in a district, that is to say, how many more are allocated to Dave than how many blocks.

(3) Paragraph: is composed of a series of areas, generally speaking, when the creation of an object (table, index), will assign a segment to the object. So in a sense, a segment is a certain kind of data. such as Create TABLE Dave, this segment is the data segment, and create index on Dave (NAME), Oracle will also assign a segment to the index, but this is an index segment. The information for the query segment can be obtained by using the data dictionary: SELECT * from User_segments.

(4) Table space: Contains segments, areas and blocks. The data in the table space is physically stored in the data file in which it resides. A database must have at least one tablespace.

When we created a table, Oracle allocated 8 blocks to it, even if I didn't insert any one row of records. Of course, this is related to the initial parameters and minextents parameters of the table statement. Such as:

STORAGE

（

INITIAL 64K

Minextents 1

Maxextents Unlimited

）；

That is, after the object is created, Oracle assigns at least one zone, the initial size is 64K, and the size of a standard block is 8K, just 8 blocks.

Two, high water line (Water Mark)

1, official website description as follows

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#CNCPT89022

To manage spaces, Oracle Database tracks the state of blocks in the segment. The high Water mark (HWM) are the point in a segment beyond which data blocks are unformatted and have never been.

MSSM uses free lists to manage segment spaces. At table creation, no blocks in the segment are formatted. When a session I inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins ins Erting data into the blocks. In MSSM, a full table scan reads all blocks below the HWM.

ASSM does not with free lists and so must manage spaces differently. When a session I inserts data into a table, the database formats a single bitmap block instead of preformatting a Grou P of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the "place" of the free list. The database uses the bitmap to find free blocks and then formats all blocks before filling it with data. ASSM spread out inserts among blocks to avoid concurrency.

Every data block in a ASSM segment is in one of the following states:

(1) Above the HWM

These blocks are unformatted and have never been.

(2) Below the HWM

These blocks are in one of the following states:

(1) allocated, but currently unformatted and unused

(2) formatted and contain data

(3) formatted and empty because the data was deleted

Figure 12-23 depicts an ASSM segment as a horizontal series of blocks. At table creation, the "HWM is" at the beginning of the "segment on" left. Because no data has been inserted yet, all blocks in the segment are and unformatted never.

Figure 12-23 HWM at Table creation

Suppose that a transaction inserts rows into the segment. The database must allocate a group of blocks to hold the rows. The allocated blocks fall below the HWM. The database formats a bitmap blocks in this group to hold the metadata, but does not preformat the remaining the Group.

In Figure 12-24, the blocks below the HWM are allocated, whereas blocks above the HWM, are neither allocated or formatted. As inserts occur, the database can write to any blocks with available spaces. The low high water mark (low HWM) marks is below which all blocks are known to be formatted because they con Tain data or formerly contained data.

Figure 12-24 HWM and Low HWM

In Figure 12-25, the database chooses a blocks between the HWM and low HWM and writes to it. The database could have just as easily chosen any of the other blocks between the HWM and low HWM, or any blocks below the low HWM That's had available space. In Figure 12-25, the blocks to either side for the newly filled block are.

Figure 12-25 HWM and Low HWM

The low HWM are important in a full table scan. Because blocks below the HWM are formatted only when used, some blocks, could is unformatted, as in Figure 12-25. For this reason, the database reads the bitmap blocks to obtain the location of the low HWM. The database reads all blocks up to the low HWM because they are known to be formatted, and then carefully Formatted blocks between the low HWM and the HWM.

Assume that a new transaction inserts rows into the table, but the bitmap indicates this insufficient free space exists UN Der the HWM. In Figure 12-26, the database advances the HWM to the right, allocating a new group of unformatted blocks.

Figure 12-26 advancing HWM and Low HWM

When the blocks between of the HWM and the HWM are full, the HWM advances to the right and the low HWM advances to the Locati On the old HWM. As the database inserts data over time, the HWM continues to advance to "right" with the low HWM always the trailing It. Unless you manually rebuild, truncate, or shrink the object, the HWM never.