Oracle OLAP 性能优化
Oracle OLAP 性能优化
适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07
1. 概述
OLAP 场景性能优化[1]：
特点：
- 大数据量
- 复杂查询
- 聚合统计
- 历史数据
2. 分区表
2.1 Range 分区
CREATE TABLE sales (
id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p2020 VALUES LESS THAN (...),
PARTITION p2021 VALUES LESS THAN (...),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);2.2 组合分区
CREATE TABLE sales (...)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id) (
PARTITION p2020 VALUES LESS THAN (...) (
SUBPARTITION p2020_s1,
SUBPARTITION p2020_s2,
...
),
...
);详细见：Oracle 分区表性能优化。
3. 并行查询
3.1 启用
SELECT /*+ PARALLEL(s 8) */ SUM(amount)
FROM sales s
WHERE sale_date >= '2026-01-01';3.2 自动 DOP
ALTER SYSTEM SET parallel_degree_policy = AUTO;
ALTER SYSTEM SET parallel_min_time_threshold = 10;详细见：Oracle 并行查询 Parallel Query。
4. 物化视图
4.1 聚合物化视图
CREATE MATERIALIZED VIEW mv_sales_by_dept
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT
dept_id,
TO_CHAR(sale_date, 'YYYY-MM') AS month,
SUM(amount) AS total_sales,
COUNT(*) AS cnt
FROM sales
GROUP BY dept_id, TO_CHAR(sale_date, 'YYYY-MM');4.2 Query Rewrite
ALTER SYSTEM SET query_rewrite_enabled = TRUE;
ALTER SYSTEM SET query_rewrite_integrity = enforced;详细见：Oracle 物化视图性能优化。
5. In-Memory
5.1 启用
ALTER SYSTEM SET inmemory_size = 100G SCOPE=SPFILE;
ALTER TABLE sales INMEMORY
MEMCOMPRESS FOR QUERY HIGH
PRIORITY CRITICAL;5.2 列存
-- 选择性列
ALTER TABLE sales INMEMORY (sale_date, amount, product_id);详细见：Oracle 12c In-Memory Column Store。
6. Star Schema
6.1 Star Join
-- 事实表 + 维度表
SELECT
d.dept_name, p.product_name, SUM(s.amount)
FROM sales s, departments d, products p
WHERE s.dept_id = d.id AND s.product_id = p.id
AND s.sale_date >= '2026-01-01'
GROUP BY d.dept_name, p.product_name;6.2 Bitmap 索引
-- 维度表低基数列
CREATE BITMAP INDEX idx_sales_dept ON sales(dept_id);
CREATE BITMAP INDEX idx_sales_product ON sales(product_id);6.3 Star Transformation
ALTER SYSTEM SET star_transformation_enabled = TRUE;7. 压缩
7.1 OLTP 压缩
CREATE TABLE sales (...) COMPRESS FOR OLTP;7.2 ARCHIVE 压缩
ALTER TABLE sales_old MOVE COMPRESS FOR ARCHIVE HIGH;详细见：Oracle 表压缩技术。
8. 分析函数
-- 高效分析
SELECT
dept_id,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY dept_id ORDER BY sale_date) AS cum_amount,
AVG(amount) OVER (PARTITION BY dept_id) AS avg_amount,
RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rank
FROM sales
WHERE sale_date >= '2026-01-01';详细见：Oracle 分析函数。
9. SQL 优化
9.1 大表 JOIN
-- Hash Join
SELECT /*+ USE_HASH(s d) PARALLEL(s 8) PARALLEL(d 4) */ *
FROM sales s, departments d
WHERE s.dept_id = d.id;9.2 聚合
-- 减少中间结果
SELECT
dept_id,
SUM(amount)
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY dept_id;9.3 分页
-- 12c+
SELECT * FROM (
SELECT ... ORDER BY ...
) OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;10. 内存优化
10.1 PGA
-- 大排序
ALTER SYSTEM SET pga_aggregate_target = 32G;
ALTER SYSTEM SET pga_aggregate_limit = 64G;10.2 临时表空间
-- 大临时表空间
CREATE TEMPORARY TABLESPACE temp_big
TEMPFILE '/u01/temp01.dbf' SIZE 50G;详细见：Oracle PGA 与排序优化。
11. ETL 优化
11.1 数据加载
INSERT /*+ APPEND PARALLEL(t 8) */ INTO target t
SELECT /*+ PARALLEL(s 8) */ * FROM source s;
COMMIT;11.2 NOLOGGING
ALTER TABLE target NOLOGGING;
-- 加载
ALTER TABLE target LOGGING;详细见：Oracle 数据加载优化。
12. Exadata
- Smart Scan
- Storage Index
- HCC 压缩
- In-Memory详细见：Oracle Exadata 性能优化。
13. 监控
13.1 长查询
SELECT
sid,
opname,
sofar,
totalwork,
ROUND(sofar / totalwork * 100, 2) AS pct,
time_remaining
FROM v$session_longops
WHERE time_remaining > 0;13.2 SQL Monitor
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;详细见：Oracle SQL Monitoring 实时监控。
14. 常见问题
14.1 查询慢
- 全表扫描
- 无分区裁剪
- 无并行
- 无物化视图14.2 排序慢
- PGA 不足
- 临时表空间满14.3 JOIN 慢
- Hash Join 内存
- 并行不足15. 最佳实践
- 分区表：大数据基础
- 并行查询：性能
- 物化视图：聚合
- In-Memory：极致
- Star Schema：维度建模
- Bitmap 索引：低基数
- 压缩：减少 I/O
- PGA 充足：排序
- ETL 优化：加载
- Exadata：极致性能
16. 参考资料
[1] Oracle Database Data Warehousing Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/