一、Oracle存储架构概述

1.1 存储组件组成

Oracle数据库的存储层次结构如下：

-- 查看表空间信息
SELECT tablespace_name, extent_management, allocation_type, status
FROM dba_tablespaces;

1.2 存储性能关键指标

指标	说明	优化目标
I/O吞吐量	每秒读写数据量	高吞吐
I/O延迟	单次I/O响应时间	低延迟
存储空间利用率	空间使用百分比	70-80%
碎片率	空间碎片程度	<15%

二、表空间优化

2.1 创建高效表空间

-- 1. 创建本地管理表空间（自动段空间管理）
CREATE TABLESPACE app_data
  DATAFILE '/u01/app/oracle/oradata/orcl/app_data01.dbf' SIZE 10G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- 2. 创建大文件表空间
CREATE BIGFILE TABLESPACE app_big
  DATAFILE '/u01/app/oracle/oradata/orcl/app_big.dbf' SIZE 100G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- 3. 创建UNDO表空间
CREATE UNDO TABLESPACE undo_ts
  DATAFILE '/u01/app/oracle/oradata/orcl/undo01.dbf' SIZE 5G
  EXTENT MANAGEMENT LOCAL;

2.2 表空间性能调优

-- 1. 查看表空间使用情况
SELECT 
  fs.tablespace_name,
  ROUND((df.totalspace - fs.freespace) / df.totalspace * 100, 2) AS used_pct,
  fs.freespace AS free_mb,
  df.totalspace AS total_mb
FROM
  (SELECT tablespace_name, SUM(bytes) AS freespace FROM dba_free_space GROUP BY tablespace_name) fs,
  (SELECT tablespace_name, SUM(bytes) AS totalspace FROM dba_data_files GROUP BY tablespace_name) df
WHERE fs.tablespace_name = df.tablespace_name;

-- 2. 查看高使用率表空间
SELECT tablespace_name, ROUND(used_percent, 2) AS used_pct
FROM dba_tablespace_usage_metrics
WHERE used_percent > 80;

-- 3. 添加数据文件
ALTER TABLESPACE app_data ADD DATAFILE 
  '/u01/app/oracle/oradata/orcl/app_data02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- 4. 调整数据文件大小
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/app_data01.dbf' RESIZE 20G;

2.3 临时表空间优化

-- 1. 创建临时表空间组
CREATE TEMPORARY TABLESPACE temp_grp1 TEMPFILE
  '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 10G
  TABLESPACE GROUP temp_group1;

-- 2. 分配临时表空间组给用户
ALTER USER scott TEMPORARY TABLESPACE temp_group1;

-- 3. 查看临时表空间
SELECT * FROM dba_temp_files;
SELECT * FROM dba_tablespace_groups;

-- 4. 重建临时表空间
ALTER TABLESPACE temp_ts COALESCE;

三、段与区管理

3.1 段空间管理

-- 1. 查看段信息
SELECT segment_name, segment_type, tablespace_name, bytes, extents
FROM dba_segments
WHERE owner = 'SCOTT'
ORDER BY bytes DESC;

-- 2. 查看高水位线
SELECT table_name, blocks, num_rows, avg_row_len, 
  ROUND(avg_row_len * num_rows / 8192, 2) AS actual_mb,
  blocks * 8 / 1024 AS hwm_mb
FROM dba_tables
WHERE owner = 'SCOTT'
ORDER BY blocks DESC;

-- 3. 收缩表降低高水位线
ALTER TABLE scott.big_table ENABLE ROW MOVEMENT;
ALTER TABLE scott.big_table SHRINK SPACE COMPACT;
ALTER TABLE scott.big_table SHRINK SPACE;

3.2 自动段空间管理（ASSM）

-- 1. 查看段空间管理方式
SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

-- 2. 创建使用ASSM的表
CREATE TABLE new_table (
  id NUMBER,
  name VARCHAR2(50),
  created_date DATE
) TABLESPACE app_data SEGMENT SPACE MANAGEMENT AUTO;

-- 3. 查看FREELISTS（手动管理）
SELECT table_name, freelists, freelist_groups
FROM dba_tables
WHERE owner = 'SCOTT' AND segment_space_management = 'MANUAL';

3.3 区大小优化

-- 1. 查看区的分配情况
SELECT segment_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner = 'SCOTT'
ORDER BY segment_name, extent_id;

-- 2. 查看默认区大小
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TBS_TYPE';

-- 3. 创建自定义区大小的表空间
CREATE TABLESPACE app_large
  DATAFILE '/u01/app/oracle/oradata/orcl/app_large.dbf' SIZE 50G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 4. 查看高碎片化的表
SELECT table_name, num_rows, blocks, empty_blocks,
  ROUND((blocks - NVL(empty_blocks, 0)) / blocks * 100, 2) AS used_pct
FROM dba_tables
WHERE owner = 'SCOTT' AND blocks > 100;

四、I/O优化

4.1 I/O统计监控

-- 1. 查看I/O性能统计
SELECT * FROM v$filestat ORDER BY phyrds DESC;

-- 2. 查看最活跃的数据文件
SELECT 
  f.file#,
  f.name AS file_name,
  s.phyrds,
  s.phywrts,
  ROUND(s.phyrds * p.value / 100, 2) AS read_mb,
  ROUND(s.phywrts * p.value / 100, 2) AS write_mb,
  ROUND(s.readtim / DECODE(s.phyrds, 0, 1, s.phyrds), 3) AS avg_read_ms
FROM v$filestat s
JOIN v$datafile f ON s.file# = f.file#
JOIN v$parameter p ON p.name = 'db_block_size';

-- 3. 查看等待事件
SELECT event, wait_class, total_waits, time_waited,
  ROUND(time_waited / 1000, 2) AS time_sec
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;

4.2 存储布局优化

# 1. 创建ASM磁盘组
# 登录ASM实例
sqlplus / as sysasm

# 创建磁盘组
CREATE DISKGROUP data_disk EXTERNAL REDUNDANCY
  DISK '/dev/oracleasm/disk1',
       '/dev/oracleasm/disk2';

# 2. 创建OMF管理的表空间
CREATE TABLESPACE app_data;
-- 自动在+DATA_disk创建数据文件

# 3. 多路复用控制文件
ALTER SYSTEM SET control_files=
  '/u01/app/oracle/oradata/orcl/control01.ctl',
  '/u02/app/oracle/oradata/orcl/control02.ctl',
  '/u03/app/oracle/oradata/orcl/control03.ctl'
SID='*' SCOPE=SPFILE;

4.3 异步I/O配置

# 1. 检查异步I/O支持
cat /sys/block/sda/queue/scheduler
# 应该包含：none

# 2. 启用异步I/O
# 在sqlnet.ora中添加
disk_asynch_io = true

# 3. 配置Linux异步I/O
# 编辑/etc/sysctl.conf
fs.aio-max-nr = 1048576

# 4. 挂载文件系统启用aio
mount -o noatime,nodiratime,data=writeback /dev/sdb1 /u01

# 5. 使用direct I/O
# 在创建表空间时指定
CREATE TABLESPACE app_data
  DATAFILE '/u01/app/oracle/oradata/orcl/app_data.dbf' SIZE 10G
  DEFAULT STORAGE (BUFFER_POOL KEEP);

五、缓存优化

5.1 缓冲区缓存配置

-- 1. 查看当前缓存大小
SHOW PARAMETER db_cache_size

-- 2. 查看各缓存池使用情况
SELECT name, block_size, current_size, buffers
FROM v$buffer_pool;

-- 3. 设置缓冲区缓存大小
ALTER SYSTEM SET db_cache_size = 4G SCOPE=SPFILE;

-- 4. 创建KEEP池（常用数据）
ALTER SYSTEM SET db_keep_cache_size = 1G SCOPE=SPFILE;

-- 5. 创建RECYCLE池（一次性数据）
ALTER SYSTEM SET db_recycle_cache_size = 512M SCOPE=SPFILE;

-- 6. 将表放入KEEP池
ALTER TABLE scott.big_lookup STORAGE (BUFFER_POOL KEEP);

5.2 缓冲区命中率监控

-- 1. 查看缓冲区命中率
SELECT 
  ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) AS buffer_hit_ratio
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

-- 2. 查看缓存池命中率
SELECT name, ROUND((1 - physical_reads / (db_block_gets + consistent_gets)) * 100, 2) AS hit_ratio
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0;

-- 3. 查看热块
SELECT o.object_name, o.object_type, COUNT(*) AS buffers
FROM v$bh bh, dba_objects o
WHERE bh.objd = o.data_object_id
  AND o.owner = 'SCOTT'
GROUP BY o.object_name, o.object_type
ORDER BY buffers DESC;

5.3 内存优化脚本

-- 完整内存配置脚本
DECLARE
  mem_size NUMBER;
BEGIN
  -- 获取SGA总大小
  SELECT value INTO mem_size
  FROM v$parameter
  WHERE name = 'sga_target';

  -- 设置各组件大小
  DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETERS(
    'SQL Tuning Advisor',
    'MEMORY_TARGET',
    mem_size
  );
END;
/

-- 推荐内存分配比例
-- db_cache_size: 60-70% of SGA
-- shared_pool_size: 20-25% of SGA
-- large_pool_size: 5-10% of SGA
-- java_pool_size: 1-2% of SGA

六、索引优化

6.1 索引存储优化

-- 1. 查看索引存储信息
SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys,
  ROUND((1 - num_rows / (leaf_blocks * 100)) * 100, 2) AS selectivity
FROM dba_indexes
WHERE owner = 'SCOTT'
ORDER BY leaf_blocks DESC;

-- 2. 重建索引
ALTER INDEX scott.pk_emp REBUILD TABLESPACE app_index NOLOGGING;

-- 3. 压缩索引
ALTER INDEX scott.idx_name REBUILD COMPRESS 2;

-- 4. 查看索引碎片
SELECT index_name, fragmentation_ratio
FROM (
  SELECT index_name,
    ROUND((del_lf_rows / lf_rows) * 100, 2) AS fragmentation_ratio
  FROM dba_indexes
  WHERE owner = 'SCOTT'
)
WHERE fragmentation_ratio > 15;

6.2 索引策略

-- 1. 创建本地分区索引
ALTER INDEX scott.idx_date REBUILD PARTITION p2026 LOCAL;

-- 2. 创建函数索引
CREATE INDEX scott.idx_upper_name ON scott.employees(UPPER(last_name));

-- 3. 创建位图索引（适合低基数列）
CREATE BITMAP INDEX scott.idx_gender ON scott.employees(gender);

-- 4. 查看索引使用情况
SELECT index_name, table_name, num_rows, distinct_keys, last_analyzed
FROM dba_indexes
WHERE owner = 'SCOTT';

-- 5. 监控索引使用
ALTER INDEX scott.idx_name MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name = 'IDX_NAME';

6.3 索引优化建议

-- 使用SQL Tuning Advisor分析索引
DECLARE
  l_task_id NUMBER;
BEGIN
  l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM scott.employees WHERE department_id = 50',
    scope => 'COMPREHENSIVE',
    time_limit => 60
  );

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task_id);
END;
/

-- 查看建议
SELECT DBMS_SQLTUNE.report_tuning_task('&task_name') FROM DUAL;

七、压缩与归档

7.1 表压缩

-- 1. 查看表压缩信息
SELECT table_name, compression, compress_for
FROM dba_tables
WHERE owner = 'SCOTT';

-- 2. 创建压缩表
CREATE TABLE scott.sales_compressed
COMPRESS FOR QUERY HIGH
AS SELECT * FROM scott.sales;

-- 3. 压缩现有表
ALTER TABLE scott.big_table MOVE COMPRESS FOR QUERY LOW;

-- 4. 查看压缩节省空间
SELECT table_name, ROUND(num_rows * avg_row_len / 1024 / 1024, 2) AS actual_mb,
  ROUND(blocks * 8 / 1024, 2) AS size_mb, compression
FROM dba_tables
WHERE owner = 'SCOTT' AND compression = 'ENABLED';

7.2 索引压缩

-- 1. 查看索引压缩
SELECT index_name, leaf_blocks, blevel, compression
FROM dba_indexes
WHERE owner = 'SCOTT';

-- 2. 压缩索引
ALTER INDEX scott.pk_emp REBUILD COMPRESS ADVANCED LOW;

-- 3. 创建压缩索引
CREATE INDEX scott.idx_multi_col
ON scott.orders (customer_id, order_date, status)
COMPRESS 2;

-- 4. 查看压缩比
SELECT index_name, leaf_blocks, 
  ROUND(leaf_blocks * 8 / 1024, 2) AS size_mb,
  compression
FROM dba_indexes
WHERE owner = 'SCOTT';

7.3 归档管理

-- 1. 启用表归档
ALTER TABLE scott.historical_data MOVE TABLESPACE archive_ts ROW STORE COMPRESS ADVANCED;

-- 2. 查看归档表
SELECT table_name, archive_type, compress_for
FROM user_tables
WHERE archive_type = 'ENABLED';

-- 3. 分区归档策略
CREATE TABLE scott.sales_partitioned (
  sale_date DATE,
  product_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) COMPRESS FOR ARCHIVE,
  PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

八、自动存储管理（ASM）

8.1 ASM配置

# 1. 安装ASM软件
# 下载oracleasm-support-2.1.11-1.el7.x86_64.rpm
# 下载oracleasmlib-2.0.12-1.el7.x86_64.rpm

sudo rpm -ivh oracleasm-support-2.1.11-1.el7.x86_64.rpm
sudo rpm -ivh oracleasmlib-2.0.12-1.el7.x86_64.rpm

# 2. 配置ASM
sudo /usr/sbin/oracleasm configure -i

# 3. 创建ASM磁盘
sudo /usr/sbin/oracleasm createdisk DATA1 /dev/sdb1
sudo /usr/sbin/oracleasm createdisk DATA2 /dev/sdc1

# 4. 扫描ASM磁盘
sudo /usr/sbin/oracleasm scandisks
sudo /usr/sbin/oracleasm listdisks

8.2 ASM磁盘组管理

-- 1. 创建磁盘组
CREATE DISKGROUP data_dg EXTERNAL REDUNDANCY
  DISK '/dev/oracleasm/disk1'
  ATTRIBUTE 
    au_size = '4M',
    sector_size = '512',
    compatible.asm = '19.0',
    compatible.rdbms = '19.0';

-- 2. 查看磁盘组
SELECT group_number, name, type, total_mb, free_mb, usable_file_mb
FROM v$asm_diskgroup;

-- 3. 添加磁盘
ALTER DISKGROUP data_dg ADD DISK '/dev/oracleasm/disk2';

-- 4. 删除磁盘
ALTER DISKGROUP data_dg DROP DISK 'DATA_0002';

-- 5. 平衡磁盘组
ALTER DISKGROUP data_dg REBALANCE POWER 10;

8.3 ASM性能优化

-- 1. 查看ASM性能
SELECT * FROM v$asm_diskgroup_stat;

-- 2. 查看磁盘IO统计
SELECT d.group_number, d.disk_number, d.name, p.reads, p.writes, p.read_time, p.write_time
FROM v$asm_disk_stat p, v$asm_disk d
WHERE p.group_number = d.group_number AND p.disk_number = d.disk_number;

-- 3. 配置 ASM_POWER_LIMIT
ALTER DISKGROUP data_dg REBALANCE POWER 8;

-- 4. 查看Rebalance进度
SELECT group_number, operation, state, POWER, actual, SOFAR, EST_MINUTES
FROM v$asm_operation;

九、备份与恢复优化

9.1 增量备份策略

# 1. 配置RMAN
rman target /

# 2. 设置备份优化
CONFIGURE BACKUP OPTIMIZATION ON;

# 3. 创建增量备份脚本
# Level 0: 完整备份（每周）
RUN {
  BACKUP INCREMENTAL LEVEL 0 DATABASE
  FORMAT '/backup/rman/level0_%T_%s.bak'
  TAG 'LEVEL0_WEEKLY';
}

# Level 1: 增量备份（每天）
RUN {
  BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE
  FORMAT '/backup/rman/level1_%T_%s.bak'
  TAG 'LEVEL1_DAILY';
}

# 4. 备份到快速恢复区
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES ON DISK;

9.2 存储空间管理

-- 1. 查看快速恢复区
SELECT * FROM v$recovery_dest_size;
SELECT * FROM v$recovery_file_dest;

-- 2. 设置快速恢复区大小
ALTER SYSTEM SET db_recovery_file_dest_size = 100G SCOPE=BOTH;

-- 3. 清理过期备份
DELETE OBSOLETE;

-- 4. 交叉检查备份
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;

-- 5. 删除过期归档日志
DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DISK;

十、总结

CentOS上Oracle存储优化包括：

1. 表空间优化：使用本地管理表空间、自动段空间管理
2. I/O优化：合理布局数据文件、启用异步I/O
3. 缓存优化：配置缓冲区缓存、监控命中率
4. 索引优化：重建碎片索引、使用压缩索引
5. 压缩技术：表压缩、索引压缩、归档压缩
6. ASM管理：使用ASM简化存储管理
7. 备份策略：增量备份配合压缩

核心要点：
– 监控表空间使用率，保持70-80%利用率
– 使用ASSM自动管理段空间
– 定期重建碎片化的索引
– 对历史数据启用压缩
– 使用ASM简化存储管理
– 配置合理的备份策略

通过本文的优化措施，可以显著提升Oracle数据库的存储性能和资源利用率。

注：本文基于CentOS 7/8和Oracle 19c编写，具体配置可能因版本差异而略有不同。