2 Replies Latest reply on Feb 23, 2013 8:01 AM by user622061

    How to measure "log file sync" from views


      1) How can we measure "log file sync" and "log file parallel write" from views ?
      On our database, some experts - that are gone- said that it seems to be respectively 24ms and 8ms.

      2) I'm trying to understand the view v$event_histogram, what does the column WAIT_TIME_MILLI mean ? can we use this view to extract measure of "log file sync" speed ?

      Thank you very much in your answer.
        • 1. Re: How to measure "log file sync" from views
          Nikolay Savvinov

          1) you should be more clear about what you mean by "measure". Generally, V$ views are useless for investigating how much a certain event affects database work, because information there is not tied to any specific timeframe (these views simply accumulate instance statistics since the last startup). If you're only interested in averages, then it's quite straightforward:
          select event, average_wait
          from v$system_event
          where event in ('log file sync', 'log file parallel write');
          2) V$EVENT_HISTOGRAM view is for understanding distribution of wait times. For example, if average time is 8 ms it can mean that all events take roughly 8ms, or it could mean that half of events take 1ms while the other half 15ms, or that 99% events take less than 1ms while 1% of multisecond waits brings the average up to 8ms, etc. These are very different scenarios. In order to understand which you are dealing with, you can use V$EVENT_HISTOGRAM.

          WAIT_TIME_MILLI denotes histogram bins in milliseconds of wait time. E.g. WAIT_TIME_MILLI = 32 means that this row shows counts of events that take between 16 and 32 milliseconds.

          Best regards,
          • 2. Re: How to measure "log file sync" from views
            Hi nikolay, thank you very much for your very interesting answer.
            I will use these informations next monday on the office, and check the result.
            Thank you very much.