Oracle教程FG284-Oracle SQL调优顾问实战

内容大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

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

Part01-基础概念与理论知识

1.1 SQL调优顾问概念

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

1.2 SQL调优顾问功能

SQL语句分析：分析SQL语句的执行计划和性能
性能问题识别：识别SQL语句的性能瓶颈
优化建议提供：提供具体的SQL优化建议
索引建议：建议创建或修改索引
SQL重写建议：建议重写SQL语句以提高性能

1.3 SQL调优顾问使用方法

DBMS_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性能

2.3 SQL调优结果管理

SQL调优结果管理建议：

保存SQL调优顾问的分析结果
建立SQL调优的审核机制
跟踪优化建议的实施情况
分析SQL调优的趋势
与开发团队分享SQL调优结果，提高SQL编写质量

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

3.1 SQL调优顾问使用

# 1. 使用DBMS_SQLTUNE包执行SQL调优顾问
SQL> DECLARE
l_sql_id VARCHAR2(13);
l_task_id VARCHAR2(30);
BEGIN
— 获取SQL ID
SELECT sql_id INTO l_sql_id
FROM v$sql
WHERE sql_text LIKE ‘%SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN%’
AND rownum = 1;

— 创建调优任务
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => l_sql_id,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘TUNE_ORDER_QUERY’,
description => ‘Tune order query’
);

— 执行调优任务
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task_id);

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

# 2. 查看调优任务状态
SQL> SELECT task_name, status
FROM dba_advisor_log
WHERE task_name = ‘TUNE_ORDER_QUERY’;

# 3. 生成调优报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNE_ORDER_QUERY’) FROM dual;

# 4. 使用Enterprise Manager执行SQL调优顾问
# 登录EM控制台
# 导航到”目标” -> “数据库” -> “fgedudb” -> “性能” -> “SQL调优顾问”
# 输入SQL语句或选择SQL ID
# 点击”调优”按钮
# 查看调优报告

3.2 SQL调优建议实施

# 1. 分析调优建议
# 查看SQL调优顾问的调优报告

# 2. 实施索引建议
# 如果调优顾问建议创建索引，执行创建索引的SQL语句
SQL> CREATE INDEX idx_fgedu_orders_order_date ON fgedu.fgedu_orders(order_date);

# 3. 实施SQL重写建议
# 如果调优顾问建议重写SQL语句，修改应用程序中的SQL语句

# 4. 实施统计信息收集建议
# 如果调优顾问建议收集统计信息，执行统计信息收集
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘FGEDU’, ‘FGEDU_ORDERS’);

# 5. 实施其他建议
# 根据调优顾问的其他建议，实施相应的优化措施

3.3 SQL调优效果验证

# 1. 验证SQL执行计划
# 查看优化前后的SQL执行计划
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;
SQL> SELECT * FROM TABLE(dbms_xplan.display);

# 2. 验证SQL执行时间
# 执行SQL语句并记录执行时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;

# 3. 验证SQL资源消耗
# 查看SQL语句的资源消耗
SQL> SELECT * FROM v$sqlstats WHERE sql_id = ‘1234567890abcdef’;

# 4. 生成优化后的ADDM分析
# 验证SQL调优对整体数据库性能的影响

3.4 SQL调优结果管理

# 1. 查看调优任务
SQL> SELECT task_name, status, creation_date
FROM dba_advisor_log
WHERE task_name LIKE ‘TUNE_%’
ORDER BY creation_date DESC;

# 2. 查看调优建议
SQL> SELECT task_name, advice, benefit
FROM dba_advisor_recommendations
WHERE task_name = ‘TUNE_ORDER_QUERY’;

# 3. 删除调优任务
SQL> EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(‘TUNE_ORDER_QUERY’);

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

Part04-生产案例与实战讲解

4.1 SQL调优顾问使用实战

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

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

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

# 2. 识别需要调优的SQL语句
SQL> SELECT sql_id, sql_text, elapsed_time, executions
FROM v$sql
WHERE elapsed_time > 1000000
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

SQL_ID SQL_TEXT ELAPSED_TIME EXECUTIONS
————- —————————————- ———— ———-
1234567890ab SELECT * FROM fgedu.fgedu_orders WHERE o 10000000000 10
cdef rder_date BETWEEN ‘2026-01-01’ AND ‘2026-0
4-01’

# 3. 执行SQL调优顾问
SQL> DECLARE
l_task_id VARCHAR2(30);
BEGIN
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘1234567890abcdef’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘TUNE_ORDER_QUERY’,
description => ‘Tune order query’
);

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task_id);

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

Tuning task executed: TUNE_ORDER_QUERY

# 4. 查看调优任务状态
SQL> SELECT task_name, status
FROM dba_advisor_log
WHERE task_name = ‘TUNE_ORDER_QUERY’;

TASK_NAME STATUS
——————- ———-
TUNE_ORDER_QUERY COMPLETED

# 5. 生成调优报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNE_ORDER_QUERY’) FROM dual;

4.2 SQL调优建议实施实战

# 1. 分析调优报告
# 查看SQL调优顾问的调优报告：

调优建议：
1. 创建索引：为FGEDU_ORDERS表的order_date列创建索引
预期收益：减少90%的执行时间

2. 收集统计信息：收集FGEDU_ORDERS表的统计信息
预期收益：减少10%的执行时间

# 2. 实施索引建议
SQL> CREATE INDEX idx_fgedu_orders_order_date ON fgedu.fgedu_orders(order_date);

Index created.

# 3. 实施统计信息收集建议
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘FGEDU’, ‘FGEDU_ORDERS’);

PL/SQL procedure successfully completed.

# 4. 验证优化效果
# 验证SQL执行计划
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;
SQL> SELECT * FROM TABLE(dbms_xplan.display);

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 |
—————————————————————————————

# 5. 验证SQL执行时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;

10000 rows selected.

Elapsed: 00:00:01.23

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

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

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

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

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

SQL> SELECT sql_id, elapsed_time, cpu_time, buffer_gets, executions
FROM v$sql
WHERE sql_id = ‘1234567890abcdef’;

SQL_ID ELAPSED_TIME CPU_TIME BUFFER_GETS EXECUTIONS
————- ————- ———- ———– ———-
1234567890ab 1230000 800000 1000 10
cdef

# 优化前资源消耗：
# Elapsed Time: 10560000 microseconds
# CPU Time: 8000000 microseconds
# Buffer Gets: 100000

# 优化后资源消耗：
# Elapsed Time: 1230000 microseconds
# CPU Time: 800000 microseconds
# Buffer Gets: 1000

# 资源消耗显著减少

Part05-风哥经验总结与分享

5.1 SQL调优最佳实践

定期分析：定期执行SQL调优顾问，及时发现SQL性能问题
优先处理：优先调优高频率执行和高消耗资源的SQL语句
实施方案：按照SQL调优顾问的建议实施优化措施
验证效果：验证优化效果，确保SQL性能得到提升
持续监控：持续监控SQL性能，及时发现新问题

5.2 SQL调优注意事项

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

5.3 SQL调优建议

建立SQL调优流程，定期执行SQL调优顾问
培训开发人员，提高SQL编写质量
建立SQL调优结果的审核机制
跟踪优化建议的实施情况
与Oracle支持团队保持沟通，获取SQL调优的最佳实践

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

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

from oracle:www.itpux.com

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