接下来，我们将通过三大环节详细讲解如何发现、分析和优化“慢SQL”，具体包括利用慢日志定位问题、分析执行计划并可通过hint手段人为调整优化、以及有效搜集统计信息优化执行计划等。这个通用思路可以在遇到性能问题时，提供一种可行的解决方案，帮助大家尝试着手解决问题。
从业务层的表现来看，比如应用反应慢、接口获取数据超时、应用夯住等等。但若业务反馈说某条SQL很慢，能帮忙优化一下吗？我们该怎么确定这条SQL一定就是那条“慢SQL”呢？
在YashanDB中，有多种方式可以辅助我们准确定位慢SQL，以下是一些常用方式：
- YCM监控平台
- AWR报告
- 系统性能视图，比如V$SQL，V$SESSION等
- 慢日志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 的执行计划情况。
explain select object_name from test limit 10;- set autotrace on explain：输出语句的执行结果和执行计划
- set autotrace on statistics：输出语句的执行结果和SQL执行统计信息
- set autotrace traceonly：只输出语句的执行计划和执行统计信息
- set autotrace on：输出语句的执行结果、执行计划和执行统计信息
注：输出统计信息需要设置参数statistics_level=all（alter system set statistics_level=all;)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1.创建测试表和索引
# 创建测试表create table test as select * from dba_objects;# 创建索引，为后面测试hint修改执行计划做准备create index idx_obj_name on test (object_name);
explain select object_name from test limit 10;
set autotrace on explainselect object_name from test limit 10;
(alter system set statistics_level=all;)set autotrace on statisticsselect object_name from test limit 10;
set autotrace traceonlyselect object_name from test limit 10;
set autotrace onselect object_name from test limit 10;
当测试完毕后，需要将相关设置还原。
# 关闭autotraceset autotrace off# 测试完成，调整statistics_level为默认的typicalalter system set statistics_level=typical;
- Id：执行步骤的唯一标识，并不是执行顺序。
- Operation type：执行算子，前面的空格标示计划的层次关系。
- INDEX FAST FULL SCAN：代表扫描方式是索引快速全扫描
- WINDOW：limit
- SELECT STATEMENT：代表是SELECT查询
- Name：对象名
- Owner：对象所属用户
- Rows：优化器根据统计信息和特定算法计算出来的行数预估值。图中显示的100000行其实不是真实的数据行数，为什么呢？咱们先留个悬念。
- Cost(%CPU)：优化器根据算子和硬件等信息，算出的一个参考值，Cost值越大，表示该层计划占用的资源越大。
- Partition info：分区相关信息，因为这张表未分区，所以没有该部分相关信息。
如果在某些极端场景导致优化器生成的执行计划并不是最优的，YashanDB也提供可以手动修改执行计划的手段。
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）。
- 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是指上一次该对象统计信息的收集时间，为空代表从未收集过。
情况1：全库收集
exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER', 1, 48, 'FOR ALL COLUMNS SIZE AUTO', 'ALL', TRUE, FALSE);exec DBMS_STATS.GATHER_SCHEMA_STATS('SKYE',1,TRUE,'FOR ALL COLUMNS SIZE AUTO',48,'ALL',TRUE);exec DBMS_STATS.GATHER_TABLE_STATS('SKYE', 'TEST','', 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 32, 'AUTO', TRUE);
可以看到，统计信息收集完成之后，视图会记录该对象的收集情况，在执行计划的Rows估算中，能更加真实的预估行数，Cost也会随之降低。
工欲善其事，必先利其器。不论是AWR、系统视图还是slow log工具，大家能够找到适合自己的某一种方法，熟练掌握即可。本文主要介绍的是通过慢日志功能进行SQL调优，感兴趣的同学也可以到YashanDB官网的文档中心（doc.yashandb.com）参考官方文档，试试通过AWR、系统视图等方式，找到慢SQL进行分析。
原创文章，作者：guozi，如若转载，请注明出处：https://www.sudun.com/ask/80992.html
