架构层优化

就是根据业务的特性选择数据库结构

表3-1 架构类型的使用场景表

架构类型	核心特性	适用场景	性能优势
数据守护集群	主备实时同步，秒级切换	高可用核心业务	故障自动切换，RPO=0
读写分离集群	自动分流只读操作到备库	读多写少业务（OA/ERP）	降低主库负载，提升吞吐量
数据共享集群(DMDSC)	多实例共享存储	金融核心/高频交易	负载均衡，横向扩展
分布式集群(DMDPC)	分布式事务+分析	海量数据/HTAP	高扩展，透明分片

根据业务的机器，实际的环境，调整参数。

当面临高并发访问压力时，可以适当调大共享内存池的大小并增加内存池的个数，同时提升缓冲区的分区数量，以此减少线程间的锁竞争并提高数据吞吐能力；若业务涉及复杂的查询逻辑或处理海量数据对象，则建议扩大回收缓冲区以优化中间结果的处理，并增加字典缓冲区的大小来加速元数据访问；在CPU资源充足且需要并行处理大量计算任务的场景下，可以将工作线程数设置为CPU核数或其倍数，并显著增加哈希连接与分组操作的全局及局部缓存容量，从而加速大规模数据的关联分析与聚合运算；此外，在执行建索引或大规模排序任务时，临时调大排序缓冲区能有效减少磁盘I/O开销；最后，针对特定负载类型（如OLTP），应确保优化器标志位设置正确，并在进行性能瓶颈排查或深度调优阶段开启详细监控，同时保持视图上拉、过滤条件下推及哈希连接等高级优化策略的启用状态，以确保执行计划始终处于最优路径。

参数修改的方法：

-- 动态修改（立即生效，重启后失效）
SP_SET_PARA_VALUE(1, 'BUFFER', 4096);

-- 静态修改（需重启生效）
ALTER SYSTEM SET 'WORKER_THREADS' = 8 SPFILE;

-- 会话级修改（仅当前会话）
ALTER SESSION SET 'SORT_BUF_SIZE' = 50;

参数优化

根据业务的机器，实际的环境，调整参数。

当面临高并发访问压力时，可以适当调大共享内存池的大小并增加内存池的个数，同时提升缓冲区的分区数量，以此减少线程间的锁竞争并提高数据吞吐能力；若业务涉及复杂的查询逻辑或处理海量数据对象，则建议扩大回收缓冲区以优化中间结果的处理，并增加字典缓冲区的大小来加速元数据访问；在CPU资源充足且需要并行处理大量计算任务的场景下，可以将工作线程数设置为CPU核数或其倍数，并显著增加哈希连接与分组操作的全局及局部缓存容量，从而加速大规模数据的关联分析与聚合运算；此外，在执行建索引或大规模排序任务时，临时调大排序缓冲区能有效减少磁盘I/O开销；最后，针对特定负载类型（如OLTP），应确保优化器标志位设置正确，并在进行性能瓶颈排查或深度调优阶段开启详细监控，同时保持视图上拉、过滤条件下推及哈希连接等高级优化策略的启用状态，以确保执行计划始终处于最优路径。

参数修改的方法：

-- 动态修改（立即生效，重启后失效）

SP_SET_PARA_VALUE(1, 'BUFFER', 4096);



-- 静态修改（需重启生效）

ALTER SYSTEM SET 'WORKER_THREADS' = 8 SPFILE;



-- 会话级修改（仅当前会话）

ALTER SESSION SET 'SORT_BUF_SIZE' = 50;

优化方法

定位慢SQL

日志跟踪

# 修改dm.ini

SVR_LOG = 1



# 配置sqllog.ini

BUF_TOTAL_SIZE = 10240

BUF_SIZE = 1024

ASYNC_FLUSH = 1              # 异步刷新，减少性能影响

SQL_TRACE_MASK = 2:3:23:24:25 # 记录DML/DDL/错误/执行时间

MIN_EXEC_TIME = 1000         # 只记录执行超过1秒的SQL

视图监控

-- 查看当前正在执行的慢SQL

SELECT SESS_ID, SQL_TEXT, DATEDIFF(SS, LAST_SEND_TIME, SYSDATE) exec_time

FROM V$SESSIONS WHERE STATE = 'ACTIVE';



-- 查看历史慢SQL（需ENABLE_MONITOR=1）

SELECT * FROM V$LONG_EXEC_SQLS WHERE EXEC_TIME > 10000;

执行计划分析

查看执行计划

-- 预估执行计划

EXPLAIN SELECT * FROM employees WHERE department_id = 10;



-- 真实执行计划（含实际统计信息）

SET AUTOTRACE TRACEONLY;

SELECT * FROM employees WHERE department_id = 10;

逻辑读(Logical Reads)：从缓冲区读取的页数，应尽量减少

物理读(Physical Reads)：从磁盘读取的页数，反映缓存命中率

执行时间(Exec Time)：实际耗时，重点关注

返回行数(Rows Processed)：与实际需求对比，检查是否全表扫描

表3-2 常见操作符解析

操作符	全称	含义	优化建议
CSCN	Cluster Index Scan	聚集索引全表扫描	大表避免，考虑添加索引
SSEK	Secondary Index Seek	二级索引定位+回表	回表开销大时考虑覆盖索引
CSEK	Cluster Index Seek	聚集索引定位	最高效，无需回表
SSCN	Secondary Index Scan	二级索引全扫描	仅访问索引列时使用
BLKUP	Block Lookup	回表操作	减少SELECT *，使用覆盖索引
NEST LOOP	Nested Loop Join	嵌套循环连接	驱动表要小，被驱动表要有索引
HASH JOIN	Hash Join	哈希连接	大数据量无索引时使用，需足够内存
MERGE JOIN	Merge Join	归并连接	两表连接列都有索引时使用

ET工具深度分析

开启ET功能

SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 1);

SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 1);

步骤为：1.执行SQL后获取执行号，2.点击执行号查看ET结果，3.分析每个操作符的实际开销（时间、内存、IO）

优化后关闭

SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 0);

SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 0);

索引优化

索引设计原则：选择性原则，索引列选择性（不同值/总行数）应>0.1，接近1最优；最左前缀原则，复合索引查询条件必须从最左列开始；避免冗余，已有索引(A,B)，则(A)冗余，但(B,A)不冗余；控制数量，单表索引不超过5个，过多影响DML性能。

索引类型选择：B树索引，默认类型，适合等值查询和范围查询；聚集索引，数据按索引键排序，只能有一个，适合范围查询；函数索引，对列进行函数运算后建立索引，如UPPER(name)；位图索引，适合低基数列（性别、状态等），OLAP场景

索引维护

-- 重建索引（消除碎片）

ALTER INDEX idx_emp_name REBUILD;



-- 合并索引（在线操作，不锁表）

ALTER INDEX idx_emp_name COALESCE;



-- 监控索引使用情况（长期未使用考虑删除）

SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'IDX_EMP_NAME';

统计信息维护

信息收集：

-- 全库收集（耗时较长，建议在业务低峰执行）

DBMS_STATS.GATHER_DATABASE_STATS;



-- 单表收集

DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'EMPLOYEES');



-- 索引统计信息收集

DBMS_STATS.GATHER_INDEX_STATS('SYSDBA', 'IDX_EMP_DEPT');



-- 分区表收集（仅收集特定分区）

DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'SALES', PARTNAME => 'P202401');

信息查看：

-- 表统计信息

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED

FROM USER_TABLES WHERE TABLE_NAME = 'EMPLOYEES';



-- 列统计信息（直方图）

SELECT COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, HISTOGRAM

FROM USER_TAB_COLS WHERE TABLE_NAME = 'EMPLOYEES';

定期进行维护任务

-- 每周：收集统计信息

EXEC DBMS_STATS.GATHER_DATABASE_STATS;



-- 每月：重建碎片率>30%的索引

SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME IN (

    SELECT INDEX_NAME FROM USER_IND_STATISTICS WHERE DEL_LF_ROWS/LF_ROWS > 0.3

);



-- 每季度：清理历史回滚段

-- 自动清理由UNDO_RETENTION参数控制，默认900秒

https://eco.dameng.com