2. 首页 > Oracle教程 > 正文

Oracle教程FG285-Oracle SQL访问顾问实战

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

内容大纲

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

Part01-基础概念与理论知识

1.1 SQL访问顾问概念

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

1.2 SQL访问顾问功能

  • 访问路径分析：分析SQL语句的访问路径
  • 索引建议：建议创建或修改索引
  • 分区建议：建议表分区策略
  • 物化视图建议：建议创建物化视图
  • 访问路径优化：优化SQL语句的访问路径

1.3 SQL访问顾问使用方法

  • DBMS_ADVISOR包：通过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_ADVISOR包执行SQL访问顾问
SQL> DECLARE
l_task_id VARCHAR2(30);
BEGIN
— 创建访问顾问任务
l_task_id := DBMS_ADVISOR.CREATE_TASK(‘SQL Access 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);

— 添加SQL语句
DBMS_ADVISOR.ADD_SQL_TUNING_SET(
task_name => l_task_id,
sqlset_name => ‘MY_SQLSET’,
sqlset_owner => ‘SYS’
);

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

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

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

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

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

3.2 SQL访问建议实施

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

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

# 3. 实施分区建议
# 如果访问顾问建议表分区，执行分区操作
SQL> ALTER TABLE fgedu.fgedu_orders MODIFY
PARTITION BY RANGE (order_date) (
PARTITION p_2026_q1 VALUES LESS THAN (TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q2 VALUES LESS THAN (TO_DATE(‘2026-07-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q3 VALUES LESS THAN (TO_DATE(‘2026-10-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q4 VALUES LESS THAN (TO_DATE(‘2027-01-01’, ‘YYYY-MM-DD’))
);

# 4. 实施物化视图建议
# 如果访问顾问建议创建物化视图，执行创建物化视图的SQL语句
SQL> CREATE MATERIALIZED VIEW fgedu.mv_order_summary
AS SELECT order_date, COUNT(*) order_count, SUM(amount) total_amount
FROM fgedu.fgedu_orders
GROUP BY order_date;

# 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 ‘SQL Access Advisor%’
ORDER BY creation_date DESC;

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

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

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

Part04-生产案例与实战讲解

4.1 SQL访问顾问使用实战

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

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Apr 3 17: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> EXEC DBMS_SQLTUNE.CREATE_SQLSET(‘MY_SQLSET’, ‘SQL statements for access advisor’);

# 3. 添加SQL语句到SQL集
SQL> DECLARE
l_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN l_cursor FOR
SELECT sql_id, plan_hash_value, sql_text
FROM v$sql
WHERE elapsed_time > 1000000
AND executions > 5
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

DBMS_SQLTUNE.LOAD_SQLSET(‘MY_SQLSET’, l_cursor);
END;
/

# 4. 执行SQL访问顾问
SQL> DECLARE
l_task_id VARCHAR2(30);
BEGIN
l_task_id := DBMS_ADVISOR.CREATE_TASK(‘SQL Access 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.ADD_SQL_TUNING_SET(
task_name => l_task_id,
sqlset_name => ‘MY_SQLSET’,
sqlset_owner => ‘SYS’
);
DBMS_ADVISOR.EXECUTE_TASK(l_task_id);
DBMS_OUTPUT.PUT_LINE(‘Access advisor task executed: ‘ || l_task_id);
END;
/

# 5. 查看访问顾问任务状态
SQL> SELECT task_name, status
FROM dba_advisor_log
WHERE task_name LIKE ‘SQL Access Advisor%’;

TASK_NAME STATUS
——————- ———-
SQL Access Advisor_1 COMPLETED

# 6. 生成访问顾问报告
SQL> SET LONG 1000000
SQL> SELECT DBMS_ADVISOR.GET_TASK_REPORT(‘SQL Access Advisor_1’) FROM dual;

4.2 SQL访问建议实施实战

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

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

2. 分区表：对FGEDU_ORDERS表按order_date列进行范围分区
预期收益：减少50%的I/O时间

3. 创建物化视图：创建订单汇总物化视图
预期收益：减少80%的汇总查询时间

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

Index created.

# 3. 实施分区建议
SQL> ALTER TABLE fgedu.fgedu_orders MODIFY
PARTITION BY RANGE (order_date) (
PARTITION p_2026_q1 VALUES LESS THAN (TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q2 VALUES LESS THAN (TO_DATE(‘2026-07-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q3 VALUES LESS THAN (TO_DATE(‘2026-10-01’, ‘YYYY-MM-DD’)),
PARTITION p_2026_q4 VALUES LESS THAN (TO_DATE(‘2027-01-01’, ‘YYYY-MM-DD’))
);

Table altered.

# 4. 实施物化视图建议
SQL> CREATE MATERIALIZED VIEW fgedu.mv_order_summary
AS SELECT order_date, COUNT(*) order_count, SUM(amount) total_amount
FROM fgedu.fgedu_orders
GROUP BY order_date;

Materialized view created.

# 5. 验证优化效果
# 验证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 |
—————————————————————————————

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> SET TIMING ON
SQL> SELECT order_date, order_count, total_amount
FROM fgedu.mv_order_summary
WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-04-01’;

90 rows selected.

Elapsed: 00:00:00.12

# 优化前执行时间：00:00:05.67
# 优化后执行时间：00:00:00.12
# 执行时间减少：98%

# 4. 验证分区表查询
# 执行分区表查询并记录执行时间
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders PARTITION (p_2026_q1);

10000 rows selected.

Elapsed: 00:00:00.89

# 优化前执行时间：00:00:03.45
# 优化后执行时间：00:00:00.89
# 执行时间减少：74%

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

相关推荐

联系我们

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

微信号：itpux-com

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