影响SQL执行效率的因素主要包括以下几点。
1）统计信息，具体如下
-
陈旧的统计信息
-
错误的优化器参数配置
-
数据库迁移前后优化器的改变
-
频繁的数据量变化
2）SQL语句编写问题
3）游标共享问题，具体如下
-
变量窥探
-
使用常量
4）资源争用问题，具体如下
-
硬件资源不足
-
锁或latch等的争用问题
以上几种原因最终都会导致主机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。
-
主机资源异常，找出排行靠前的进程（top process），检查执行的SQL语句，查询命令如下：
<!--替换对应的SPID即可 -->
SQL> select p.inst_id,
p.spid,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine
from gv$session a, gv$process p
where a.inst_id = p.inst_id
and a.paddr = p.addr
and p.spid = 15883;
-
业务超时反馈，检查对应的应用模块正在执行的SQL语句。
-
数据库性能整体下降，检查异常等待事件，查询语句如下：
<!--替换等待事件名称即可-->
SQL> select a.inst_id,
a.process,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine
from gv$session a
where event = ‘db file scattered read‘;
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主要包含如下三个文件。
-
sqldx.sql：仅收集信息而不进行任何修改操作，较为常用。
-
sqlhc.sql：收集SQL相关数据字典信息。
-
sqlhcxec.sql：执行SQL，再收集信息，会产生变更操作。
要想执行以上脚本，我们需要拥有DBA或访问数据字典视图的权限。操作也非常简单，只需要上传sqlhc.sql到数据库服务器指定的目录下执行即可，具体方法如下：
shell> unzip sqlhc.zip Archive: sqlhc.zip creating: sqlhc/ inflating: sqlhc/sqlhc.sql inflating: sqlhc/sqldx.sql inflating: sqlhc/sqlhcxec.sql shell> cd sqlhc shell> ls -tlr total 2 -rw-r--r-- 1 mfggprd dba 48747 Nov 11 2013 sqldx.sql -rw-r--r-- 1 mfggprd dba 288298 Apr 16 2014 sqlhc.sql -rw-r--r-- 1 mfggprd dba 292838 Apr 16 2014 sqlhcxec.sql shell> sqlplus / as sysdba SQL> @sqlhc.sql T d18wwg2f3txc0
执行时需要输入以下两个参数。-
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)。
-
T：调优，较为常用。
-
D：诊断。
-
N：无。
-
SQL_ID：需要诊断的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中的部分内容说明如下。
-
health_check：用于诊断，以及统计信息异常、Index异常、特殊参数设置等。
-
diagnostics：内容包括SQL文本、执行计划绑定情况、当前和历史SQL的相关信息、表格/列/索引/列柱状图详细信息、系统参数、表格创建语句、SQL相关等待事件，等等。
-
execution plan：SQL文本、当前和历史执行计划。
-
sqldx：SQL绑定、直方图、各对象的数据字典信息等。
-
10053跟踪：SQL的执行方式。
03SQL PROFILE
SQL profile是查询中的辅助信息的集合，包括查询中引用的所有表和列。SQL profile存储在数据字典中，优化器在优化过程中使用这些信息来确定最优的计划。
1. 使用coe_xfr_sql_profile.sql
1）运行分析脚本，命令如下：
shell> sqlplus "/ as sysdba" SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh <!--后跟问题SQL对应的ID-->
2）输入所希望的执行计划哈希值，命令如下：
SQL> SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2979024279 .011
647855111 5.164
从以上输出结果来看，2979024279对应的执行计划响应时间更快。
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 2979024279
Values passed:
~~~~~~~~~~~~~
SQL_ID : "cdwjdd67x27mh"
PLAN_HASH_VALUE: "2979024279"
Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
3）输出结果如下：
SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
4）检查profile情况。通过查询dba_sql_profiles视图，查看具体的固化情况，命令如下：
SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
5）删除profile。带入dba_sql_profiles中对应的profile名进行删除，命令如下：
SQL> exec dbms_sqltune.drop_sql_profile(‘coe_f4sgavkagjb1q_2593387201‘);
2. 使用SQL调优建议工具
1）运行SQL调优建议工具（SQL Tuning Advisor）。带入问题SQL_ID，命令如下：
SQL> var tuning_task varchar2(100); SQL> DECLARE 2 l_sql_id v$session.prev_sql_id%TYPE; 3 l_tuning_task VARCHAR2(30); 4 BEGIN 5 l_sql_id:=‘gg859wbj3hkfq‘; 6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id); 7 :tuning_task:=l_tuning_task; 8 dbms_sqltune.execute_tuning_task(l_tuning_task); 9 dbms_output.put_line(l_tuning_task); 10 END; 11 / PL/SQL procedure successfully completed. SQL> SQL> print tuning_task; TUNING_TASK -------------- TASK_8233
查看建议内容，查询语句如下：
SQL> SELECT dbms_sqltune.report_tuning_task(‘TASK_8233‘) FROM dual;
2）接受SQL profile。根据SQL调优建议工具提供的建议，接受SQL profile，命令如下：
SQL> exec dbms_sqltune.accept_sql_profile(task_name => ‘TASK_8193‘,replace => TRUE, force_match=>true);
如果数据库同时给出了创建索引和SQL profile两个建议，那么在某些情况下，只要接受SQL profile即可，某些情况下则需要同时采纳创建索引和接受SQL porfile两个建议。因为创建索引之后，数据库可能需要通过SQL profile的帮助才能选择新的索引。
3）查看SQL profile。数据字典视图DBA_SQL_PROFILES可用于展示数据库中存储的SQL profile，命令如下：
SQL> SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
4）删除SQL profile，命令如下：
SQL> BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name => ‘sql_profile‘ );
END;
/
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> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile;
SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile;接下来将为大家演示将SQL执行计划手动加载到SQL计划基线中的优化案例。
1）执行SQL语句，命令如下：
SQL> set autotrace on
SQL> set line 150 pagesize 0
SQL> select * from scott.dept where deptno=10;
10 YDFD NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
查找对应的SQL_ID和PLAN HASH VALUE，命令如下：
SQL> select sql_id,plan_hash_value from v$sql where sql_text like ‘%scott.dept where deptno = 10%‘; SQL_ID PLAN_HASH_VALUE ------------- --------------- fxchug4tpuqcj 2852011669
2）将以上SQL语句加载至SQL计划基线中，命令如下：
SQL> var n number SQL> begin 2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>‘fxchug4tpuqcj‘, plan_hash_value=>2852011669, fixed =>‘NO‘, enabled=>‘YES‘); 3 end; 4 / PL/SQL procedure successfully completed.
3）查看DBA_SQL_PLAN_BASELINES视图以确认情况，命令如下：
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT from dba_SQL_PLAN_BASELINES where ACCEPTED = ‘YES‘ order by LAST_MODIFIED; SQL_HANDLE PLAN_NAME ENABLED ACCEPTED SQL_TEXT -------------------- ------------------------------ ------- --------- -------- SQL_59f9d6822a74ea01 SQL_PLAN_5myfqh8p79uh10348d329 YES YES <CLOB>
4）在以上的SELECT语句中加入HINT改变执行计划，强制全表扫描，命令如下：
SQL> select /*+ full(dept) */ * from scott.dept where deptno = 10; 10 YDFD NEW YORK Execution Plan -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 19 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
5）查找对应的SQL_ID和PLAN HASH VALUE，命令如下：
SQL> select sql_id, plan_hash_value from v$sql where sql_text like ‘%select /*+ full(dept) */%‘; SQL_ID PLAN_HASH_VALUE ------------- --------------- fgb5t3n5w5btx 3383998547
6）将加有HINT的执行计划加载到原SQL中的SPM中，命令如下：SQL> var n number SQL> begin 2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>‘fgb5t3n5w5btx‘, plan_hash_value=>3383998547, fixed =>‘NO‘, enabled=>‘YES‘); 3 end; 4 /
7）查看DBA_SQL_PLAN_BASELINES视图以确认情况，命令如下：SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT from dba_SQL_PLAN_BASELINES where ACCEPTED = ‘YES‘ order by LAST_MODIFIED;
DBA_SQL_PLAN_BASELINES的视图信息如图2所示。
图2 DBA_SQL_PLAN_BASELINES视图信息
8）删除第一个SQL 执行计划，命令如下：
SQL> variable n number ; SQL> exec :n:=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> ‘SQL_59f9d6822a74ea01‘, PLAN_NAME=> ‘SQL_PLAN_5myfqh8p79uh10348d329‘); PL/SQL procedure successfully completed.
9）重新执行SQL语句，命令如下：
SQL> set autotrace on
SQL> set line 150 pagesize 0
SQL> select * from scott.dept where deptno=10;
10 YDFD NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 19 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Note
-----
- SQL plan baseline "SQL_PLAN_5myfqh8p79uh10e23be79" used for this statement
执行计划中，SQL计划基线表明以上SQL已经开始按照我们想要的方式在执行。
持续运行的系统和数据的不断变化可能会影响某些SQL的执行计划，从而导致整体性能的下降，使用SQL计划管理有助于最大程度地防止由于执行计划更改而导致的性能下降。对于一些特殊的SQL，我们也可以使用SQL计划管理的特性，随时调整执行计划。