Oracle full table scan 的跟踪监视

最近一个客户的数据库反应不定时的性能问题,经过跟踪发现是一系列的全表扫相关SQL,其中还碰到了比较诡异的全表扫都是顺序读的情况.和周亮以前也讨论过此类情况只是当时这方面的事情并没有太多的眉目,这次跟踪发现了是和undo一致性读有关,还存在大量的row chains.当然这里不讨论这个case,这个case可能会在新书中描述.这个文章不描述具体的原理,只是作为一个对数据库全表扫相关信息收集诊断建议.

在跟踪某一特定的事件时候,有时候没有监控系统或者其他相关捕获信息的手段,则诊断起来比较耗时间,比如跟踪系统中全表扫的情况,默认情况下是只能获取当时时刻具体的情况,这里介绍通过AWR记录中来获取相关的信息,由于AWR从v$sql里面获取的sql是有条件限制的,因此该方法并不能补全数据库中所有的全表扫相关sql(其他情况也是),因为awr收集sql的情况是以statistics_level 的设置为依赖,typical(top 30)或者all(top 100)收集的top sql信息不同.

 

1.周期的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
COUNT(1) c2
FROM dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
WHERE p.object_owner <> 'SYS'
AND p.object_owner = o.owner
AND p.object_name = o.segment_name
AND o.blocks > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Full-table scans Per Snapshot Period
Begin
 Interval FTS
 time Count
 -------------------- --------
 04-10-18 11 4
 04-10-21 17 1
 04-10-21 23 2
 04-10-22 15 2
 04-10-22 16 2
 04-10-22 23 2
 04-10-24 00 2

2.每小时的全表扫信息

col c1 heading "Day|Hour" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Table Full-table scans
Averages per Hour

Day                       FTS
Hour                    Count
-------------------- --------
00                          4
10                          2
11                          4
12                         23
13                         16
14                          6
15                         17
16                         10
17                         17
18                         21
19                          1
23                          6

3.基于星期的全表扫信息

col c1 heading "Week|Day" format a20
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'day') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'day')
ORDER BY 1;

查询结果类似如下:

Week                      FTS
Day                     Count
-------------------- --------
sunday                      2
monday                     19
tuesday                    31
wednesday                  34
thursday                   27
friday                     15
Saturday                    2

以上脚本作者为Donald K. Burleson.
我对此脚本做了些变更,除了生成html格式外额外加入索引类的信息,变更的脚本未在blog上发布.