redo log file 优化
本文主要介绍redo log file大小的优化。
#######################################################################################
Oracle Environment=>11.2.0.4.0 rac on two nodes
OS Environment=>redhat 6.3 (64bit)
#######################################################################################
总体思路如下:
1.redo log file现状监控
2.redo log file切换总览
3.redo log file优化
3.1 官方建议20分钟切换一次
3.2 MTTR角度优化
1.redo log file现状监控
#redo log files 现状监控#
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 4965 YES ACTIVE +DATA/BMCTEST/redo01.log 50
2 1 4966 NO CURRENT +DATA/BMCTEST/redo02.log 50
3 2 3405 YES INACTIVE +DATA/BMCTEST/redo03.log 50
4 2 3406 NO CURRENT +DATA/BMCTEST/redo04.log 50
2.redo log file切换总览
#redo log files查看脚本#
select
Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
Vdb.NAME AS Dbname
FROM
(SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM
V$log_History Vlh
GROUP BY
To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE
Vl.Group# = 1
ORDER BY
Log_Hist.Start_Date,
Log_Hist.Start_Time;
...........
2015-05-29 10:00 88 44000 BMCTEST
2015-05-29 11:00 22 11000 BMCTEST
2015-05-29 12:00 1 500 BMCTEST
2015-05-30 00:00 1 500 BMCTEST
2015-06-01 02:00 1 500 BMCTEST
2015-06-01 22:00 1 500 BMCTEST
2015-06-03 11:00 64 32000 BMCTEST
START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2015-06-03 12:00 157 78500 BMCTEST
2015-06-03 13:00 49 24500 BMCTEST
2015-06-03 14:00 9 4500 BMCTEST
2015-06-03 15:00 17 8500 BMCTEST
参考:http://www.dba-oracle.com/m_redo_size.htm
3.redo log file优化
3.1 官方建议20分钟切换一次
一般优化指导方针:
当前 10分钟/切换 200M =>优化目标 20分钟/切换=>200M*2=400M
#从性能切换角度,10分钟切换一次,估算redo log files大小#(从0点到23点59分)#
SELECT
(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
ROUND((10 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)
FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 3
AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
'00:00' AND '23:59');
Redo size (MB) Recommended Size (MB)
-------------- ---------------------
500 514.53
3.2 MTTR角度优化
#从恢复时间角度,MTTR角度#
SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=60; #秒
System altered.
SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
OPTIMAL_LOGFILE_SIZE
--------------------
880(M)
参考:http://www.gokhanatil.com/2009/08/optimum-size-of-the-online-redo-log-files.html
总结:性能优化,尽量“库存”更多的监控,分析脚本。
########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928
本文链接: http://blog.itpub.net/26442936/viewspace-1701223/
########################################################################################
本文主要介绍redo log file大小的优化。
#######################################################################################
Oracle Environment=>11.2.0.4.0 rac on two nodes
OS Environment=>redhat 6.3 (64bit)
#######################################################################################
总体思路如下:
1.redo log file现状监控
2.redo log file切换总览
3.redo log file优化
3.1 官方建议20分钟切换一次
3.2 MTTR角度优化
1.redo log file现状监控
#redo log files 现状监控#
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 4965 YES ACTIVE +DATA/BMCTEST/redo01.log 50
2 1 4966 NO CURRENT +DATA/BMCTEST/redo02.log 50
3 2 3405 YES INACTIVE +DATA/BMCTEST/redo03.log 50
4 2 3406 NO CURRENT +DATA/BMCTEST/redo04.log 50
2.redo log file切换总览
#redo log files查看脚本#
select
Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
Vdb.NAME AS Dbname
FROM
(SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM
V$log_History Vlh
GROUP BY
To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE
Vl.Group# = 1
ORDER BY
Log_Hist.Start_Date,
Log_Hist.Start_Time;
...........
2015-05-29 10:00 88 44000 BMCTEST
2015-05-29 11:00 22 11000 BMCTEST
2015-05-29 12:00 1 500 BMCTEST
2015-05-30 00:00 1 500 BMCTEST
2015-06-01 02:00 1 500 BMCTEST
2015-06-01 22:00 1 500 BMCTEST
2015-06-03 11:00 64 32000 BMCTEST
START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2015-06-03 12:00 157 78500 BMCTEST
2015-06-03 13:00 49 24500 BMCTEST
2015-06-03 14:00 9 4500 BMCTEST
2015-06-03 15:00 17 8500 BMCTEST
参考:http://www.dba-oracle.com/m_redo_size.htm
3.redo log file优化
3.1 官方建议20分钟切换一次
一般优化指导方针:
当前 10分钟/切换 200M =>优化目标 20分钟/切换=>200M*2=400M
#从性能切换角度,10分钟切换一次,估算redo log files大小#(从0点到23点59分)#
SELECT
(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
ROUND((10 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)
FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 3
AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
'00:00' AND '23:59');
Redo size (MB) Recommended Size (MB)
-------------- ---------------------
500 514.53
3.2 MTTR角度优化
#从恢复时间角度,MTTR角度#
SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=60; #秒
System altered.
SQL> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
OPTIMAL_LOGFILE_SIZE
--------------------
880(M)
参考:http://www.gokhanatil.com/2009/08/optimum-size-of-the-online-redo-log-files.html
总结:性能优化,尽量“库存”更多的监控,分析脚本。
########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928
本文链接: http://blog.itpub.net/26442936/viewspace-1701223/
########################################################################################
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-1701223/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-1701223/