在statspack中需要模仿AWR的delta列,为了避免self-join带来的开销,我使用了Oracle中强悍的分析函数,可以对相邻的两个snap做差值计算,SQL语句有点长,不过关键点就是两处,一个是over(partition by…)将hash值和address值相同的sql分成一组,然后使用lag函数将统计值下移一行并做差值计算,这个结果基本上就是大致的结果了,不过还要注意两点,就是每组的第一个snap没有前一个snap做差值,这边使用nvl将前一个snap做0处理,但实际上这个snap的值就变成了累积值,所以要将其过滤,这里使用分析函数row_number()计算行号并做rn > 1的处理就将每组的第一个累积值给排除了,另外一点就是那些累计值在同一个实例周期内重新计算的情况,且hash value、address都没改变的SQL[我想到的可能的情况就是这些SQL age out之后又被重新加载],从而age out之前和之后的两个snap[如果那条SQL在age out之后的下一次快照收集之前被重新加载的话],则两个snap的差值是一个负值,为了规避这种情况,我使用了case子句进行了选择,如果是负数,则取重新加载之后的第一次累计值。

以stats$sql_summary为例,完整的SQL如下:

select *
  from (select a.*,
               case
                 when (executions –
                      nvl(lag(executions, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (executions –
                  nvl(lag(executions, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  executions
               end executions_delta,
               case
                 when (fetches –
                      nvl(lag(fetches, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (fetches –
                  nvl(lag(fetches, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  fetches
               end fetches_delta,
               case
                 when (parse_calls –
                      nvl(lag(parse_calls, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (parse_calls –
                  nvl(lag(parse_calls, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  parse_calls
               end parse_calls_delta,
               case
                 when (disk_reads –
                      nvl(lag(disk_reads, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (disk_reads –
                  nvl(lag(disk_reads, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  disk_reads
               end disk_reads_delta,
               case
                 when (buffer_gets –
                      nvl(lag(buffer_gets, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (buffer_gets –
                  nvl(lag(buffer_gets, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  buffer_gets
               end buffer_gets_delta,
               case
                 when (cpu_time –
                      nvl(lag(cpu_time, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (cpu_time –
                  nvl(lag(cpu_time, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  cpu_time
               end cpu_time_delta,
               case
                 when (elapsed_time –
                      nvl(lag(elapsed_time, 1) over(partition by hash_value,
                                address order by snap_id),
                           0)) >= 0 then
                  (elapsed_time –
                  nvl(lag(elapsed_time, 1)
                       over(partition by hash_value, address order by snap_id),
                       0))
                 else
                  elapsed_time
               end elapsed_time_delta,
            
               row_number() over(partition by hash_value, address order by snap_id) rn
          from stats$sql_summary a)
where rn > 1




Related posts

coded by nessus
, ,
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

only 1 comment untill now

  1. wyman@deficient.historian” rel=”nofollow”>.…

    hello….

Add your comment now

无觅相关文章插件