The datafiles are the physical storage space on the server. Storage in the datafiles is allocated by blocks to each extent that is used by an object. The block size is operating system-dependent and is determined when the database is created and cannot be changed. The database block size is set in the database parameter file DB_BLOCK_SIZE. Typically, a database block is either 2K or 4K.
•The PCTFREE parameter specifies the percentage of space in each segment's data block reserved for future record expansion into the block.
If records within a block are rarely updated after they are inserted, you should set PCTFREE lower to allow for full space usage. If records within a block are subject to many updates, you should set PCTFREE higher to allow for more growth.
Once the PCTFREE threshold is reached, no additional rows will be inserted until the PCTUSED threshold is reached due to deletion of data. If PCTFREE is set too low, row chaining and row migration will result because updates to the record will not fit into the block.
After that point, no new rows can be inserted into that block until the free space percentage falls below the PCTUSED threshold. The default for PCTFREE is 10%.
The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain. This is only relevant if you are not using the new Automatic Segment Space Management .
•The PCTUSED parameter defines the minimum percentage of data block used space that is necessary before the block is eligible for row insertion. A segment block is added to the free space list once its used space falls below this threshold. The default for PCTUSED is 40%.
If data is static, you will be able to set the PCTUSED lower and more fully use the space. If large amounts of data are inserted and deleted, you should set the PCTUSED higher to prevent block fragmentation.
You might set these parameters as follows: CREATE table emp (......) PCTFREE 5 PCTUSED 80.
The combined sum of PCTFREE and PCTUSED must be less than 100. Correct choice of these parameters can be used to improve the efficiency of table and index segments.
For example, tables that are insert-only (auditing tables, history tables, etc.) should have a PCTFREE setting of 1.
This simple change to a table's storage definition can reduce its total disk space requirements by 10%.
This means less object extension and less physical disk I/O when reading and writing: 10% more data can be read or written with a single data block read; 10% more data can be held in the database's SGA.
INITRANS is the initial number of concurrent transactions allocated in each block header when the block is allocated. The default is 1, which is the minimum. The maximum is 255. The size of each transaction entry is operating system-dependent.
MAXTRANS is the maximum number of concurrent transactions for the blocks within the tablespace. The default is 255, which is the maximum. The minimum setting is 1.
•The PCTFREE parameter specifies the percentage of space in each segment's data block reserved for future record expansion into the block.
If records within a block are rarely updated after they are inserted, you should set PCTFREE lower to allow for full space usage. If records within a block are subject to many updates, you should set PCTFREE higher to allow for more growth.
Once the PCTFREE threshold is reached, no additional rows will be inserted until the PCTUSED threshold is reached due to deletion of data. If PCTFREE is set too low, row chaining and row migration will result because updates to the record will not fit into the block.
After that point, no new rows can be inserted into that block until the free space percentage falls below the PCTUSED threshold. The default for PCTFREE is 10%.
The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain. This is only relevant if you are not using the new Automatic Segment Space Management .
•The PCTUSED parameter defines the minimum percentage of data block used space that is necessary before the block is eligible for row insertion. A segment block is added to the free space list once its used space falls below this threshold. The default for PCTUSED is 40%.
If data is static, you will be able to set the PCTUSED lower and more fully use the space. If large amounts of data are inserted and deleted, you should set the PCTUSED higher to prevent block fragmentation.
You might set these parameters as follows: CREATE table emp (......) PCTFREE 5 PCTUSED 80.
The combined sum of PCTFREE and PCTUSED must be less than 100. Correct choice of these parameters can be used to improve the efficiency of table and index segments.
For example, tables that are insert-only (auditing tables, history tables, etc.) should have a PCTFREE setting of 1.
This simple change to a table's storage definition can reduce its total disk space requirements by 10%.
This means less object extension and less physical disk I/O when reading and writing: 10% more data can be read or written with a single data block read; 10% more data can be held in the database's SGA.
This is a very simple way of increasing your block buffer cache without having to buy any more memory.
INITRANS is the initial number of concurrent transactions allocated in each block header when the block is allocated. The default is 1, which is the minimum. The maximum is 255. The size of each transaction entry is operating system-dependent.
MAXTRANS is the maximum number of concurrent transactions for the blocks within the tablespace. The default is 255, which is the maximum. The minimum setting is 1.
No comments:
Post a Comment