Oracle 数据库容量规划

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

容量规划确保数据库资源充足[1]：

详细见：Oracle 数据库健康检查。

---

2. 评估因素

2.1 业务

- 数据量增长
- 用户数
- 并发
- 保留期

2.2 性能

- 响应时间
- 吞吐量
- 资源利用率

2.3 SLA

- 可用性
- RPO/RTO
- 性能指标

---

3. 数据增长

3.1 历史

SELECT snap_id, 
       TO_CHAR(begin_interval_time, 'YYYY-MM-DD') AS day,
       ROUND(SUM(size_mb)/1024, 2) AS gb
FROM (
  SELECT snap_id, begin_interval_time, tablespace_size/1024 AS size_mb
  FROM dba_hist_tbspc_space_usage
)
GROUP BY snap_id, begin_interval_time
ORDER BY day DESC FETCH FIRST 30 ROWS ONLY;

3.2 趋势

SELECT tablespace_name,
       ROUND(AVG(size_gb), 2) AS avg_gb,
       ROUND(MAX(size_gb), 2) AS max_gb,
       ROUND(MIN(size_gb), 2) AS min_gb
FROM (
  SELECT tablespace_name, snap_id, 
         SUM(tablespace_size)/1024/1024 AS size_gb
  FROM dba_hist_tbspc_space_usage
  GROUP BY tablespace_name, snap_id
)
GROUP BY tablespace_name;

---

4. 表空间

4.1 当前

SELECT 
  tablespace_name,
  ROUND(SUM(total)/1024/1024/1024, 2) AS total_gb,
  ROUND(SUM(used)/1024/1024/1024, 2) AS used_gb,
  ROUND(SUM(free)/1024/1024/1024, 2) AS free_gb,
  ROUND(SUM(used)/NULLIF(SUM(total),0)*100, 2) AS pct_used
FROM (
  SELECT tablespace_name, bytes AS total, 0 AS used, 0 AS free
  FROM dba_data_files
  UNION ALL
  SELECT tablespace_name, 0, bytes, 0 FROM dba_segments
  UNION ALL
  SELECT tablespace_name, 0, 0, bytes FROM dba_free_space
)
GROUP BY tablespace_name
ORDER BY pct_used DESC;

4.2 增长预测

SELECT tablespace_name,
       ROUND(MIN(size_gb), 2) AS min_gb,
       ROUND(MAX(size_gb), 2) AS max_gb,
       ROUND((MAX(size_gb) - MIN(size_gb)) / COUNT(DISTINCT snap_id), 2) AS daily_growth_gb
FROM (
  SELECT tablespace_name, snap_id, SUM(tablespace_size)/1024/1024 AS size_gb
  FROM dba_hist_tbspc_space_usage
  GROUP BY tablespace_name, snap_id
)
GROUP BY tablespace_name;

4.3 扩展

-- 添加数据文件
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/users02.dbf' SIZE 10G AUTOEXTEND ON;

-- 自动扩展
ALTER DATABASE DATAFILE '/u01/oradata/users01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- 大文件表空间
CREATE BIGFILE TABLESPACE big_ts DATAFILE '/u01/oradata/big_ts.dbf' SIZE 1T;

---

5. CPU

5.1 当前使用

SELECT snap_id, 
       ROUND(AVG(value), 2) AS cpu_usage
FROM dba_hist_sysmetric_history
WHERE metric_name = 'CPU Usage Per Sec'
GROUP BY snap_id
ORDER BY snap_id DESC FETCH FIRST 30 ROWS ONLY;

5.2 容量

- 物理 CPU
- vCPU
- 实例进程
- 并行

5.3 升级

- 增加核数
- NUMA
- CPU 亲和

详细见：Oracle-Linux 调优详解。

---

6. 内存

6.1 SGA

SELECT * FROM v$sgainfo;
SHOW PARAMETER sga

6.2 PGA

SELECT name, value, unit FROM v$pgastat;
SHOW PARAMETER pga

6.3 容量评估

- 连接数 × PGA/连接
- 缓存命中率
- 解析率

详细见：Oracle 内存管理 SGA/PGA。

---

7. I/O

7.1 吞吐

SELECT snap_id,
       ROUND(SUM(value)/1024/1024, 2) AS mb_per_sec
FROM dba_hist_sysmetric_history
WHERE metric_name IN ('Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec')
GROUP BY snap_id
ORDER BY snap_id DESC FETCH FIRST 30 ROWS ONLY;

7.2 IOPS

SELECT snap_id,
       ROUND(AVG(value), 2) AS iops
FROM dba_hist_sysmetric_history
WHERE metric_name = 'Physical Read Total IO Requests Per Sec'
GROUP BY snap_id;

7.3 容量

- 磁盘数量
- IOPS 上限
- 吞吐上限

---

8. 网络

8.1 流量

- 连接数
- 数据传输
- Data Guard 同步

8.2 容量

- 带宽
- 延迟
- 并发

---

9. 备份

9.1 备份大小

SELECT session_key,
       output_bytes/1024/1024/1024 AS backup_gb,
       TO_CHAR(start_time, 'YYYY-MM-DD') AS day
FROM v$rman_backup_job_details
ORDER BY start_time DESC;

9.2 FRA

SELECT name, 
       space_limit/1024/1024/1024 AS limit_gb,
       space_used/1024/1024/1024 AS used_gb
FROM v$recovery_file_dest;

9.3 保留

- 恢复窗口
- 冗余
- 长期归档

详细见：Oracle 备份恢复规划。

---

10. 归档

10.1 速率

SELECT TO_CHAR(completion_time, 'YYYY-MM-DD') AS day,
       COUNT(*) AS log_count,
       ROUND(SUM(blocks * block_size)/1024/1024/1024, 2) AS gb
FROM v$archived_log
WHERE completion_time > SYSDATE - 30
GROUP BY TO_CHAR(completion_time, 'YYYY-MM-DD')
ORDER BY day DESC;

10.2 容量

- 每日 GB
- 保留天数
- FRA 大小

详细见：Oracle 归档日志管理详解。

---

11. 增长预测

11.1 历史趋势

SELECT TO_CHAR(begin_interval_time, 'YYYY-MM') AS month,
       ROUND(AVG(size_gb), 2) AS avg_size_gb
FROM (
  SELECT begin_interval_time, SUM(tablespace_size)/1024/1024 AS size_gb
  FROM dba_hist_tbspc_space_usage
  GROUP BY begin_interval_time
)
GROUP BY TO_CHAR(begin_interval_time, 'YYYY-MM')
ORDER BY month DESC;

11.2 预测

- 线性回归
- 业务增长
- 季节性

11.3 容量阈值

- 70%：警告
- 80%：紧急
- 90%：扩展

---

12. 监控

12.1 告警

SELECT tablespace_name,
       ROUND(used/total*100, 2) AS pct_used
FROM (
  SELECT tablespace_name, 
         SUM(bytes) AS total,
         SUM(CASE WHEN bytes < maxbytes THEN maxbytes - bytes ELSE 0 END) AS free
  FROM dba_data_files
  GROUP BY tablespace_name
);
-- > 80% 告警

12.2 报告

- 每日容量
- 每周增长
- 每月预测

---

13. 扩展策略

13.1 垂直

- 增加资源（CPU / 内存）
- 单机增强

13.2 水平

- RAC
- 分片
- 读写分离

13.3 云

- 弹性扩展
- 自动
- 按需

详细见：Oracle Cloud 部署详解。

---

14. 优化

14.1 数据归档

-- 历史数据归档
ALTER TABLE sales EXCHANGE PARTITION p2020 WITH TABLE sales_archive;
ALTER TABLE sales DROP PARTITION p2020;

详细见：Oracle 分区表设计详解。

14.2 压缩

ALTER TABLE sales MOVE COMPRESS FOR OLTP;
ALTER TABLE sales_archive MOVE COMPRESS FOR ARCHIVE HIGH;

详细见：Oracle 表压缩技术 SQL。

14.3 清理

- 临时表
- 旧数据
- 日志

---

15. 常见坑与排错

15.1 空间不足

- 监控
- 提前扩展
- 自动扩展

15.2 增长异常

- 业务分析
- 异常数据
- 调整

15.3 性能下降

- I/O 瓶颈
- 内存不足
- 扩展

---

16. 最佳实践

1. 历史数据：基线
2. 趋势分析：预测
3. 容量阈值：告警
4. 自动扩展：保护
5. 归档压缩：优化
6. 垂直 + 水平：扩展
7. 云弹性：现代
8. 定期评审：调整
9. 业务沟通：增长
10. 文档化：规划

---

17. 参考资料

[1] Oracle Database 2 Day DBA 19c, “Capacity Planning” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/