SQL执行计划之sql_trace

### SQL执行计划之sql_trace详解 #### 一、SQL Trace简介 SQL Trace 是 Oracle 提供的一种强大工具，用于记录 SQL 语句的执行过程。它能够帮助数据库管理员和开发人员详细了解 SQL 语句是如何被 Oracle 数据库处理的，从而更好地进行性能调优。 #### 二、SQL Trace命令详解 SQL Trace 的开启与配置涉及到几个关键参数，具体如下： 1. **timed_statistics** - 该参数用于控制是否收集执行统计信息。默认情况下，如果设置为 FALSE，则 SQL Trace 不会收集时间相关的统计信息；若设置为 TRUE，则会收集这些信息。 ```sql ALTER SESSION SET timed_statistics = true; ``` 2. **max_dump_file_size** - 这个参数决定了 dump 文件的最大大小，同时也间接影响了 SQL Trace 文件的大小。如果设置为 unlimited，则意味着不限制文件大小。 ```sql ALTER SESSION SET max_dump_file_size = unlimited; ``` 3. **tracefile_identifier** - 用于标识 trace 文件的一个特定字符串，可以用来更容易地识别某个特定的 trace 文件。 ```sql ALTER SESSION SET tracefile_identifier = 'my_trace_session'; ``` #### 三、SQL Trace的启用与禁用 SQL Trace 可以在系统级别或会话级别上启用或禁用。下面是一些示例命令： 1. **系统级别启用/禁用 SQL Trace** ```sql ALTER SYSTEM SET sql_trace = true; -- 启用系统级别的 SQL Trace ALTER SYSTEM SET sql_trace = false; -- 禁用系统级别的 SQL Trace ``` 2. **会话级别启用/禁用 SQL Trace** ```sql ALTER SESSION SET sql_trace = true; -- 启用当前会话的 SQL Trace ALTER SESSION SET sql_trace = false; -- 禁用当前会话的 SQL Trace ``` 3. **指定会话启用 SQL Trace** - 若要对特定会话启用 SQL Trace，可以使用 `dbms_system.set_sql_trace_in_session` 函数，这需要知道目标会话的 SID 和 SERIAL#。 ```sql EXECUTE dbms_system.set_sql_trace_in_session(sid, serial#, true); ``` 4. **查看正在跟踪的会话** - 使用以下查询可以获取正在被跟踪的会话的信息： ```sql SELECT DISTINCT (m.sid), p.pid, p.tracefile FROM v$mystat m, v$session s, v$process p WHERE m.sid = s.sid AND s.paddr = p.addr; ``` #### 四、使用 tkprof 分析 TRC 文件 tkprof 是 Oracle 提供的一个工具，用于分析由 SQL Trace 生成的 `.trc` 文件，并生成易于理解的报告。 1. **tkprof 命令行参数解析** - **Usage:** `tkprof <tracefile> <outputfile> [explain=] [table=] [print=] [insert=] [sys=] [sort=]` - `<tracefile>`: 需要分析的 trace 文件路径。 - `<outputfile>`: 输出文件的路径。 - `[explain=]`: 设置后，对于每个 SQL 语句都会提供 EXPLAIN PLAN 的结果。 - `[table=]`: 指定输出文件中的表名。 - `[print=]`: 默认情况下显示所有 SQL 语句，如果设置为 `NSQL`，则仅显示非 SQL 语句。 - `[insert=]`: 如果设置此选项，则会将统计数据插入到数据库中。 - `[sys=]`: 如果设置为 `yes`，则不包括用户 SQL 语句。 - `[sort=]`: 排序依据，可以选择按时间排序(`elapsed time`)、按获取数据的时间(`fetching`)等。 2. **EXPLAIN PLAN 与 SQL 执行计划** - 当使用 `explain=` 参数时，tkprof 会为每一个 SQL 语句生成 EXPLAIN PLAN 结果，这些结果可以帮助分析 SQL 语句的执行计划，了解每一步操作的具体情况，包括使用的索引、访问的数据量等信息。 3. **TRACE 文件分析** - 在分析 TRACE 文件时，tkprof 会根据提供的参数对数据进行分类和汇总。例如，可以使用 `sort=elapsed time` 对 SQL 语句按照执行时间进行排序，这样就可以快速找到那些耗时较长的操作。 4. **输出文件格式** - 输出文件通常包含以下几部分信息： - **CALLS**: SQL 语句的调用次数。 - **PARSE CALLS**: 解析次数。 - **EXECUTIONS**: 执行次数。 - **FETCHES**: 获取数据的次数。 - **ELAPSED TIME**: SQL 语句总的执行时间。 - **CPU TIME**: CPU 执行时间。 - **ROWS PROCESSED**: 处理的行数。 - **SORT (ROW SOURCE)**: 排序操作的行源。 - **SORT (MEMORY)**: 内存中的排序操作。 - **SORT (DISK)**: 磁盘上的排序操作。 - **Optimizer mode**: 优化器模式，如 ALL_ROWS。 #### 五、总结 SQL Trace 是 Oracle 数据库中一个非常有用的工具，通过它可以深入了解 SQL 语句在数据库中的执行细节。利用 SQL Trace 结合 tkprof 工具，可以有效地分析 SQL 语句的性能问题，进而进行针对性的优化。无论是日常维护还是性能调优，掌握 SQL Trace 和 tkprof 的使用都是非常重要的。