Oracle Database administrators must correctly understand segment space management and the concept of PCTFREE to manage data efficiently. This article provides a careful explanation of the basics and offers practical tips you can use in day-to-day operations.
Table of Contents
- What Is Segment Space Management?
- What Is PCTFREE?
- Differences Between MSSM and ASSM and How to Choose
- Understanding PCTFREE with a Diagram
- Practical Examples: Using PCTFREE
- Cautions and Best Practices
- Checking and Monitoring Methods
- Summary
1. What Is Segment Space Management?
Segment space management is the method used to manage free space within data blocks and extents. It affects storage efficiency and performance during updates. There are two types:
1.1 Manual Segment Space Management (MSSM)
- How it manages space: Uses freelists to track free space.
- Characteristics:
- Controls data block usage by combining PCTFREE and PCTUSED.
- Allows fine-grained tuning.
- Typical use cases:
- Applications with extremely high update frequency.
- Example:
CREATE TABLESPACE example_tbs DATAFILE '/path/to/datafile.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;
1.2 Automatic Segment Space Management (ASSM)
- How it manages space: Uses bitmaps to manage free space.
- Characteristics:
- Automatically optimizes free space.
- Only PCTFREE is applicable.
- Recommended as the default method.
- Typical use cases:
- Applications with few updates and large volumes of inserts.
- Example:
CREATE TABLESPACE example_tbs DATAFILE '/path/to/datafile.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2. What Is PCTFREE?
PCTFREE (Percentage Free) specifies the percentage of space in each data block reserved for future updates.
2.1 The Role of PCTFREE
- Reserves space in a block for cases where rows grow due to updates.
- Ensures some free space remains before the block becomes
FULL.
2.2 Example Settings
CREATE TABLE customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
phone_number VARCHAR2(20)
) PCTFREE 20;
In this example, 20% of each data block is reserved for updates.
2.3 Default Value of PCTFREE
- The default value is 10%.
- A higher value can improve update performance but reduces storage efficiency.
3. Differences Between MSSM and ASSM and How to Choose
| Item | MSSM | ASSM |
|---|---|---|
| Space management | Freelist | Bitmap |
| Parameters in effect | PCTFREE, PCTUSED | PCTFREE |
| Recommended scenario | Very frequent updates | Insert-heavy workloads |
| Ease of adoption | Medium | High |
4. Understanding PCTFREE with a Diagram
The following diagram illustrates the concept of PCTFREE.
4.1 Structure of a Data Block
+-------------------------------------------+
| Block Header (10%) |
+-------------------------------------------+
| Used Space (70%) |
+-------------------------------------------+
| PCTFREE Area (20%) |
+-------------------------------------------+
- Block Header: Holds metadata for the block.
- Used Space: Area where inserted row data is stored.
- PCTFREE Area: Space reserved for updates.
5. Practical Examples: Using PCTFREE
5.1 When You Expect Many Updates After Bulk Inserts
Set PCTFREE higher to reserve sufficient update space.
CREATE TABLE orders (
order_id NUMBER,
order_date DATE,
total_amount NUMBER
) PCTFREE 25;
5.2 When Updates Are Rare
Set PCTFREE lower to improve block usage efficiency.
CREATE TABLE archived_data (
record_id NUMBER,
archive_date DATE
) PCTFREE 5;
6. Cautions and Best Practices
- PCTFREE too high
- Lowers block utilization and increases storage consumption.
- Recommendation: If updates are not frequent, set to 10% or lower.
- PCTFREE too low
- Increases the likelihood of row chaining/migration during updates, which may degrade performance.
- Recommendation: If updates are frequent, set to 15% or higher.
- Consider using ASSM
- When both inserts and updates are mixed, ASSM is generally appropriate due to its automatic management.
7. Checking and Monitoring Methods
7.1 Checking PCTFREE
SELECT TABLE_NAME, PCT_FREE
FROM USER_TABLES
WHERE TABLE_NAME = 'CUSTOMERS';
7.2 Monitoring Block Utilization
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER,
COUNT(*) AS ROW_COUNT
FROM CUSTOMERS
GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
8. Summary
Segment space management and PCTFREE are essential for operating Oracle Database efficiently. In particular, configuring PCTFREE to match your data characteristics contributes to both performance improvement and resource optimization.
[Reference]
Oracle Database Database Performance Tuning Guide, 19c
コメント