High Water Mark - Space Allocation and Release

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space/blocks. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But when you delete the Rows then HWM cannot come down automatically, extra steps needs to done to bring down HWM. Deleting rows will never reset the HWM even if all the rows are deleted from the table.

• The high water mark level is just a line separate the used blocks and free blocks.
• The blocks above the HWM level is free blocks, they are ready to use.
• The blocks below the HWM level is used blocks, they are already used.

First let's have a look at the question when space is allocated

When you create a table at least one extent (contiguous blocks) is allocated to the table

- If you have specified MINEXTENTS the number of MINEXTENTS extents will be allocated immediately to the table

- If you have not specified MINEXTENTS then exactly one extent will be allocated.

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

Suppose we create an empty table, the high-water mark would be at the beginning of the table segment

X	Unused space

HWM

When you insert rows into the table the high watermark will be bumped up step by step. This is done by the server process which makes the inserts.

Used DATA/Rows

X

Un used Space

                                                               HWM                                                            

Now let us take a look at when space is released again from a segment like a table or index: Now if we insert a table with 10,000 rows. And let’s assume that we deleted 5,000 rows later.        

Used data

Empty blocks

X

Un used space

                           <--------------------------------------------------------------->HWM       

                                 Full table scan

In this case the high watermark will have reached the level of 10,000 and will have stayed there. Which means that we have empty blocks below the high watermark now.

Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary

(=> dba_free_space, dba_extents, dba_segements) .

Furthermore the physical addresses of the deleted row get recycled by new rows.

  As you seen above by deleting the data, HWM does not move.  The main disadvantage of this is that oracle always read the blocks up to high water mark in case of full table scan.  You may have ever notice that doing a count (*) on empty table, takes time to show you 0 rows.  The reason for delay is setting of HWM at higher position.

NOTE:  Whenever optimizer takes full table scan, it scans all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. Serial direct load:

INSERT /*+ APPEND */ 

INTO hr.employees 

NOLOGGING

SELECT * 

FROM oe.emps;

2. Parallel direct load:

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+PARALLLEL(hr.employees,2)

INTO hr.employees 

NOLOGGING

SELECT * 

FROM oe.emps;

3. Direct path loads:

sqlldr hr/hr control=lcaselutz.ctl ... direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA. There will be temporary segments filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

Release Unused Space From Table

There are a number of possible options which are already available before Oracle 10g:

• What we always could do is export and import the segment. After an import the table will have only one extent. The rows will have new physical addresses and the high watermark will be adjusted.
• Another option would be to TRUNCATE the table. With this we would loose all rows which are in the table. So we cannot use this if we want to keep existing records.

With Oracle 9i another possibilty was implemented:

ALTER TABLE emp MOVE TABLESPACE users;

This statement will also cause that

- The rows will have new physical addresses and

- The high watermark will be adjusted.

But for this:

- We need a full (exclusive) table lock

- The indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called Segment Shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segment Space Management. In such a tablespace a table does not really have a High watermark. It uses two watermarks instead:

- The High High Watermark referred to as HHWM, above which alle blocks ar unformatted.

- The Low High Watermark referred to as LHWM below which all blocks are formatted.

We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.

With the table shrinking feature we can get Oracle to move rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and by this make the segment more compact. 

For this we must first allow ORACLE to change the ROWIDs of these rows by issuing 

ALTER TABLE emp ENABLE ROW MOVEMENT;

ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs we can now issue a shrink statement.

ALTER TABLE emp SHRINK SPACE;

This statement will proceed in two steps:

• The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.
• The second step adjusts the high watermark. For this Oracle needs an exclusive table lock, but for a very short moment only.

Table shrinking Feature:

• will adjust the high watermark
• can be done online
• will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
• indexes will be maintained and remain usable
• can be made in one go
• can be made in two steps

This can be usefull if you cannot get a full table lock during certain hours, you only make the first step and adjust the high watermark later when it is more conveniant.

- ALTER TABLE emp SHRINK SPACE; -- only for the emp table

- ALTER TABLE emp SHRINK SPACE CASCADE; -- for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; -- only makes the first step (moves the rows)

How are the Indexes maintained?

In the first phase Oracle scans the segment from the back to find the position of the last row.

Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement. Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.

Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.

The following restrictions apply to table shrinking:

1.)It is only possible in tablespaces with ASSM.

2.) You cannot shrink:

- UNDO segments

- Temporary segments

- Clustered tables

- Tables with a column of datatype LONG

- LOB indexes

- IOT mapping tables and IOT overflow segments

- Tables with MVIEWS with ON COMMIT

- Tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.

The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates. This advisor is run automatically by an autotask job on a regular basis in the default maintainance window. You can use the built in package DBMS_SPACE to run the advisor manually as well.

Source:

http://chandu208.blogspot.in/2012/02/high-water-mark-hwm.html

http://www.sysdba.ch/index.php/13-how-to-adjust-the-high-watermark-as-of-oracle-10g-alter-table-shrink

Index Organized Table - IOT

An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure.

Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike normal primary key indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area).

Properties and restrictions

• An IOT must contain a primary key.
• Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables.
• An IOT cannot be in a cluster.
• An IOT cannot contain a column of LONG data type.
• You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.

Advantages of an IOT

• As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don't need to access the table to get additional column values.
• As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
• As the index and the table are in the same segment, less storage space is needed.
• In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
• As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.

Row overflow area

If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area. The overflow segments can reside in a tablespace different from the main segments.

Notes:

• The overflow area can contains only columns that are not part of the primary key.
• If a row cannot fit in a block, you must define an overflow area.
• Consequently, the primary key values of an IOT must fit in a single block.

The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (see example below).

Example of an IOT without an overflow area

The following example creates a simple IOT table and shows the objects and segments that are created. (This example was tested on Oracle versions 9.2 to 11.2.)

SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50))
  2    ORGANIZATION INDEX;

Table created.

SQL> SELECT table_name, iot_type, iot_name FROM user_tables;

TABLE_NAME                     IOT_TYPE     IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT                         IOT

SQL> SELECT index_name, index_type, table_name FROM user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IOT_TOP_71133              IOT - TOP                   MY_IOT

SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
     71133 MY_IOT               TABLE
     71134 SYS_IOT_TOP_71133    INDEX

SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
SYS_IOT_TOP_71133    INDEX

Note: In 11g, you must use the following syntax to see the same output:

CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50)) 
  SEGMENT CREATION IMMEDIATE 
  ORGANIZATION INDEX;

The reason for is that by default in 11g the segment is created only when the first row is inserted.

As you can see 2 objects are created: the table and the index, but there is only 1 segment (implementation of the object) which is the index one.

The name of the index is by default "SYS_IOT_TOP_" and its type is "IOT - TOP". You can choose the name of your index using the following syntax:

SQL> CREATE TABLE my_iot (id INTEGER CONSTRAINT my_iot_pk PRIMARY KEY, value VARCHAR2(50))
  2    ORGANIZATION INDEX;

Table created.

SQL> SELECT table_name, iot_type, iot_name FROM user_tables;

TABLE_NAME                     IOT_TYPE     IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT                         IOT

SQL> SELECT index_name, index_type, table_name FROM user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
MY_IOT_PK                      IOT - TOP                   MY_IOT

SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
     71135 MY_IOT               TABLE
     71136 MY_IOT_PK            INDEX

SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
MY_IOT_PK            INDEX

The IOT_NAME column is empty and will be useful in the example of the next section.

Example of an IOT with an overflow area

The following example creates an IOT with an overflow area and shows the objects and segments that are created. (This example was tested in versions 9.2 to 11.2, see the note in the previous section about 11g.)

SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50), comments varchar2(1000))
2    ORGANIZATION INDEX
  3    INCLUDING value OVERFLOW;

Table created.

SQL> SELECT table_name, iot_type, iot_name FROM user_tables ORDER BY 1;

TABLE_NAME                     IOT_TYPE     IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT                         IOT
SYS_IOT_OVER_71142             IOT_OVERFLOW MY_IOT

SQL> SELECT table_name, column_name FROM user_tab_columns ORDER by table_name, column_id;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
MY_IOT                         ID
MY_IOT                         VALUE
MY_IOT                         COMMENTS

SQL> SELECT index_name, index_type, table_name, include_column FROM user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                INCLUDE_COLUMN
------------------------------ --------------------------- ------------------------- --------------
SYS_IOT_TOP_71142              IOT - TOP                   MY_IOT                                 2

SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
     71142 MY_IOT               TABLE
     71143 SYS_IOT_OVER_71142   TABLE
     71144 SYS_IOT_TOP_71142    INDEX 

SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
SYS_IOT_OVER_71142   TABLE
SYS_IOT_TOP_71142    INDEX

All the columns up to and including the one named in the INCLUDING option of the OVERFLOW clause are in the top segment; the remaining ones are in the overflow segment.

Here, we see that 2 table objects are created, the main one is of iot_type IOT and the overflow one is of iot_type IOT_OVERFLOW. The name of the overflow table is SYS_IOT_OVER_. You can see now the purpose of the IOT_NAME column, it gives the name of the IOT table for its overflow one.

You can also see that 2 segments are created: the index of the IOT and the overflow area.

In the end, the USER_INDEXES view gives you the last column included in the index in its INCLUDE_COLUMN column.

FAQ

How to move an IOT into another tablespace?

Based on the example used in the previous section:

SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_71142             TABLE              TS_D01
SYS_IOT_TOP_71142              INDEX              TS_D01

Let's assume you want to move all the segments from their current tablespace to another tablespace. You'll quickly discover that you cannot use the standard commands:

SQL> ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01;

ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01
            *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

SQL> ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01;

ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

You have to first find the name of the associated IOT table:

SQL> SELECT iot_name FROM user_tables WHERE table_name = 'SYS_IOT_OVER_71142';

IOT_NAME
------------------------------
MY_IOT

SQL> SELECT table_name FROM user_indexes WHERE index_name = 'SYS_IOT_TOP_71142';

TABLE_NAME
------------------------------
MY_IOT

Then you can move the segments:

SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_i01;

Table altered.

However, this is only the main part of the IOT - the overflow part continues to reside in its original tablespace:

SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_71142             TABLE              TS_D01
SYS_IOT_TOP_71142              INDEX              TS_I01

To move the overflow area also, an additional statement is necessary:

 SQL> ALTER TABLE my_iot MOVE OVERFLOW TABLESPACE ts_i01;

Table altered.

SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
SYS_IOT_OVER_71142             TABLE              TS_I01
SYS_IOT_TOP_71142              INDEX              TS_I01

Both steps can be combined in one statement:

SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_d01 OVERFLOW TABLESPACE ts_d01;

Table altered.

How to know which columns are in the overflow segment?

Remember that the INCLUDE_COLUMN of the USER_INDEXES view gives you the last column included in the index and so in the top segment, so joining this view with the USER_TAB_COLUMNS you can separate the columns that are in the top segment from those that are in the overflow one as with the following statement:

SQL> SELECT c.table_name, c.column_name, 
  2         CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment
  3  FROM user_tab_columns c, user_indexes i
  4  WHERE i.table_name = c.table_name
  5  ORDER by table_name, column_id;

TABLE_NAME                     COLUMN_NAME                    SEGMENT
------------------------------ ------------------------------ --------
MY_IOT                         ID                             TOP
MY_IOT                         VALUE                          TOP
MY_IOT                         COMMENTS                       OVERFLOW

http://www.orafaq.com/wiki/Index-organized_table

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables012.htm#ADMIN11685

http://www.dba-oracle.com/t_index_organized_tables.htm

Data Blocks, Extents, Segments, PCTUSED and PCTFREE

Introduction to Data Blocks, Extents, and Segments

Oracle Database allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments. Figure 1 shows the relationships among these data structures.

Figure 1 The Relationships Among Segments, Extents, and Data Blocks

At the finest level of granularity, Oracle Database stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

The level of logical database storage greater than an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.

Oracle Database allocates space for segments in units of one extent. When the existing extents of a segment are full, Oracle Database allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile.

Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.

Overview of Data Blocks

Oracle Database manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle Database requests data in multiples of Oracle Database data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. Oracle Database data blocks are the smallest units of storage that Oracle Database can use or allocate.

Data Block Format

The Oracle Database data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2 illustrates the format of a data block.

Figure 2-2 Data Block Format

Following are the components of Data Block:

Header (Common and Variable)

The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table Directory

This portion of the data block contains information about the table having rows in this block.

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle Database reuses this space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row Data

This portion of the data block contains table or index data. Rows can span blocks.

Availability and Optimization of Free Space in a Data Block

Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

• If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available.
• If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERTstatement can use the space made available only after the other transaction commits and only if the space is needed.

Released space may or may not be contiguous with the main area of free space in a data block. Oracle Database coalesces the free space of a data block only when (1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and (2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle Database does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.

Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle Database stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle Database migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle Database preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle Database must scan more than one data block to retrieve the information for the row.

PCTFREE, PCTUSED, and Row Chaining

For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster (which has its own data segment). You can also specify the storage parameter PCTFREE when creating or altering an index (which has its own index segment).

The PCTFREE Parameter

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:

PCTFREE 20 

This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size. Figure 2-3 illustrates PCTFREE.

Figure 2-3 PCTFREE

Description of "Figure 2-3 PCTFREE"

The PCTUSED Parameter

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a CREATE TABLE statement:

PCTUSED 40 

In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 4 illustrates this.

Figure 4 PCTUSED

How PCTFREE and PCTUSED Work Together

PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 5 illustrates the interaction of these two parameters.

Figure 5 Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED

In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accessible to inserts.

For each data and index segment, Oracle Database maintains one or more free lists—lists of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE. These blocks are available for inserts. When you issue an INSERT statement, Oracle Database checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, then Oracle Database takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

After you issue a DELETE or UPDATE statement, Oracle Database processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions.

Overview of Extents

An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle Database allocates a new extent for the segment.

When Extents Are Allocated

When you create a table, Oracle Database allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle Database data blocks that correspond to the initial extent are reserved for that table's rows.

If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle Database automatically allocates anincremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.

Determine the Number and Size of Extents

Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments. They control how Oracle Database allocates free database space for a given segment. For example, you can determine how much space is initially reserved for a table's data segment or you can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of theCREATE TABLE statement. If you do not specify a table's storage parameters, then it uses the default storage parameters of the tablespace.

You can have dictionary managed tablespaces, which rely on data dictionary tables to track space utilization, or locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. Because of the better performance and easier manageability of locally managed tablespaces, the default for non-SYSTEM permanent tablespaces is locally managed whenever the type of extent management is not explicitly specified.

A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.

• For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.
• For system-managed extents, Oracle Database determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with 'segment space management auto', and if the database block size is 16K or higher, then Oracle Database manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.

The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be specified at the tablespace level for locally managed tablespaces. They can, however, be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and MINEXTENTS are used together to compute the initial size of the segment. After the segment size is computed, internal algorithms determine the size of each extent.

How Extents Are Allocated

Oracle Database uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.

With locally managed tablespaces, Oracle Database looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle Database looks in another datafile.

Overview of Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle Database allocates one or more extents to form that table's data segment, and for each index, Oracle Database allocates one or more extents to form its index segment.

Introduction to Data Segments

A single data segment in an Oracle Database database holds all of the data for one of the following:

• A table that is not partitioned or clustered
• A partition of a partitioned table
• A cluster of tables

Oracle Database creates this data segment when you create the table or cluster with the CREATE statement.

The storage parameters for a table or cluster determine how its data segment's extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER statement. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object.

Introduction to Index Segments

Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.

Oracle Database creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage.

Introduction to Temporary Segments

When processing queries, Oracle Database often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle Database automatically allocates this disk space called a temporary segment. Typically, Oracle Database requires a temporary segment as a database area for sorting. Oracle Database does not create a segment if the sorting operation can be done in memory or if Oracle Database finds some other way to perform the operation using indexes.

Operations that Require Temporary Segments

The following statements sometimes require the use of a temporary segment:

• CREATE INDEX
• SELECT ... ORDER BY
• SELECT DISTINCT ...
• SELECT ... GROUP BY
• SELECT . . . UNION
• SELECT ... INTERSECT
• SELECT ... MINUS

Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, aGROUP BY, and an ORDER BY, Oracle Database can require as many as two temporary segments.

Segments in Temporary Tables and Their Indexes

Oracle Database can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

Source: http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#CNCPT303