SQL执行计划分析
执行计划是Oracle优化器为执行SQL语句而选择的步骤序列。理解执行计划对于性能优化至关重要。
查看执行计划
-- 使用EXPLAIN PLAN查看执行计划
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;
-- 显示执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 使用AUTOTRACE查看执行计划和统计信息
SET AUTOTRACE ON EXPLAIN; -- 只显示执行计划
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;
SET AUTOTRACE ON STATISTICS; -- 只显示统计信息
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;
SET AUTOTRACE ON; -- 显示执行计划和统计信息
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;
SET AUTOTRACE OFF; -- 关闭AUTOTRACE
执行计划解读
-- 示例执行计划解读
----------------------------------------------------------
Plan hash value: 1514187147
----------------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |
| 1 | NESTED LOOPS | | 10 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 10 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 |
| 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
----------------------------------------------------------
-- 执行计划各部分说明：
-- Id: 操作步骤编号
-- Operation: 操作类型
-- Name: 涉及的对象名称
-- Rows: 估计的行数
-- Bytes: 估计的数据量
-- Cost: 操作的成本估算
优化器模式
-- 查看当前优化器模式
SHOW PARAMETER optimizer_mode;
-- 设置优化器模式
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; -- 优化全部行（默认）
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10; -- 优化前10行
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100; -- 优化前100行
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS; -- 优化第一行
ALTER SESSION SET OPTIMIZER_MODE = CHOOSE; -- 基于统计信息选择（已废弃）
1.2 索引优化策略
索引是提高查询性能的重要手段，但不当的索引也会降低性能。
索引选择性分析
-- 分析列的选择性
SELECT
COUNT(DISTINCT department_id) AS distinct_values,
COUNT(*) AS total_rows,
ROUND(COUNT(DISTINCT department_id)/COUNT(*), 4) AS selectivity
FROM employees;
-- 查看索引使用情况
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;
-- 监控索引使用
ALTER INDEX idx_employee_last_name MONITORING USAGE;
-- 查看索引监控结果
SELECT index_name, table_name, monitoring, used
FROM v$object_usage
WHERE index_name = 'IDX_EMPLOYEE_LAST_NAME';
-- 停止监控
ALTER INDEX idx_employee_last_name NOMONITORING USAGE;
复合索引优化
-- 创建复合索引时列的顺序很重要
-- 将选择性高的列放在前面
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary, last_name);
-- 查询条件与索引列顺序匹配
SELECT * FROM employees
WHERE department_id = 60 AND salary > 8000 AND last_name LIKE 'A%';
-- 前缀索引使用
SELECT * FROM employees
WHERE department_id = 60 AND salary > 8000; -- 可以使用索引
SELECT * FROM employees
WHERE salary > 8000; -- 无法使用索引（跳过了前导列）
索引维护
-- 重建索引
ALTER INDEX idx_employee_last_name REBUILD;
-- 在线重建索引（不影响其他操作）
ALTER INDEX idx_employee_last_name REBUILD ONLINE;
-- 收缩索引
ALTER INDEX idx_employee_last_name SHRINK SPACE;
-- 分析索引结构
ANALYZE INDEX idx_employee_last_name VALIDATE STRUCTURE;
SELECT height, blocks, lf_rows, del_lf_rows FROM index_stats;
-- 删除未使用的索引
DROP INDEX idx_unused_index;
1.3 统计信息管理
统计信息是优化器选择最佳执行计划的基础。
收集统计信息
-- 收集表统计信息
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 => DBMS_STATS.AUTO_DEGREE
);
-- 收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'HR',
indname => 'IDX_EMPLOYEE_LAST_NAME'
);
-- 收集整个模式的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'HR',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.AUTO_DEGREE,
cascade => TRUE
);
-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.AUTO_DEGREE
);
管理统计信息
-- 查看统计信息收集时间
SELECT table_name, num_rows, blocks, avg_row_len,
last_analyzed, sample_size
FROM user_tables
WHERE table_name = 'EMPLOYEES';
-- 查看列统计信息
SELECT column_name, num_distinct, density, num_nulls,
last_analyzed, sample_size
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';
-- 查看索引统计信息
SELECT index_name, num_rows, distinct_keys, leaf_blocks,
last_analyzed
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
-- 锁定统计信息（防止自动收集）
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
-- 解锁统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');
-- 删除统计信息
EXEC DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');
自动统计信息收集
-- 查看自动统计信息收集任务
SELECT job_name, enabled, state, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB';
-- 修改自动统计信息收集窗口
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'MONDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=MON;byhour=22;byminute=0;bysecond=0'
);
END;
/
-- 禁用自动统计信息收集
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
1.4 查询重写和优化技巧
使用绑定变量
-- 不使用绑定变量（硬解析）
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;
SELECT * FROM employees WHERE employee_id = 102;
-- 使用绑定变量（软解析）
VARIABLE emp_id NUMBER;
EXEC :emp_id := 100;
SELECT * FROM employees WHERE employee_id = :emp_id;
EXEC :emp_id := 101;
SELECT * FROM employees WHERE employee_id = :emp_id;
优化连接查询
-- 使用适当的连接方法
-- 1. 嵌套循环连接（NL）- 适用于小表或有索引的大表
SELECT /*+ USE_NL(e d) */ e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 2. 哈希连接（HJ）- 适用于大表连接
SELECT /*+ USE_HASH(e d) */ e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 3. 排序合并连接（SM）- 适用于已排序的数据
SELECT /*+ USE_MERGE(e d) */ e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
子查询优化
-- 相关子查询优化为连接
-- 优化前：相关子查询
SELECT employee_id, first_name, last_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
-- 优化后：使用连接
SELECT e.employee_id, e.first_name, e.last_name
FROM employees e,
(SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) d
WHERE e.department_id = d.department_id
AND e.salary > d.avg_sal;
-- 使用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);
分页查询优化
-- 传统分页（效率低）
SELECT * FROM (
SELECT ROWNUM rn, t.* FROM (
SELECT * FROM employees ORDER BY employee_id
) t WHERE ROWNUM <= 20
) WHERE rn > 10;
-- 优化分页（使用ROWNUM）
SELECT * FROM (
SELECT ROWNUM rn, employee_id, first_name, last_name, salary
FROM (
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY employee_id
)
WHERE ROWNUM <= 20
) WHERE rn > 10;
-- Oracle 12c及以上版本的分页语法
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
1.5 内存结构优化
SGA优化
-- 查看SGA配置
SHOW SGA;
-- 查看SGA组件详细信息
SELECT * FROM v$sga;
SELECT * FROM v$sgastat;
-- 调整SGA大小
ALTER SYSTEM SET sga_max_size = 2G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 1G SCOPE=BOTH;
-- 调整共享池大小
ALTER SYSTEM SET shared_pool_size = 256M SCOPE=BOTH;
-- 调整缓冲区缓存大小
ALTER SYSTEM SET db_cache_size = 512M SCOPE=BOTH;
-- 调整大型池大小
ALTER SYSTEM SET large_pool_size = 64M SCOPE=BOTH;
-- 调整Java池大小
ALTER SYSTEM SET java_pool_size = 32M SCOPE=BOTH;
PGA优化
-- 查看PGA使用情况
SELECT * FROM v$pgastat;
-- 调整PGA聚合限制
ALTER SYSTEM SET pga_aggregate_target = 512M SCOPE=BOTH;
-- 查看工作区统计信息
SELECT * FROM v$workarea_histogram;
-- 查看PGA建议
SELECT * FROM v$pga_target_advice;
缓冲区缓存优化
-- 查看缓冲区缓存命中率
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads');
-- 计算缓冲区缓存命中率
SELECT ROUND(
(1 - (SUM(DECODE(name, 'physical reads', value, 0)) /
(SUM(DECODE(name, 'db block gets', value, 0)) +
SUM(DECODE(name, 'consistent gets', value, 0))))) * 100, 2) AS buffer_cache_hit_ratio
FROM v$sysstat;
-- 查看热点块
SELECT owner, object_name, subobject_name, object_type,
value AS touches
FROM v$segment_statistics
WHERE statistic_name = 'logical reads'
AND value > 0
ORDER BY value DESC;
-- 查看缓冲区缓存建议
SELECT size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size');
1.6 本章小结
本章详细介绍了Oracle数据库性能优化的各个方面，包括执行计划分析、索引优化、统计信息管理、查询优化技巧和内存结构优化等内容。掌握这些优化技能可以帮助数据库管理员和开发人员显著提升数据库性能。
练习题
- 分析一个复杂查询的执行计划，找出性能瓶颈并提出优化建议
- 为employees表设计合适的索引策略，并验证索引的使用效果
- 定期收集和管理数据库统计信息，观察对查询性能的影响
- 优化一个包含子查询的SQL语句，将其改写为连接查询
- 调整SGA和PGA参数，监控内存使用情况和性能变化
- 实现一个高效的分页查询方案，比较不同方法的性能差异
