Home » RDBMS Server » Performance Tuning » Is there a way to find out activities due to redo logs (11.2.0.4 Windows 2008 R2 Data Center)
Is there a way to find out activities due to redo logs [message #665548] Sun, 10 September 2017 05:21 Go to next message
juniordbanewbie
Messages: 170
Registered: April 2014
Senior Member
Dear all,

here' the hours where the redo log switches are the lowest.
SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;

 RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-09-10 11          1
  2 2017-08-20 11          2
  3 2017-08-20 16          2
  4 2017-08-21 01          2
  5 2017-08-23 03          2

here' the hours where the redo log switches are the highest.
SYS@berlin>
SYS@berlin>--top 5 lowest redo log period

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count DESC) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;
RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-08-23 11         67
  2 2017-09-07 22         61
  3 2017-08-18 22         61
  4 2017-09-07 21         61
  5 2017-09-07 01         60
 
 

it flunctues a lot.

Is there a way to find out which are the sql or activies that cause a high number of redo log switches?

thanks
Re: Is there a way to find out activities due to redo logs [message #665552 is a reply to message #665548] Sun, 10 September 2017 07:39 Go to previous message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Sun, 10 September 2017 03:21
Dear all,

here' the hours where the redo log switches are the lowest.
SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;

 RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-09-10 11          1
  2 2017-08-20 11          2
  3 2017-08-20 16          2
  4 2017-08-21 01          2
  5 2017-08-23 03          2

here' the hours where the redo log switches are the highest.
SYS@berlin>
SYS@berlin>--top 5 lowest redo log period

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count DESC) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;
RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-08-23 11         67
  2 2017-09-07 22         61
  3 2017-08-18 22         61
  4 2017-09-07 21         61
  5 2017-09-07 01         60
 
 

it flunctues a lot.

Is there a way to find out which are the sql or activies that cause a high number of redo log switches?

thanks

DBMS_LOGMNR can reveal details about the DML that results in the REDO activity.

also statspack/AWR report should reveal activity during these periods.
Previous Topic: Performace issue
Next Topic: Why use Oracle In-Memory database from another perspective
Goto Forum:
  


Current Time: Fri Nov 24 18:03:33 CST 2017

Total time taken to generate the page: 0.08636 seconds