Home » RDBMS Server » Server Administration » redo log switch count for RAC instance (oracle12c)
redo log switch count for RAC instance [message #670580] Fri, 13 July 2018 17:36 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello,

I am trying to build a query to find number of redo log switches
for specific instance in RAC environment.

Here is the query.
should i use the INST_ID filter or THREAD# filter in the where clause?

If i use INST_ID, then i see the same result for each instance.

I under impression that INST_ID & THREAD# should be same.. But
it is not the case..

Can any one please clarify?


SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
  where trunc(first_time) > trunc(sysdate)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC

[Updated on: Sat, 14 July 2018 01:43] by Moderator

Report message to a moderator

redo log switch count for RAC instance [message #670581 is a reply to message #670580] Fri, 13 July 2018 17:37 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello,

I am trying to build a query to find number of redo log switches
for specific instance in RAC environment.

Here is the query.
should i use the INST_ID filter or THREAD# filter in the where clause?

If i use INST_ID, then i see the same result for each instance.

I under impression that INST_ID & THREAD# should be same.. But
it is not the case..

Can any one please clarify?


SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)
  -- AND THREAD# = 3
  -- AND INST_ID = 3
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC
Re: redo log switch count for RAC instance [message #670582 is a reply to message #670581] Fri, 13 July 2018 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
My old and senile brain is having a cramp.
Please explain what exactly is being summed & why?

>I am trying to build a query to find number of redo log switches for specific instance in RAC environment.
over what period of time?

What is expected & desired results?

What exactly will be done with the results after you have the results?
Re: redo log switch count for RAC instance [message #670583 is a reply to message #670581] Sat, 14 July 2018 00:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
should i use the INST_ID filter or THREAD# filter in the where clause?

If i use INST_ID, then i see the same result for each instance.

I under impression that INST_ID & THREAD# should be same.. But
it is not the case..
I don't know what your query is producing or what you want. However, I can say that there is little point in querying a gv$ view that is populated from the controlfile: you will get the same information twice.

There is no relationship between inst_id and thread#. I see no point in setting thread# at all, just let each instance pick up any available thread when it starts.
Re: redo log switch count for RAC instance [message #670585 is a reply to message #670580] Sat, 14 July 2018 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I never had feedback from you in your previous topics so you will no more have my help in your topics.

Re: redo log switch count for RAC instance [message #670622 is a reply to message #670585] Mon, 16 July 2018 16:12 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thank you John & Blackswan. I appreciate your response.

Sorry.. my original query was incorrect.

My database is in RAC environment.

Here is the query output with INST_ID.

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=1
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/10   81   62   31   31   35   36   37   46   44   47   43   46   38   42   41   31   35   30   30   29   24   32   35   25        931
07/11   78   59   30   30   36   35   36   43   47   44   47   42   41   37   37   30   34   26   29   29   26   34   30   25        905
07/12   77   56   30   34   36   36   36   42   48   44   48   47   42   41   36   31   31   30   29   29    7    0    0    8        818
07/13   97   74   50   36   40   42   42   44   48   48   50   46   44   38   42   36   34   34   26   24   28   30   28   22       1003
07/14   74   56   30   28   34   30   32   40   42   38   40   36   36   32   34   26   26   26   24   22   26   24   24   20        800
07/15   74   40   28   26   26   28   30   36   38   40   36   36   36   34   32   26   30   28   26   28   30   24   28   26        786
07/16   76   50   32   32   34   36   42   44   52   50   46   46   44   43    1    0    0    0    0    0    0    0    0    0        628

7 rows selected.

SQL>

Here is the query with thread#

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and THREAD#=1
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/10   52   44   20   20   24   24   24   32   28   32   28   32   24   28   28   20   24   20   20   20   16   20   24   16        620
07/11   52   40   20   20   24   24   24   28   32   28   32   28   28   24   24   20   24   16   20   20   16   24   20   16        604
07/12   52   36   20   24   24   24   24   28   32   28   32   32   28   28   24   20   20   20   20   20    8    0    0    4        548
07/13   64   52   32   24   28   28   28   28   32   32   32   32   28   28   28   24   20   24   16   16   20   20   20   12        668
07/14   52   36   20   20   20   20   24   24   28   28   24   24   24   24   20   20   16   16   16   16   16   16   16   16        536
07/15   48   28   16   20   16   20   20   24   24   28   24   24   24   20   24   16   20   20   16   20   20   16   16   20        524
07/16   48   36   20   20   24   24   28   28   36   32   32   32   28   28    0    0    0    0    0    0    0    0    0    0        416

7 rows selected.

SQL>

I am not sure either i should use THREAD# or INST_ID for finding the redo log switch
for specific instance. both output are different.

Please let me know.
Re: redo log switch count for RAC instance [message #670642 is a reply to message #670622] Tue, 17 July 2018 10:50 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I logged in third instance.

SQL> SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              3

1 row selected.

SQL> SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
 where trunc(first_time) > trunc(sysdate)-7
  --and INST_ID=3
  --and THREAD#=3
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;
 31   32   33   34

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   78   59   30   30   36   35   36   43   47   44   47   42   41   37   37   30   34   26   29   29   26   34   30   25        905
07/12   77   56   30   34   36   36   36   42   48   44   48   47   42   41   36   31   31   30   29   29    7    0    0    8        818
07/13   97   74   50   36   40   42   42   44   48   48   50   46   44   38   42   36   34   34   26   24   28   30   28   22       1003
07/14   74   56   30   28   34   30   32   40   42   38   40   36   36   32   34   26   26   26   24   22   26   24   24   20        800
07/15   74   40   28   26   26   28   30   36   38   40   36   36   36   34   32   26   30   28   26   28   30   24   28   26        786
07/16   76   50   32   32   34   36   42   44   52   50   46   46   44   44   42   34   34   34   32   28   30   36   34   26        958
07/17   76   64   40   36   36   34   38   44   32    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        400

7 rows selected.

SQL> SQL>

SQL> SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=3
  and THREAD#=3
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC; 

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151
07/12   13    9    5    6    6    6    6    7    8    7    8    8    7    7    6    5    5    5    5    5    1    0    0    2        137
07/13   16   12    9    6    6    7    7    8    8    8    8    8    7    6    7    6    6    6    4    4    5    5    4    4        167
07/14   12   10    5    4    6    5    5    7    7    6    7    6    6    5    6    4    5    4    4    4    4    4    4    3        133
07/15   13    6    5    4    5    4    5    6    7    6    6    6    6    6    5    5    5    4    5    4    5    4    5    4        131
07/16   13    8    6    5    6    6    7    7    9    8    8    7    8    7    7    6    5    6    5    5    5    6    6    4        160
07/17   13   10    7    6    6    6    6    7    5    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0         66

7 rows selected.

v$log_history and gv$log_history output are not same.

Here is the output of gv$log.

SQL> select inst_id,group#,thread#,BYTES/1024/1024 from gv$log order by 1,3,2;

   INST_ID     GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------- ---------------
         1        116          1            4096
         1        117          1            4096
         1        118          1            4096
         1        119          1            4096
         1        120          1            4096
         1        121          1            4096
         1        122          1            4096
         1        123          1            4096
         1        124          1            4096
         1        125          1            4096
         1        126          1            4096
         1        127          1            4096
         1        216          2            4096
         1        217          2            4096
         1        218          2            4096
         1        219          2            4096
         1        220          2            4096
         1        221          2            4096
         1        222          2            4096
         1        223          2            4096
         1        224          2            4096
         1        225          2            4096
         1        226          2            4096
         1        227          2            4096
         1        316          3            4096
         1        317          3            4096
         1        318          3            4096
         1        319          3            4096
         1        320          3            4096
         1        321          3            4096
         1        322          3            4096
         1        323          3            4096
         1        324          3            4096
         1        325          3            4096
         1        326          3            4096
         1        327          3            4096
         1        416          4            4096
         1        417          4            4096
         1        418          4            4096
         1        419          4            4096
         1        420          4            4096
         1        421          4            4096
         1        422          4            4096
         1        423          4            4096
         1        424          4            4096
         1        425          4            4096
         1        426          4            4096
         1        427          4            4096
         2        116          1            4096
         2        117          1            4096
         2        118          1            4096
         2        119          1            4096
         2        120          1            4096
         2        121          1            4096
         2        122          1            4096
         2        123          1            4096
         2        124          1            4096
         2        125          1            4096
         2        126          1            4096
         2        127          1            4096
         2        216          2            4096
         2        217          2            4096
         2        218          2            4096
         2        219          2            4096
         2        220          2            4096
         2        221          2            4096
         2        222          2            4096
         2        223          2            4096
         2        224          2            4096
         2        225          2            4096
         2        226          2            4096
         2        227          2            4096
         2        316          3            4096
         2        317          3            4096
         2        318          3            4096
         2        319          3            4096
         2        320          3            4096
         2        321          3            4096
         2        322          3            4096
         2        323          3            4096
         2        324          3            4096
         2        325          3            4096
         2        326          3            4096
         2        327          3            4096
         2        416          4            4096
         2        417          4            4096
         2        418          4            4096
         2        419          4            4096
         2        420          4            4096
         2        421          4            4096
         2        422          4            4096
         2        423          4            4096
         2        424          4            4096
         2        425          4            4096
         2        426          4            4096
         2        427          4            4096
         3        116          1            4096
         3        117          1            4096
         3        118          1            4096
         3        119          1            4096
         3        120          1            4096
         3        121          1            4096
         3        122          1            4096
         3        123          1            4096
         3        124          1            4096
         3        125          1            4096
         3        126          1            4096
         3        127          1            4096
         3        216          2            4096
         3        217          2            4096
         3        218          2            4096
         3        219          2            4096
         3        220          2            4096
         3        221          2            4096
         3        222          2            4096
         3        223          2            4096
         3        224          2            4096
         3        225          2            4096
         3        226          2            4096
         3        227          2            4096
         3        316          3            4096
         3        317          3            4096
         3        318          3            4096
         3        319          3            4096
         3        320          3            4096
         3        321          3            4096
         3        322          3            4096
         3        323          3            4096
         3        324          3            4096
         3        325          3            4096
         3        326          3            4096
         3        327          3            4096
         3        416          4            4096
         3        417          4            4096
         3        418          4            4096
         3        419          4            4096
         3        420          4            4096
         3        421          4            4096
         3        422          4            4096
         3        423          4            4096
         3        424          4            4096
         3        425          4            4096
         3        426          4            4096
         3        427          4            4096
         4        116          1            4096
         4        117          1            4096
         4        118          1            4096
         4        119          1            4096
         4        120          1            4096
         4        121          1            4096
         4        122          1            4096
         4        123          1            4096
         4        124          1            4096
         4        125          1            4096
         4        126          1            4096
         4        127          1            4096
         4        216          2            4096
         4        217          2            4096
         4        218          2            4096
         4        219          2            4096
         4        220          2            4096
         4        221          2            4096
         4        222          2            4096
         4        223          2            4096
         4        224          2            4096
         4        225          2            4096
         4        226          2            4096
         4        227          2            4096
         4        316          3            4096
         4        317          3            4096
         4        318          3            4096
         4        319          3            4096
         4        320          3            4096
         4        321          3            4096
         4        322          3            4096
         4        323          3            4096
         4        324          3            4096
         4        325          3            4096
         4        326          3            4096
         4        327          3            4096
         4        416          4            4096
         4        417          4            4096
         4        418          4            4096
         4        419          4            4096
         4        420          4            4096
         4        421          4            4096
         4        422          4            4096
         4        423          4            4096
         4        424          4            4096
         4        425          4            4096
         4        426          4            4096
         4        427          4            4096

192 rows selected.

SQL>

I am little confused how i can calculate redo log switch count for each instance...
Re: redo log switch count for RAC instance [message #670645 is a reply to message #670642] Tue, 17 July 2018 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you need REDO produced per hour per day per instance?
How will this data be used after you actually have it?
Re: redo log switch count for RAC instance [message #670646 is a reply to message #670642] Tue, 17 July 2018 11:05 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
It looks like, the below sum of individual instance redo switch count is matching with sum of all instance redo switch count.

1st instance :

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  and INST_ID=1
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151
07/12   13    9    5    6    6    6    6    7    8    7    8    8    7    7    6    5    5    5    5    5    2    0    0    1        137
07/13   16   13    8    6    7    7    7    7    8    8    8    8    7    7    7    6    5    6    4    4    5    5    5    3        167
07/14   13    9    5    5    5    5    6    6    7    7    6    6    6    6    5    5    4    4    4    4    4    4    4    4        134
07/15   12    7    4    5    4    5    5    6    6    7    6    6    6    5    6    4    5    5    4    5    5    4    4    5        131
07/16   12    9    5    5    6    6    7    7    9    8    8    8    7    7    7    6    6    5    6    4    5    6    6    4        159
07/17   13   11    6    6    6    6    6    8    6    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0         68

2nd instance :

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  and INST_ID=2
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   39   29   15   15   18   17   18   22   23   23   23   21   20   19   19   15   16   14   14   14   14   16   15   13        452
07/12   38   29   15   16   18   18   18   21   24   23   24   23   21   20   18   16   16   15   14   14    3    0    0    3        407
07/13   48   37   25   18   20   21   21   22   24   24   25   23   22   19   21   18   17   17   13   12   14   15   14   11        501
07/14   37   28   15   14   17   15   16   20   21   19   20   18   18   16   17   13   13   13   12   11   13   12   12   10        400
07/15   37   20   14   13   13   14   15   18   19   20   18   18   18   17   16   13   15   14   13   14   15   12   14   13        393
07/16   38   25   16   16   17   18   21   22   26   25   23   23   22   22   21   17   17   17   16   14   15   18   17   13        479
07/17   38   32   20   18   18   17   19   22   23    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        207

3rd instance :
SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  and INST_ID=3
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151
07/12   13    9    5    6    6    6    6    7    8    7    8    8    7    7    6    5    5    5    5    5    1    0    0    2        137
07/13   16   12    9    6    6    7    7    8    8    8    8    8    7    6    7    6    6    6    4    4    5    5    4    4        167
07/14   12   10    5    4    6    5    5    7    7    6    7    6    6    5    6    4    5    4    4    4    4    4    4    3        133
07/15   13    6    5    4    5    4    5    6    7    6    6    6    6    6    5    5    5    4    5    4    5    4    5    4        131
07/16   13    8    6    5    6    6    7    7    9    8    8    7    8    7    7    6    5    6    5    5    5    6    6    4        160
07/17   13   10    7    6    6    6    6    7    7    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0         68

4th instance :

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  and INST_ID=4
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151
07/12   13    9    5    6    6    6    6    7    8    7    8    8    7    7    6    5    5    5    5    5    1    0    0    2        137
07/13   17   12    8    6    7    7    7    7    8    8    9    7    8    6    7    6    6    5    5    4    4    5    5    4        168
07/14   12    9    5    5    6    5    5    7    7    6    7    6    6    5    6    4    4    5    4    3    5    4    4    3        133
07/15   12    7    5    4    4    5    5    6    6    7    6    6    6    6    5    4    5    5    4    5    5    4    5    4        131
07/16   13    8    5    6    5    6    7    8    8    9    7    8    7    8    7    5    6    6    5    5    5    6    5    5        160
07/17   12   11    7    6    6    5    7    7    7    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0         68

All instance :

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  --and INST_ID=4
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   78   59   30   30   36   35   36   43   47   44   47   42   41   37   37   30   34   26   29   29   26   34   30   25        905
07/12   77   56   30   34   36   36   36   42   48   44   48   47   42   41   36   31   31   30   29   29    7    0    0    8        818
07/13   97   74   50   36   40   42   42   44   48   48   50   46   44   38   42   36   34   34   26   24   28   30   28   22       1003
07/14   74   56   30   28   34   30   32   40   42   38   40   36   36   32   34   26   26   26   24   22   26   24   24   20        800
07/15   74   40   28   26   26   28   30   36   38   40   36   36   36   34   32   26   30   28   26   28   30   24   28   26        786
07/16   76   50   32   32   34   36   42   44   52   50   46   46   44   44   42   34   34   34   32   28   30   36   34   26        958
07/17   76   64   40   36   36   34   38   44   44    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0        412

The count seems matching here..

DAY(M  H00  H01  H02  H03  H04  H05  H06  H07  H08  H09  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23      TOTAL
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151  (1st count)
07/11   39   29   15   15   18   17   18   22   23   23   23   21   20   19   19   15   16   14   14   14   14   16   15   13        452  (2nd count)
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151  (3rd count)
07/11   13   10    5    5    6    6    6    7    8    7    8    7    7    6    6    5    6    4    5    5    4    6    5    4        151  (4th count)
===========================================================================================================================================
07/11   78   59   30   30   36   35   36   43   47   44   47   42   41   37   37   30   34   26   29   29   26   34   30   25        905  (all 4 instance count)
============================================================================================================================================

Re: redo log switch count for RAC instance [message #670647 is a reply to message #670646] Tue, 17 July 2018 11:08 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
My conclusion :

query to find single instance redo switch count :

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
  and INST_ID=&instance_id
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

Query to find the redo switch count for all instance :
SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  gv$log_history  a
  where trunc(first_time) > trunc(sysdate)-7
  and INST_ID=THREAD#
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ASC;

Correct me if i am wrong on the above query...

Also my question is, why one instance has multiple threads?(output from gv$log).
Re: redo log switch count for RAC instance [message #670649 is a reply to message #670647] Tue, 17 July 2018 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you need REDO produced per hour per day per instance?
How will this data be used after you actually have it?
Re: redo log switch count for RAC instance [message #670658 is a reply to message #670649] Tue, 17 July 2018 14:16 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
It will help for me to understand if there is any heavy batch processing..
It helps to determine the DB volume to some point.
Re: redo log switch count for RAC instance [message #670659 is a reply to message #670647] Tue, 17 July 2018 14:22 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You seem to be under the impression that there is a relationship between the instance number and the thread number. There is not. Furthermore, querying the gv$ view means that you get the same rows several times, once for each open instance. Have you not noticed this? When working with views populated from the controlfile you should work from the v$ view, not the gv$ view.
Re: redo log switch count for RAC instance [message #670660 is a reply to message #670658] Tue, 17 July 2018 14:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
shrinika wrote on Tue, 17 July 2018 12:16
It will help for me to understand if there is any heavy batch processing..
It helps to determine the DB volume to some point.
How do you differentiate batch processing from OLTP processing by knowing REDO amount?

What is unit of measure for DB volume?
Re: redo log switch count for RAC instance [message #670661 is a reply to message #670660] Tue, 17 July 2018 14:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Actually, redo volumes are a nice way of monitoring the level of activity. I run these simple queries to get an idea of what is happening:
C:\Users\john>
C:\Users\john>type redoperhour.sql
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select trunc(first_time,'HH'),round(sum(blocks*block_size/1000000/2)) redoMB
from v$archived_log
where first_time > sysdate - &how_many_days
group by trunc(first_time,'HH') order by trunc(first_time,'HH')
/

C:\Users\john>
C:\Users\john>
C:\Users\john>type redoperday.sql
select trunc(first_time),round(sum(blocks*block_size/1000000/2)) redoMB from v$archived_log group by trunc(first_time) order by trunc(first_time)
/

C:\Users\john>
they assume that archiving is going to two destinations.




Re: redo log switch count for RAC instance [message #670688 is a reply to message #670661] Thu, 19 July 2018 13:17 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thank you John Watson! Your input so helpful.

Initially i thought THREAD# & INST_ID are related.. Inst_id1 relates to therad#1, Inst_id2 relates to thread#2 etc.

Now i realized, there is no relation between these two. Instance pick up any available redo thread.

Now i understand, it makes no sense to find the number of log switch per instance. since both are not related.

Now i am using v$log_history for finding number of log switches. V$archived_log to find the redo size.

Thank you for your query and all your help!
Re: redo log switch count for RAC instance [message #670689 is a reply to message #670660] Thu, 19 July 2018 13:22 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Blackswan -

How do you differentiate batch processing from OLTP processing by knowing REDO amount?
I am not curios about OLTP/BATCH.. Just want to check some trend about data volume.

What is unit of measure for DB volume?
I am looking overall growth..

Any way, thank you for your participation on this thread. I am good!
Re: redo log switch count for RAC instance [message #670690 is a reply to message #670689] Thu, 19 July 2018 13:28 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
BlackSwan -

Why do you need REDO produced per hour per day per instance?
Now i realized it makes no sense to check redo per hour per instance.. since thread# is not related to Inst#

Thanks
Re: redo log switch count for RAC instance [message #670691 is a reply to message #670689] Thu, 19 July 2018 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
shrinika wrote on Thu, 19 July 2018 11:22


What is unit of measure for DB volume?
I am looking overall growth..

There is NO direct relationship between REDO activity and database "growth".
REDO contains results from DELETE statements which is negative growth.
REDO contains results from UPDATE statements which could simply change "N" to "Y" for ZERO growth.
REDO activity is a mythical performance indicator, IMO
Re: redo log switch count for RAC instance [message #670694 is a reply to message #670691] Fri, 20 July 2018 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
REDO tells you nothing about DB growth sure, but as John already mentioned it can be indicator of how busy the system is.
Re: redo log switch count for RAC instance [message #670733 is a reply to message #670694] Mon, 23 July 2018 09:36 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thank you and i got your point!
Re: redo log switch count for RAC instance [message #670734 is a reply to message #670733] Mon, 23 July 2018 09:36 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
There is NO direct relationship between REDO activity and database "growth".
REDO contains results from DELETE statements which is negative growth.
REDO contains results from UPDATE statements which could simply change "N" to "Y" for ZERO growth.
REDO activity is a mythical performance indicator, IMO

Blackswan - Agreed your above points!
Previous Topic: Problem with creating new user in Cloud Control
Next Topic: .patch_storage cleanup
Goto Forum:
  


Current Time: Thu Mar 28 10:05:37 CDT 2024