2. 首页 > Oracle教程 > 正文

Oracle教程FG291-Oracle自动SQL调优实战

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

内容大纲

内容简介：本文主要介绍Oracle数据库的自动SQL调优功能，包括自动SQL调优的配置、使用、分析和管理。风哥教程参考Oracle官方文档自动SQL调优相关内容，为生产环境提供完整的自动SQL调优解决方案。

Part01-基础概念与理论知识

1.1 自动SQL调优概念

Oracle自动SQL调优是Oracle数据库自动分析和优化SQL语句的工具，它会自动识别性能问题SQL，分析其执行计划和性能瓶颈，并提供相应的优化建议。自动SQL调优是Oracle数据库性能调优的重要工具，能够自动识别和解决SQL性能问题，减少人工分析的工作量。

1.2 自动SQL调优功能

  • 自动SQL识别：自动识别性能问题SQL
  • 执行计划分析：分析SQL的执行计划
  • 性能瓶颈识别：识别SQL的性能瓶颈
  • 优化建议生成：生成SQL优化建议
  • 优化实施：自动实施优化建议

1.3 自动SQL调优使用方法

  • DBMS_AUTO_SQLTUNE包：通过PL/SQL包执行自动SQL调优
  • Enterprise Manager：通过EM控制台执行自动SQL调优
  • SQL Developer：通过SQL Developer执行自动SQL调优

Part02-生产环境规划与建议

2.1 自动SQL调优规划

制定合理的自动SQL调优规划：

  • 配置自动SQL调优参数
  • 制定自动SQL调优计划
  • 建立自动SQL调优的流程和规范
  • 定期执行自动SQL调优
  • 跟踪自动SQL调优的效果

2.2 自动SQL调优建议

自动SQL调优建议：

  • 根据数据库负载情况调整自动SQL调优参数
  • 定期执行自动SQL调优，及时发现和解决SQL性能问题
  • 按照自动SQL调优的建议实施优化
  • 验证优化效果，确保SQL性能得到提升
  • 结合其他性能工具，全面分析SQL性能

2.3 自动SQL调优结果管理

自动SQL调优结果管理建议：

  • 保存自动SQL调优的分析结果
  • 建立自动SQL调优结果的审核机制
  • 跟踪优化建议的实施情况
  • 分析SQL性能趋势，预测潜在问题
  • 与开发团队分享自动SQL调优结果，提高应用程序性能

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

3.1 自动SQL调优配置与管理

# 1. 查看自动SQL调优配置
SQL> SHOW PARAMETER optimizer_capture_sql_plan_baselines;
SQL> SHOW PARAMETER optimizer_use_sql_plan_baselines;
SQL> SHOW PARAMETER sql_plan_baseline_capture_mode;

# 2. 启用自动SQL调优
SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=true SCOPE=both;
SQL> ALTER SYSTEM SET optimizer_use_sql_plan_baselines=true SCOPE=both;

# 3. 配置自动SQL调优任务
SQL> EXEC DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => ‘AUTO_TUNING_MODE’,
value => ‘FULL’
);

# 4. 执行自动SQL调优任务
SQL> EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK();

# 5. 查看自动SQL调优任务状态
SQL> SELECT task_name, status, start_time, end_time
FROM dba_advisor_log
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

3.2 自动SQL调优分析

# 1. 查看自动SQL调优建议
SQL> SELECT task_name, execution_name, status, start_time, end_time
FROM dba_advisor_executions
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

# 2. 生成自动SQL调优报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
execution_name => ‘EXECUTION_1’
) FROM dual;

# 3. 分析自动SQL调优建议
# 查看自动SQL调优报告中的优化建议，包括：
# – SQL执行计划分析
# – 性能瓶颈识别
# – 优化建议（索引创建、SQL重写等）
# – 预期性能提升

# 4. 实施自动SQL调优建议
# 根据自动SQL调优报告中的建议，实施相应的优化措施

3.3 自动SQL调优结果管理

# 1. 查看自动SQL调优历史
SQL> SELECT execution_name, start_time, end_time, status
FROM dba_advisor_executions
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

# 2. 查看自动SQL调优建议
SQL> SELECT task_name, execution_name, object_name, recommendation_type, benefit
FROM dba_advisor_recommendations
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

# 3. 实施自动SQL调优建议
SQL> EXEC DBMS_AUTO_SQLTUNE.IMPLEMENT_TUNING_TASK(
task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
execution_name => ‘EXECUTION_1’
);

# 4. 保存自动SQL调优报告
# 将自动SQL调优报告保存到文件中，用于后续分析和参考

3.4 自动SQL调优监控

# 1. 监控自动SQL调优任务
SQL> SELECT task_name, status, progress
FROM dba_advisor_tasks
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’;

# 2. 监控SQL计划基线
SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
ORDER BY created DESC;

# 3. 监控SQL性能变化
SQL> SELECT sql_id, plan_hash_value, executions, elapsed_time, buffer_gets
FROM v$sql
WHERE sql_id = ‘1234567890abcdef’;

# 4. 定期生成自动SQL调优报告
# 定期生成自动SQL调优报告，分析SQL性能趋势

Part04-生产案例与实战讲解

4.1 自动SQL调优配置与管理实战

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

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Apr 3 23: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调优配置
SQL> SHOW PARAMETER optimizer_capture_sql_plan_baselines;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_capture_sql_plan_baselines boolean FALSE

SQL> SHOW PARAMETER optimizer_use_sql_plan_baselines;

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_sql_plan_baselines boolean TRUE

# 3. 启用自动SQL调优
SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=true SCOPE=both;

System altered.

# 4. 配置自动SQL调优任务
SQL> EXEC DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => ‘AUTO_TUNING_MODE’,
value => ‘FULL’
);

PL/SQL procedure successfully completed.

# 5. 执行自动SQL调优任务
SQL> EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK();

PL/SQL procedure successfully completed.

# 6. 查看自动SQL调优任务状态
SQL> SELECT task_name, status, start_time, end_time
FROM dba_advisor_log
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

TASK_NAME STATUS START_TIME END_TIME
—————————— ———- ——————– ——————–
SYS_AUTO_SQL_TUNING_TASK COMPLETED 2026-04-03 23:05:00 2026-04-03 23:10:00

4.2 自动SQL调优分析与实施实战

# 1. 查看自动SQL调优执行
SQL> SELECT execution_name, start_time, end_time, status
FROM dba_advisor_executions
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
ORDER BY start_time DESC;

EXECUTION_NAME START_TIME END_TIME STATUS
——————– ——————– ——————– ———-
EXEC_20260403_230500 2026-04-03 23:05:00 2026-04-03 23:10:00 COMPLETED

# 2. 生成自动SQL调优报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
execution_name => ‘EXEC_20260403_230500’
) FROM dual;

# 3. 分析自动SQL调优报告
# 查看自动SQL调优报告的主要部分：

– 报告概览：
Task Name : SYS_AUTO_SQL_TUNING_TASK
Execution : EXEC_20260403_230500
Type : AUTO_TUNING
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 2026-04-03 23:05:00
Completed : 2026-04-03 23:10:00

– 优化建议：
SQL ID : 1234567890abc
Plan Hash : 1234567890
Execution Count : 1000
Elapsed Time : 123.45 seconds
CPU Time : 67.89 seconds
Buffer Gets : 1234567

建议1: 创建索引
索引名称: IDX_FGEDU_ORDERS_ORDER_DATE
表名: FGEDU.FGEDU_ORDERS
列名: ORDER_DATE
预期收益: 减少90%的执行时间

建议2: 收集统计信息
对象: FGEDU.FGEDU_ORDERS
预期收益: 减少20%的执行时间

# 4. 实施自动SQL调优建议
SQL> EXEC DBMS_AUTO_SQLTUNE.IMPLEMENT_TUNING_TASK(
task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
execution_name => ‘EXEC_20260403_230500’
);

PL/SQL procedure successfully completed.

# 5. 验证优化效果
# 查看优化后的SQL执行情况
SQL> SELECT sql_id, plan_hash_value, executions, elapsed_time, buffer_gets
FROM v$sql
WHERE sql_id = ‘1234567890abc’;

4.3 自动SQL调优结果验证实战

# 1. 验证SQL执行计划
# 查看优化前后的SQL执行计划

优化前执行计划：
Plan hash value: 1234567890

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10000 | 500K | 5000 (1)| 00:01:00 |
|* 1 | TABLE ACCESS FULL| FGEDU_ORDERS| 10000 | 500K | 5000 (1)| 00:01:00 |
——————————————————————————-

优化后执行计划：
Plan hash value: 9876543210

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 10000 | 500K | 100 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_ORDERS | 10000 | 500K | 100 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_ORDERS_ORDER_DATE| 10000 | | 10 (0)| 00:00:01 |
—————————————————————————————

# 2. 验证SQL执行时间
# 执行SQL语句并记录执行时间

优化前执行时间：00:00:10.56
优化后执行时间：00:00:01.23
执行时间减少：88%

# 3. 验证SQL资源消耗
# 查看SQL语句的资源消耗

优化前：
SQL_ID PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME BUFFER_GETS
————- ——————– ———- ———— ————
1234567890abc 1234567890 1000 123450000 123456700

优化后：
SQL_ID PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME BUFFER_GETS
————- ——————– ———- ———— ————
1234567890abc 9876543210 1000 14820000 12345670

# 资源消耗显著减少

# 4. 验证SQL计划基线
# 查看SQL计划基线的状态
SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE ‘%SELECT * FROM fgedu.fgedu_orders WHERE order_date%’;

SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED
—————— ————————– —————- ——– ——- ——-
SQL_1234567890abc SQL_PLAN_1234567890abc_1 AUTO-CAPTURE YES YES NO
SQL_1234567890abc SQL_PLAN_1234567890abc_2 AUTO-TUNE YES YES NO

Part05-风哥经验总结与分享

5.1 自动SQL调优最佳实践

  • 启用自动SQL调优：启用自动SQL调优功能，及时发现和解决SQL性能问题
  • 定期执行：定期执行自动SQL调优任务，保持SQL性能的最佳状态
  • 分析建议：认真分析自动SQL调优的建议，选择适合的优化措施
  • 验证效果：验证优化效果，确保SQL性能得到提升
  • 持续监控：持续监控SQL性能，及时发现新的性能问题

5.2 自动SQL调优注意事项

  • 确保自动SQL调优有足够的时间和资源进行分析
  • 关注自动SQL调优的所有建议，不仅仅是索引建议
  • 验证优化效果，确保SQL性能得到提升
  • 与开发团队分享自动SQL调优结果，提高应用程序性能
  • 结合其他性能工具，全面分析SQL性能

5.3 自动SQL调优建议

  • 建立自动SQL调优流程，定期执行自动SQL调优任务
  • 培训DBA，提高自动SQL调优结果分析能力
  • 建立自动SQL调优结果的审核机制
  • 跟踪优化建议的实施情况
  • 与Oracle支持团队保持沟通，获取自动SQL调优的最佳实践

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

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

from oracle:www.itpux.com

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

相关推荐

联系我们

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

微信号：itpux-com

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