Oracle 性能优化 + 完整可做实验手册

整体框架（DBA 标准体系）

1. 优化思路 & 层次
2. AWR / ASH / ADDM 实验
3. SQL 执行计划、索引、访问路径
4. 等待事件、锁、latch、并发
5. 优化实战实验（建索引、收集统计、改写SQL）
6. 完整诊断流程

全程不废话、全部可直接上机做。

---

一、Oracle 性能优化 4 层（必背）

1. 系统层：CPU、内存、I/O、网络
2. 实例层：SGA、PGA、Latch、队列、锁
3. 应用层：SQL、解析、游标、事务
4. 数据层：索引、统计信息、分区、表设计

优化优先级：
SQL 优化 > 索引 > 统计信息 > 配置 > 硬件

---

二、实验环境准备

创建测试表，制造负载：

-- 创建测试表
create table test as select * from dba_objects;

-- 插入大量数据（制造负载）
insert into test select * from test;
insert into test select * from test;
commit;

-- 注意：先**不建索引、不收集统计信息**

手动制造 AWR 快照（实验必须）

-- 初始快照
exec dbms_workload_repository.create_snapshot();

-- 运行慢SQL
select count(*) from test where object_name = 'TEST';

-- 结束快照
exec dbms_workload_repository.create_snapshot();

---

三、AWR / ASH / ADDM 实验

实验1：生成 AWR 报告

@?/rdbms/admin/awrrpt.sql

输入：

• html
• 1
• 选择刚才 2 个 snap_id

看重点：

• Top foreground events
• Top SQL by elapsed time
• Physical reads
• Parse CPU

实验2：生成 ASH 报告

@?/rdbms/admin/ashrpt.sql

选择最近 10 分钟。

ASH 看什么：

• 哪个 SQL 最耗时间
• 哪个等待事件最高
• 哪个块、对象最热
• 短尖刺性能问题（AWR 看不到）

实验3：生成 ADDM 报告

@?/rdbms/admin/addmrpt.sql

ADDM 一定会给出：

• 表没有索引
• SQL 消耗过高
• 建议建索引
• 建议收集统计信息

结论：ADDM = 自动DBA，直接告诉你优化点

---

四、执行计划与 SQL 优化实验

实验4：查看执行计划（3种最重要）

1. EXPLAIN PLAN（预估）

explain plan for
select * from test where object_id = 12345;

select * from table(dbms_xplan.display());

你会看到：

TABLE ACCESS FULL --> 全表扫描

2. 真实执行计划（已运行）

-- 获取 sql_id
select sql_id, sql_text from v$sql
where sql_text like '%where object_id =%' and sql_text not like '%v$sql%';

-- 查看真实计划
select * from table(dbms_xplan.display_cursor('这里填SQL_ID',0,'ALLSTATS LAST'));

3. AWR 里的历史执行计划

select * from table(dbms_xplan.display_awr('SQL_ID'));

---

实验5：索引优化实验

步骤1：无索引 → 全表扫描

select * from test where object_id=12345;

执行计划：TABLE ACCESS FULL

步骤2：创建索引

create index idx_test_object_id on test(object_id);

步骤3：再次查看执行计划

explain plan for select * from test where object_id=12345;
select * from table(dbms_xplan.display());

现在变成：

INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID

速度极大提升

---

实验6：统计信息优化实验

Oracle 靠统计信息选择最优计划。

1. 无统计信息 → 可能选错执行计划

-- 清空统计
exec dbms_stats.delete_table_stats('SCOTT','TEST');

执行计划可能不准确、估算行数错误。

2. 收集统计信息

exec dbms_stats.gather_table_stats(
   ownname=>'SCOTT',
   tabname=>'TEST',
   cascade=>true,         -- 收集索引
   estimate_percent=>100,
   method_opt=>'FOR ALL COLUMNS SIZE AUTO');

效果：
执行计划行数精确、选择最优索引、路径正确。

---

五、等待事件实验（性能核心）

实验7：查看 TOP 等待事件

-- 历史等待 AWR
select event, total_waits, time_waited_micro
from dba_hist_system_event
order by time_waited_micro desc;

-- 当前等待
select event, count(*)
from v$session_wait
where wait_class <> 'Idle'
group by event order by 2 desc;

高频重要等待事件（必须懂）

• db file sequential read 索引读
• db file scattered read 全表扫描
• direct path read 直接路径读（临时表、并行）
• enq: TX - row lock contention 行锁等待
• latch: shared pool 共享池竞争
• library cache pin/hit 解析竞争

---

六、锁与并发实验

实验8：模拟行锁竞争

窗口1：

update test set object_name='A' where object_id=123;
commit;
update test set object_name='A' where object_id=123;
-- 不提交

窗口2：

update test set object_name='B' where object_id=123;
-- 会卡住

查看锁与阻塞

-- 查看阻塞
select blocking_session, sid, event, sql_id
from v$session where blocking_session is not null;

-- 查看锁对象
select * from v$lock;

你会看到：

• enq: TX - row lock contention

---

七、完整性能优化实战流程（考试/面试满分）

标准 6 步：

1. 看 ASH 现在最高等待事件是什么
2. 看 AWR 看整体负载、TOP SQL、TOP 等待
3. 看 ADDM 获取优化建议
4. 拿出 TOP SQL 的 SQL_ID
5. 看执行计划：全表扫描？索引？排序？
6. 优化：建索引、收集统计、改写SQL、绑定变量

---

八、最核心优化结论（背下来）

1. 90% 性能问题都是 SQL 问题
2. 全表扫描 ≠ 坏，小表可以；大表必须索引
3. 无统计信息 → 执行计划一定错
4. 索引不是越多越好，DML 会变慢
5. AWR 看全局，ASH 看瞬间，ADDM 看结论
6. 等待事件 = 数据库在告诉你它在等什么

---