印刻数据库

外观

Oracle 表空间与数据文件优化 ​

Oracle 表空间与数据文件优化概述 ​

表空间和数据文件是 Oracle 数据库存储架构的核心组成部分，合理的表空间设计和数据文件管理对数据库性能和可靠性至关重要。表空间是数据库存储的逻辑单位，数据文件是物理存储文件，一个表空间可以包含多个数据文件。优化表空间和数据文件可以提高数据库的 I/O 性能、减少存储开销、简化管理并提高系统的可扩展性。

表空间与数据文件优化的重要性 ​

  • 提高数据库 I/O 性能，减少 I/O 等待时间
  • 优化存储资源使用，降低存储成本
  • 简化数据库管理，提高系统可维护性
  • 提高数据库的可靠性和可用性
  • 支持不同类型的数据存储需求

表空间与数据文件优化的基本原则 ​

  • 合理规划表空间：根据数据类型和访问模式设计表空间
  • 优化数据文件布局：将数据文件分布在不同的物理磁盘上，平衡 I/O 负载
  • 合理设置数据文件大小：避免数据文件过小导致频繁扩展，或过大导致管理困难
  • 使用自动扩展功能：减少手动管理开销，但需要合理设置扩展参数
  • 考虑使用压缩技术：减少存储开销，提高查询性能
  • 定期监控和维护：定期检查表空间使用情况，及时进行扩展或清理

表空间设计优化 ​

1. 表空间类型选择 ​

Oracle 数据库支持多种类型的表空间，每种类型适合不同的使用场景：

1.1 永久表空间 ​

  • 普通表空间：存储永久数据，如用户表、索引等
  • Bigfile 表空间：支持单个数据文件最大 128TB，适合大型数据库
  • 只读表空间：存储不经常修改的数据，提高安全性和性能

1.2 临时表空间 ​

  • 本地临时表空间：为每个会话提供私有的临时存储，提高并发性能
  • 共享临时表空间：所有会话共享的临时存储，适用于小型数据库

1.3 undo 表空间 ​

  • 自动 undo 管理：Oracle 10g 及以上版本推荐使用，自动管理 undo 数据
  • 手动 undo 管理：需要手动管理回滚段，不推荐使用

2. 表空间规划策略 ​

2.1 按功能划分表空间 ​

  • 系统表空间：存储系统数据，如数据字典等
  • 用户表空间：存储用户数据
  • 索引表空间：存储索引数据，建议与用户表存储在不同的物理磁盘上
  • 大对象表空间：存储大对象数据（LOB）
  • 临时表空间：存储临时数据，如排序结果等
  • undo 表空间：存储 undo 数据，用于事务回滚和一致性读取

2.2 按访问模式划分表空间 ​

  • 高频访问表空间：存储经常访问的数据，使用高性能存储
  • 低频访问表空间：存储不经常访问的数据，使用普通存储
  • 归档表空间：存储归档数据，使用低成本存储

2.3 按业务划分表空间 ​

  • 为不同业务系统创建独立的表空间，便于管理和维护
  • 为不同业务周期的数据创建独立的表空间，便于归档和清理

3. 表空间创建与配置 ​

3.1 普通表空间创建 ​

sql
-- 创建普通表空间
CREATE TABLESPACE users
DATAFILE '/oradata/users01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建索引表空间
CREATE TABLESPACE indexes
DATAFILE '/oradata/indexes01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

-- 创建大对象表空间
CREATE TABLESPACE lob_data
DATAFILE '/oradata/lob01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 2G MAXSIZE 100G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

3.2 Bigfile 表空间创建 ​

sql
-- 创建 Bigfile 表空间
CREATE BIGFILE TABLESPACE big_data
DATAFILE '/oradata/big_data01.dbf' SIZE 100G
AUTOEXTEND ON NEXT 10G MAXSIZE 200G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

3.3 临时表空间创建 ​

sql
-- 创建临时表空间组
CREATE TEMPORARY TABLESPACE temp1
TEMPFILE '/oradata/temp1.dbf' SIZE 20G
AUTOEXTEND ON NEXT 2G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M;

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/oradata/temp2.dbf' SIZE 20G
AUTOEXTEND ON NEXT 2G MAXSIZE 50G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M;

-- 将临时表空间添加到表空间组
ALTER TABLESPACE temp1 TABLESPACE GROUP temp_group;
ALTER TABLESPACE temp2 TABLESPACE GROUP temp_group;

-- 将临时表空间组设置为数据库默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;

3.4 undo 表空间创建 ​

sql
-- 创建 undo 表空间
CREATE UNDO TABLESPACE undo_new
DATAFILE '/oradata/undo_new01.dbf' SIZE 20G
AUTOEXTEND ON NEXT 2G MAXSIZE 50G;

-- 将新的 undo 表空间设置为当前使用的 undo 表空间
ALTER SYSTEM SET undo_tablespace = undo_new SCOPE=BOTH;

数据文件优化 ​

1. 数据文件布局优化 ​

1.1 数据文件分布策略 ​

  • 平衡 I/O 负载：将数据文件分布在不同的物理磁盘或存储卷上
  • 分离热点数据：将频繁访问的数据文件与其他数据文件分离
  • 分离不同类型的数据：将数据文件、索引文件、日志文件存储在不同的物理磁盘上

1.2 数据文件放置建议 ​

数据类型存储建议
系统表空间高性能存储，如 SSD
用户表数据高性能存储，如 SSD 或 SAS
索引数据高性能存储，与用户表数据分离
LOB 数据普通存储，如 SATA
临时数据高性能存储，如 SSD
undo 数据高性能存储，与 redo 日志分离
备份数据低成本存储，如磁带或云存储

2. 数据文件大小优化 ​

2.1 数据文件大小设计原则 ​

  • 避免过小的数据文件：过小的数据文件会导致频繁扩展，增加 I/O 开销
  • 避免过大的数据文件：过大的数据文件会增加备份和恢复时间，影响管理效率
  • 考虑存储系统限制：不同存储系统对单个文件大小有不同的限制
  • 考虑数据库版本限制：不同 Oracle 数据库版本对数据文件大小有不同的限制

2.2 数据文件大小建议 ​

数据库类型数据文件大小建议
OLTP 系统10-50GB
OLAP 系统50-200GB
数据仓库100-500GB
Bigfile 表空间100GB-128TB

3. 数据文件自动扩展优化 ​

3.1 自动扩展设置 ​

sql
-- 创建带自动扩展的数据文件
CREATE TABLESPACE auto_ts
DATAFILE '/oradata/auto_ts01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- 修改现有数据文件为自动扩展
ALTER DATABASE DATAFILE '/oradata/existing.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

3.2 自动扩展最佳实践 ​

  • 合理设置 NEXT 参数：NEXT 参数决定了数据文件每次扩展的大小，建议设置为数据文件大小的 10%-20%
  • 设置 MAXSIZE 参数：限制数据文件的最大大小，避免数据文件无限增长
  • 监控自动扩展事件：通过告警日志或监控工具监控数据文件自动扩展事件
  • 考虑使用统一的扩展策略：为同一表空间的所有数据文件设置相同的扩展策略

4. 数据文件管理命令 ​

4.1 查看数据文件信息 ​

sql
-- 查看所有数据文件
SELECT file_name, tablespace_name, bytes/1024/1024/1024 AS size_gb, 
       autoextensible, maxbytes/1024/1024/1024 AS max_size_gb
FROM dba_data_files;

-- 查看临时数据文件
SELECT file_name, tablespace_name, bytes/1024/1024/1024 AS size_gb,
       autoextensible, maxbytes/1024/1024/1024 AS max_size_gb
FROM dba_temp_files;

-- 查看表空间使用情况
SELECT tablespace_name, 
       SUM(bytes)/1024/1024/1024 AS total_gb,
       SUM(bytes - free_bytes)/1024/1024/1024 AS used_gb,
       SUM(free_bytes)/1024/1024/1024 AS free_gb,
       ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
) 
GROUP BY tablespace_name
ORDER BY used_percent DESC;

4.2 添加数据文件 ​

sql
-- 为表空间添加数据文件
ALTER TABLESPACE users ADD DATAFILE '/oradata/users02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

4.3 调整数据文件大小 ​

sql
-- 增加数据文件大小
ALTER DATABASE DATAFILE '/oradata/users01.dbf' RESIZE 20G;

-- 减小数据文件大小（需要确保有足够的空闲空间）
ALTER DATABASE DATAFILE '/oradata/users01.dbf' RESIZE 5G;

4.4 重命名数据文件 ​

sql
-- 1. 离线数据文件
ALTER DATABASE DATAFILE '/oradata/old_name.dbf' OFFLINE;

-- 2. 操作系统级重命名文件
-- mv /oradata/old_name.dbf /oradata/new_name.dbf

-- 3. 修改数据库中的文件名
ALTER DATABASE RENAME FILE '/oradata/old_name.dbf' TO '/oradata/new_name.dbf';

-- 4. 在线数据文件
ALTER DATABASE DATAFILE '/oradata/new_name.dbf' ONLINE;

表空间扩展策略 ​

1. 手动扩展策略 ​

手动扩展是指 DBA 根据表空间使用情况，手动添加数据文件或调整数据文件大小。

手动扩展的优缺点 ​

  • 优点：

    • 可以精确控制表空间大小
    • 避免意外的空间增长
    • 便于规划存储资源

  • 缺点：

    • 需要定期监控表空间使用情况
    • 可能导致业务中断，如果表空间满了而没有及时扩展
    • 增加 DBA 管理开销

手动扩展最佳实践 ​

  • 定期监控表空间使用情况，设置告警阈值（如 80%）
  • 提前规划存储资源，确保有足够的空间用于扩展
  • 采用批量扩展策略，一次添加多个数据文件
  • 记录扩展操作，便于审计和管理

2. 自动扩展策略 ​

自动扩展是指通过设置数据文件的 AUTOEXTEND 属性，让 Oracle 数据库自动扩展数据文件大小。

自动扩展的优缺点 ​

  • 优点：

    • 减少 DBA 管理开销
    • 避免因表空间满导致的业务中断
    • 适应不可预测的空间增长

  • 缺点：

    • 可能导致数据文件大小失控
    • 自动扩展过程会产生 I/O 开销
    • 可能掩盖应用程序的空间泄漏问题

自动扩展最佳实践 ​

  • 合理设置 NEXT 和 MAXSIZE 参数
  • 监控自动扩展事件，及时调整扩展策略
  • 结合手动扩展使用，定期检查和调整数据文件大小
  • 对关键业务表空间，考虑使用更保守的自动扩展策略

3. 混合扩展策略 ​

混合扩展策略是指结合手动扩展和自动扩展的优点，根据不同的表空间类型和业务需求选择合适的扩展方式。

混合扩展建议 ​

  • 系统表空间：使用自动扩展，设置合理的 MAXSIZE
  • 用户表空间：结合手动扩展和自动扩展，设置较高的 MAXSIZE
  • 临时表空间：使用自动扩展，设置较高的 MAXSIZE
  • undo 表空间：使用自动扩展，设置合理的 MAXSIZE

表空间压缩技术 ​

表空间压缩是 Oracle 数据库提供的一项重要功能，可以减少存储开销，提高查询性能。Oracle 支持多种压缩技术，适用于不同的使用场景。

1. 基本表压缩 ​

基本表压缩适用于批量加载数据的场景，通过压缩重复数据减少存储开销。

sql
-- 创建压缩表空间
CREATE TABLESPACE compressed_ts
DATAFILE '/oradata/compressed01.dbf' SIZE 10G
DEFAULT COMPRESS FOR OLTP;

-- 创建压缩表
CREATE TABLE compressed_table (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    description CLOB
)
TABLESPACE compressed_ts
COMPRESS FOR OLTP;

2. 高级压缩选项 ​

Oracle 19c 和 21c 提供了更高级的压缩选项，包括：

2.1 Hybrid Columnar Compression (HCC) ​

HCC 是 Oracle Exadata 和 Oracle Cloud 存储提供的高级压缩技术，适用于数据仓库和归档数据。

sql
-- 创建使用 HCC 压缩的表
CREATE TABLE hcc_table (
    id NUMBER PRIMARY KEY,
    data_date DATE,
    value NUMBER
)
TABLESPACE data_warehouse
COMPRESS FOR QUERY HIGH;

2.2 Columnar Compression ​

Columnar Compression 是 Oracle 21c 引入的新功能，适用于分析型查询，通过按列存储数据提高查询性能。

sql
-- 创建使用 Columnar Compression 的表
CREATE TABLE columnar_table (
    id NUMBER PRIMARY KEY,
    data_date DATE,
    value1 NUMBER,
    value2 NUMBER,
    value3 NUMBER
)
TABLESPACE analytical_ts
COMPRESS FOR ANALYTIC QUERY;

3. 压缩技术选择建议 ​

压缩类型适用场景Oracle 版本要求
Basic Compression批量加载数据11g+
OLTP Compression在线事务处理11g+
HCC Compression数据仓库、归档数据11g+ (Exadata 或 Cloud)
Columnar Compression分析型查询21c+

表空间监控与维护 ​

1. 表空间监控 ​

1.1 监控表空间使用情况 ​

sql
-- 创建表空间使用情况监控视图
CREATE OR REPLACE VIEW tablespace_usage AS
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_gb,
       ROUND(SUM(bytes - free_bytes)/1024/1024/1024, 2) AS used_gb,
       ROUND(SUM(free_bytes)/1024/1024/1024, 2) AS free_gb,
       ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes
    FROM dba_free_space
) 
GROUP BY tablespace_name;

-- 查询表空间使用情况
SELECT * FROM tablespace_usage ORDER BY used_percent DESC;

1.2 设置表空间告警 ​

可以通过 Oracle Enterprise Manager (OEM) 或第三方监控工具设置表空间使用告警，当表空间使用率超过阈值时发送告警。

2. 表空间维护 ​

2.1 定期整理表空间 ​

  • 重组表：使用 ALTER TABLE ... MOVE 命令重组表，减少碎片
  • 重组索引：使用 ALTER INDEX ... REBUILD 命令重组索引
  • 收集统计信息：使用 DBMS_STATS 包收集表和索引的统计信息

2.2 处理表空间碎片 ​

sql
-- 查看表空间碎片情况
SELECT tablespace_name, COUNT(*) AS extents,
       ROUND(SUM(bytes)/1024/1024, 2) AS size_mb,
       ROUND(MAX(bytes)/1024/1024, 2) AS max_extent_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY tablespace_name;

-- 重组表减少碎片
ALTER TABLE fragmented_table MOVE TABLESPACE users;

-- 重组索引
ALTER INDEX fragmented_index REBUILD TABLESPACE indexes;

2.3 清理表空间 ​

  • 删除不再使用的表和索引
  • 使用分区表管理历史数据
  • 考虑使用表压缩减少存储开销
  • 归档不经常访问的数据

Oracle 19c 和 21c 新特性 ​

Oracle 19c 新特性 ​

1. 自动表空间管理增强 ​

  • 改进了表空间自动扩展算法，减少 I/O 开销
  • 增强了表空间监控功能，提供更详细的使用统计信息
  • 支持在线调整表空间的压缩属性

2. 大对象 (LOB) 存储优化 ​

  • 改进了 LOB 数据的存储结构，提高访问性能
  • 支持 LOB 数据的自动压缩
  • 增强了 LOB 数据的管理功能

Oracle 21c 新特性 ​

1. 列存储表 ​

  • 引入了列存储表，适用于分析型查询
  • 提供了更高的压缩率和查询性能
  • 支持混合行存储和列存储

2. 自动表优化 ​

  • 支持自动识别需要优化的表
  • 自动执行表重组和压缩操作
  • 减少 DBA 管理开销

3. 增强的表空间加密 ​

  • 支持更强大的表空间加密算法
  • 改进了加密表空间的性能
  • 支持在线加密和解密表空间

表空间与数据文件优化最佳实践 ​

1. 设计阶段最佳实践 ​

  • 合理规划表空间：根据数据类型、访问模式和业务需求设计表空间
  • 优化数据文件布局：将数据文件分布在不同的物理磁盘上，平衡 I/O 负载
  • 选择合适的表空间类型：根据数据特性选择普通表空间、Bigfile 表空间或其他类型
  • 考虑使用分区表：对于大型表，考虑使用分区表提高查询性能和管理效率

2. 实施阶段最佳实践 ​

  • 合理设置数据文件大小：避免数据文件过小或过大
  • 使用自动扩展功能：减少手动管理开销，但设置合理的扩展参数
  • 启用表空间压缩：根据数据特性选择合适的压缩技术
  • 设置表空间告警：及时发现和处理表空间问题

3. 运维阶段最佳实践 ​

  • 定期监控表空间使用情况：设置合理的告警阈值
  • 定期维护表空间：重组表和索引，收集统计信息
  • 优化存储资源：清理不再使用的数据，归档历史数据
  • 规划容量增长：根据业务增长趋势，提前规划存储资源

常见问题（FAQ） ​

Q1: 如何确定表空间的合理大小？ ​

A1: 确定表空间合理大小需要考虑以下因素：

  • 当前数据量大小
  • 数据增长趋势
  • 存储系统限制
  • 数据库版本限制
  • 备份和恢复需求

Q2: 如何优化表空间的 I/O 性能？ ​

A2: 优化表空间 I/O 性能可以从以下方面入手：

  • 将数据文件分布在不同的物理磁盘上
  • 使用高性能存储设备，如 SSD
  • 优化数据文件大小和扩展策略
  • 使用分区表和索引
  • 启用表空间压缩

Q3: 如何处理表空间碎片？ ​

A3: 处理表空间碎片的方法包括：

  • 重组表：使用 ALTER TABLE ... MOVE 命令
  • 重组索引：使用 ALTER INDEX ... REBUILD 命令
  • 重建表空间：创建新表空间，将数据迁移到新表空间
  • 使用自动段空间管理：减少碎片产生

Q4: 如何选择合适的表空间压缩技术？ ​

A4: 选择表空间压缩技术需要考虑以下因素：

  • 数据类型和访问模式
  • Oracle 数据库版本
  • 存储系统类型
  • 性能要求
  • 压缩率要求

Q5: 如何监控表空间使用情况？ ​

A5: 监控表空间使用情况的方法包括：

  • 使用 Oracle Enterprise Manager (OEM)
  • 查询数据字典视图（如 DBA_DATA_FILES, DBA_FREE_SPACE）
  • 使用第三方监控工具
  • 设置表空间使用告警

Q6: Oracle 19c 和 21c 在表空间管理方面有什么新特性？ ​

A6: Oracle 19c 和 21c 在表空间管理方面的新特性包括：

  • 增强的自动表空间管理
  • 改进的 LOB 存储
  • 列存储表（Oracle 21c）
  • 自动表优化（Oracle 21c）
  • 增强的表空间加密

总结 ​

表空间和数据文件优化是 Oracle 数据库性能优化的重要组成部分，合理的表空间设计和数据文件管理可以提高数据库的 I/O 性能、减少存储开销、简化管理并提高系统的可扩展性。在设计和管理表空间时，需要根据数据类型、访问模式和业务需求选择合适的表空间类型和数据文件配置。

Oracle 19c 和 21c 提供了许多新特性，如自动表空间管理、列存储表、自动表优化等，可以进一步提高表空间管理的效率和性能。DBA 需要定期监控和维护表空间，及时处理表空间问题，确保数据库的稳定运行。

通过合理的表空间和数据文件优化，可以提高 Oracle 数据库的性能、可靠性和可用性，为业务提供更好的支持。