How to use SQL Monitor

RWP团队谈性能优化之大开眼界篇(五)SQL Monitor报告实例分析


首先介绍了用于数据库诊断的各种工具汇总,强调SQL Monitor报告是用于SQL级别性能诊断和分析的最佳工具,它显示一条SQL语句的某次执行的详细信息。对于长时间运行的SQL,可以进行精准的分析以确定时间都花在哪里


SQL Monitor报告在Oracle 11g开始引入,它总是可用,是默认启用的,用于监视SQL的单次执行,也包括尚未完成的SQL语句。

SQL语句要被监视需要满足下面的三种情形之一:
1. 所有的并行执行语句
2. 超过5秒的串行执行语句
3. 带有 /*+ monitor */ 提示的语句

SQL Monitor报告有多种格式,推荐使用Active(HTML)的格式,在没有浏览器可以的情况下可以使用TEXT格式的直接在终端查看。有多种方法生成或者直接查看SQL Monitor报告:
 OEM的SQL监视页面
 EM Express
 PerfHub
 命令行方式直接生成
 SQL Developer

接下来介绍了如何解读SQL Monitor报告。RWP推荐采用自顶向下的方式来查看:
 时间都花在哪里?
 哪一个行源(row sources)?
 估计的和实际的行数一致吗?
 执行数
 并行服务器执行数
 Nested loop 迭代次数
 智能分区操作
 并行执行:并行进程倾斜?

在SQL Monitor的General部分可以查看SQL的一般信息:是否使用了并行;SQL消耗的数据库时间,数据库时间中CPU和各种等待的占比;SQL消耗的IO信息等等。

Plan Statistics页展示执行计划和过程信息。在这个页面,Activity % 显示了时间花在哪里;可以获得每个操作的估计行数,执行过程中实际返回的行数,从而确定估计值是否有偏差;在此首先关注表或者索引扫描的估计行数,不好的估计值往往导致不好的执行计划。

Plan页可以查看执行计划的更多信息,可以查看每个步骤的过滤条件以及分区裁剪信息。

Activity页以图形的方式显示活动会话和等待事件——类似于单条SQL的顶级活动。在这个页面可以看到整个SQL的执行过程,另外这个页面提供了多个维度来查看SQL的DB时间的分布。

Parallel页只有在并行执行的SQL才会出现,显示了各个并行进程的信息,可以很清楚的发现和定位并行进程或者数据库节点的倾斜问题。


接下来详细分析了五个SQL Monitor报告实例。

第1个案例的查询执行时间40秒,而实际要求在5秒钟之内完成。检查SQL Monitor报告可以发现:其中CPU占67%,IO占33%,而代价最高步骤的是HASH JOIN RIGHT OUTER ,有大量的临时表空间读写。然而,HASH 本身是问题所在吗?通过检查估计的行数发现:
第11行对CARGUYS表的扫描,估计 42M 行,实际也是40M 行,估计值非常准确
第9行对CARGUYS表的扫描,估计 186K 行,实际 40M 行,估计值偏小200多倍

造成估计值严重偏低的原因是什么?
检查过滤条件信息: "MODEL"='458 Italia' AND "MAKE"='Ferrari' AND "COUNTRY"='Italy'
所有的三个过滤条件高度相关:Ferrari 只在 Italy 生产,只有 Ferrari 才生产 ‘458 Italia’ 这个车型。
优化器首先估计单独每个过滤条件的选择度,然后将三个单独的选择度做乘积得到最终的选择度,这导致估计值严重偏低。

案例1的解决方案:
使用扩展的统计信息,在 MAKE, MODEL, COUNTRY 三个字段上创建一个列的组合。现在优化器可以准确的估计这三个过滤条件组合在一起的选择度。估计值变成了40M,并行执行的数据分发方式从 BROADCAST 变成了 HASH-HASH 的模式。从而得到一个更高效的 HASH Join,也不需要使用临时表空间,查询仅需3秒钟即可完成。


第2个案例的查询耗时1.2分钟。通过SQL Monitor报告可以看到LINEORDER表扫描消耗了绝大部分的时间,估计的行数看起来都偏差很大,采用NESTED LOOP的Join方式。那是哪个表的统计信息有问题呢?采用NESTED LOOP的Join方式的时候首先需要关注驱动表的估计值,驱动表返回的每一行,都要扫描一次被驱动表。驱动表SUPPLIER估计返回1行,实际返回708行,被驱动表LINEORDER需要被访问的次数严重低估了。

案例2的解决方案:重新收集SUPPLIER的统计信息,LINEORDER的统计信息保持不变。优化器采用HASH JOIN代替NESTED LOOP JOIN。

第3个案例的查询耗时56分钟,最耗时的部分是 HASH JOIN BUFFERED,估计的行数都是准确的。但是消耗大量的TEMP IO,而且扫描1TB 的表耗时1800秒,远远低于系统的能力。表扫描受到HASH JOIN缓存的影响。

检查Parallel Tab页,可以看到在实例6上并行进程消耗的数据库时间高很多。展开实例6的并行进程组1的详情,可以看到其中一个PX进程消耗了53分钟的数据库时间,查询的大部分时间只有一个并行PX进程在执行. 造成这个问题的背景知识:应用要支持高级会员卡服务,绝大部分的用户号码是未知或者没有标识的,使用默认值来实现: UserID = -1。做HASH算法时,UserID= -1 的值会落到同一个hash bucket 里,在HASH JOIN过程中,所有 UserID= -1 的记录只能通过一个PX 进程来处理。

案例3解决方案:一个方案是对于未知的客户,使用随机的一个负数来表示,而不是统一的一个值;另外一个解决方案是还保持原来默认值“-1”来标识,但是执行两个单独的查询来实现:
一个查询针对已知的客户,比如: CUST_IDENTIFIER_ID > 0,这个查询使用HASH HASH 的并行分发方式,和之前一样;
另一个查询针对未知的客户: CUST_IDENTIFIER_ID = -1,这个查询使用BROADCAST 并行分发方式。


第4个案例是19c的none-flash版本的SQL Monitor报告,其查询耗时20分钟,大部分时间消耗在HASH Group By上,最大消耗330GB的TEMP。TEMP是造成执行效率不高的根本原因吗?通过分析SQL Monitor报告可以知道,需要做Group By的行数估计值和实际行数差别巨大。虽然对于单个表的估计值都很准确,但是store_sales 和store_returns Join之后的行数估计值比实际值小100倍,Join的条件有两个:
"SS_ITEM_SK"="SR_ITEM_SK" AND "SR_TICKET_NUMBER"="SS_TICKET_NUMBER"
优化器选择在Join store_sales和store_returns后先做Group By,再将结果和date_dim表Join


案例4的解决方案:分别在表store_sales(SS_ITEM_SK,SS_TICKET_NUMBER)和表store_returns(SR_ITEM_SK, SR_TICKET_NUMBER)的两个列上创建扩展统计信息。估计值准确后Join顺序发生改变,优化器选择先Join date_dim和 store_sales,再将结果和store_returns表Join。最后再在更小的结果集上做Group By,极大减少了需要的TEMP,执行时间7.6分钟。


第5个案例查询耗费 42 分钟,都是消耗CPU。循环进行全表扫描消耗大量时间,优化器估计只需要一次全表扫描,而实际执行了 5776 次全表扫描。通过查看Nested Loop的驱动表的过滤条件,其中的SUBSTR() 函数导致不好的统计信息估计。

案例5的解决方案:在 SUBSTR() 函数上收集扩展的统计信息,现在优化器估计返回141行而不是1行,优化器选择Hash连接而不是嵌套循环。大表只需要扫描一次,执行时间: 6 秒。


最后总结了SQL Monitor 报告分析的注意事项:
 除非是索引唯一扫描,否则估计值为 1 的那些步骤一定要引起重视
 查看实际行数的时候同时要考虑执行数
 对于Nested Loop表关联的执行计划
 估计的行数是基于单次执行的
 而实际的行数是针对所有执行数的总和
 并行进程的响应时间进度条长度和实际时间的大小可能不完全成比例
 SQL Monitor报告保存在一个有限的内存空间里,意味着被Monitor的语句会因为过期而被刷出内存
 被Monitor的SQL执行计划行数默认上限300,超过这个行数的不会被Monitor
 可以通过 _sqlmon_max_planlines 参数来修改

本次课程的回顾就到这里,祝大家周末愉快。

Comments

Popular posts from this blog

SQL Monitor and SQL Quarantine