Oracle autotrace使用说明

本文详细介绍了如何在Oracle数据库中启用Autotrace功能,包括解决常见错误的方法、设置Autotrace的不同命令及其作用,以及Autotrace执行计划和统计信息的列解释,帮助读者深入理解Autotrace的使用。

一、启用Autotrace功能

  任何以SQL*PLUS连接Session都可以用Autotrace,不过还是要做一些设置的,否则可能报错。

  1、报错示例:

 1 SQL :> set autotrace on;
 2 SP2-0613: Unable to verify PLAN_TABLE format or existence
 3 SP2-0611: Error enabling EXPLAIN report
 4 SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
 5 SP2-0611: Error enabling STATISTICS report
 6 
 7 *******************************************************************************
 8 
 9 SQL> set autotrace on;
10 SP2-0613: 无法验证 PLAN_TABLE 格式或实体
11 SP2-0611: 启用EXPLAIN报告时出现错误
12 SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
13 SP2-0611: 启用STATISTICS报告时出现错误

  该错误的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。

  2、解决方法:

  2.1、以SYS用户登录:

1 SQL> conn /as sysdba
2 Connected.

  2.2、运行utlxplan.sql($ORACLE_HOME/rdbms/admin下)脚本创建PLAN_TABLE:
  grant all on plan_table to scott;

  2.3、通过执行plustrace.sql($ORACLE_HOME/sqlplus/admin/plustrace.sql)脚本创建plustrace角色:

  grant plustrace to scott;

  grant select any dictionary to scott;

二、设置Autotrace的命令:

序号命令解释
1SET AUTOTRACE OFF此为默认值,即关闭Autotrace
2SET AUTOTRACE ON产生结果集和解释计划并列出统计
3SET AUTOTRACE ON EXPLAIN显示结果集和解释计划不显示统计
4SET AUTOTRACE TRACEONLY显示解释计划和统计,尽管执行该语句但您将看不到结果集
5SET AUTOTRACE只显示统计

  Eg:SET AUTOTRACE ON

        set timing on

        alter session set time_statistics=true;

三、Autotrace执行计划的各列的涵义

序号列名解释
1ID_PLUS_EXP每一步骤的行号
2PARENT_ID_PLUS_EXP每一步的Parent的级别号
3PLAN_PLUS_EXP实际的每步
4OBJECT_NODE_PLUS_EXPDblink或并行查询时才会用到

四、Autotrace Statistics常用列解释

序号列名解释
1db block gets从buffer cache中读取的block的数量
2consistent gets从buffer cache中读取的undo数据的block的数量
3physical reads从磁盘读取的block的数量
4redo sizeDML生成的redo的大小
5sorts (memory)在内存执行的排序量
6sorts (disk)在磁盘上执行的排序量
<think>好的,我现在需要回答用户关于如何在Oracle数据库使用执行计划来优化长SQL性能的问题。用户之前已经询问过优化长SQL的方法,现在他们想更具体地了解执行计划的使用。首先,我要回顾之前的回答和相关引用,确保信息一致并补充新的细节。 首先,用户提供的引用[1]、[2]、[3]都提到了执行计划的重要性,特别是EXPLAIN PLAN工具。我需要将这些信息整合到当前的回答中,确保引用正确。比如,引用[1]提到EXPLAIN PLAN生成执行计划和指标,引用[2]展示了执行计划中的全表扫描例子,引用[3]强调降低查询成本和提高选择性。 接下来,用户的问题是如何使用执行计划来优化长SQL性能。我需要分步骤说明,从生成执行计划到分析关键指标,再到具体的优化措施。比如,生成执行计划的方法包括EXPLAIN PLAN、AUTOTRACE和DBMS_XPLAN包,这些在引用中都有涉及,需要详细说明。 然后,分析执行计划时要关注全表扫描、连接方式、排序操作和预估行数等,这些在之前的回答和引用中都提到过,比如引用[2]中的全表扫描示例。需要指出如何识别这些问题,并给出优化建议,如创建索引、调整连接方式等。 优化策略部分需要结合之前的建议,比如改写查询逻辑、使用HINTS、设计索引等,同时引用中的内容,如引用[3]提到的选择性对索引的影响。还要提到高级技术如SQL Profile和Baseline,这些可能在之前的回答中没有详细展开,但可以补充进来。 最后,示例代码部分要展示如何实际应用这些优化步骤,比如使用HINTS或改写查询。同时,相关问题需要引导用户深入思考,比如执行计划准确性、动态采样和绑定变量影响,这些在引用和之前内容中有相关点。 需要确保回答结构清晰,步骤明确,引用正确,并且语言符合中文习惯。避免使用Markdown,用自然的分段和标点。检查是否有遗漏的优化点,确保覆盖用户需求的所有方面。</think>### 如何通过执行计划优化 Oracle 长 SQL 性能 #### 一、生成执行计划 执行计划是优化 SQL 性能的核心工具,它能清晰展示 Oracle 优化器如何处理查询。以下是生成执行计划的常用方法: 1. **`EXPLAIN PLAN` 命令** 通过以下语句生成文本格式的执行计划: ```sql EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- 查看结果 [^1] ``` 2. **`AUTOTRACE` 功能** 在 SQL*Plus 中启用自动跟踪,直接显示实际执行计划和统计信息: ```sql SET AUTOTRACE TRACEONLY EXPLAIN; SELECT * FROM orders WHERE order_date > SYSDATE - 30; ``` 3. **动态性能视图 `DBMS_XPLAN`** 结合 `V$SQL_PLAN` 查看已执行 SQL 的实际计划: ```sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abc123', format => 'ALLSTATS LAST')); ``` --- #### 二、分析执行计划的关键指标 执行计划中的以下指标需重点关注: 1. **全表扫描(TABLE ACCESS FULL)** 若大表出现全表扫描,可能因缺乏索引或索引失效。例如: ```sql -- 示例执行计划片段 | Id | Operation | Name | Rows | Cost | |----|--------------------|------------|-------|------| | 0 | SELECT STATEMENT | | 100 | 45 | | 1 | TABLE ACCESS FULL | EMPLOYEES | 100 | 45 | [^2] ``` **优化方案**:为 `WHERE` 或 `JOIN` 条件中的列创建索引。 2. **高成本连接方式** - **嵌套循环(NESTED LOOPS)**:适合小数据集,大表需警惕。 - **哈希连接(HASH JOIN)**:适合大数据量且无索引的场景。 - **排序合并连接(SORT MERGE JOIN)**:需额外排序操作,可能增加开销。 3. **排序操作(SORT ORDER BY)** 若 `ORDER BY` 导致大量数据排序,考虑添加索引或减少返回行数。 4. **预估行数(ROWS)与实际行数偏差** 若 `ROWS` 列与实际情况差异大,说明统计信息过时,需更新: ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'ORDERS'); ``` --- #### 三、针对性优化策略 根据执行计划结果采取具体措施: 1. **减少全表扫描** - **复合索引**:为多列查询条件创建联合索引。例如: ```sql CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary); ``` - **函数索引**:针对含函数的条件(如 `UPPER(last_name)`)建立索引。 2. **优化连接顺序** 调整 `FROM` 子句中的表顺序,优先过滤小表或高选择性条件。 3. **使用 HINTS 引导优化器** 强制使用特定索引或连接方式: ```sql SELECT /*+ INDEX(emp idx_emp_dept) */ emp_name FROM employees emp WHERE department_id = 20; ``` 4. **减少子查询复杂度** 将多层嵌套子查询改写为 `WITH` 子句或临时表: ```sql WITH dept_summary AS ( SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id ) SELECT * FROM dept_summary WHERE avg_sal > 5000; ``` --- #### 四、高级调优技术 1. **SQL Profile 与 Baseline** - **SQL Profile**:通过 `SQL Tuning Advisor` 生成优化建议,固定高效执行计划。 - **Baseline**:防止优化器因统计信息变化选择次优计划。 2. **动态采样(Dynamic Sampling)** 对未收集统计信息的表启用动态采样,提高执行计划准确性: ```sql SELECT /*+ DYNAMIC_SAMPLING(employees 4) */ * FROM employees WHERE hire_date < SYSDATE - 365; ``` --- ### 示例:优化长 SQL 的分页查询 **原始低效查询**: ```sql SELECT * FROM (SELECT rownum rn, t.* FROM (SELECT * FROM orders ORDER BY order_date DESC) t) WHERE rn BETWEEN 1000 AND 2000; ``` **优化后版本**: ```sql SELECT * FROM ( SELECT /*+ INDEX(orders idx_order_date) */ order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date DESC) rn FROM orders ) WHERE rn BETWEEN 1000 AND 2000; -- 利用窗口函数减少排序开销 [^3] ``` --- ### 相关问题 1. **如何验证执行计划的准确性?是否存在因统计信息过时导致计划偏差的典型案例?** 2. **动态采样在哪些场景下会显著提升执行计划质量?它是否会影响查询性能?** 3. **当 SQL 语句使用绑定变量时,执行计划会如何变化?如何避免绑定变量导致的执行计划不稳定?** --- 通过执行计划分析,可系统性地定位性能瓶颈并针对性优化。建议结合 `AWR` 报告和 `SQL Monitor` 工具进行长期跟踪[^1][^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值