Storage Management
Outline
"High water" and "low water" marks
- PCTFREE and PCTUSED parameters
- PCTFREE ("high water" mark) and PCTUSED ("low water" mark) parameters control the use of free space for inserts and updates in data blocks
- Both parameters can only be specified when creating or altering tables and clusters
- PCTFREE parameter can be specified when creating or altering indexes
- PCTFREE parameter determines the percentage of a data block to be reserved (kept free) for the possible updates to rows that are already stored in the block
"High water" and "low water" marks
- After a data block becomes full, as determined by PCTFREE, the block is not considered for insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED
- A data block which is not considered for insertion of new rows is a excluded from a list of blocks available for insertions
CREATE TABLE EMPLOYEE( E# NUMBER (5) NOT NULL, ENAME VARCHAR(20) NOT NULL, ... ... ... ) PCTFREE 5 PCTUSED 75;
Storage Management
Outline
PCTFREE and PCTUSED parameters
- Example
- Consider the following values of PCTFREE and PCTUSED parameters: PCTFREE 20, PCTUSED 40
PCTFREE and PCTUSED parameters
- Example
- Insertions and updates uncrease occupancy level to 79%
- With 21% of a data block free, the insertions are still allowed
- Insertion of a new row fails because it would overlap 20% of a block reserved for updates
- A block is marked as not available for insertions
PCTFREE and PCTUSED parameters
- Example
- Updates do not increase occupancy level
- A block is still marked as not available for insertions
- Updates are allowed
PCTFREE and PCTUSED parameters
- Example
- Updates increase occupancy level to 90%
- A block is still marked as not available for insertions
- Updates are allowed
PCTFREE and PCTUSED parameters
- Example
- Updates and deletions decrease occupancy level to 60%
- A block is still marked as not available for insertions because occupancy level did not drop below 40%
- Updates are allowed
PCTFREE and PCTUSED parameters
- Example
- Updates and deletions decrease occupancy level to 39%
- A block is marked as available for insertions because occupancy level dropped below 40%
- Updates are allowed
PCTFREE and PCTUSED parameters
- Example
- The values of PCTFREE and PCTUSED parameters: PCTFREE 20, PCTUSED 40
Storage Management
Outline
Setting PCTFREE parameter
- Performance related observations:
- Low PCTFREE
- reserves less room for updates to existing table rows,
- allows inserts to fill the block more completely,
- may save space, because the total data for a table or index is stored in fewer blocks,
- increases processing costs because database system must frequently reorganise blocks as their free space area becomes filled,
- decreases costs of full table scans because a tables is stored in fewer blocks,
- potentially increases processing costs and space required if updates to rows or index entries cause rows to grow and span over many blocks
Setting PCTFREE parameter
- Performance related observations:
- High PCTFREE
- reserves more room for future updates to existing table rows,
- may require more blocks for the same amount of inserted data,
- lessens processing cost because blocks less frequently need reorganisation,
- may improve update performance, because database system does not need to chain rows,
- decreases performance of full table scans
Storage Management
Outline
Setting PCTUSED parameter
- Performance related observations:
- Low PCTUSED
- keeps data blocks less full,
- reduces processing costs during UPDATE and DELETE statements for moving a block to the free list of blocks when it has fallen below that percentage of usage,
- increases unused space in a database
- High PCTUSED
- keeps data blocks fuller,
- improves space efficiency
- increases processing costs of INSERT and UPDATE statements
Storage Management
Outline
PCTFREE versus PCTUSED
- Performance related observations:
- Dependencies between PCTFREE and PCTUSED
- The sum of PCTFREE and PCTUSED must be equal or less than 100
- If the sum is less than 100, then the ideal compromise of space utilization and I/O performance is a sum of PCTFREE and PCTUSED that differs from 100 by the percentage of space in the available block that an average row occupies
PCTFREE versus PCTUSED
- Performance related observations:
- Dependencies between PCTFREE and PCTUSED
- If the sum is equal 100 then database system attempts to keep no more than PCTFREE free space and the processing costs are the highest
- The smaller the difference between 100 and the sum of PCTFREE and PCTUSED, the more efficient space usage is at some performance costs
PCTFREE versus PCTUSED
- Example 1
- UPDATE statements frequently increase the size of rows,
PCTFREE = 20
PCTUSED = 40 - PCTFREE is set to 20 to allow enough room for rows that increase in size
- PCTUSED is set to 40 so that less processing is done during high update activity
- UPDATE statements frequently increase the size of rows,
PCTFREE versus PCTUSED
- Example 2
- Most operations include INSERT and DELETE statements and most of UPDATE statements do not increase the size of rows,
PCTFREE = 5
PCTUSED = 60 - PCTFREE is set to 5 because most of UPDATE statements do not increase the size of rows
- PCTUSED is set to 60 so that space freed by DELETEstatements is used soon
- Most operations include INSERT and DELETE statements and most of UPDATE statements do not increase the size of rows,
PCTFREE versus PCTUSED
- Example 3
- A table is very large, therefore storage is a primary concern, and most processing includes read-only transactions,
PCTFREE = 5
PCTUSED = 90 - PCTFREE is set to 5 because UPDATE statements are rare
- PCTUSED is set to 90 so that more space per block is used to store data
- A table is very large, therefore storage is a primary concern, and most processing includes read-only transactions,