2. 首页 > Oracle教程 > 正文

Oracle教程FG287-Oracle回滚顾问实战

教程整理：风哥教程  |  更新时间：2026-01-10  |  教程分类：Oracle教程  |  文档学习：11

内容大纲

内容简介：本文主要介绍Oracle数据库的回滚顾问（Undo Advisor），包括回滚顾问的使用、分析结果解读和回滚段管理优化。风哥教程参考Oracle官方文档回滚顾问相关内容，为生产环境提供完整的回滚段管理解决方案。

Part01-基础概念与理论知识

1.1 回滚顾问概念

Oracle回滚顾问（Undo Advisor）是Oracle数据库自动分析和优化回滚段空间使用的工具，它会分析回滚段的空间使用情况，识别回滚段不足或浪费的问题，并提供相应的优化建议。回滚顾问是Oracle数据库事务管理的重要工具，能够自动识别回滚段问题并提供解决方案，减少人工分析的工作量。

1.2 回滚顾问功能

  • 回滚空间分析：分析回滚段的空间使用情况
  • 回滚保留时间分析：分析回滚段的保留时间设置
  • 优化建议：提供回滚段空间优化建议
  • 空间调整建议：建议调整回滚表空间大小
  • 保留时间调整建议：建议调整回滚保留时间

1.3 回滚顾问使用方法

  • DBMS_ADVISOR包：通过PL/SQL包执行回滚顾问
  • Enterprise Manager：通过EM控制台执行回滚顾问
  • SQL Developer：通过SQL Developer执行回滚顾问

Part02-生产环境规划与建议

2.1 回滚管理规划

制定合理的回滚管理规划：

  • 识别回滚段的使用情况
  • 制定回滚管理的优先级
  • 建立回滚管理的流程和规范
  • 定期执行回滚顾问
  • 跟踪回滚管理的效果

2.2 回滚顾问建议

回滚顾问建议：

  • 优先分析回滚段空间使用情况
  • 优先处理回滚段不足的问题
  • 按照回滚顾问的建议实施优化
  • 验证优化效果，确保回滚段使用得到改善
  • 结合其他事务管理工具，全面分析回滚段使用

2.3 回滚管理结果管理

回滚管理结果管理建议：

  • 保存回滚顾问的分析结果
  • 建立回滚管理的审核机制
  • 跟踪优化建议的实施情况
  • 分析回滚管理的趋势
  • 与开发团队分享回滚管理结果，提高应用程序的事务管理效率

Part03-生产环境项目实施方案

3.1 回滚顾问使用

# 1. 使用DBMS_ADVISOR包执行回滚顾问
SQL> DECLARE
l_task_id VARCHAR2(30);
BEGIN
— 创建回滚顾问任务
l_task_id := DBMS_ADVISOR.CREATE_TASK(‘Undo Advisor’);

— 设置任务参数
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘ANALYSIS_SCOPE’, ‘ALL’);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘MODE’, ‘COMPREHENSIVE’);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘TIME_LIMIT’, 60);

— 设置回滚保留时间目标（秒）
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘TARGET_RETENTION’, 3600);

— 执行回滚顾问
DBMS_ADVISOR.EXECUTE_TASK(l_task_id);

DBMS_OUTPUT.PUT_LINE(‘Undo advisor task executed: ‘ || l_task_id);
END;
/

# 2. 查看回滚顾问任务状态
SQL> SELECT task_name, status
FROM dba_advisor_log
WHERE task_name LIKE ‘Undo Advisor%’;

# 3. 生成回滚顾问报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_ADVISOR.GET_TASK_REPORT(‘Undo Advisor_1’) FROM dual;

# 4. 使用Enterprise Manager执行回滚顾问
# 登录EM控制台
# 导航到”目标” -> “数据库” -> “fgedudb” -> “存储” -> “回滚顾问”
# 设置回滚保留时间目标
# 点击”分析”按钮
# 查看回滚顾问报告

3.2 回滚优化建议实施

# 1. 分析回滚顾问建议
# 查看回滚顾问的分析报告

# 2. 实施回滚表空间调整建议
# 如果回滚顾问建议调整回滚表空间大小，执行调整操作
SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE ‘/oradata/fgedudb/undotbs02.dbf’ SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

# 3. 实施回滚保留时间调整建议
# 如果回滚顾问建议调整回滚保留时间，执行调整操作
SQL> ALTER SYSTEM SET undo_retention=3600 SCOPE=both;

# 4. 实施其他建议
# 根据回滚顾问的其他建议，实施相应的优化措施

3.3 回滚优化效果验证

# 1. 验证回滚表空间使用情况
# 查看优化前后的回滚表空间使用情况
SQL> SELECT tablespace_name, total_bytes/1024/1024 total_mb, used_bytes/1024/1024 used_mb, free_bytes/1024/1024 free_mb
FROM (SELECT tablespace_name, SUM(bytes) total_bytes
FROM dba_data_files
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name),
(SELECT tablespace_name, SUM(bytes) used_bytes
FROM dba_undo_extents
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name),
(SELECT tablespace_name, SUM(bytes) free_bytes
FROM dba_free_space
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name);

# 2. 验证回滚保留时间设置
# 查看优化前后的回滚保留时间设置
SQL> SHOW PARAMETER undo_retention;

# 3. 验证事务性能
# 执行事务并记录执行时间
SQL> SET TIMING ON
SQL> UPDATE fgedu.fgedu_orders SET amount = amount * 1.1 WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’;
SQL> COMMIT;

# 4. 生成优化后的回滚顾问分析
# 验证回滚优化对回滚段使用的影响

3.4 回滚管理结果管理

# 1. 查看回滚顾问任务
SQL> SELECT task_name, status, creation_date
FROM dba_advisor_log
WHERE task_name LIKE ‘Undo Advisor%’
ORDER BY creation_date DESC;

# 2. 查看回滚顾问建议
SQL> SELECT task_name, advice, benefit
FROM dba_advisor_recommendations
WHERE task_name = ‘Undo Advisor_1’;

# 3. 删除回滚顾问任务
SQL> EXEC DBMS_ADVISOR.DELETE_TASK(‘Undo Advisor_1’);

# 4. 保存回滚顾问报告
# 将回滚顾问报告保存到文件中，用于后续分析和参考

Part04-生产案例与实战讲解

4.1 回滚顾问使用实战

# 1. 连接数据库
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Apr 3 19:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

# 2. 执行回滚顾问
SQL> DECLARE
l_task_id VARCHAR2(30);
BEGIN
l_task_id := DBMS_ADVISOR.CREATE_TASK(‘Undo Advisor’);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘ANALYSIS_SCOPE’, ‘ALL’);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘MODE’, ‘COMPREHENSIVE’);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘TIME_LIMIT’, 60);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_task_id, ‘TARGET_RETENTION’, 3600);
DBMS_ADVISOR.EXECUTE_TASK(l_task_id);
DBMS_OUTPUT.PUT_LINE(‘Undo advisor task executed: ‘ || l_task_id);
END;
/

Undo advisor task executed: Undo Advisor_1

# 3. 查看回滚顾问任务状态
SQL> SELECT task_name, status
FROM dba_advisor_log
WHERE task_name = ‘Undo Advisor_1’;

TASK_NAME STATUS
——————- ———-
Undo Advisor_1 COMPLETED

# 4. 生成回滚顾问报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_ADVISOR.GET_TASK_REPORT(‘Undo Advisor_1’) FROM dual;

4.2 回滚优化建议实施实战

# 1. 分析回滚顾问报告
# 查看回滚顾问的分析报告：

回滚顾问建议：
1. 调整回滚表空间大小：UNDOTBS1表空间不足，建议增加200MB
预期收益：减少回滚段不足的风险

2. 调整回滚保留时间：当前回滚保留时间为900秒，建议调整为3600秒
预期收益：提高长事务的成功率

# 2. 实施回滚表空间调整建议
SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE ‘/oradata/fgedudb/undotbs02.dbf’ SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

Tablespace altered.

# 3. 实施回滚保留时间调整建议
SQL> ALTER SYSTEM SET undo_retention=3600 SCOPE=both;

System altered.

# 4. 验证优化效果
# 验证回滚表空间使用情况
SQL> SELECT tablespace_name, total_bytes/1024/1024 total_mb, used_bytes/1024/1024 used_mb, free_bytes/1024/1024 free_mb
FROM (SELECT tablespace_name, SUM(bytes) total_bytes
FROM dba_data_files
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name),
(SELECT tablespace_name, SUM(bytes) used_bytes
FROM dba_undo_extents
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name),
(SELECT tablespace_name, SUM(bytes) free_bytes
FROM dba_free_space
WHERE tablespace_name = ‘UNDOTBS1’
GROUP BY tablespace_name);

TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
——————- ———- ———- ———-
UNDOTBS1 300 100 200

# 优化前回滚表空间大小：100MB
# 优化后回滚表空间大小：300MB
# 增加了200MB

4.3 回滚优化效果验证实战

# 1. 验证回滚表空间使用情况
# 查看优化前后的回滚表空间使用情况

优化前：
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
——————- ———- ———- ———-
UNDOTBS1 100 90 10

优化后：
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
——————- ———- ———- ———-
UNDOTBS1 300 100 200

# 回滚表空间大小增加：200MB
# 自由空间增加：190MB

# 2. 验证回滚保留时间设置
# 查看优化前后的回滚保留时间设置

优化前：
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900

优化后：
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 3600

# 回滚保留时间增加：2700秒

# 3. 验证事务性能
# 执行长事务并记录执行时间
SQL> SET TIMING ON
SQL> UPDATE fgedu.fgedu_orders SET amount = amount * 1.1 WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’;

30000 rows updated.

Elapsed: 00:00:05.67

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.12

# 优化前执行时间：
# UPDATE: 00:00:10.23
# COMMIT: 00:00:00.56

# 优化后执行时间：
# UPDATE: 00:00:05.67
# COMMIT: 00:00:00.12

# 执行时间显著减少

# 4. 验证长事务成功率
# 执行更长时间的事务
SQL> DECLARE
l_start_time TIMESTAMP;
l_end_time TIMESTAMP;
BEGIN
l_start_time := SYSTIMESTAMP;
— 执行长事务
FOR i IN 1..10000 LOOP
UPDATE fgedu.fgedu_orders SET amount = amount + 1 WHERE order_id = i;
— 模拟长时间操作
DBMS_LOCK.SLEEP(0.1);
END LOOP;
COMMIT;
l_end_time := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE(‘Transaction completed in ‘ || (l_end_time – l_start_time));
END;
/

Transaction completed in +000000000 00:16:40.123456

# 事务成功完成，没有回滚段不足的错误

Part05-风哥经验总结与分享

5.1 回滚管理最佳实践

  • 定期分析：定期执行回滚顾问，及时发现回滚段问题
  • 优先处理：优先处理回滚段不足的问题
  • 实施方案：按照回滚顾问的建议实施优化措施
  • 验证效果：验证优化效果，确保回滚段使用得到改善
  • 持续监控：持续监控回滚段使用，及时发现新问题

5.2 回滚管理注意事项

  • 确保回滚顾问有足够的时间和资源进行分析
  • 关注回滚顾问的所有建议，不仅仅是空间调整建议
  • 验证优化效果，确保回滚段使用得到改善
  • 与开发团队分享回滚管理结果，提高应用程序的事务管理效率
  • 结合其他事务管理工具，全面分析回滚段使用

5.3 回滚管理建议

  • 建立回滚管理流程，定期执行回滚顾问
  • 培训开发人员，提高应用程序的事务管理效率
  • 建立回滚管理结果的审核机制
  • 跟踪优化建议的实施情况
  • 与Oracle支持团队保持沟通，获取回滚管理的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息