本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
前言
说起SQL调优,不少人认为是个高难度的事情,让人望而却步。但其实在日常的运维过程中,慢SQL却是一个常见的问题,这时大家或多或少会用到一些常用的调优手段,比如加索引、调整执行计划等。
接下来,我们将通过三大环节详细讲解如何发现、分析和优化“慢SQL”,具体包括利用慢日志定位问题、分析执行计划并可通过hint手段人为调整优化、以及有效搜集统计信息优化执行计划等。这个通用思路可以在遇到性能问题时,提供一种可行的解决方案,帮助大家尝试着手解决问题。
如何找到慢SQL
首先,SQL调优最重要的是如何找到慢SQL。
从业务层的表现来看,比如应用反应慢、接口获取数据超时、应用夯住等等。但若业务反馈说某条SQL很慢,能帮忙优化一下吗?我们该怎么确定这条SQL一定就是那条“慢SQL”呢?
在YashanDB中,有多种方式可以辅助我们准确定位慢SQL,以下是一些常用方式:
- YCM监控平台
- AWR报告
- 系统性能视图,比如V$SQL,V$SESSION等
- 慢日志slow.log
本次主要介绍怎么使用慢日志功能来找到符合条件的慢SQL。
01 slow log的配置
主要通过两个基本参数控制,可以找到慢SQL。
- ENABLE_SLOW_LOG:是否开启慢日志,默认关闭。(即时生效,不建议长期开启,用于复现性能问题时抓取识别慢SQL。)
- SLOW_LOG_TIME_THRESHOLD :时间阈值,单位毫秒。(代表超过这个时间阈值的SQL,认为是慢SQL,会记录在slow.log里面
执行下列命令开启慢日志:
ALTER SYSTEM SET ENABLE_SLOW_LOG = TRUE;
慢日志相关的参数介绍如下:
- SLOW_LOG_FILE_NAME:慢日志文件名,使用默认即可。
- SLOW_LOG_FILE_PATH:慢日志文件路径,使用默认即可。
- SLOW_LOG_OUTPUT:慢日志输出方式,默认是FILE,也可以设置成TABLE,慢SQL信息会输出到系统表SYS.SLOW_LOG$,建议使用默认的FILE即可。
- SLOW_LOG_SQL_MAX_LEN:慢SQL打印的SQL长度,最长也是2000,使用默认即可。
按照如上配置设置完成之后,执行时间超过1s的SQL就会被记录在slow log中。
测试完成之后,需要将slow log关闭:
ALTER SYSTEM SET ENABLE_SLOW_LOG = FALSE;
既然找到了慢SQL,接下来需要针对该SQL进行优化,一般会去查看该SQL 的执行计划情况。
执行计划的生成和调整
01 如何查看执行计划
查看执行计划通常有两种手段,通过explain+SQL 或是通过autotrace。
方式1:通过explain + SQL
比如:
explain select object_name from test limit 10;
方式2:autotrace
各操作类别如下:
- set autotrace on explain:输出语句的执行结果和执行计划
- set autotrace on statistics:输出语句的执行结果和SQL执行统计信息
- set autotrace traceonly:只输出语句的执行计划和执行统计信息
- set autotrace on:输出语句的执行结果、执行计划和执行统计信息
注:输出统计信息需要设置参数statistics_level=all(alter system set statistics_level=all;)
通过表格对比各个操作类别,更能直观感受其区别:
类别 | 输出执行结果 | 输出执行计划 | 输出统计信息 |
set autotrace on explain | ✔ | ✔ | _ |
set autotrace on statistics | ✔ | _ | ✔ |
set autotrace traceonly | _ | ✔ | ✔ |
set autotrace on | ✔ | ✔ | ✔ |
以下通过一个完整的测试用例,查看explain和autotrace各类别打印出来的具体信息。
1.创建测试表和索引
# 创建测试表
create table test as select * from dba_objects;
# 创建索引,为后面测试hint修改执行计划做准备
create index idx_obj_name on test (object_name);
2.使用方式1 explain 查看执行计划
explain select object_name from test limit 10;
explain的执行计划可以看到输出的表格信息,这个表格所展示的就是SQL的执行计划,后面会进行解读。
3.使用方式2的set autotrace on explain
set autotrace on explain
select object_name from test limit 10;
可以看到输出了执行结果和执行计划:
4.使用方式2的set autotrace on statistics
(alter system set statistics_level=all;)
set autotrace on statistics
select object_name from test limit 10;
可以看到输出了执行结果和统计信息:
5.使用方式2的set autotrace traceonly
set autotrace traceonly
select object_name from test limit 10;
可以看到输出了执行计划和统计信息:
6.使用方式2的set autotrace on
set autotrace on
select object_name from test limit 10;
可以看到输出了执行结果、执行计划以及统计信息:
7.关闭autotrace和调整statistics_level
当测试完毕后,需要将相关设置还原。
# 关闭autotrace
set autotrace off
# 测试完成,调整statistics_level为默认的typical
alter system set statistics_level=typical;
通过上面这个例子,相信大家对explain和autotrace的使用有了一个全面的了解。不同的使用方式会有不同的效果,大家根据实际情况选择合适的方法使用即可。
02 执行计划解读
还是上面这个例子,我们来看看执行计划里都有哪些信息,各代表什么含义。
执行计划中各信息介绍如下:
- Id:执行步骤的唯一标识,并不是执行顺序。
- Operation type:执行算子,前面的空格标示计划的层次关系。
- INDEX FAST FULL SCAN:代表扫描方式是索引快速全扫描
- WINDOW:limit
- SELECT STATEMENT:代表是SELECT查询
- Name:对象名
- Owner:对象所属用户
- Rows:优化器根据统计信息和特定算法计算出来的行数预估值。图中显示的100000行其实不是真实的数据行数,为什么呢?咱们先留个悬念。
- Cost(%CPU):优化器根据算子和硬件等信息,算出的一个参考值,Cost值越大,表示该层计划占用的资源越大。
- Partition info:分区相关信息,因为这张表未分区,所以没有该部分相关信息。
如果在某些极端场景导致优化器生成的执行计划并不是最优的,YashanDB也提供可以手动修改执行计划的手段。
03 通过hint调整执行计划
一般情况下,优化器是可以做出最优的执行效果。如果我们的统计信息未收集或者收集失效,这时候优化器可能无法根据错误的统计信息做出最优的执行计划,需要我们手动调整。这时可以选择通过hint去调整执行计划。
hint是一种特殊的注释,其格式和位置固定,作用是影响优化器对执行计划的选择,但非强制。
通过hint填写的内容可以分为以下三种:
- leading:改变表的join顺序;
- use_nl/use_hash/use_merge等:改变表的连接类型;
- index/full等:改变访问路径。
通过添加hint /*+full(test)*/ 调整SQL使用全表扫描
explain select /*+full(test)*/ object_name from test limit 10;
可以看到,通过hint,将原本的索引快速全扫描(INDEX FAST FULL SCAN)调整为了全表扫描(TABLE ACCESS FULL)。
注:这里只是为了验证hint确实可以改变执行计划。在此用例中,全表扫描并不是最优的。
统计信息的收集
在前面执行计划的解读中,TEST表的预估行数是100000,这其实是因为没有收集统计信息,系统给的默认值为100000行。下面我们来看看怎么收集统计信息。
01 查看统计信息是否有效
统计信息相关参数介绍如下:
- DBA_TAB_STATISTICS:表统计信息
- DBA_TAB_COL_STATISTICS:列统计信息
- DBA_IND_STATISTICS:索引的统计信息
- DBA_PART_COL_STATISTICS:分区列的统计信息
比如前面所使用的test表,并未收集过统计信息,通过视图查出来信息如下:
select table_name,last_analyzed,stale_stats from dba_tab_statistics where owner='SKYE' and table_name='TEST';
# SKYE 是 TEST表的所属用户
LAST_ANALYZED是指上一次该对象统计信息的收集时间,为空代表从未收集过。
02 统计信息收集方式
在YashanDB中,可以从全库、按用户、按表,3个维度进行统计信息收集,来应对不同的场景。
情况1:全库收集
exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER', 1, 48, 'FOR ALL COLUMNS SIZE AUTO', 'ALL', TRUE, FALSE);
情况2:按用户收集
exec DBMS_STATS.GATHER_SCHEMA_STATS('SKYE',1,TRUE,'FOR ALL COLUMNS SIZE AUTO',48,'ALL',TRUE);
情况3:按表收集
exec DBMS_STATS.GATHER_TABLE_STATS('SKYE', 'TEST','', 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 32, 'AUTO', TRUE);
注:一般大库、大表建议使用按表收集统计信息,因为收集统计信息也会消耗系统性能。这里的1 代表采样率为1,表数据量比较大的情况下可以使用0.2即可。这里的32代表并行度,如果系统CPU等资源充足,可以适当调大该值加快统计信息速度。
收集完test表统计信息之后,视图查询如下:
可以看到,统计信息收集完成之后,视图会记录该对象的收集情况,在执行计划的Rows估算中,能更加真实的预估行数,Cost也会随之降低。
总结
很多时候SQL突然变慢,可能是执行计划改变了;而执行计划突然改变很可能是统计信息失效了,导致优化器不能根据真实的统计信息选择出最优的执行计划。从以往经验来看,可以先查看涉及到的表、分区和索引相关的统计信息是否是正常有效的。
工欲善其事,必先利其器。不论是AWR、系统视图还是slow log工具,大家能够找到适合自己的某一种方法,熟练掌握即可。本文主要介绍的是通过慢日志功能进行SQL调优,感兴趣的同学也可以到YashanDB官网的文档中心(https://doc.yashandb.com/)参考官方文档,试试通过AWR、系统视图等方式,找到慢SQL进行分析。