# 实施方案
1. 配置AWR报告
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 7*24*60, — 7天
interval => 30, — 30分钟
topnsql => 20 — 前20条SQL
);

2. 手动创建AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

3. 生成AWR报告
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

# 输入报告类型：html
# 输入开始快照ID：100
# 输入结束快照ID：101
# 输入报告文件名：awrrpt.html

4. 分析AWR报告中的等待事件
# 查看Top 5等待事件
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
———————————— ———– ———– —— —— ———-
db file sequential read 10000 5000 500 40.0 User I/O
log file sync 5000 2500 500 20.0 Commit
db file scattered read 2500 1250 500 10.0 User I/O
enqueue 1000 500 500 4.0 Concurrency
CPU time 1000 8.0 CPU

5. 分析I/O等待事件
SQL> SELECT
event,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE wait_class = ‘User I/O’
ORDER BY time_waited DESC;

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ———– ———– ————
db file sequential read 10000 50000 5.0
db file scattered read 5000 25000 5.0

6. 分析SQL语句
SQL> SELECT
sql_id,
sql_text,
elapsed_time,
buffer_gets,
disk_reads
FROM v$sql
WHERE disk_reads > 1000
ORDER BY disk_reads DESC
LIMIT 10;

SQL_ID SQL_TEXT ELAPSED_TIME BUFFER_GETS DISK_READS
————- —————————————- ———— ———– ———–
abcd1234 SELECT * FROM orders WHERE customer_id = :cust 1000000 10000 5000

7. 优化措施
– 为orders表的customer_id列创建索引
– 优化SQL语句，减少不必要的列查询
– 增加数据库服务器的I/O性能

# 实施效果
– 数据库性能提高50%
– I/O等待事件减少60%
– SQL执行时间减少70%
– 系统稳定性提高

# 优化方案
1. 配置ASH报告
SQL> ALTER SYSTEM SET ash_sampling_interval = 1 SCOPE=BOTH;

2. 启用实时监控
SQL> CREATE OR REPLACE PROCEDURE monitor_wait_events IS
BEGIN
LOOP
INSERT INTO wait_event_history
SELECT
SYSDATE,
sw.event,
sw.wait_class,
COUNT(*)
FROM v$session_wait sw
WHERE sw.wait_class != ‘Idle’
GROUP BY sw.event, sw.wait_class;
COMMIT;
DBMS_LOCK.SLEEP(60); — 每60秒采集一次
END LOOP;
END;
/

3. 生成ASH报告
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

# 输入报告类型：html
# 输入开始时间：SYSDATE – 1/24
# 输入结束时间：SYSDATE
# 输入报告文件名：ashrpt.html

4. 分析ASH报告中的等待事件
# 查看Top 5等待事件
Top 5 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
———————————— ———– ———– —— —— ———-
db file sequential read 10000 5000 500 40.0 User I/O
log file sync 5000 2500 500 20.0 Commit
db file scattered read 2500 1250 500 10.0 User I/O
enqueue 1000 500 500 4.0 Concurrency
CPU time 1000 8.0 CPU

5. 分析SQL语句
SQL> SELECT
h.sql_id,
h.sql_text,
COUNT(*) AS execution_count,
SUM(h.wait_time + h.time_waited) / 100 AS total_wait_time_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time BETWEEN SYSDATE – 1/24 AND SYSDATE
AND h.wait_class != ‘Idle’
GROUP BY h.sql_id, h.sql_text
ORDER BY total_wait_time_seconds DESC
LIMIT 10;

SQL_ID SQL_TEXT EXECUTION_COUNT TOTAL_WAIT_TIME_SECONDS
————- —————————————- ————— ———————-
abcd1234 SELECT * FROM orders WHERE customer_id = :cust 1000 500

6. 优化措施
– 为orders表的customer_id列创建索引
– 优化SQL语句，减少不必要的列查询
– 增加数据库服务器的I/O性能
– 配置连接池，减少连接开销

# 优化效果
– 等待事件分析效率提高70%
– 性能问题发现时间减少80%
– 数据库性能提高50%
– 系统稳定性提高

# 分析步骤
1. 查看当前等待事件
SQL> SELECT
sw.event,
sw.wait_class,
COUNT(*)
FROM v$session_wait sw
WHERE sw.wait_class != ‘Idle’
GROUP BY sw.event, sw.wait_class
ORDER BY COUNT(*) DESC;

EVENT WAIT_CLASS COUNT(*)
—————————— ——————– ——–
db file sequential read User I/O 50
db file scattered read User I/O 25
log file sync Commit 10

2. 生成AWR报告
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

# 输入报告类型：html
# 输入开始快照ID：100
# 输入结束快照ID：101
# 输入报告文件名：awrrpt.html

3. 分析AWR报告中的等待事件
# 查看Top 5等待事件
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
———————————— ———– ———– —— —— ———-
db file sequential read 50000 25000 500 60.0 User I/O
log file sync 10000 5000 500 12.0 Commit
db file scattered read 5000 2500 500 6.0 User I/O
enqueue 2000 1000 500 2.4 Concurrency
CPU time 2000 4.8 CPU

4. 分析SQL语句
SQL> SELECT
sql_id,
sql_text,
elapsed_time,
buffer_gets,
disk_reads
FROM v$sql
WHERE disk_reads > 1000
ORDER BY disk_reads DESC
LIMIT 10;

SQL_ID SQL_TEXT ELAPSED_TIME BUFFER_GETS DISK_READS
————- —————————————- ———— ———– ———–
abcd1234 SELECT * FROM orders WHERE status = ‘PENDING’ 5000000 50000 25000

5. 分析表统计信息
SQL> SELECT
table_name,
last_analyzed
FROM dba_tables
WHERE table_name = ‘ORDERS’;

TABLE_NAME LAST_ANALYZED
—————————— ——————-
ORDERS 2026-01-01 00:00:00

# 问题原因
– 表统计信息过时
– 缺少合适的索引
– SQL语句未优化
– I/O性能不足

# 解决方案
1. 收集表统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘ORDERS’);

2. 为orders表的status列创建索引
SQL> CREATE INDEX ord_status_idx ON orders(status);

3. 优化SQL语句
SQL> CREATE OR REPLACE PROCEDURE get_pending_orders IS
BEGIN
SELECT order_id, customer_id, amount
FROM orders
WHERE status = ‘PENDING’;
END;
/

4. 增加数据库服务器的I/O性能
– 增加磁盘缓存
– 优化存储配置
– 考虑使用SSD

5. 验证问题解决
SQL> SELECT
sw.event,
sw.wait_class,
COUNT(*)
FROM v$session_wait sw
WHERE sw.wait_class != ‘Idle’
GROUP BY sw.event, sw.wait_class
ORDER BY COUNT(*) DESC;

EVENT WAIT_CLASS COUNT(*)
—————————— ——————– ——–
db file sequential read User I/O 10
db file scattered read User I/O 5
log file sync Commit 2

# 解决效果
– 数据库性能恢复正常
– I/O等待事件减少80%
– SQL执行时间减少90%
– 系统负载降低