Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dhanasekar.

Asked: April 02, 2017 - 1:27 pm UTC

Last updated: April 10, 2017 - 4:00 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

I have doubt on arrive at the count of redo log switch in the database. From the AWR report i can see that the log switch(derived) total is 82 and per hour value 6.83. However when i query using the below sql

SELECT INST_ID,to_char(completion_time,'DD/MM/YYYY HH24') "Archival Time", count(1) FROM Gv$archived_log
where completion_time between to_date('01-MAR-2017', 'dd-MON-YYYY') and to_date('27-MAR-2017', 'dd-MON-YYYY')
group by INST_ID,to_char(completion_time,'DD/MM/YYYY HH24')
order by to_char(completion_time,'DD/MM/YYYY HH24');


select substr(first_time,1,5) day
, sum(decode(to_char(first_time,'HH24'), '00', 1, 0 )) "00"
, sum(decode(to_char(first_time,'HH24'), '01', 1, 0 )) "01"
, sum(decode(to_char(first_time,'HH24'), '02', 1, 0 )) "02"
, sum(decode(to_char(first_time,'HH24'), '03', 1, 0 )) "03"
, sum(decode(to_char(first_time,'HH24'), '04', 1, 0 )) "04"
, sum(decode(to_char(first_time,'HH24'), '05', 1, 0 )) "05"
, sum(decode(to_char(first_time,'HH24'), '06', 1, 0 )) "06"
, sum(decode(to_char(first_time,'HH24'), '07', 1, 0 )) "07"
, sum(decode(to_char(first_time,'HH24'), '08', 1, 0 )) "08"
, sum(decode(to_char(first_time,'HH24'), '09', 1, 0 )) "09"
, sum(decode(to_char(first_time,'HH24'), '10', 1, 0 )) "10"
, sum(decode(to_char(first_time,'HH24'), '11', 1, 0 )) "11"
, sum(decode(to_char(first_time,'HH24'), '12', 1, 0 )) "12"
, sum(decode(to_char(first_time,'HH24'), '13', 1, 0 )) "13"
, sum(decode(to_char(first_time,'HH24'), '14', 1, 0 )) "14"
, sum(decode(to_char(first_time,'HH24'), '15', 1, 0 )) "15"
, sum(decode(to_char(first_time,'HH24'), '16', 1, 0 )) "16"
, sum(decode(to_char(first_time,'HH24'), '17', 1, 0 )) "17"
, sum(decode(to_char(first_time,'HH24'), '18', 1, 0 )) "18"
, sum(decode(to_char(first_time,'HH24'), '19', 1, 0 )) "19"
, sum(decode(to_char(first_time,'HH24'), '20', 1, 0 )) "20"
, sum(decode(to_char(first_time,'HH24'), '21', 1, 0 )) "21"
, sum(decode(to_char(first_time,'HH24'), '22', 1, 0 )) "22"
, sum(decode(to_char(first_time,'HH24'), '23', 1, 0 )) "23"
from v$log_history
group by substr(first_time,1,5)

I am getting the count per hours between 12 and 18.

Regards,
Dhanasekarf

and Connor said...

From the docs for V$ARCHIVED_LOG

"If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#"

So if (for example) have a dataguard node, or any other form of secondary archive destion, you will be getting multiple rows in V$ARCHIVED_LOG compared to V$LOG_HISTORY.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dhanasekar, April 03, 2017 - 8:24 am UTC

I have only one destination, the output i get from v$archived_log and v$log_history is same. But the log switch derived from AWR is less.
Connor McDonald
April 10, 2017 - 4:00 am UTC

The way AWR reports that figure is:

- get the sequence# at the time of begin snapshot
- get the sequence# at the time of end snapshot
- subtract one from the other
- divide by the hours between the two snapshots

SO if the log seq# was 17 at 8am, and 29 at 10am, then derived switch will be:

(29-17)/(7200secs/2600) = 5


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.