四步搞定异常SQL

教程大全 2026-01-08 05:56:42 浏览次

影响SQL执行效率的因素主要包括以下几点。

1)统计信息，具体如下

2)SQL语句编写问题

3)游标共享问题，具体如下

4)资源争用问题，具体如下

以上几种原因最终都会导致主机CPU的使用率增加、主机I/O异常繁忙、语句执行时间异常增加、数据库整体性能下降、应用超时等问题。

01定位问题SQL

进行SQL优化的第一步是定位问题SQL。Oracle会在内存中记录每条SQL语句执行所消耗的资源，再由专门的进程(MMON)将这些统计指标定期保存为AWR快照，笔者认为，AWR的快照采集，是Oracle优于其他数据库的一大利器。

两次快照内统计值的差异可以反映快照时间段内资源的消耗情况。Oracle可以通过指定的两个快照来生成性能报告。执行Oracle内置的awrrpt脚本，按照提示逐步进行即可生成AWR报告。AWR报告中有关于SQL的各项排名，如按SQL耗时、CPU消耗、I/O消耗、逻辑读消耗、物理读消耗、执行次数、解析次数、共享内存使用大小、子游标使用量、Cluster等待等进行的排名。

除了通过AWR查找异常SQL之外，我们还可以通过以下方式定位异常SQL。

02SQL健康检查

接下来介绍SQL调优健康检查(SQL Tuning Health Check，SQLHC)脚本的基础知识，以及如何使用它来收集性能较差SQL的关键信息。SQLHC可以帮助我们专注于特定的SQL，并检查基于成本的优化器统计信息、对象元数据、配置参数和其他可能会影响性能的因素。与SQLT(SQLTXPLAIN)相比，SQLHC不需要在数据库中提前配置脚本，只需要利用已执行语句的SQL_ID生成报告即可。SQLHC适用于Oracle 10g及以上版本，同样也支持RAC。

对于简单的SQL问题，我们通过执行计划就能判断其优化方向;而对于复杂SQL问题的诊断，则需要借助于更多信息，比如，数据库版本信息、参数设置、表/索引/字段统计信息、统计信息变化情况、当前和历史执行计划、sql path/sql profile/sql plan baseline等。

好消息是SQLHC不必运行多个脚本来收集数据，单个SQLHC就能收集所有的数据，并以HTML这种易于阅读的格式显示。

SQLHC的官方下载地址为：SQL Tuning Health-Check Script (SQLHC) (Doc ID：1366133.1)。

首先从MOS中下载脚本代码并将其上传到服务器，然后找到需要评估的SQL_ID，其可以来自于AWR、ASH报告或V$SQL视图。

接下来，我们重点介绍SQLHC的使用方法，SQLHC主要包含如下三个文件。

要想执行以上脚本，我们需要拥有DBA或访问数据字典视图的权限。操作也非常简单，只需要上传sqlhc.sql到数据库服务器指定的目录下执行即可，具体方法如下：

执行时需要输入以下两个参数。

下面以SQL d18wwg2f3txc0为例，执行完之后自动打包生成一个压缩文件sqlhc_ 20200303_1555_d18wwg2f3txc0.zip，生成的内容包括health_check、diagnostics、execution plan、sql_detail、10053 trace、sqldx、SQL monitor(可选)，如图1所示。

图1 SQLHC.SQL执行后生成的文件

图1中的部分内容说明如下。

03SQL PROFILE

SQL profile是查询中的辅助信息的集合，包括查询中引用的所有表和列。SQL profile存储在数据字典中，优化器在优化过程中使用这些信息来确定最优的计划。

1. 使用coe_xfr_sql_profile.sql

1)运行分析脚本，命令如下：

2)输入所希望的执行计划哈希值，命令如下：

3)输出结果如下：

4)检查profile情况。通过查询dba_sql_profiles视图，查看具体的固化情况，命令如下：

5)删除profile。带入dba_sql_profiles中对应的profile名进行删除，命令如下：

2. 使用SQL调优建议工具

1)运行SQL调优建议工具(SQL Tuning Advisor)。带入问题SQL_ID，命令如下：

查看建议内容，查询语句如下：

2)接受SQL profile。根据SQL调优建议工具提供的建议，接受SQL profile，命令如下：

如果数据库同时给出了创建索引和SQL profile两个建议，那么在某些情况下，只要接受SQL profile即可，某些情况下则需要同时采纳创建索引和接受SQL porfile两个建议。因为创建索引之后，数据库可能需要通过SQL profile的帮助才能选择新的索引。

3)查看SQL profile。数据字典视图DBA_SQL_PROFILES可用于展示数据库中存储的SQL profile，命令如下：

4)删除SQL profile，命令如下：

04SQL计划管理

Oracle 11g R1引进了SQL Plan Management(SPM，SQL计划管理)，SPM是一种预防性机制，使优化程序可以自动管理执行计划，从而确保数据库使用的是已知的或经过验证的最优计划。当系统开启自动SQL Plan Baseline(SQL计划基线)捕获时，CBO会记录会话内执行的任意SQL，并把SQL的相关信息存储为SQL计划基线。第一次执行的语句，由于没有基础数据，因此会被当成最优的执行计划。第二次执行时，CBO会与存储在SQL计划基线中的计划进行比较，如果新执行计划的性能有所改善，那么SPM会把新的执行计划标记为该语句最优的执行计划。默认情况下，CBO会使用SQL计划基线中最优的执行计划。而对于异常SQL自动捕获，则需要设置参数optimizer_capture_sql_plan_baselines的值为true，默认是false，命令如下：

接下来将为大家演示将SQL执行计划手动加载到SQL计划基线中的优化案例。

1)执行SQL语句，命令如下：

查找对应的SQL_ID和PLAN HASH VALUE，命令如下：

2)将以上SQL语句加载至SQL计划基线中，命令如下：

3)查看DBA_SQL_PLAN_BASELINES视图以确认情况，命令如下：

4)在以上的 select语句中加入HINT改变执行计划，强制全表扫描，命令如下：

5)查找对应的SQL_ID和PLAN HASH VALUE，命令如下：

6)将加有HINT的执行计划加载到原SQL中的SPM中，命令如下：

7)查看DBA_SQL_PLAN_BASELINES视图以确认情况，命令如下：

DBA_SQL_PLAN_BASELINES的视图信息如图2所示。