前言
SQL调优作为数据库性能调优重要的一环，对查询性能产生着直接的影响。在进行SQL调优之前，需要关注以下几点：
- 数据库安装时，配置参数是否符合应用场景需求；
- 数据库INI 配置文件中各项参数是否已经处于最优配置；
- 应用系统中数据库设计是否合理。
本文将主要介绍，如何定位高耗SQL，执行计划解读，优化单条SQL等角度出发来进行分享。
一、如何定位慢SQL
待优化SQL可以大致分为两类：
- 第一类SQL耗时在十几秒到几十秒，但不会频繁执行。此类SQL对数据库性能影响并不算太大，可以最后进行优化；
- 第二类SQL耗时几百毫秒到几秒，虽然耗时不长，但过于频繁执行，在高并发下执行效率很低，很可能会导致系统瘫痪。此类SQL才是优化的重点目标。
定位慢SQL的方式有两种方法，第一种根据跟踪日志查找慢SQL，第二种根据系统视图查看执行慢SQL。
1.1跟踪日志查找慢SQL
跟踪日志文件命名方式：以dmsql_实例名_日期_时间 命名；
默认存放路径：DM安装目录的log子目录下；
跟踪日志内容：包含系统各会话执行的SQL语句、参数信息、错误信息、执行时间等；
跟踪日志作用；用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。
1.1.1跟踪日志配置方式
根据需要配置文件目录下的sqllog.ini，如下图所示：
各配置项详细说明
| 参数名 | 缺省值 | 说明 | |
| FILE_PATH | ../log | 日志文件所在的文件夹路径 | |
| PART_STOR | 0 | SQL 日志分区存储，表示 SQL 日志进行分区存储的划分条件。0 表示不划分； 1 表示 USER：根据不同用户分布存储 | |
| SWITCH_MODE | 0 | 表示 SQL 日志文件切换的模式： 0：不切换 1：按文件中记录数量切换 2：按文件大小切换 3：按时间间隔切换 | |
| SWITCH_LIMIT | 100000 | 不同切换模式 SWITCH_MODE 下，意义不同： | |
| ASYNC_FLUSH | 0 | 是否打开异步 SQL 日志功能。0：表示关闭；1：表示打开 | |
| FILE_NUM | 0 | 总共记录多少个日志文件，当日志文件达到这个设定值以后，再生成新的文件时，会删除最早的那个日志文件，日志文件的命令格式为 dmsql_实例名_日期时间.log。 当这个参数配置成 0 时，只会生成两个日志相互切换着记录。有效值范围（0~1024）。例如，当 FILE_NUM=0，实例名为 PDM 时，根据当时的日期时间，生成的日志名称为： DMSQL_PDM_20180719_163701.LOG， DMSQL_PDM_20180719_163702.LOG | |
| ITEMS | 0 |
| |
| SQL_TRACE_MASK | 1 | LOG 记录的语句类型掩码，是一个格式化的字符串，表示一个 32 位整数上哪一位将被置为 1，置为 1 的位则表示该类型的语句要记录，格式为：号:位号:位号。如：3:5:7 表示第 3，第 5，第 7 位上的值被置为 1。每一位的含义见下面说明（2~17 前提是:SQL 标记位 24 也要设置)： | |
| MIN_EXEC_TIME | 0 |
| |
| USER_MODE | 0 | SQL 日志按用户过滤时的过滤模式，取值 | |
| USERS | 空串 | 打开 USER_MODE 时指定的用户列表。格式为：用户名：用户名：用户名 |
配置sqllog.ini后，需要修改SVR_LOG参数为1，修改方式有如下两种：
通过SQL语句进行修改，如下图所示：
修改配置文件dm.ini
如果对sqllog.ini进行了修改，需要调用以下函数进行即时生效，无需要重启数据库，如下图所示：
1.1.2根据跟踪日志查找慢SQL
配置成功后可在 dmsql 指定目录下生成 dmsql 开头的 log 日志文件。日志内容如下所示：
1.2根据系统视图查看执行慢SQL
DM 数据库提供系统动态视图，可自动记录执行时间超过设定阈值的 SQL 语句。
1.2.1 SQL 记录配置
当ini参数ENABLE_MONITOR=1、MONITOR_TIME=1打开时，显示系统最近1000条执行时间超过预定值的SQL语句。默认预定义值为1000毫秒。通过可通过 SP_SET_LONG_TIME 系统函数修改，通过 SF_GET_LONG_TIME 系统函数查看当前值。
第一种方法：通过SQL语句进行修改，如下图所示：
第二种方法：修改dm.ini文件，如下图所示：
第三种方法：SP_SET_PARA_VALUE 方式
--两个参数均为动态参数，可直接调用系统函数进行修改
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);
注意：
通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。
1.2.2查询方式
超过执行时间阈值的 SQL 语句记录在 V$LONG_EXEC_SQLS 系统视图中。
通过此视图获取结果，如下图所示：
由于最近执行的SQL语句没有超过1000毫秒的，故没有显示。
| SESS_ID | 会话 ID，会话唯一标识 |
| SQL_ID | 语句 ID，语句唯一标识 |
| SQL_TEXT | SQL 文本 |
| EXEC_TIME | 执行时间（毫秒） |
| FINISH_TIME | 执行结束时间 |
| N_RUNS | 执行次数 |
| SEQNO | 编号 |
| TRX_ID | 事务号 |
二、执行计划解读
执行计划是一条SQL语句在数据库中的执行过程或访问路径的描述。对于执行计划来说，相同的SQL，不同的访问方式就是两种不同的执行计划，查询优化器(CBO)会根据代价也就是开销来选择最优的执行计划。
2.1 SQL方式查看执行计划
执行计划的每行即为一个计划节点，主要包含三部分信息：
- NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
- 的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数。
- 为操作符的补充信息。
例如：第三个计划节点：操作符是CSN2即全表扫描，代价估算是1ms，扫描的记录行数是3，输出字节数是16。
准备测试表及测试数据
收集统计信息
| 操作符 | 含义 | 作用 |
| NSET | 结果集收集 | 用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。 |
| PRJT | 投影 | 关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。 |
| SLCT | 选择 | 关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。 |
| AAGR | 简单聚集 | 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。 |
| SAGR | 快速聚集 | 用于没有过滤条件时从表或索引快速获取 MAX、MIN、COUNT 值。 |
| HAGR | HASH 分组聚集 | 用于分组列没有索引只能走全表扫描的分组聚集，该示例中 C2 列没有创建索引。 |
| SAGR | 流分组聚集 | 用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。 |
| BLKUP | 二次扫描 (回表) | 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。 |
| CSCN | 全表扫描 | CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。 |
| SSEK | 索引扫描 | SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。 |
| CSEK | CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。 | |
| SSCN | SSCN 是索引全扫描，不需要扫描表。 | |
| NEST LOOP | 嵌套循环连接 | 嵌套循环连接最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。 |
| HASH JOIN | 哈希连接 | 哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。 |
| MERGE JOIN | 归并排序连接 | 归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。 |
三、ET优化单条SQL
ET工具：能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。
3.1 ET功能的开启
ET 功能默认关闭，可通过配置 INI 参数中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 开启该功能。
通过SQL方式进行更改，如下图所示：
开启：
关闭：
注意：
ET 功能的开启将对数据库整体性能造成一定影响，优化工作结束后尽量关闭该功能以提升数据库整体运行效率。
3.2 ET查看方式
执行 SQL 语句后，客户端会返回 SQL 语句的执行号。鼠标单机执行号即可查看 SQL 语句对应的 ET 结果。
如果没有图形界面，调用存储过程可返回相同结果
ET 结果说明
OP: 操作符
TIME(us): 时间开销，单位为微秒
PERCENT: 执行时间占总时间百分比
RANK: 执行时间耗时排序
SEQ: 执行计划节点号
N_ENTER: 进入次数
四、统计信息与索引详解
4.1统计信息收集方法
统计信息：描述数据库中表和索引的大小数以及数据分布状况等的一类信息。比如：表的行数、块数、平均每行的大小、索引的高度、叶子节点数以及索引字段的行数等。
统计信息对于 CBO（基于代价的优化器）生成执行计划具有直接影响。例如在嵌套循环中需要选择小表作为驱动表，两个关联表哪个是小表完全取决于统计信息中记录的数据量信息。此外，访问一个表是否要走索引，关联查询能否采用其它关联方式等都是 CBO 基于统计信息确定的。因此，统计信息的准确是生成最优执行计划的必要前提。
收集统计信息的方法如下所示：
--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
--或 收集单个索引统计信息：
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');
--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集某表某列的统计信息：
STAT 100 ON table_name(column_name);
注意：统计信息收集过程中将对数据库性能造成一定影响，避免在业务高峰期收集统计信息。4.2自动收集统计信息
DM 数据库支持统计信息的自动收集，当全表数据量变化超过设定阈值后可自动更新统计信息。
--打开表数据量监控开关，参数值为 1 时监控所有表，2 时仅监控配置表
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);
--设置 SYSDBA.T 表数据变化率超过 15% 时触发自动更新统计信息
DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);
--配置自动收集统计信息触发时机
SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'17:16', '2021/10/29',60,1);
--函数各参数介绍
SP_CREATE_AUTO_STAT_TRIGGER(
TYPE INT, --间隔类型，默认为天
FREQ_INTERVAL INT, --间隔频率，默认 1
FREQ_SUB_INTERVAL INT, --间隔频率，与 FREQ_INTERVAL 配合使用
FREQ_MINUTE_INTERVAL INT, --间隔分钟，默认为 1440
STARTTIME VARCHAR(128), --开始时间，默认为 22:00
DURING_START_DATE VARCHAR(128), --重复执行的起始时间，默认 1900/1/1
MAX_RUN_DURATION INT, --允许的最长执行时间(秒)，默认不限制
ENABLE INT --0 关闭，1 启用 --默认为 1
);4.3 索引适用范围
1.在以下场景下可考虑创建索引：
(1)仅当要通过索引访问表中很少的一部分行（1%~20%）。
(2)索引可覆盖查询所需的所有列，不需额外去访问表。
注意：
对于一个表来说索引并非越多越好，过多的索引将影响该表的 DML 效率。
2.存在下列情况将导致无法使用索引：
(1)组合索引中，条件列中没有组合索引的首列。
(2)条件列带有函数或计算。
索引排序是按照字段值进行排序的，字段值通过函数或计算后的值索引无法获取。
(3)索引过滤性能不好时。如对一张 10 万条记录的表进行条件查询，获取 5 万条数据，通过索引进行查找效率低于全表扫描，将放弃使用索引。
五、开发有效的SQL 语句
SQL 语言是一种相当灵活的结构化查询语言。用户可以利用多种不同形式的查询语句完 成相同的查询功能。为了使执行效率达到最优，用户需要参考以下原则以开发出有效的 SQL 语句。
5.1避免使用 OR 子句
OR 子句在实际执行中会被转换为类似于 UNION 的查询。如果某一个 OR 子句不能利用 上索引则会使用全表扫描造成效率低下，应避免使用。
如果 OR 子句都是对同一列进行过滤，用户可以考虑使用 IN VALUE LIST 的过滤形式。
5.2 SELECT 项避免‘*’
除非用户确实要选择表中所有列，否则 SELECT *这种写法将让执行器背上沉重的负荷。 因为每一列的数据不得不自下往上层层向上传递。不仅仅如此，如果用户查询的是垂直分区 表，那么更大的麻烦在于垂直分区表的所有子表都要参与连接操作；如果用户查询的是列存 储表，那么列存储所带来的 IO 优势将损耗殆尽。
任何时候，用户都要了解表结构和业务需求，小心地选择需要的列并一一给出名称，避 免直接用 SELECT *。
5.3 UNION 和 UNION ALL 的选择
UNION 和 UNION ALL 的区别是前者会过滤掉值完全相同的元组，为此 UNION 操作符 需要建立 HASH 表缓存所有数据并去除重复，当 HASH 表大小超过了 INI 参数指定的限制时 还会做刷盘。 因此如果应用场景并不关心重复元组或者不可能出现重复，那么 UNION ALL 无疑优于 UNION。
5.4 优化 GROUP BY ... HAVING
GROUP BY 最常见的实现有 HASH 分组（HAGR）和排序分组（SAGR）。前者需要缓存中 间结果；如果用户在 GROUP BY 的列上建立索引，那么优化器就会判断并可能使用上该索引， 这时的 GROUP BY 就会变为 SAGR。
HAVING 是分组后对结果集进行的过滤，如果过滤条件无关集函数操作，用户可以考虑 将过滤条件放在WHERE而不是HAVING中。DM7优化器会判断并自动转换部分等效于WHERE 的 HAVING 子句，但显式地给出最佳 SQL 语句会让优化器工作得更好。
转载本文请备明出处！！！
282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
