log file sync 诊断脚本
来源：互联网 发布：黑客引导页html源码 编辑：程序博客网 时间：2024/06/03 23:41
文档 ID 1064487.1) log file sync 诊断
脚本内容如下：
-- NAME: LFSDIAG.SQL
-- ------------------------------------------------------------------------
-- AUTHOR: Michael Polaski - Oracle Support Services
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- log file sync waits. The script will look at important parameters involved
-- in log file sync waits, log file sync wait histogram data, and the script
-- will look at the worst average log file sync times in the active session
-- history data and AWR data and dump information to help determine why those
-- times were the highest. The script will create a file called
-- lfsdiag_<timestamp>.out in your local directory.
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hh24mi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool lfsdiag_&&dbname&×tamp&&suffix
set trim on
set trims on
set lines 140
set pages 100
set verify off
alter session set optimizer_features_enable = '10.2.0.4';
PROMPT LFSDIAG DATA FOR &&dbname&×tamp
PROMPT Note: All timings are in milliseconds (1000 milliseconds = 1 second)
PROMPT
PROMPT IMPORTANT PARAMETERS RELATING TO LOG FILE SYNC WAITS:
column name format a40 wra
column value format a40 wra
select inst_id, name, value from gv$parameter
where ((value is not null and name like '%log_archive%') or
name like '%commit%' or name like '%event=%' or name like '%lgwr%')
and name not in (select name from gv$parameter where (name like '%log_archive_dest_state%'
and value = 'enable') or name = 'log_archive_format')
order by 1,2,3;
PROMPT
PROMPT ASH THRESHOLD...
PROMPT
PROMPT This will be the threshold in milliseconds for average log file sync
PROMPT times. This will be used for the next queries to look for the worst
PROMPT 'log file sync' minutes. Any minutes that have an average log file
PROMPT sync time greater than the threshold will be analyzed further.
column threshold_in_ms new_value threshold format 999999999.999
select min(threshold_in_ms) threshold_in_ms
from (select inst_id, to_char(sample_time,'Mondd_hh24mi') minute,
avg(time_waited)/1000 threshold_in_ms
from gv$active_session_history
where event = 'log file sync'
group by inst_id,to_char(sample_time,'Mondd_hh24mi')
order by 3 desc)
where rownum <= 10;
PROMPT
PROMPT ASH WORST MINUTES FOR LOG FILE SYNC WAITS:
PROMPT
PROMPT APPROACH: These are the minutes where the avg log file sync time
PROMPT was the highest (in milliseconds).
column minute format a12 tru
column event format a30 tru
column program format a40 tru
column total_wait_time format 999999999999.999
column avg_time_waited format 999999999999.999
select to_char(sample_time,'Mondd_hh24mi') minute, inst_id, event,
sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id, event
having avg(time_waited)/1000 > &&threshold
order by 1,2;
PROMPT
PROMPT ASH LFS BACKGROUND PROCESS WAITS DURING WORST MINUTES:
PROMPT
PROMPT APPROACH: What is LGWR doing when 'log file sync' waits
PROMPT are happening? LMS info may be relevent for broadcast
PROMPT on commit and LNS data may be relevant for dataguard.
PROMPT If more details are needed see the ASH DETAILS FOR WORST
PROMPT MINUTES section at the bottom of the report.
column inst format 999
column minute format a12 tru
column event format a30 tru
column program format a40 wra
select to_char(sample_time,'Mondd_hh24mi') minute, inst_id inst, program, event,
sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where to_char(sample_time,'Mondd_hh24mi') in (select to_char(sample_time,'Mondd_hh24mi')
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id
having avg(time_waited)/1000 > &&threshold and sum(time_waited)/1000 > 1)
and (program like '%LGWR%' or program like '%LMS%' or
program like '%LNS%' or event = 'log file sync')
group by to_char(sample_time,'Mondd_hh24mi'), inst_id, program, event
order by 1,2,3,5 desc, 4;
PROMPT
PROMPT HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS:
PROMPT
PROMPT APPROACH: Look at the wait distribution for log file sync waits
PROMPT by looking at "wait_time_milli". Look at the high wait times then
PROMPT see if you can correlate those with other related wait events.
column event format a40 wra
select inst_id, event, wait_time_milli, wait_count
from gv$event_histogram
where event in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way') or
event like '%LGWR%' or event like '%LNS%'
order by 2 desc,1,3;
PROMPT
PROMPT ORDERED BY WAIT_TIME_MILLI
select inst_id, event, wait_time_milli, wait_count
from gv$event_histogram
where event in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or event like '%LGWR%' or event like '%LNS%'
order by 3,1,2 desc;
PROMPT
PROMPT REDO WRITE STATS
PROMPT
PROMPT "redo write time" in centiseconds (100 per second)
PROMPT 11.1: "redo write broadcast ack time" in centiseconds (100 per second)
PROMPT 11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
column value format 99999999999999999999
column milliseconds format 99999999999999.999
select v.version, ss.inst_id, ss.name, ss.value,
decode(substr(version,1,4),
'11.1',decode (name,'redo write time',value*10,
'redo write broadcast ack time',value*10),
'11.2',decode (name,'redo write time',value*10,
'redo write broadcast ack time',value/1000),
decode (name,'redo write time',value*10)) milliseconds
from gv$sysstat ss, v$instance v
where name like 'redo write%' and value > 0
order by 1,2,3;
PROMPT
PROMPT AWR WORST AVG LOG FILE SYNC SNAPS:
PROMPT
PROMPT APPROACH: These are the AWR snaps where the average 'log file sync'
PROMPT times were the highest.
column begin format a12 tru
column end format a12 tru
column name format a13 tru
select dhs.snap_id, dhs.instance_number inst, to_char(dhs.begin_interval_time,'Mondd_hh24mi') BEGIN,
to_char(dhs.end_interval_time,'Mondd_hh24mi') END,
en.name, se.time_waited_micro/1000 total_wait_time, se.total_waits,
se.time_waited_micro/1000 / se.total_waits avg_time_waited
from dba_hist_snapshot dhs, wrh$_system_event se, v$event_name en
where (dhs.snap_id = se.snap_id and dhs.instance_number = se.instance_number)
and se.event_id = en.event_id and en.name = 'log file sync' and
dhs.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,2;
PROMPT
PROMPT AWR REDO WRITE STATS
PROMPT
PROMPT "redo write time" in centiseconds (100 per second)
PROMPT 11.1: "redo write broadcast ack time" in centiseconds (100 per second)
PROMPT 11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
column stat_name format a30 tru
select v.version, ss.snap_id, ss.instance_number inst, sn.stat_name, ss.value,
decode(substr(version,1,4),
'11.1',decode (stat_name,'redo write time',value*10,
'redo write broadcast ack time',value*10),
'11.2',decode (stat_name,'redo write time',value*10,
'redo write broadcast ack time',value/1000),
decode (stat_name,'redo write time',value*10)) milliseconds
from wrh$_sysstat ss, wrh$_stat_name sn, v$instance v
where ss.stat_id = sn.stat_id
and sn.stat_name like 'redo write%' and ss.value > 0
and ss.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,2,3;
PROMPT
PROMPT AWR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
PROMPT
PROMPT APPROACH: These are the AWR snaps where the average 'log file sync'
PROMPT times were the highest. Look at related waits at those times.
column name format a40 tru
select se.snap_id, se.instance_number inst, en.name,
se.total_waits, se.time_waited_micro/1000 total_wait_time,
se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and (en.name in
('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and se.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1, 6 desc;
PROMPT
PROMPT AWR HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
PROMPT Note: This query won't work on 10.2 - ORA-942
PROMPT
PROMPT APPROACH: Look at the wait distribution for log file sync waits
PROMPT by looking at "wait_time_milli". Look at the high wait times then
PROMPT see if you can correlate those with other related wait events.
select eh.snap_id, eh.instance_number inst, en.name, eh.wait_time_milli, eh.wait_count
from wrh$_event_histogram eh, v$event_name en
where eh.event_id = en.event_id and
(en.name in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,3 desc,2,4;
PROMPT
PROMPT ORDERED BY WAIT_TIME_MILLI
PROMPT Note: This query won't work on 10.2 - ORA-942
select eh.snap_id, eh.instance_number inst, en.name, eh.wait_time_milli, eh.wait_count
from wrh$_event_histogram eh, v$event_name en
where eh.event_id = en.event_id and
(en.name in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,4,2,3 desc;
PROMPT
PROMPT ASH DETAILS FOR WORST MINUTES:
PROMPT
PROMPT APPROACH: If you cannot determine the problem from the data
PROMPT above, you may need to look at the details of what each session
PROMPT is doing during each 'bad' snap. Most likely you will want to
PROMPT note the times of the high log file sync waits, look at what
PROMPT LGWR is doing at those times, and go from there...
column program format a45 wra
column sample_time format a25 tru
column event format a30 tru
column time_waited format 999999.999
column p1 format a40 tru
column p2 format a40 tru
column p3 format a40 tru
select sample_time, inst_id inst, session_id, program, event, time_waited/1000 TIME_WAITED,
p1text||': '||p1 p1,p2text||': '||p2 p2,p3text||': '||p3 p3
from gv$active_session_history
where to_char(sample_time,'Mondd_hh24mi') in (select
to_char(sample_time,'Mondd_hh24mi')
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id
having avg(time_waited)/1000 > &&threshold)
and time_waited > 0.5
order by 1,2,3,4,5;
select to_char(sysdate,'Mondd hh24:mi:ss') TIME from dual;
spool off
PROMPT
PROMPT OUTPUT FILE IS: lfsdiag_&&dbname&×tamp&&suffix
PROMPT
执行后输出结果如下：
[oracle@node3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 26 01:35:22 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/home/oracle/lfsdiag.sql
TIMECOL SPOO
---------- ----
Apr26_0157 .out
OUTPUT
--------------------------------------------------------------------------------
node3_
LFSDIAG DATA FOR node3_Apr26_0157
Note: All timings are in milliseconds (1000 milliseconds = 1 second)
IMPORTANT PARAMETERS RELATING TO LOG FILE SYNC WAITS:
INST_ID NAME VALUE
---------- ---------------------------------------- ----------------------------------------
1 commit_logging
1 commit_point_strength 1
1 commit_wait
1 commit_write
1 log_archive_dest_1 location=/oradata/arch
1 log_archive_local_first TRUE
1 log_archive_max_processes 4
1 log_archive_min_succeed_dest 1
1 log_archive_start FALSE
1 log_archive_trace 0
ASH THRESHOLD...
This will be the threshold in milliseconds for average log file sync
times. This will be used for the next queries to look for the worst
'log file sync' minutes. Any minutes that have an average log file
sync time greater than the threshold will be analyzed further.
THRESHOLD_IN_MS
---------------
.000
ASH WORST MINUTES FOR LOG FILE SYNC WAITS:
APPROACH: These are the minutes where the avg log file sync time
was the highest (in milliseconds).
MINUTE INST_ID EVENT TOTAL_WAIT_TIME WAITS AVG_TIME_WAITED
------------ ---------- ------------------------------ ----------------- ---------- -----------------
Apr25_2300 1 log file sync 417.397 1 417.397
ASH LFS BACKGROUND PROCESS WAITS DURING WORST MINUTES:
APPROACH: What is LGWR doing when 'log file sync' waits
are happening? LMS info may be relevent for broadcast
on commit and LNS data may be relevant for dataguard.
If more details are needed see the ASH DETAILS FOR WORST
MINUTES section at the bottom of the report.
MINUTE INST PROGRAM EVENT TOTAL_WAIT_TIME WAITS AVG_TIME_WAITED
------------ ---- ---------------------------------------- ------------------------------ ----------------- ---------- -----------------
Apr25_2300 1 oracle@node3 (LGWR) log file parallel write 913.163 1 913.163
Apr25_2300 1 oracle@node3 (M000) log file sync 417.397 1 417.397
HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS:
APPROACH: Look at the wait distribution for log file sync waits
by looking at "wait_time_milli". Look at the high wait times then
see if you can correlate those with other related wait events.
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ---------------------------------------- --------------- ----------
1 log file sync 1 66
1 log file sync 2 1
1 log file sync 4 8
1 log file sync 8 37
1 log file sync 16 7
1 log file sync 32 0
1 log file sync 64 12
1 log file sync 128 2
1 log file sync 256 2
1 log file sync 512 1
1 log file sync 1024 1
1 log file switch completion 1 0
1 log file switch completion 2 0
1 log file switch completion 4 1
1 log file switch completion 8 0
1 log file switch completion 16 0
1 log file switch completion 32 1
1 log file parallel write 1 2486
1 log file parallel write 2 188
1 log file parallel write 4 73
1 log file parallel write 8 116
1 log file parallel write 16 102
1 log file parallel write 32 44
1 log file parallel write 64 28
1 log file parallel write 128 10
1 log file parallel write 256 7
1 log file parallel write 512 11
1 log file parallel write 1024 5
1 LGWR wait for redo copy 1 25
1 LGWR wait for redo copy 2 0
1 LGWR wait for redo copy 4 0
1 LGWR wait for redo copy 8 1
1 LGWR wait for redo copy 16 8
ORDERED BY WAIT_TIME_MILLI
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ---------------------------------------- --------------- ----------
1 log file sync 1 66
1 log file switch completion 1 0
1 log file parallel write 1 2486
1 LGWR wait for redo copy 1 25
1 log file sync 2 1
1 log file switch completion 2 0
1 log file parallel write 2 188
1 LGWR wait for redo copy 2 0
1 log file sync 4 8
1 log file switch completion 4 1
1 log file parallel write 4 73
1 LGWR wait for redo copy 4 0
1 log file sync 8 37
1 log file switch completion 8 0
1 log file parallel write 8 116
1 LGWR wait for redo copy 8 1
1 log file sync 16 7
1 log file switch completion 16 0
1 log file parallel write 16 102
1 LGWR wait for redo copy 16 8
1 log file sync 32 0
1 log file switch completion 32 1
1 log file parallel write 32 44
1 log file sync 64 12
1 log file parallel write 64 28
1 log file sync 128 2
1 log file parallel write 128 10
1 log file sync 256 2
1 log file parallel write 256 7
1 log file sync 512 1
1 log file parallel write 512 11
1 log file sync 1024 1
1 log file parallel write 1024 5
REDO WRITE STATS
"redo write time" in centiseconds (100 per second)
11.1: "redo write broadcast ack time" in centiseconds (100 per second)
11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
VERSION INST_ID NAME VALUE MILLISECONDS
----------------- ---------- ---------------------------------------- --------------------- -------------------
11.2.0.4.0 1 redo write info find 162
11.2.0.4.0 1 redo write time 1494 14940.000
11.2.0.4.0 1 redo writes 3070
AWR WORST AVG LOG FILE SYNC SNAPS:
APPROACH: These are the AWR snaps where the average 'log file sync'
times were the highest.
SNAP_ID INST BEGIN END NAME TOTAL_WAIT_TIME TOTAL_WAITS AVG_TIME_WAITED
---------- ---- ------------ ------------ ------------- ----------------- ----------- -----------------
293 1 Apr25_1325 Apr25_1400 log file sync 1296.545 7 185.221
AWR REDO WRITE STATS
"redo write time" in centiseconds (100 per second)
11.1: "redo write broadcast ack time" in centiseconds (100 per second)
11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
VERSION SNAP_ID INST STAT_NAME VALUE MILLISECONDS
----------------- ---------- ---- ------------------------------ --------------------- -------------------
11.2.0.4.0 56 1 redo writes 148
11.2.0.4.0 56 1 redo write time 704 7040.000
11.2.0.4.0 56 1 redo write info find 19
11.2.0.4.0 57 1 redo writes 455
11.2.0.4.0 57 1 redo write time 1248 12480.000
11.2.0.4.0 57 1 redo write info find 34
11.2.0.4.0 293 1 redo writes 98
11.2.0.4.0 293 1 redo write time 153 1530.000
11.2.0.4.0 293 1 redo write info find 9
AWR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
APPROACH: These are the AWR snaps where the average 'log file sync'
times were the highest. Look at related waits at those times.
SNAP_ID INST NAME TOTAL_WAITS TOTAL_WAIT_TIME AVG_TIME_WAITED
---------- ---- ---------------------------------------- ----------- ----------------- -----------------
56 1 log file sync 18 3667.963 203.776
56 1 log file parallel write 148 7039.040 47.561
56 1 LGWR wait for redo copy 2 .044 .022
57 1 log file sync 30 4068.399 135.613
57 1 log file parallel write 449 12375.569 27.563
57 1 log file switch completion 4 34.045 8.511
57 1 LGWR wait for redo copy 5 10.986 2.197
293 1 log file sync 7 1296.545 185.221
293 1 log file parallel write 98 1529.263 15.605
293 1 LGWR wait for redo copy 2 10.030 5.015
AWR HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
Note: This query won't work on 10.2 - ORA-942
APPROACH: Look at the wait distribution for log file sync waits
by looking at "wait_time_milli". Look at the high wait times then
see if you can correlate those with other related wait events.
SNAP_ID INST NAME WAIT_TIME_MILLI WAIT_COUNT
---------- ---- ---------------------------------------- --------------- ----------
56 1 log file sync 1 1
56 1 log file sync 4 2
56 1 log file sync 8 2
56 1 log file sync 32 1
56 1 log file sync 64 1
56 1 log file sync 128 5
56 1 log file sync 256 4
56 1 log file sync 512 1
56 1 log file sync 4096 1
56 1 log file parallel write 1 72
56 1 log file parallel write 2 9
56 1 log file parallel write 4 12
56 1 log file parallel write 8 6
56 1 log file parallel write 16 14
56 1 log file parallel write 32 10
56 1 log file parallel write 64 7
56 1 log file parallel write 128 10
56 1 log file parallel write 256 5
56 1 log file parallel write 512 3
56 1 log file parallel write 2048 1
56 1 log file parallel write 4096 1
56 1 LGWR wait for redo copy 1 2
57 1 log file sync 1 1
57 1 log file sync 2 1
57 1 log file sync 4 3
57 1 log file sync 8 3
57 1 log file sync 16 4
57 1 log file sync 32 2
57 1 log file sync 64 4
57 1 log file sync 128 5
57 1 log file sync 256 5
57 1 log file sync 512 1
57 1 log file sync 4096 1
57 1 log file switch completion 2 1
57 1 log file switch completion 4 1
57 1 log file switch completion 8 1
57 1 log file switch completion 32 1
57 1 log file parallel write 1 163
57 1 log file parallel write 2 37
57 1 log file parallel write 4 39
57 1 log file parallel write 8 44
57 1 log file parallel write 16 51
57 1 log file parallel write 32 51
57 1 log file parallel write 64 36
57 1 log file parallel write 128 19
57 1 log file parallel write 256 9
57 1 log file parallel write 512 3
57 1 log file parallel write 1024 1
57 1 log file parallel write 2048 1
57 1 log file parallel write 4096 1
57 1 LGWR wait for redo copy 1 4
57 1 LGWR wait for redo copy 16 1
293 1 log file sync 1 5
293 1 log file sync 8 1
293 1 log file sync 2048 1
293 1 log file parallel write 1 86
293 1 log file parallel write 2 1
293 1 log file parallel write 4 2
293 1 log file parallel write 8 2
293 1 log file parallel write 16 3
293 1 log file parallel write 32 3
293 1 log file parallel write 2048 1
293 1 LGWR wait for redo copy 1 1
293 1 LGWR wait for redo copy 16 1
ORDERED BY WAIT_TIME_MILLI
Note: This query won't work on 10.2 - ORA-942
SNAP_ID INST NAME WAIT_TIME_MILLI WAIT_COUNT
---------- ---- ---------------------------------------- --------------- ----------
56 1 log file sync 1 1
56 1 log file parallel write 1 72
56 1 LGWR wait for redo copy 1 2
56 1 log file parallel write 2 9
56 1 log file sync 4 2
56 1 log file parallel write 4 12
56 1 log file sync 8 2
56 1 log file parallel write 8 6
56 1 log file parallel write 16 14
56 1 log file sync 32 1
56 1 log file parallel write 32 10
56 1 log file sync 64 1
56 1 log file parallel write 64 7
56 1 log file sync 128 5
56 1 log file parallel write 128 10
56 1 log file sync 256 4
56 1 log file parallel write 256 5
56 1 log file sync 512 1
56 1 log file parallel write 512 3
56 1 log file parallel write 2048 1
56 1 log file sync 4096 1
56 1 log file parallel write 4096 1
57 1 log file sync 1 1
57 1 log file parallel write 1 163
57 1 LGWR wait for redo copy 1 4
57 1 log file sync 2 1
57 1 log file switch completion 2 1
57 1 log file parallel write 2 37
57 1 log file sync 4 3
57 1 log file switch completion 4 1
57 1 log file parallel write 4 39
57 1 log file sync 8 3
57 1 log file switch completion 8 1
57 1 log file parallel write 8 44
57 1 log file sync 16 4
57 1 log file parallel write 16 51
57 1 LGWR wait for redo copy 16 1
57 1 log file sync 32 2
57 1 log file switch completion 32 1
57 1 log file parallel write 32 51
57 1 log file sync 64 4
57 1 log file parallel write 64 36
57 1 log file sync 128 5
57 1 log file parallel write 128 19
57 1 log file sync 256 5
57 1 log file parallel write 256 9
57 1 log file sync 512 1
57 1 log file parallel write 512 3
57 1 log file parallel write 1024 1
57 1 log file parallel write 2048 1
57 1 log file sync 4096 1
57 1 log file parallel write 4096 1
293 1 log file sync 1 5
293 1 log file parallel write 1 86
293 1 LGWR wait for redo copy 1 1
293 1 log file parallel write 2 1
293 1 log file parallel write 4 2
293 1 log file sync 8 1
293 1 log file parallel write 8 2
293 1 log file parallel write 16 3
293 1 LGWR wait for redo copy 16 1
293 1 log file parallel write 32 3
293 1 log file sync 2048 1
293 1 log file parallel write 2048 1
ASH DETAILS FOR WORST MINUTES:
APPROACH: If you cannot determine the problem from the data
above, you may need to look at the details of what each session
is doing during each 'bad' snap. Most likely you will want to
note the times of the high log file sync waits, look at what
LGWR is doing at those times, and go from there...
SAMPLE_TIME INST SESSION_ID PROGRAM EVENT TIME_WAITED
------------------------- ---- ---------- --------------------------------------------- ------------------------------ -----------
P1 P2 P3
---------------------------------------- ---------------------------------------- ----------------------------------------
25-APR-16 11.00.45.557 PM 1 11 oracle@node3 (LGWR) log file parallel write 913.163
files: 1 blocks: 184 requests: 1
25-APR-16 11.00.45.557 PM 1 40oracle@node3 (M000) log file sync 417.397
buffer#: 1853 sync scn: 913952899 : 0
TIME
--------------
Apr26 01:57:24
OUTPUT FILE IS: lfsdiag_node3_Apr26_0157.out
- log file sync 诊断脚本
- Scripts:查询log file sync 等待的脚本lfsdiag.sql
- log file sync
- log file sync
- 漫谈LOG FILE SYNC
- log file sync
- log file sync
- LOG FILE SYNC概述
- log file sync总结
- log file sync
- 漫谈LOG FILE SYNC
- LOG FILE SYNC
- 2017-04-12 DBA日记，频繁commit导致的log file sync的诊断
- log file sync, log file parallell write
- log file sync, log file parallell write
- log file switch and log file sync
- log file sync, log file parallell write
- 如何产生Log File Sync
- yii2发送邮件简单教程
- 浅谈关于递归的一些感悟
- wordpress 访问 gravatar 以及 google map api 慢的解决办法
- 使用Genism进行词向量训练:教程版
- Android视图绘制流程完全解析，带你一步步深入了解View(二)
- log file sync 诊断脚本
- Java二叉树（三）--非递归的中序_先序_后序遍历二叉树
- RxJava&RxAndroid学习
- 浮层引导页Activity
- JSP标签开发--详解
- GCD 深入理解（二）
- BMDThread控件动态创建多线程示例
- zookeeper启动失败，zkServer.sh status 出错
- MongoDB常用命令
