Do you have enough Redo?

Dec 23, 2013 / By Timur Akhmadeev

Tags: , , , ,

The question of whether a database has enough redo logs available is quite common. The documentation suggests to use FAST_START_MTTR_TARGET and V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE to identify “the optimal” redo log size based on the target recovery time. I’ve never used it and can’t comment if it is a correct/reasonable way or not. The easiest way to identify if you need to increase the redo log files size is to check for ‘log file switch (checkpoint incomplete)’ waits, and, depending on the total wait time, decide how much more redo you need. The obvious source of such information is Statspack or AWR. I have written a query which pulls the necessary information and calculates the amount of redo you need to add based on the total wait time.

Here is the query and an example output from 10.2.0.4 database:

set pagesize 50000 linesize 300

col instance_number             format 99                   head 'In|st'
col tim                                                     head 'Period end'
col cpu_sec                     format 999,999,999.9        head 'CPU used|sec'
col phy_reads                   format 999,999,999          head 'Physical|reads'
col phy_writes                  format 999,999,999          head 'Physical|writes'
col cr_served                   format 999,999,999          head 'CR blocks|served'
col current_served              format 999,999,999          head 'CUR blocks|served'
col redo_mb                     format 999,999,999.9        head 'Redo, MB'
col processes                   format 999,999              head 'Proc|esses'
col avg_df_seq                  format 9,999.9              head 'Avg 1|read'
col avg_df_scat                 format 9,999.9              head 'Avg N|read'
col redo_diff_to_md_pct         format 999,999              head 'Redo Diff|to median, %'
col avg_lfpw                    format 999.99               head 'Avg|LFPW'
col avg_log_sync                format 9,999.99             head 'Avg Log|Sync, ms'
col log_ckpt_sec                format 999,999              head 'CKPT|waits, s'
col redo_needed                 format 999,999              head 'Redo to|Add, MB'

compute max of cpu_sec          on instance_number
compute max of phy_reads        on instance_number
compute max of phy_writes       on instance_number
compute max of cr_served        on instance_number
compute max of current_served   on instance_number
compute max of phy_writes       on instance_number
compute max of redo_needed      on instance_number
compute max of log_ckpt_sec     on instance_number
compute max of avg_log_sync     on instance_number
compute max of avg_lfpw         on instance_number
compute max of redo_mb          on instance_number
compute max of processes        on instance_number
compute max of avg_df_seq       on instance_number
compute max of avg_df_scat      on instance_number

break on instance_number skip page

with t_interval as
(
 select /*+ inline */ sysdate-30 begin, sysdate as end from dual
)
select
  stats.dbid                                                                 dbid,
  stats.instance_number                                                      instance_number,
  to_char(stats.snap_time, 'YYYYMMDD HH24MI')                                tim,
  stats.cpu_used / 100                                                       cpu_sec,
  stats.phy_reads                                                            phy_reads,
  stats.phy_writes                                                           phy_writes,
  stats.cr_served                                                            cr_served,
  stats.current_served                                                       current_served,
  stats.redo_size / 1024 / 1024                                              redo_mb,
  procs.current_utilization                                                     processes,
--
  waits.df_seq_micro / 1000 / nullif(waits.df_seq_waits,0)                   avg_df_seq,
  waits.df_scat_micro / 1000 / nullif(waits.df_scat_waits,0)                 avg_df_scat,
  (stats.redo_size - stats.md_redo_size) * 100 / stats.md_redo_size          redo_diff_to_md_pct,
  stats.redo_write_time*10/stats.redo_writes                                 avg_lfpw,
  waits.log_sync_micro/nullif(waits.log_sync_waits, 0) / 1000                avg_log_sync,
  waits.log_ckpt_micro/1e6                                                   log_ckpt_sec,
  ( stats.redo_size /
     ( nullif(waits.snap_interval, 0) * 86400 ) ) *
   ( waits.log_ckpt_micro/1e6 ) / 1024 / 1024                                redo_needed,
  stats.is_restart
from
  (
   select
     snap_id,
     snap_time,
     snap_interval,
     instance_number,
     dbid,
     log_sync_micro,
     log_sync_waits,
     log_ckpt_micro,
     log_ckpt_waits,
     df_seq_micro,
     df_seq_waits,
     df_scat_micro,
     df_scat_waits,
     direct_micro,
     direct_waits,
     median(log_sync_micro/nullif(log_sync_waits, 0)) over (partition by dbid, instance_number) md_log_sync_micro
   from
   (
      select
        snap_id,
        snap_time,
        instance_number,
        dbid,
        max(snap_interval) snap_interval,
        max(decode(event, 'log file sync',                            wait_micro))    log_sync_micro,
        max(decode(event, 'log file sync',                            total_waits))   log_sync_waits,
        max(decode(event, 'log file switch (checkpoint incomplete)',  wait_micro))    log_ckpt_micro,
        max(decode(event, 'log file switch (checkpoint incomplete)',  total_waits))   log_ckpt_waits,
        max(decode(event, 'db file sequential read',                  wait_micro))    df_seq_micro,
        max(decode(event, 'db file sequential read',                  total_waits))   df_seq_waits,
        max(decode(event, 'db file scattered read',                   wait_micro))    df_scat_micro,
        max(decode(event, 'db file scattered read',                   total_waits))   df_scat_waits,
        max(decode(event, 'direct path read',                         wait_micro))    direct_micro,
        max(decode(event, 'direct path read',                         total_waits))   direct_waits
      from
      (
        select
          e.snap_id,
          e.instance_number,
          e.dbid,
          sn.snap_time,
          snap_time - lag(snap_time) over (partition by e.dbid, e.instance_number, e.event order by sn.snap_time) snap_interval,
          sn.startup_time,
          e.event,
          case when (sn.snap_time >= sn.startup_time and lag(sn.snap_time) over (partition by e.dbid, e.instance_number, e.event order by sn.snap_time) < sn.startup_time)
            then e.time_waited_micro
            else e.time_waited_micro - lag(e.time_waited_micro) over (partition by e.dbid, e.instance_number, e.event order by sn.snap_time)
          end wait_micro,
          case when (sn.snap_time >= sn.startup_time and lag(sn.snap_time) over (partition by e.dbid, e.instance_number, e.event order by sn.snap_time) < sn.startup_time)
            then e.total_waits
            else e.total_waits - lag(e.total_waits) over (partition by e.dbid, e.instance_number, e.event order by sn.snap_time)
          end total_waits
        from
          stats$system_event e,
          stats$snapshot     sn,
          t_interval         t
        where
          sn.snap_id = e.snap_id and
          sn.dbid = e.dbid and
          sn.instance_number = e.instance_number and
          sn.snap_time between t.begin and t.end and
          e.event in (
            'log file sync',
            'log file switch (checkpoint incomplete)',
            'db file sequential read',
            'db file scattered read',
            'direct path read'
            )
      )
      group by dbid, instance_number, snap_time, snap_id
    )
  ) waits,
  (
    select
      snap_id,
      snap_time,
      instance_number,
      dbid,
      redo_size,
      redo_write_time,
      redo_writes,
      is_restart,
      cpu_used,
      phy_reads,
      phy_reads_cache,
      phy_writes,
      phy_writes_cache,
      cr_served,
      current_served,
      median(redo_size) over (partition by dbid, instance_number) md_redo_size
    from
    (
      select
        snap_id,
        snap_time,
        instance_number,
        dbid,
        max(is_restart) is_restart,
        max(decode(name, 'redo size',                       stat_diff)) redo_size,
        max(decode(name, 'redo write time',                 stat_diff)) redo_write_time,
        max(decode(name, 'redo writes',                     stat_diff)) redo_writes,
        max(decode(name, 'CPU used by this session',        stat_diff)) cpu_used,
        max(decode(name, 'physical read total IO requests', stat_diff)) phy_reads,
        max(decode(name, 'physical reads cache',            stat_diff)) phy_reads_cache,
        max(decode(name, 'physical write total IO requests',stat_diff)) phy_writes,
        max(decode(name, 'physical writes from cache',      stat_diff)) phy_writes_cache,
        max(decode(name, 'gc cr blocks served',             stat_diff)) cr_served,
        max(decode(name, 'gc current blocks served',        stat_diff)) current_served
      from
      (
        select
          stats.snap_id,
          stats.instance_number,
          stats.dbid,
          sn.snap_time,
          sn.startup_time,
          n.name,
          case when (sn.snap_time >= sn.startup_time and lag(sn.snap_time) over (partition by stats.dbid, stats.instance_number, stats.statistic# order by sn.snap_time) < sn.startup_time)
            then stats.value
            else stats.value - lag(stats.value) over (partition by stats.dbid, stats.instance_number, stats.statistic# order by stats.snap_id)
          end stat_diff,
          case when (sn.snap_time >= sn.startup_time and lag(sn.snap_time) over (partition by stats.dbid, stats.instance_number, stats.statistic# order by sn.snap_time) < sn.startup_time)
            then 'Yes'
          end is_restart
        from
          stats$sysstat      stats,
          stats$snapshot     sn,
          v$statname         n,
          t_interval         t
        where
          sn.snap_id = stats.snap_id and
          sn.dbid = stats.dbid and
          sn.instance_number = stats.instance_number and
          sn.snap_time between t.begin and t.end and
          stats.statistic# = n.statistic# and
          n.name in (
            'redo size',
            'redo write time',
            'redo writes',
            'CPU used by this session',
            'physical read total IO requests',
            'physical reads cache',
            'physical write total IO requests',
            'physical writes from cache',
            'gc cr blocks served',
            'gc current blocks served'
          )
      )
      group by dbid, instance_number, snap_time, snap_id
    )
  ) stats,
  (
    select
      stats.snap_id,
      stats.instance_number,
      stats.dbid,
      stats.resource_name,
      stats.current_utilization
    from
      stats$resource_limit stats,
      stats$snapshot       sn,
      t_interval           t
    where
      sn.snap_id = stats.snap_id and
      sn.dbid = stats.dbid and
      sn.instance_number = stats.instance_number and
      sn.snap_time between t.begin and t.end and
      stats.resource_name = 'processes'
  ) procs
where
  waits.dbid = stats.dbid and
  waits.instance_number = stats.instance_number and
  waits.snap_id = stats.snap_id and
  waits.dbid = procs.dbid and
  waits.instance_number = procs.instance_number and
  waits.snap_id = procs.snap_id
order by
 stats.dbid, stats.instance_number, stats.snap_time
;

 

                                Redo Diff     Avg   Avg Log     CKPT  Redo to
Period end          Redo, MB to median, %    LFPW  Sync, ms waits, s  Add, MB IS_
------------- -------------- ------------ ------- --------- -------- -------- ---
20131213 0800
20131213 0900        3,659.1           21    2.77      8.73       63       64
20131213 1000        4,964.8           64   15.06     15.45       90      125
20131213 1100        3,533.0           17   26.42      9.43      114      112
20131213 1200          206.5          -93   10.86      3.83        0        0
20131213 1300        1,363.5          -55   34.17     10.65       26       10
20131213 1400          133.4          -96    9.17      4.07        0        0
20131213 1500          123.7          -96    8.52      4.62        0        0
20131213 1600        2,037.8          -33   36.22      8.73       40       23
20131213 1700           13.6         -100    3.82      8.43        0        0
20131213 1800           60.7          -98   91.51    251.81                   Yes
20131213 1900        4,177.9           38    6.67     53.66       52       30
20131213 2000        3,145.3            4   39.61     12.79       41       35
20131213 2100        4,606.2           52   19.65      7.90       63       80
20131213 2200        3,303.8            9   77.90    115.53       73       67
20131213 2300        2,673.2          -12   57.37     85.43      123       91
20131214 0000        1,213.9          -60   56.35    155.38       83       28
20131214 0100        3,569.0           18  110.49    304.81      121      120
20131214 0200        5,587.7           84   17.92     26.62      123      191
20131214 0300        7,630.4          152   30.87     28.08      138      293
20131214 0400        5,706.7           88   37.36     39.41      179      284
20131214 0500        7,688.8          154   42.66     26.86      196      419
20131214 0600        1,377.6          -55   29.46     19.79       29       11
20131214 0700        2,176.3          -28   26.62     24.13       43       26
20131214 0800        2,179.6          -28   25.23     22.50       43       26
20131214 0900        3,892.0           28    2.54     14.32       93      101
20131214 1000        5,354.2           77   32.67     20.26      127      189
20131214 1100        4,232.6           40   49.24     86.27      108      127
20131214 1200        1,778.2          -41   25.76     10.03       29       14
20131214 1300           23.0          -99    4.07      6.14        0        0
20131214 1400        1,378.1          -55   32.63     14.99       23        9
20131214 1500        2,121.7          -30   34.60    285.24       38       22
20131214 1600          701.6          -77   39.29      9.90        7        1
20131214 1700        1,557.5          -49   43.51     52.96       24       10
20131214 1800          455.3          -85   35.41     57.68        1        0
20131214 1900        4,175.1           38    7.22     22.67       71       82
20131214 2000        3,116.7            3   45.78     20.85       72       63
20131214 2100        4,578.1           51   27.92      7.34       68       86
20131214 2200        3,751.4           24   22.54     25.00       73       76
20131214 2300        1,962.1          -35   52.08     46.90       33       18
20131215 0000        3,161.0            4   32.76     67.61       60       53
20131215 0100        6,516.9          115   46.88     52.77      147      267
20131215 0200        3,147.0            4   17.63     35.01      112       98
20131215 0300        5,084.3           68   50.88     95.94      164      232
20131215 0400        5,727.6           89   56.45     49.84      143      228
20131215 0500        7,783.3          157   59.43     53.49      198      428
20131215 0600        1,828.4          -40   68.17    136.70       46       23
20131215 0700          840.2          -72   39.24      8.99       20        5
20131215 0800        2,202.9          -27   28.78     28.14       42       26
20131215 0900        3,704.2           22    2.53     17.06       80       82
20131215 1000        4,847.2           60   32.70     57.14        6        8
20131215 1100        2,987.0           -1   29.51      9.84        0        0
20131215 1200          203.2          -93   16.36     10.84        0        0
20131215 1300           42.3          -99    1.12      2.00        0        0
20131215 1400          596.7          -80    9.57     12.94        0        0
20131215 1500          186.8          -94   10.67     11.89                   Yes
20131215 1600          942.0          -69    7.57     17.27
20131215 1700           14.5         -100    1.82      3.56
20131215 1800          462.6          -85    9.31      8.47
20131215 1900        3,762.0           24    4.79      8.23
20131215 2000        3,171.9            5   34.83     16.26
20131215 2100        4,525.7           49   28.30      6.52
20131215 2200        3,623.0           20   18.74     21.89        1        0
20131215 2300        2,625.7          -13   42.53     12.18        0        0
20131216 0000        2,256.5          -26   39.12     41.47        0        0
20131216 0100        6,061.9          100   93.86    129.36       11       19
20131216 0200        6,297.2          108   19.90     30.07        0        0
20131216 0300        5,079.6           68   23.35     59.40        0        0
20131216 0400        5,555.2           83   38.73     34.99        0        0
20131216 0500        7,560.7          150   46.22     61.08        0        0
20131216 0600        1,364.1          -55   35.62     34.54        0        0
20131216 0700        2,212.2          -27   35.83     16.32        0        0
20131216 0800        2,117.8          -30   25.73     12.24        0        0
20131216 0900        3,684.7           22   40.42     28.94        0        0
20131216 1000        6,502.9          115    5.65     29.35        0        1
20131216 1100        4,372.9           44   29.11     14.23        0        0
20131216 1200        1,742.8          -42   25.10     12.52        0        0
20131216 1300           31.1          -99    5.92     12.56        0        0
20131216 1400        1,486.6          -51   44.12     11.59        0        0
20131216 1500        6,108.9          102   36.05     38.18        0        0
20131216 1600          686.5          -77   27.43      7.14        0        0
20131216 1700        1,557.2          -49   22.99      7.41        0        0
20131216 1800          464.3          -85   26.43     25.12        0        0
20131216 1900        3,974.1           31    4.59      9.26        6        6
20131216 2000        3,102.7            2   30.15     28.33        0        0
20131216 2100        5,400.9           78   20.70      8.15        0        0
20131216 2200        3,130.8            3   11.57     15.79        0        0
20131216 2300        2,203.7          -27   48.37     63.28        0        0
20131217 0000        3,098.9            2   29.81     52.57        0        0
20131217 0100        3,909.3           29   71.10    169.28        0        0
20131217 0200        6,227.8          106   19.69     30.72        0        0
20131217 0300        7,325.3          142   27.64     45.12        0        0
20131217 0400        6,068.9          100   37.96     37.70        0        0
20131217 0500        7,231.6          139   35.11     26.07        0        0
20131217 0600        1,263.5          -58   24.18     13.22        0        0
20131217 0700        2,019.4          -33   25.45      5.84        0        0
20131217 0800        2,130.4          -30   22.27     12.88        0        0
20131217 0900        4,018.1           33    5.33     58.09        0        0
20131217 1000        5,193.5           71    6.36     63.90        2        3
20131217 1100        3,217.3            6   25.76     11.66        0        0
20131217 1200        1,779.8          -41   21.45      8.97        0        0
20131217 1300          734.1          -76   20.19      5.34        0        0
20131217 1400        2,697.0          -11   24.35      8.52        0        0
20131217 1500          122.0          -96   36.35     18.74        0        0
20131217 1600          817.4          -73   29.34      7.29        0        0
20131217 1700        1,616.1          -47   22.52      8.10        0        0
20131217 1800          466.8          -85   43.67     73.78        0        0
20131217 1900        3,915.3           29    4.58     10.88        0        0
20131217 2000        3,082.5            2   35.76     21.84        0        0
20131217 2100        5,320.9           76   20.40      8.94        0        0
20131217 2200        3,020.1           -0   52.09     44.66        1        1
20131217 2300        2,034.3          -33   43.56     62.13        0        0
20131218 0000        2,723.5          -10   30.35     51.05        0        0
20131218 0100        4,740.9           56   61.93    124.11        0        0
20131218 0200        7,426.3          145   19.91     32.82        0        0
20131218 0300        4,703.2           55   21.78     28.80        0        0
20131218 0400        5,658.4           87   37.75     20.70        0        0
20131218 0500        7,858.4          159   42.53     29.13        0        0
20131218 0600        1,304.4          -57   30.40      9.91        0        0
20131218 0700        2,143.6          -29   28.20     13.88        1        1
20131218 0800        2,078.0          -31   24.94     18.97        0        0
20131218 0900        3,833.6           27    4.17     22.95        2        2
20131218 1000        5,933.3           96   10.73     19.28        0        0
20131218 1100        2,533.8          -16   23.92     11.09        0        0
20131218 1200        1,775.0          -41   21.39      9.89        0        0
20131218 1300           31.5          -99    3.95      4.02        0        0
20131218 1400        1,916.7          -37   28.73     15.65        0        0
20131218 1500        1,794.5          -41   34.64    215.95        0        0
20131218 1600          876.2          -71   27.94      4.64        0        0
20131218 1700        1,544.7          -49   21.90      6.72        0        0
20131218 1800          465.2          -85   26.04     25.71        0        0
20131218 1900        3,662.1           21    4.19      7.11        0        0
20131218 2000        3,085.3            2   34.51     16.00        0        0
20131218 2100        5,286.9           75   20.71      7.70        0        0
20131218 2200        3,072.3            1   15.16     11.18        0        0
20131218 2300        2,489.8          -18   22.96     17.53        0        0
20131219 0000        2,249.1          -26   31.52     77.49        2        1
20131219 0100        3,466.3           14  169.29    321.38        7        7
20131219 0200        4,365.9           44   11.78     25.60        0        0
20131219 0300        9,837.6          225   32.10     42.25       12       33
20131219 0400        7,251.2          139   42.75     27.18        0        0
20131219 0500        8,428.8          178   40.70     22.69        0        0
20131219 0600        1,304.1          -57   25.98      8.51        0        0
20131219 0700        2,026.6          -33   28.31     14.68        0        0
20131219 0800        2,413.5          -20    8.62      1.15        0        0
20131219 0900        4,025.8           33    3.17     19.64        0        0
...

This query is large and can print not only redo-related statistics, you just need to uncomment then necessary columns. The columns which are relevant to this post are “CKPT waits, s” and “Redo To Add, MB”. First is total time wasted due to ‘log file switch (checkpoint incomplete)’ waits, and the second is the projected additional amount of redo which might have been enough to reduce the waits down to minimum. When calculating this projected size, the average redo rate per hour is used which is then multiplied by the total time waited. Of course this is an incorrect approximation: if you had 10 sessions waiting for the checkpoint to complete, then the total time waited by sessions is obviously more than wall clock time, which means that multiplying it by an average redo rate is totally wrong; but it is almost correct with a database that at any point in time has only 1 session waiting for checkpoint to complete. Nevertheless I think this approximation is a good starting point.
In this particular case, the database had 4 groups of redo of 60MB each, and on December 15th it has been changed to 4 groups of 150MB each. As you can see it helped to reduce the number of ‘checkpoint incomplete’ waits significantly, although not completely. If you look at the redo rate per hour, it is clear that after the redo log size change the rate of database changes has also increased, so the waits are somewhat consequence of the ability to generate more changes per second now.

And just for the sake of completeness here is a version of this query for AWR. I didn’t test it much but it should be fine as most things are similar to Statspack tables. Enjoy!

set pagesize 50000 linesize 300

col instance_number             format 99                   head 'In|st'
col tim                                                     head 'Period end'
col cpu_sec                     format 999,999,999.9        head 'CPU used|sec'
col phy_reads                   format 999,999,999          head 'Physical|reads'
col phy_writes                  format 999,999,999          head 'Physical|writes'
col cr_served                   format 999,999,999          head 'CR blocks|served'
col current_served              format 999,999,999          head 'CUR blocks|served'
col redo_mb                     format 999,999,999.9        head 'Redo, MB'
col processes                   format 999,999              head 'Proc|esses'
col avg_df_seq                  format 9,999.9              head 'Avg 1|read'
col avg_df_scat                 format 9,999.9              head 'Avg N|read'
col redo_diff_to_md_pct         format 999,999              head 'Redo Diff|to median, %'
col avg_lfpw                    format 999.99               head 'Avg|LFPW'
col avg_log_sync                format 9,999.99             head 'Avg Log|Sync, ms'
col log_ckpt_sec                format 999,999              head 'CKPT|waits, s'
col redo_needed                 format 999,999              head 'Redo to|Add, MB'

compute max of cpu_sec          on instance_number
compute max of phy_reads        on instance_number
compute max of phy_writes       on instance_number
compute max of cr_served        on instance_number
compute max of current_served   on instance_number
compute max of phy_writes       on instance_number
compute max of redo_needed      on instance_number
compute max of log_ckpt_sec     on instance_number
compute max of avg_log_sync     on instance_number
compute max of avg_lfpw         on instance_number
compute max of redo_mb          on instance_number
compute max of processes        on instance_number
compute max of avg_df_seq       on instance_number
compute max of avg_df_scat      on instance_number

break on instance_number skip page

with t_interval as
(
 select /*+ inline */ sysdate-30 begin, sysdate as end from dual
)
select
  stats.dbid                                                                dbid,
  stats.instance_number                                                     instance_number,
  to_char(stats.begin_interval_time, 'YYYYMMDD HH24MI')                     tim,
  stats.cpu_used / 100                                                      cpu_sec,
  stats.phy_reads                                                           phy_reads,
  stats.phy_writes                                                          phy_writes,
  stats.cr_served                                                           cr_served,
  stats.current_served                                                      current_served,
  stats.redo_size / 1024 / 1024                                             redo_mb,
  procs.current_utilization                                                 processes,
--
  waits.df_seq_micro / 1000 / nullif(waits.df_seq_waits,0)                  avg_df_seq,
  waits.df_scat_micro / 1000 / nullif(waits.df_scat_waits,0)                avg_df_scat,
  (stats.redo_size - stats.md_redo_size) * 100 / stats.md_redo_size         redo_diff_to_md_pct,
  stats.redo_write_time*10/stats.redo_writes                                avg_lfpw,
  waits.log_sync_micro/nullif(waits.log_sync_waits, 0) / 1000               avg_log_sync,
  waits.log_ckpt_micro/1e6                                                  log_ckpt_sec,
  ( stats.redo_size /
     ( waits.snap_interval * 86400 ) ) *
   ( waits.log_ckpt_micro/1e6 ) / 1024 / 1024                               redo_needed,
  stats.is_restart
from
  (
   select
     snap_id,
     begin_interval_time,
     snap_interval,
     instance_number,
     dbid,
     log_sync_micro,
     log_sync_waits,
     log_ckpt_micro,
     log_ckpt_waits,
     df_seq_micro,
     df_seq_waits,
     df_scat_micro,
     df_scat_waits,
     direct_micro,
     direct_waits,
     median(log_sync_micro/nullif(log_sync_waits, 0)) over (partition by dbid, instance_number) md_log_sync_micro
   from
   (
      select
        snap_id,
        begin_interval_time,
        instance_number,
        dbid,
        max(snap_interval) snap_interval,
        max(decode(event_name, 'log file sync',                            wait_micro))    log_sync_micro,
        max(decode(event_name, 'log file sync',                            total_waits))   log_sync_waits,
        max(decode(event_name, 'log file switch (checkpoint incomplete)',  wait_micro))    log_ckpt_micro,
        max(decode(event_name, 'log file switch (checkpoint incomplete)',  total_waits))   log_ckpt_waits,
        max(decode(event_name, 'db file sequential read',                  wait_micro))    df_seq_micro,
        max(decode(event_name, 'db file sequential read',                  total_waits))   df_seq_waits,
        max(decode(event_name, 'db file scattered read',                   wait_micro))    df_scat_micro,
        max(decode(event_name, 'db file scattered read',                   total_waits))   df_scat_waits,
        max(decode(event_name, 'direct path read',                         wait_micro))    direct_micro,
        max(decode(event_name, 'direct path read',                         total_waits))   direct_waits
      from
      (
        select
          e.snap_id,
          e.instance_number,
          e.dbid,
          sn.begin_interval_time,
          cast(begin_interval_time as date) - cast(lag(begin_interval_time) over (partition by e.dbid, e.instance_number, e.event_name order by sn.begin_interval_time) as date) snap_interval,
          sn.startup_time,
          e.event_name,
          case when (sn.begin_interval_time >= sn.startup_time and lag(sn.begin_interval_time) over (partition by e.dbid, e.instance_number, e.event_name order by sn.begin_interval_time) < sn.startup_time)
            then e.time_waited_micro
            else e.time_waited_micro - lag(e.time_waited_micro) over (partition by e.dbid, e.instance_number, e.event_name order by sn.begin_interval_time)
          end wait_micro,
          case when (sn.begin_interval_time >= sn.startup_time and lag(sn.begin_interval_time) over (partition by e.dbid, e.instance_number, e.event_name order by sn.begin_interval_time) < sn.startup_time)
            then e.total_waits
            else e.total_waits - lag(e.total_waits) over (partition by e.dbid, e.instance_number, e.event_name order by sn.begin_interval_time)
          end total_waits
        from
          dba_hist_system_event e,
          dba_hist_snapshot     sn,
          t_interval            t
        where
          sn.snap_id = e.snap_id and
          sn.dbid = e.dbid and
          sn.instance_number = e.instance_number and
          sn.begin_interval_time between t.begin and t.end and
          e.event_name in (
            'log file sync',
            'log file switch (checkpoint incomplete)',
            'db file sequential read',
            'db file scattered read',
            'direct path read'
          )
      )
      group by dbid, instance_number, begin_interval_time, snap_id
    )
  ) waits,
  (
    select
      snap_id,
      begin_interval_time,
      instance_number,
      dbid,
      redo_size,
      redo_write_time,
      redo_writes,
      is_restart,
      cpu_used,
      phy_reads,
      phy_reads_cache,
      phy_writes,
      phy_writes_cache,
      cr_served,
      current_served,
      median(redo_size) over (partition by dbid, instance_number) md_redo_size
    from
    (
      select
        snap_id,
        begin_interval_time,
        instance_number,
        dbid,
        max(is_restart) is_restart,
        max(decode(stat_name, 'redo size',                       stat_diff)) redo_size,
        max(decode(stat_name, 'redo write time',                 stat_diff)) redo_write_time,
        max(decode(stat_name, 'redo writes',                     stat_diff)) redo_writes,
        max(decode(stat_name, 'CPU used by this session',        stat_diff)) cpu_used,
        max(decode(stat_name, 'physical read total IO requests', stat_diff)) phy_reads,
        max(decode(stat_name, 'physical reads cache',            stat_diff)) phy_reads_cache,
        max(decode(stat_name, 'physical write total IO requests',stat_diff)) phy_writes,
        max(decode(stat_name, 'physical writes from cache',      stat_diff)) phy_writes_cache,
        max(decode(stat_name, 'gc cr blocks served',             stat_diff)) cr_served,
        max(decode(stat_name, 'gc current blocks served',        stat_diff)) current_served
      from
      (
        select
          stats.snap_id,
          stats.instance_number,
          stats.dbid,
          sn.begin_interval_time,
          sn.startup_time,
          stats.stat_name,
          case when (sn.begin_interval_time >= sn.startup_time and lag(sn.begin_interval_time) over (partition by stats.dbid, stats.instance_number, stats.stat_id order by sn.begin_interval_time) < sn.startup_time)
            then stats.value
            else stats.value - lag(stats.value) over (partition by stats.dbid, stats.instance_number, stats.stat_id order by stats.snap_id)
          end stat_diff,
          case when (sn.begin_interval_time >= sn.startup_time and lag(sn.begin_interval_time) over (partition by stats.dbid, stats.instance_number, stats.stat_id order by sn.begin_interval_time) < sn.startup_time)
            then 'Yes'
          end is_restart
        from
          dba_hist_sysstat   stats,
          dba_hist_snapshot  sn,
          t_interval         t
        where
          sn.snap_id = stats.snap_id and
          sn.dbid = stats.dbid and
          sn.instance_number = stats.instance_number and
          sn.begin_interval_time between t.begin and t.end and
          stats.stat_name in (
            'redo size',
            'redo write time',
            'redo writes',
            'CPU used by this session',
            'physical read total IO requests',
            'physical reads cache',
            'physical write total IO requests',
            'physical writes from cache',
            'gc cr blocks served',
            'gc current blocks served'
          )
      )
      group by dbid, instance_number, begin_interval_time, snap_id
    )
  ) stats,
  (
    select
      stats.snap_id,
      stats.instance_number,
      stats.dbid,
      stats.resource_name,
      stats.current_utilization
    from
      dba_hist_resource_limit stats,
      dba_hist_snapshot       sn,
      t_interval              t
    where
      sn.snap_id = stats.snap_id and
      sn.dbid = stats.dbid and
      sn.instance_number = stats.instance_number and
      sn.begin_interval_time between t.begin and t.end and
      stats.resource_name = 'processes'
  ) procs
where
  waits.dbid = stats.dbid and
  waits.instance_number = stats.instance_number and
  waits.snap_id = stats.snap_id and
  waits.dbid = procs.dbid and
  waits.instance_number = procs.instance_number and
  waits.snap_id = procs.snap_id
order by
 stats.dbid, stats.instance_number, stats.begin_interval_time
;

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>