Trend Oracle log history : How much archives created per day/week or in an hour

Posted By Sagar Patil

Count of archive files and size of the redo generated by day

SELECT   A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb
  FROM   (  SELECT   TO_CHAR (First_Time, 'YYYY-MM-DD') DAY,
                     COUNT (1) Count#,
                     MIN (RECID) Min#,
                     MAX (RECID) Max#
              FROM   v$log_history
          GROUP BY   TO_CHAR (First_Time, 'YYYY-MM-DD')
          ORDER BY   1 DESC) A, (SELECT   AVG (BYTES) AVG#,
                                          COUNT (1) Count#,
                                          MAX (BYTES) Max_Bytes,
                                          MIN (BYTES) Min_Bytes
                                   FROM   v$log) B;

DAY LOG COUNT DAILY_AVG_MB
02/07/2010 98 4480
01/07/2010 126 5760
30/06/2010 54 2469
29/06/2010 28 1280
28/06/2010 37 1691
27/06/2010 14 640
26/06/2010 14 640
25/06/2010 14 640
24/06/2010 19 869
23/06/2010 14 640

Copy this table into Excel & click on Chart Wizard. Select X,Y co-ordinates and you will get a nice picture like this

Count of archive files and size of the redo generated every hour

  SELECT   TO_CHAR (first_time, 'YYYY-MON-DD') day,
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '00', 1, 0)),
                    '99')
              "00",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '01', 1, 0)),
                    '99')
              "01",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '02', 1, 0)),
                    '99')
              "02",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '03', 1, 0)),
                    '99')
              "03",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '04', 1, 0)),
                    '99')
              "04",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '05', 1, 0)),
                    '99')
              "05",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '06', 1, 0)),
                    '99')
              "06",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '07', 1, 0)),
                    '99')
              "07",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '08', 1, 0)),
                    '99')
              "0",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '09', 1, 0)),
                    '99')
              "09",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '10', 1, 0)),
                    '99')
              "10",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '11', 1, 0)),
                    '99')
              "11",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '12', 1, 0)),
                    '99')
              "12",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '13', 1, 0)),
                    '99')
              "13",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '14', 1, 0)),
                    '99')
              "14",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '15', 1, 0)),
                    '99')
              "15",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '16', 1, 0)),
                    '99')
              "16",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '17', 1, 0)),
                    '99')
              "17",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '18', 1, 0)),
                    '99')
              "18",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '19', 1, 0)),
                    '99')
              "19",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '20', 1, 0)),
                    '99')
              "20",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '21', 1, 0)),
                    '99')
              "21",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '22', 1, 0)),
                    '99')
              "22",
           TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '23', 1, 0)),
                    '99')
              "23"
    FROM   v$log_history
GROUP BY   TO_CHAR (first_time, 'YYYY-MON-DD')
order by DAY desc;

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu