direct path read temp等待引发的job异常耗时

问题引出

巡检发现，某job运行耗时异常，原来一般3分钟完成的到后来需要90分钟，指标不能及时计算出来。

问题分析1

根据任务运行日志应该是计算f指标时出现了异常耗时，但session工具对应的sql为空，对应的Event是direct path read temp。
从历史会话快照和历史Sql中查看包含该事件最多的sql。

select sql_id, count(*)
  from dba_hist_active_sess_history
 where event = 'direct path read temp'
 group by sql_id;

select * from DBA_HIST_SQLTEXT where sql_id = '1q373n80fyvhq';

结合awr日志也是该sql最耗时。
最终确认是计算f指标的sql异常了。

1.direct path read temp等待事件

• 原理
  从临时表空间直接读取数据到pga。
  当大量数据的排序、group、union、hash操作在pga中无法直接完成时，需要借用temp表空间完成，然后再从temp表空间读取中间结果时发生的等待事件。
  direct path:指将数据直接硬盘读取到pga，不经过sga。
  temp:指读取的是临时表空间，与direct path read对应。

• 参数说明
  p1:读取数据文件的绝对文件号码file number。临时文件的文件号
  p2:起始块号first dba
  p3:要读取的块数block cnt
  可参考系统视图dba_extents，包括extent_id、起始块id、extent大小、包含的块数。

问题分析2

查看执行计划，未发现明显异常。
因为任务一开始是正常的，到4月8日开始时间越来越慢。难道是临时表空间不够，导致频繁的在小部分的temp里读写？需要查看临时表空间使用情况

2.临时表空间的使用

• 临时表空间的用途
  1 排序、group、union
  2 hash join
  3 并行操作
• 临时表空间剩余
  查看表空间剩余：select * from dba_temp_free_space;
  剩余空间还很多。

问题分析3

再回到event，计算f的sql有用笛卡尔积的设计，难道笛卡尔积导致数据量巨大？
检查数据量：原始数据量1343996，乘积后2499417。200多万的记录占空间不过300M，pga放不下吗？看看pga

3.pga的使用

1)pga的用途

1 server process专属，存放控制及数据信息
2 排序、hash

2)pga的使用情况

通过pga统计查看v$pgastat，关键条目包括：

条目名称	条目含义
aggregate PGA target parameter	pga_aggregate_target参数大小
total PGA allocated	已分配的pga大小
total PGA inuse	已使用的pga
total freeable PGA memory	空闲的pga

pga_aggregate_target参数是5G，已使用的远远不到这个数。

3)pga大小

查看awr报告，pga_advisor并没有建议增加pga大小。
但查阅网上资料，pga大小经验值：

系统类型	PGA大小
oltp型	物理内存*0.8*0.2
dss 型	物理内存*0.8*0.4

根据经验公式，服务器的pga可以设置到25G，修改pga_aggregate_target。

问题分析4

重新执行sql检查效果，效果不明显。200多万记录，全放内存也不过300M，应该用不到temp的。
再看执行计划，发现对表a的操作很诡异，注释表a部分，sql后执行速度大大提升。
注：（表a是老口径需要的表，新口径下多余，但表数据量不大且有索引，就没修改口径）。
比较2个执行计划，发现耗时的多了merge join cartesian。

4.merge join cartesian

笛卡尔积

问题分析5

为什么引入了笛卡尔积呢？
综合之前的分析，temp没问题、pga没问题、开始没问题运行一段时间出现问题，是不是统计信息失效了呢？

5.统计信息

1)查看 dba_tab_statistics

果然，程序依赖的4个表统计信息全部失效。手工收集后，恢复正常。

6.排障的一般思路

其实绕了一圈，最后回到了简单却关键的统计信息上，排查的过程值得梳理下。
- 1)运行过程中，程序和数据量没有改变，耗时越来越长的，第一反应应该是统计信息
- 2)排查应该从简单到复杂，先排除容易确定的，如统计信息
- 3)trouble shoot考验的知识很全面，多做总结，关联知识点，点到面才记得牢