Skip to Main Content
  • Questions

  • Redo log file size and Database Performance

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vishnudas.

Asked: April 18, 2019 - 11:02 am UTC

Last updated: April 30, 2019 - 2:35 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hi there,
We have a database setup as follows

1) SQL server -- Where transactions are happening here

2) Oracle(DSS)-- we are transferring the incremental data from SQL server(NOT ALL THE TABLES ONLY 22 TABLES) with the help of a 24/7 running batch file.

After inserting the incremental data into Oracle tables we are firing some procedures(Scheduled to run every 1 hour) and updating some sales figures.(This update is expensive because almost every tables are getting updated via fired procedures).

We have 3 redo log file group with size 50 MB each,During sales closing time these procedures are taking time to get complete and the overall performance is getting degraded.
So i executed the following query 

select /*+ ordered */ 
 a.sid, 
 decode(a.type, 'BACKGROUND', substr (a.program,instr(a.program,'(',1,1)), 'FOREGROUND') type, 
 b.time_waited, 
 round(b.time_waited/b.total_waits,4) average_wait, 
 round((sysdate - a.logon_time)*24) hours_connected 
 from   v$session_event b, v$session a 
 where  a.sid   = b.sid 
 and    b.event = 'control file parallel write' 
 order by type, time_waited;


and found the result as follows

      SID TYPE          TIME_WAITED AVERAGE_WAIT HOURS_CONNECTED
---------- ------------ -----------------------------------------
      1138 (CKPT)          94272       1.5514              47 
       855 (LGWR)          11100       1.2042              47 
      1990 (MMON)          4856        1.3167              47


From the above output is this really a redolog size problem?

Please share your ideas on this..









and Connor said...

That's probably not the wait to be focussed on...However, 50MB sounds very small for redo logs for any database doing a lot of work. (50MB is the default in some versions so I can understand where it comes from).

Check v$log_history for the frequency of log switches (ie, filling and switching to the next redo log).

Generally I like my size redo logs so that they switch about every 30-60mins.

There are some overheads to a switch (these used to be large, but with each release its gets better) so I'm not fan of really really frequent switches. To make them larger, just add new ones and then drop the older ones one you've cycled into the new ones.

Rating

  (1 rating)

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

Comments

vishnudas, April 29, 2019 - 7:35 am UTC

As your suggestion i have checked v$log_history and found a lot of log switching
i am sharing the query output

select 
to_char(first_time,'yyyy-mm-dd') day,
to_char(first_time,'hh24') hour,
count(*) total 
from 
v$log_history
where FIRST_TIME >= to_date('29-APR-2019','DD-MON-YYYY')
AND FIRST_TIME <= to_date('30-APR-2019','DD-MON-YYYY')
group by 
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by 
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
ASC;


output

DAY        HOUR      TOTAL
---------- ---- ----------
2019-04-29 00           21 
2019-04-29 01           18 
2019-04-29 02           21 
2019-04-29 03           22 
2019-04-29 04           25 
2019-04-29 05           21 
2019-04-29 06           20 
2019-04-29 07           21 
2019-04-29 08           21 
2019-04-29 09           22 
2019-04-29 10           12 
2019-04-29 11           21 
2019-04-29 12           24

as our procedure is set to run every 1 hour, the log switch is happening intensively.
So i have to maximize the redo log size right??
would you suggest me what are the sizing factors to set the size for a redo log?







Connor McDonald
April 30, 2019 - 2:35 am UTC

So 20 switches per hour is one every 3 mins.

So perhaps look at making them 8 times larger than current (which will then be every 20-30mins)
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

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database