A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace.
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) option specifies the type of allocation.
For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT
STORAGE are not valid for extents that are managed locally. Actually, you cannot create a locally managed SYSTEM tablespace. Locally managed temporary tablespaces must of type “temporary” (not “permanent”).
Advantages
Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Example:
An insert causes a request for an extent
Oracle allocates space in the data tablespace
This causes an update of system tables in the data
dictionary if tablespace is dictionary-managed
Consequently, an update is made to the redo log.
So a large number of inserts in to a tablespace with a small extent size may cause many I/O’s to the system tablespace and consequently the redo log files. Also, large sorts from “read-only” databases may cause many I/O’s to the log file due to system tablespace update for temporary tablespace extent allocation.
How to calculate the size of locally managed tablespaces ?
When creating tablespaces with a uniform extent size it is important to understand that 64 Kbytes per datafile is allocated for the storage management information. When creating database files, add an additional 64 Kbytes to the size of your datafile.
Consider the following example to illustrate the matter:
SQL> CREATE TABLESPACE demo1
DATAFILE ‘D:\Oradata\SAP1\tab\SAP1_demo1.dbf’ SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
Tablespace created.
SQL> select bytes from dba_free_space where
TABLESPACE_NAME = ‘DEMO1′;
BYTES
———-
5242880
What happens here is we ask for 5 Mbyte extents in a 10 Mbyte file. After 64 Kbytes is allocated for the bitmap, we are left with one 5 Mbyte extent and one less then 5 Mbytes extent. We cannot use the less then 5 Mbyte extent so it does not show up — it is wasted. This can also happen when you have larger uniform extents when the remainder of space in the datafile is just 64 Kbytes short of being able to accomodate your uniform extent size.
SQL> drop TABLESPACE demo1;
Tablespace dropped.
If you change the test case to allow for the extra 64 Kbytes:
SQL> CREATE TABLESPACE demo1
DATAFILE ‘D:\Oradata\SAP1\tab\SAP1_demo1.dbf’ SIZE 10304K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
Tablespace created.
SQL> select bytes from dba_free_space where
TABLESPACE_NAME = ‘DEMO1′;
BYTES
———-
10485760
You can see that when we add 64 Kbytes to the datafile size the full 2 extents you want are there. Locally managed tablespaces should have datafiles that are 64 Kbytes
LARGER then a multiple of their extent size when using uniform sizing.
Sizing LMT
More and more we are using locally managed tablespaces. They offer a large amount of benefits, so why should we not use this new feature?
Some thoughts are needed when you decided to use Uniform Extent Allocation. With the uniform method, you specify an extent size when you create the tablespace, and all extents for all objects created within that tablespace will be that size.
The uniform method also provides an enforcement mechanism, because you can’t override the uniform extent size of locally managed tablespaces when you create a schema object such as a table or an index.
Calculate the Size of Tablespaces
The goal is to allocate as much disk space as really needed and as really used. With the uniform extent allocation you can calculate or even estimate the number of extents you want to allocate. Gaps or unused disk space within the tablespace should be avoided.
Lets assume that we create a tablespace with the uniform extent size of 1 MByte and 10 extents. Remember that locally managed tablespaces will use another 64 KBytes or the Header Bitmap:
10 * 1 * 1024K + 64K = 10304K
Note that all calculations are made in KBytes and that your chosen extent size is the multiple of your defined block size. The following statement creates this locally managed tablespace with a uniform extent size of 1 MByte:
CREATE TABLESPACE uni_test
DATAFILE ‘C:\Oradata\ASU1\tab\uni_test.dbf’
SIZE 10304K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
Check the Size and Number of Extents
Now every object created within the newly created tablespace gets its uniform extent size of 1 MByte:
CREATE TABLE tab_1 (
num NUMBER
) TABLESPACE uni_test;
CREATE TABLE tab_2 (
num NUMBER,
text VARCHAR2(255)
) TABLESPACE uni_test
STORAGE
(INITIAL 100K
NEXT 100K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
CREATE TABLE tab_3 (
num NUMBER,
text VARCHAR2(255),
create_date DATE
) TABLESPACE uni_test
STORAGE
(MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
If you are including a STORAGE clause when you create tables or indexes, Oracle will allocate as much extents as you indicate to use. Table TAB_1 will be allocated with one extent, table TAB_2 too because you need at least 100 KBytes. Table TAB_3 will be created with two extents. This could also be done by defining an INITIAL value of 2 MBytes.
The allocated blocks and extents can be verified using the view DBA_SEGMENTS:
SELECT segment_name, segment_type, blocks, extents
FROM dba_segments
WHERE owner = ‘TEST’
ORDER BY EXTENTS
/
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
——————– —————— ———- ———-
TAB_1 TABLE 256 1
TAB_2 TABLE 256 1
TAB_3 TABLE 512 2
The free space in the tablespace UNI_TEST can be verified using the view DBA_FREE_SPACE:
SELECT tablespace_name, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = ‘UNI_TEST’
/
TABLESPACE_NAME BYTES BLOCKS
—————————— ———- ———-
UNI_TEST 6291456 1536
That means in the tablespace UNI_TEST are still 1536 blocks available. How many extents are these blocks? This can be calculated by multiplying the number of available blocks by the block size and divided by the extent size:
1536 * 4K / 1024K = 6 extents
That fits with our calculations and verifications: 4 extents are already used and another 6
extents could be used to fill up the whole tablespace.
Check the File Size
If you check the physical file size used for the tablespace UNI_TEST you will be surprised: Instead of the calculated 10304 KBytes (10’551’296 Bytes) you will find the disk file’s size of 10’555’392 Bytes. Oracle allocates another block which can not be used for object allocation. Some of the Oracle tools such as the Tablespace Manger shows the total number of blocks according to the disk file size. In our example this are 2577 blocks, but usable are only 2576 blocks minus 64 KBytes (for header bitmap).
Summary
Keep the following rules in mind during the sizing of tablespaces:
Each extent size is the multiple of your defined block size.
The usable tablespace size is the multiple of your estimated number of extents.
The defined tablespace size used during CREATE TABLESPACE statement adds 64
KBytes for the header bitmap (HB) to the usable tablespace size.
The physical file size adds one block (AB) to the defined tablespace size.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace.
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) option specifies the type of allocation.
For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT
STORAGE are not valid for extents that are managed locally. Actually, you cannot create a locally managed SYSTEM tablespace. Locally managed temporary tablespaces must of type “temporary” (not “permanent”).
Advantages
Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Example:
An insert causes a request for an extent
Oracle allocates space in the data tablespace
This causes an update of system tables in the data
dictionary if tablespace is dictionary-managed
Consequently, an update is made to the redo log.
So a large number of inserts in to a tablespace with a small extent size may cause many I/O’s to the system tablespace and consequently the redo log files. Also, large sorts from “read-only” databases may cause many I/O’s to the log file due to system tablespace update for temporary tablespace extent allocation.
How to calculate the size of locally managed tablespaces ?
When creating tablespaces with a uniform extent size it is important to understand that 64 Kbytes per datafile is allocated for the storage management information. When creating database files, add an additional 64 Kbytes to the size of your datafile.
Consider the following example to illustrate the matter:
SQL> CREATE TABLESPACE demo1
DATAFILE ‘D:\Oradata\SAP1\tab\SAP1_demo1.dbf’ SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
Tablespace created.
SQL> select bytes from dba_free_space where
TABLESPACE_NAME = ‘DEMO1′;
BYTES
———-
5242880
What happens here is we ask for 5 Mbyte extents in a 10 Mbyte file. After 64 Kbytes is allocated for the bitmap, we are left with one 5 Mbyte extent and one less then 5 Mbytes extent. We cannot use the less then 5 Mbyte extent so it does not show up — it is wasted. This can also happen when you have larger uniform extents when the remainder of space in the datafile is just 64 Kbytes short of being able to accomodate your uniform extent size.
SQL> drop TABLESPACE demo1;
Tablespace dropped.
If you change the test case to allow for the extra 64 Kbytes:
SQL> CREATE TABLESPACE demo1
DATAFILE ‘D:\Oradata\SAP1\tab\SAP1_demo1.dbf’ SIZE 10304K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
Tablespace created.
SQL> select bytes from dba_free_space where
TABLESPACE_NAME = ‘DEMO1′;
BYTES
———-
10485760
You can see that when we add 64 Kbytes to the datafile size the full 2 extents you want are there. Locally managed tablespaces should have datafiles that are 64 Kbytes
LARGER then a multiple of their extent size when using uniform sizing.
Sizing LMT
More and more we are using locally managed tablespaces. They offer a large amount of benefits, so why should we not use this new feature?
Some thoughts are needed when you decided to use Uniform Extent Allocation. With the uniform method, you specify an extent size when you create the tablespace, and all extents for all objects created within that tablespace will be that size.
The uniform method also provides an enforcement mechanism, because you can’t override the uniform extent size of locally managed tablespaces when you create a schema object such as a table or an index.
Calculate the Size of Tablespaces
The goal is to allocate as much disk space as really needed and as really used. With the uniform extent allocation you can calculate or even estimate the number of extents you want to allocate. Gaps or unused disk space within the tablespace should be avoided.
Lets assume that we create a tablespace with the uniform extent size of 1 MByte and 10 extents. Remember that locally managed tablespaces will use another 64 KBytes or the Header Bitmap:
10 * 1 * 1024K + 64K = 10304K
Note that all calculations are made in KBytes and that your chosen extent size is the multiple of your defined block size. The following statement creates this locally managed tablespace with a uniform extent size of 1 MByte:
CREATE TABLESPACE uni_test
DATAFILE ‘C:\Oradata\ASU1\tab\uni_test.dbf’
SIZE 10304K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
Check the Size and Number of Extents
Now every object created within the newly created tablespace gets its uniform extent size of 1 MByte:
CREATE TABLE tab_1 (
num NUMBER
) TABLESPACE uni_test;
CREATE TABLE tab_2 (
num NUMBER,
text VARCHAR2(255)
) TABLESPACE uni_test
STORAGE
(INITIAL 100K
NEXT 100K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
CREATE TABLE tab_3 (
num NUMBER,
text VARCHAR2(255),
create_date DATE
) TABLESPACE uni_test
STORAGE
(MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
If you are including a STORAGE clause when you create tables or indexes, Oracle will allocate as much extents as you indicate to use. Table TAB_1 will be allocated with one extent, table TAB_2 too because you need at least 100 KBytes. Table TAB_3 will be created with two extents. This could also be done by defining an INITIAL value of 2 MBytes.
The allocated blocks and extents can be verified using the view DBA_SEGMENTS:
SELECT segment_name, segment_type, blocks, extents
FROM dba_segments
WHERE owner = ‘TEST’
ORDER BY EXTENTS
/
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
——————– —————— ———- ———-
TAB_1 TABLE 256 1
TAB_2 TABLE 256 1
TAB_3 TABLE 512 2
The free space in the tablespace UNI_TEST can be verified using the view DBA_FREE_SPACE:
SELECT tablespace_name, bytes, blocks
FROM dba_free_space
WHERE tablespace_name = ‘UNI_TEST’
/
TABLESPACE_NAME BYTES BLOCKS
—————————— ———- ———-
UNI_TEST 6291456 1536
That means in the tablespace UNI_TEST are still 1536 blocks available. How many extents are these blocks? This can be calculated by multiplying the number of available blocks by the block size and divided by the extent size:
1536 * 4K / 1024K = 6 extents
That fits with our calculations and verifications: 4 extents are already used and another 6
extents could be used to fill up the whole tablespace.
Check the File Size
If you check the physical file size used for the tablespace UNI_TEST you will be surprised: Instead of the calculated 10304 KBytes (10’551’296 Bytes) you will find the disk file’s size of 10’555’392 Bytes. Oracle allocates another block which can not be used for object allocation. Some of the Oracle tools such as the Tablespace Manger shows the total number of blocks according to the disk file size. In our example this are 2577 blocks, but usable are only 2576 blocks minus 64 KBytes (for header bitmap).
Summary
Keep the following rules in mind during the sizing of tablespaces:
Each extent size is the multiple of your defined block size.
The usable tablespace size is the multiple of your estimated number of extents.
The defined tablespace size used during CREATE TABLESPACE statement adds 64
KBytes for the header bitmap (HB) to the usable tablespace size.
The physical file size adds one block (AB) to the defined tablespace size.
No comments:
Post a Comment