 Optimal REDO log file size [message #493828] |
Thu, 10 February 2011 02:53  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
hi all,
I would like to make a change on the live system!
I have read a book and found a information about REDO log file size is impact on DB performance.
My DB current log file size is 100 MB. But, Oracle 10g's Redo Logfile Sizing Advisor offer the optimal log file size is 1845 MB.
What REDO log file size is best for my Oracle database?
#Optimal log file size:
select optimal_logfile_size
from v$instance_recovery
----------------------------
OPTIMAL_LOGFILE_SIZE
1842
#Current log file size:
SELECT * FROM V$LOG
-----------------------------
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
1 1 49105 104857600 2 "NO" "INACTIVE" 50178891611 10.02.11 15:37:31
2 1 49106 104857600 2 "NO" "INACTIVE" 50179010744 10.02.11 15:58:46
3 1 49107 104857600 2 "NO" "CURRENT" 50179227943 10.02.11 16:09:44
4 1 49104 104857600 2 "NO" "INACTIVE" 50178673366 10.02.11 15:17:09
#Current REDO log file switch statistic:
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
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
DAY HOUR TOTAL
"2011-02-01" "00" 3
"2011-02-01" "02" 3
"2011-02-01" "03" 7
"2011-02-01" "04" 1
"2011-02-01" "05" 2
"2011-02-01" "08" 2
"2011-02-01" "09" 5
"2011-02-01" "10" 6
"2011-02-01" "11" 1
"2011-02-01" "12" 3
"2011-02-01" "13" 5
"2011-02-01" "14" 5
"2011-02-01" "15" 3
"2011-02-01" "16" 16
"2011-02-01" "17" 3
"2011-02-01" "18" 3
"2011-02-01" "21" 2
"2011-02-01" "22" 1
"2011-02-01" "23" 2
"2011-02-02" "01" 1
"2011-02-02" "03" 6
"2011-02-02" "04" 5
"2011-02-02" "05" 2
"2011-02-02" "08" 2
"2011-02-02" "09" 3
"2011-02-02" "10" 3
"2011-02-02" "11" 2
"2011-02-02" "12" 3
"2011-02-02" "13" 2
"2011-02-02" "14" 2
"2011-02-02" "15" 1
"2011-02-02" "16" 3
"2011-02-02" "17" 5
"2011-02-02" "18" 4
"2011-02-02" "19" 2
"2011-02-02" "20" 3
"2011-02-02" "21" 1
"2011-02-02" "22" 1
"2011-02-02" "23" 4
"2011-02-03" "00" 3
"2011-02-03" "01" 3
"2011-02-03" "02" 4
"2011-02-03" "03" 3
"2011-02-03" "04" 3
"2011-02-03" "05" 4
"2011-02-03" "06" 4
"2011-02-03" "07" 2
"2011-02-03" "08" 4
"2011-02-03" "09" 3
"2011-02-03" "10" 4
"2011-02-03" "11" 5
"2011-02-03" "12" 2
"2011-02-03" "13" 1
"2011-02-03" "14" 3
"2011-02-03" "15" 4
"2011-02-03" "16" 5
"2011-02-03" "17" 4
"2011-02-03" "18" 3
"2011-02-03" "19" 4
"2011-02-03" "20" 4
"2011-02-03" "21" 1
"2011-02-03" "22" 3
"2011-02-03" "23" 7
"2011-02-04" "00" 3
"2011-02-04" "01" 19
"2011-02-04" "02" 38
"2011-02-04" "03" 39
"2011-02-04" "04" 26
"2011-02-04" "05" 38
"2011-02-04" "06" 39
"2011-02-04" "07" 30
"2011-02-04" "08" 26
"2011-02-04" "09" 17
"2011-02-04" "10" 11
"2011-02-04" "11" 4
"2011-02-04" "12" 4
"2011-02-04" "13" 4
"2011-02-04" "14" 15
"2011-02-04" "15" 12
"2011-02-04" "16" 2
"2011-02-04" "17" 3
"2011-02-04" "18" 4
"2011-02-04" "19" 4
"2011-02-04" "20" 3
"2011-02-04" "22" 1
"2011-02-04" "23" 16
"2011-02-05" "00" 14
"2011-02-05" "02" 12
"2011-02-05" "03" 1
"2011-02-05" "08" 3
"2011-02-05" "09" 3
"2011-02-05" "10" 4
"2011-02-05" "11" 4
"2011-02-05" "12" 4
"2011-02-05" "13" 1
"2011-02-05" "14" 4
"2011-02-05" "15" 3
"2011-02-05" "16" 3
"2011-02-05" "17" 2
"2011-02-05" "19" 2
"2011-02-05" "20" 2
"2011-02-05" "22" 1
"2011-02-06" "01" 6
"2011-02-06" "02" 11
"2011-02-06" "03" 10
"2011-02-06" "08" 2
"2011-02-06" "09" 1
"2011-02-06" "10" 3
"2011-02-06" "11" 2
"2011-02-06" "12" 3
"2011-02-06" "13" 2
"2011-02-06" "14" 2
"2011-02-06" "15" 15
"2011-02-06" "16" 4
"2011-02-06" "17" 5
"2011-02-06" "18" 20
"2011-02-06" "19" 6
"2011-02-06" "20" 4
"2011-02-06" "21" 4
"2011-02-06" "22" 2
"2011-02-06" "23" 3
"2011-02-07" "00" 3
"2011-02-07" "01" 1
"2011-02-07" "02" 5
"2011-02-07" "03" 1
"2011-02-07" "04" 1
"2011-02-07" "05" 1
"2011-02-07" "06" 4
"2011-02-07" "08" 2
"2011-02-07" "09" 2
"2011-02-07" "10" 3
"2011-02-07" "11" 5
"2011-02-07" "12" 3
"2011-02-07" "13" 2
"2011-02-07" "14" 3
"2011-02-07" "15" 3
"2011-02-07" "16" 2
"2011-02-07" "17" 3
"2011-02-07" "18" 3
"2011-02-07" "19" 10
"2011-02-07" "20" 4
"2011-02-07" "22" 1
"2011-02-07" "23" 1
"2011-02-08" "01" 6
"2011-02-08" "02" 8
"2011-02-08" "03" 7
"2011-02-08" "04" 11
"2011-02-08" "05" 5
"2011-02-08" "06" 7
"2011-02-08" "07" 4
"2011-02-08" "08" 3
"2011-02-08" "09" 3
"2011-02-08" "10" 2
"2011-02-08" "11" 2
"2011-02-08" "12" 10
"2011-02-08" "13" 3
"2011-02-08" "14" 2
"2011-02-08" "15" 4
"2011-02-08" "16" 4
"2011-02-08" "17" 2
"2011-02-08" "18" 3
"2011-02-08" "19" 8
"2011-02-08" "20" 2
"2011-02-08" "22" 1
"2011-02-08" "23" 1
"2011-02-09" "01" 1
"2011-02-09" "03" 2
"2011-02-09" "04" 3
"2011-02-09" "05" 1
"2011-02-09" "08" 3
"2011-02-09" "09" 7
"2011-02-09" "10" 3
"2011-02-09" "11" 5
"2011-02-09" "12" 3
"2011-02-09" "13" 3
"2011-02-09" "14" 4
"2011-02-09" "15" 4
"2011-02-09" "16" 2
"2011-02-09" "17" 4
"2011-02-09" "18" 3
"2011-02-09" "19" 2
"2011-02-09" "20" 6
"2011-02-09" "21" 1
"2011-02-09" "22" 1
"2011-02-09" "23" 1
"2011-02-10" "01" 4
"2011-02-10" "02" 9
"2011-02-10" "03" 6
"2011-02-10" "04" 8
"2011-02-10" "05" 6
"2011-02-10" "06" 8
"2011-02-10" "07" 4
"2011-02-10" "08" 3
"2011-02-10" "09" 17
"2011-02-10" "10" 6
"2011-02-10" "11" 3
"2011-02-10" "12" 3
"2011-02-10" "13" 3
"2011-02-10" "14" 4
"2011-02-10" "15" 4
|
|
|
|
|
|
| Re: Optimal REDO log file size [message #493831 is a reply to message #493828] |
Thu, 10 February 2011 03:03  |
knight
Messages: 111
Registered: January 2009
|
Senior Member |
|
|
Quote:SELECT * FROM V$LOG
-----------------------------
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
1 1 49105 104857600 2 "NO" "INACTIVE" 50178891611 10.02.11 15:37:31
2 1 49106 104857600 2 "NO" "INACTIVE" 50179010744 10.02.11 15:58:46
3 1 49107 104857600 2 "NO" "CURRENT" 50179227943 10.02.11 16:09:44
4 1 49104 104857600 2 "NO" "INACTIVE" 50178673366 10.02.11 15:17:09
is your live database in no-archive log mode?
|
|
|
|
|
|
|
|
|
|
| Re: Optimal REDO log file size [message #493843 is a reply to message #493833] |
Thu, 10 February 2011 03:30 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Tlg13team wrote on Thu, 10 February 2011 10:11I can't direct this change on DB, because this system is live system. I think that I should get suggestions from experts before change on live system.
I gave you my opinion.
And my advice is that you should run in archivelog mode.
Regards
Michel
[Updated on: Thu, 10 February 2011 03:31] Report message to a moderator |
|
|
|
| Re: Optimal REDO log file size [message #493859 is a reply to message #493843] |
Thu, 10 February 2011 04:15 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Let's assume that you convert to archivelog mode. Then:
Your peak rate of redo generation was 39 log switches in one hour, say 4G. I would use groups sized 1G, so that during that hour you'll have only four log switches. Also, go from 4 groups to 8 groups.
Justification for the size: a log switch and archive is very stressful on the operating system, it triggers lots of opening/closing files, lots of disc I/O. Also some stress in the instance: there will be a short period when you cannot write to the log buffer, because it will have filled while the log switch is in progress and cannot be flushed until the switch is complete. I have no real reason for saying max switches should be four per hour (rather than 8 or 2, for instance) except that I have seen the associated wait events disappear at that sort of frequency.
Justification for more groups: if during your peak redo rate, you have four log switches before the checkpoint position has advanced naturally through the last redo log, the entire instance will hang until the checkpoint position goes through it: look for "Checkpoint not complete" messages in your alert log. Also, if anything goes wrong with your archiving, eight groups will give you twice as much time to fix the problem.
|
|
|
|
| Re: Optimal REDO log file size [message #493862 is a reply to message #493828] |
Thu, 10 February 2011 04:45 |
knight
Messages: 111
Registered: January 2009
|
Senior Member |
|
|
please post SQL> show parameter fast_start_mttr_target
Quote:there will be a short period when you cannot write to the log buffer, because it will have filled while the log switch is in progress and cannot be flushed until the switch is complete.
isn't lgwr writing continuosly in background(1 mb redo,every 3 sec etc) so that transactions dont wait while writing to log buffer
Tlg13team :do you have online logs multiplexed?
loss of active log file might cause 1 gb data loss?
|
|
|
|
| Re: Optimal REDO log file size [message #494393 is a reply to message #493862] |
Mon, 14 February 2011 03:44 |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi knight,
Please see the ast_start_mttr_target parameter value below:
SQL> show parameter fast_start_mttr_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 300
SELECT a.group#, a.member, b.bytes
FROM v$logfile a, v$log b WHERE a.group# = b.group#;
GROUP# MEMBER BYTES
1 "/........./prod/redo01a.log" 104857600
1 "/........./prod/redo01b.log" 104857600
2 "/........./prod/redo02a.log" 104857600
2 "/........./prod/redo02b.log" 104857600
3 "/........./prod/redo03a.log" 104857600
3 "/........./prod/redo03b.log" 104857600
4 "/........./prod/redo04a.log" 104857600
4 "/........./prod/redo04b.log" 104857600
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
