Oracle性能优化 #

性能优化目标
├── 响应时间
│   └── 减少查询执行时间
├── 吞吐量
│   └── 提高单位时间处理量
├── 资源利用
│   └── 降低CPU、内存、IO消耗
└── 可扩展性
    └── 支持更多并发用户

优化层次
├── 应用层
│   ├── SQL优化
│   └── 业务逻辑优化
├── 数据库层
│   ├── 索引优化
│   ├── 内存配置
│   └── 参数调优
└── 系统层
    ├── 操作系统配置
    └── 存储优化

-- 使用EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 使用AUTOTRACE
SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 10;

SET AUTOTRACE TRACEONLY;
SELECT * FROM employees WHERE department_id = 10;

-- 使用DBMS_XPLAN.DISPLAY_CURSOR
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

-- 执行计划关键信息
-- 1. 操作类型（TABLE ACCESS, INDEX SCAN, JOIN等）
-- 2. 访问路径（FULL, INDEX, ROWID等）
-- 3. 连接方法（NESTED LOOPS, HASH JOIN, MERGE JOIN）
-- 4. 成本（COST）
-- 5. 行数（ROWS）
-- 6. 字节数（BYTES）

-- 查看详细执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED'));

-- 全表扫描
-- TABLE ACCESS FULL
-- 适用：小表、无选择性条件

-- 索引扫描
-- INDEX RANGE SCAN：范围扫描
-- INDEX UNIQUE SCAN：唯一扫描
-- INDEX FULL SCAN：全索引扫描
-- INDEX FAST FULL SCAN：快速全扫描

-- 连接方法
-- NESTED LOOPS：嵌套循环，适合小表驱动大表
-- HASH JOIN：哈希连接，适合大表连接
-- MERGE JOIN：排序合并，适合已排序数据

-- 创建合适的索引
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 复合索引顺序
-- 选择性高的列在前
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary);

-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- 使用索引提示
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

-- 避免SELECT *
SELECT employee_id, first_name, last_name FROM employees;

-- 使用WHERE替代HAVING
-- 不推荐
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING department_id = 10;

-- 推荐
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id = 10
GROUP BY department_id;

-- 使用EXISTS替代IN
-- 不推荐
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- 推荐
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1700);

-- 使用UNION ALL替代UNION
-- UNION会去重，UNION ALL不会
SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE department_id = 20;

-- 使用绑定变量减少硬解析
-- 不推荐（每次硬解析）
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;

-- 推荐（使用绑定变量）
VARIABLE v_emp_id NUMBER;
EXEC :v_emp_id := 100;
SELECT * FROM employees WHERE employee_id = :v_emp_id;
EXEC :v_emp_id := 101;
SELECT * FROM employees WHERE employee_id = :v_emp_id;

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- 收集Schema统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- 收集统计信息选项
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
);

-- 查看表统计信息
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables;

-- 查看列统计信息
SELECT column_name, num_distinct, num_nulls, density, histogram
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

-- 查看索引统计信息
SELECT index_name, num_rows, distinct_keys, clustering_factor
FROM user_indexes;

-- 查看快照
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

-- 生成AWR报告
-- 使用脚本
-- @?/rdbms/admin/awrrpt.sql

-- 或使用DBMS_WORKLOAD_REPOSITORY
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid => 100,  -- 开始快照ID
    l_eid => 101   -- 结束快照ID
));

AWR报告关键部分
├── Load Profile
│   └── 系统负载概况
├── Instance Efficiency Percentages
│   └── 实例效率指标
├── Top 10 Foreground Events
│   └── 等待事件排名
├── SQL Statistics
│   └── SQL执行统计
├── IO Statistics
│   └── IO性能统计
└── Wait Events
    └── 等待事件详情

-- 当前等待事件
SELECT event, count(*)
FROM v$session_wait
GROUP BY event
ORDER BY 2 DESC;

-- 系统等待事件
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
ORDER BY time_waited DESC;

-- 等待事件类别
SELECT wait_class, event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

-- 常见等待事件及解决方法
-- db file sequential read：索引扫描，优化索引
-- db file scattered read：全表扫描，优化查询
-- log file sync：提交等待，优化日志写入
-- buffer busy waits：缓冲区争用，优化热点块
-- latch: cache buffers chains：缓冲区链争用，减少逻辑读

-- 查看SGA配置
SELECT * FROM v$sga;
SELECT * FROM v$sgainfo;

-- 调整SGA大小
ALTER SYSTEM SET sga_target = 2G SCOPE = SPFILE;

-- 调整各组件大小
ALTER SYSTEM SET shared_pool_size = 500M SCOPE = SPFILE;
ALTER SYSTEM SET db_cache_size = 1G SCOPE = SPFILE;
ALTER SYSTEM SET log_buffer = 100M SCOPE = SPFILE;

-- 查看PGA使用
SELECT * FROM v$pgastat;

-- 调整PGA大小
ALTER SYSTEM SET pga_aggregate_target = 500M SCOPE = SPFILE;

-- 查看PGA建议
SELECT * FROM v$pga_target_advice;