Oracle SQL 执行历史|极客笔记

Oracle SQL 执行历史

1. 引言

在使用 Oracle 数据库进行开发和维护过程中，我们经常需要查看 SQL 语句的执行历史。执行历史记录可以帮助我们分析代码性能、排查问题，并提供了一个基于 SQL 查询语句的跟踪工具。本文将详细介绍如何获取和分析 Oracle SQL 执行历史。

2. 获取执行历史

要获取 Oracle SQL 执行历史，我们需要使用 Oracle 提供的性能监视工具和视图。主要的工具包括：

会话追踪（Session Tracing）
SQL 跟踪（SQL Trace）
自动跟踪会话（Automatic SQL Tuning）

这些工具可以帮助我们捕获和记录 SQL 语句的执行历史，以及相关的性能指标和统计信息。接下来，我们将分别介绍这些工具的使用方法。

2.1 会话追踪

会话追踪是一种捕获和记录会话执行历史的高级工具。通过会话追踪，我们可以获得详细的会话信息，包括 SQL 语句、执行计划、绑定变量、等待事件和锁信息等。下面是使用会话追踪的示例代码：

-- 启用会话追踪
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SESSION_TRACE';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 运行需要追踪的 SQL 语句
SELECT * FROM employees;

-- 关闭会话追踪
ALTER SESSION SET EVENTS '10046 trace name context off';

在上面的示例代码中，我们使用 ALTER SESSION 语句来启用和关闭会话追踪。在启用会话追踪后，执行的 SQL 语句将会被追踪和记录。追踪文件通常保存在 Oracle 数据库服务器的特定目录中，我们可以使用 V$DIAG_INFO 视图获取追踪文件的位置。

2.2 SQL 跟踪

SQL 跟踪是一种基于事件的跟踪工具，可以用于捕获和记录 SQL 语句的执行历史。与会话追踪不同，SQL 跟踪是针对特定的 SQL 语句进行跟踪和记录。下面是使用 SQL 跟踪的示例代码：

-- 创建跟踪会话
ALTER SESSION SET SQL_TRACE = TRUE;

-- 运行需要跟踪的 SQL 语句
SELECT * FROM employees;

-- 关闭跟踪会话
ALTER SESSION SET SQL_TRACE = FALSE;

在上面的示例代码中，我们使用 ALTER SESSION 语句来创建和关闭跟踪会话。在创建跟踪会话后，执行的 SQL 语句将会被跟踪和记录。跟踪文件通常保存在 Oracle 数据库服务器的特定目录中，我们可以使用 V$DIAG_INFO 视图获取跟踪文件的位置。

2.3 自动跟踪会话

自动跟踪会话是一种自动化的 SQL 跟踪工具，它可以为我们自动捕获和记录 SQL 语句的执行历史。自动跟踪会话可以基于不同的条件来触发跟踪，包括 SQL 语句的执行时间、执行计划、资源消耗等。下面是使用自动跟踪会话的示例代码：

-- 创建自动跟踪会话
ALTER SESSION SET SQL_TRACE = TRUE;

-- 运行需要自动跟踪的 SQL 语句
SELECT /*+AUTOTRACE*/ * FROM employees;

-- 关闭自动跟踪会话
ALTER SESSION SET SQL_TRACE = FALSE;

在上面的示例代码中，我们使用 ALTER SESSION 语句来创建和关闭自动跟踪会话。在创建自动跟踪会话后，执行的 SQL 语句将会被自动跟踪和记录。跟踪文件通常保存在 Oracle 数据库服务器的特定目录中，我们可以使用 V$DIAG_INFO 视图获取跟踪文件的位置。

3. 分析执行历史

获取 SQL 执行历史后，我们可以使用一些工具和视图来分析和解读执行历史。主要的工具包括：

SQL 监视器（SQL Monitor）
自动实施计划（Automatic Plan Evolution）
SQL 优化器统计信息（SQL Optimizer Statistics）

这些工具可以帮助我们分析 SQL 语句的执行计划、性能指标和统计信息，进而提高代码性能和优化查询过程。接下来，我们将分别介绍这些工具的使用方法。

3.1 SQL 监视器

SQL 监视器是一种用于分析和监控 SQL 语句性能的工具。它可以提供详细的执行计划信息、等待事件统计和数据总结等。可以通过以下步骤启用 SQL 监视器：

执行 SQL 语句并记录执行历史。
使用以下代码开始 SQL 监视器：

-- 启用 SQL 监视器
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 运行需要监视的 SQL 语句
SELECT * FROM employees;

查看监视结果，可以使用以下代码：

-- 查看 SQL 监视器报告
SELECT dbms_sqltune.report_sql_monitor(sql_id => 'SQL_ID') FROM dual;

在上面的示例代码中，我们使用 ALTER SESSION 语句来启用 SQL 监视器，并使用 SELECT 语句来查看监视结果。SQL_ID 是需要监视的 SQL 语句的标识符，通过替换具体的 SQL 语句标识符可以查看相应的监视结果。

3.2 自动实施计划

自动实施计划是一种用于自动优化查询计划的工具。它可以分析 SQL 语句的执行历史和性能指标，并提供优化建议和实施计划。可以通过以下步骤使用自动实施计划：

执行 SQL 语句并记录执行历史。
通过以下代码收集 SQL 语句的执行计划和统计信息：

-- 收集执行计划和统计信息
EXEC DBMS_STATS.GATHER_SQL_STATS(sql_id => 'SQL_ID');

查看优化建议和实施计划，可以使用以下代码：

-- 查看优化建议和实施计划
SELECT * FROM TABLE(DBMS_SQLTUNE.IMPORT_SQL_PROFILE(task_name => 'TASK_NAME'));

在上面的示例代码中，我们使用 DBMS_STATS.GATHER_SQL_STATS 过程来收集 SQL 语句的执行计划和统计信息，并使用 SELECT 语句来查看优化建议和实施计划。SQL_ID 是需要收集信息的 SQL 语句的标识符，TASK_NAME 是任务名称，通过替换具体的 SQL 语句和任务名称可以查看相应的优化建议和实施计划。

3.3 SQL 优化器统计信息

SQL 优化器统计信息是一种用于改进查询性能的工具。它可以分析 SQL 语句的执行计划和统计信息，并提供优化建议和统计信息更新。可以通过以下步骤使用 SQL 优化器统计信息：

执行 SQL 语句并记录执行历史。
通过以下代码收集 SQL 语句的统计信息：

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCHEMA_NAME', tabname => 'TABLE_NAME');

查看优化建议和统计信息更新，可以使用以下代码：

-- 查看优化建议和统计信息更新
SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME'));

在上面的示例代码中，我们使用 DBMS_STATS.GATHER_TABLE_STATS 过程来收集 SQL 语句的统计信息，并使用 SELECT 语句来查看优化建议和统计信息更新。SCHEMA_NAME 是要收集统计信息的架构名称，TABLE_NAME 是要收集统计信息的表名称，TASK_NAME 是任务名称，通过替换具体的架构名称、表名称和任务名称可以查看相应的优化建议和统计信息更新。