Home » RDBMS Server » Server Administration » log file sync issue / archive file size issue (oracle 11g/r2/unix)
|
|
|
|
| Re: log file sync issue / archive file size issue [message #609461 is a reply to message #609460] |
Fri, 07 March 2014 02:11   |
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
|
Junior Member |
|
|
i ran sql to identify log file sync issue.
select
e.sid
,e.event
,e.time_waited
,e.time_waited_micro
from
v$session_event e
,v$session s
where
e.sid=s.sid
and s.program like '%LGWR%'
order by
3
;
SID EVENT TIME_WAITED TIME_WAITED_MICRO
1801 latch: redo writing 0 2822
1801 Disk file operations I/O 1 7808
1801 os thread startup 1 13561
1801 KSV master wait 56 559133
1801 ARCH wait for netserver start 100 1000823
1801 log file single write 334 3336734
1801 log file sequential read 614 6136717
1801 control file sequential read 2291 22911340
1801 control file parallel write 5129 51292891
1801 LGWR wait on LNS 21333 213333623
1801 log file parallel write 4129019 41290185371
1801 events in waitclass Other 5162060 51620602574
1801 LGWR real time apply sync 9312341 93123410076
1801 rdbms ipc message 22764167 227641665233
i do see bigger number on log file parallel write ? looks like some issue ..
2. sql for v$loghist
with in an hour +20 files switches , is it good ?
select * from v$loghist order by 3 desc
thread sequence first_change first_time switch_change
2 128184 13626447354661 3/6/2014 11:51:22 PM 13626449908837
4 197021 13626447350367 3/6/2014 11:51:20 PM 13626449909335
1 130735 13626445312333 3/6/2014 11:46:50 PM 13626449911297
4 197020 13626445308954 3/6/2014 11:46:48 PM 13626447350367
3 163577 13626442151002 3/6/2014 11:40:24 PM 13626449909749
4 197019 13626442146372 3/6/2014 11:40:21 PM 13626445308954
2 128183 13626439524617 3/6/2014 11:35:21 PM 13626447354661
4 197018 13626439519909 3/6/2014 11:35:18 PM 13626442146372
1 130734 13626437892003 3/6/2014 11:31:25 PM 13626445312333
4 197017 13626437506949 3/6/2014 11:31:22 PM 13626439519909
3 163576 13626435003494 3/6/2014 11:27:21 PM 13626442151002
4 197016 13626434996316 3/6/2014 11:27:17 PM 13626437506949
2 128182 13626431936815 3/6/2014 11:21:56 PM 13626439524617
4 197015 13626431935985 3/6/2014 11:21:56 PM 13626434996316
1 130733 13626430228837 3/6/2014 11:19:00 PM 13626437892003
4 197014 13626430219917 3/6/2014 11:18:58 PM 13626431935985
3 163575 13626428935264 3/6/2014 11:16:03 PM 13626435003494
4 197013 13626428934791 3/6/2014 11:16:03 PM 13626430219917
2 128181 13626426885908 3/6/2014 11:12:03 PM 13626431936815
4 197012 13626426878204 3/6/2014 11:12:00 PM 13626428934791
1 130732 13626424001070 3/6/2014 11:07:47 PM 13626430228837
4 197011 13626423994615 3/6/2014 11:07:44 PM 13626426878204
3 163574 13626421494808 3/6/2014 11:02:27 PM 13626428935264
4 197010 13626421493371 3/6/2014 11:02:26 PM 13626423994615
|
|
|
|
| Re: log file sync issue / archive file size issue [message #609470 is a reply to message #609461] |
Fri, 07 March 2014 03:12 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:i ran sql to identify log file sync issue.
select total_waits, total_timeouts, time_waited, average_wait
from v$system_event where event='log file sync';
Quote:i do see bigger number on log file parallel write ? looks like some issue ..
You must run an AWR/StatsPack report. (AWR only if you are licensed for, StatsPack is free but you have to install it.)
Quote:with in an hour +20 files switches , is it good ?
Run the following query instead:
Column nop noprint
Column total format 999G990 heading "Total"
Column h00 format a03 heading "H00" trunc
Column h01 format a03 heading "H01" trunc
Column h02 format a03 heading "H02" trunc
Column h03 format a03 heading "H03" trunc
Column h04 format a03 heading "H04" trunc
Column h05 format a03 heading "H05" trunc
Column h06 format a03 heading "H06" trunc
Column h07 format a03 heading "H07" trunc
Column h08 format a03 heading "H08" trunc
Column h09 format a03 heading "H09" trunc
Column h10 format a03 heading "H10" trunc
Column h11 format a03 heading "H11" trunc
Column h12 format a03 heading "H12" trunc
Column h13 format a03 heading "H13" trunc
Column h14 format a03 heading "H14" trunc
Column h15 format a03 heading "H15" trunc
Column h16 format a03 heading "H16" trunc
Column h17 format a03 heading "H17" trunc
Column h18 format a03 heading "H18" trunc
Column h19 format a03 heading "H19" trunc
Column h20 format a03 heading "H20" trunc
Column h21 format a03 heading "H21" trunc
Column h22 format a03 heading "H22" trunc
Column h23 format a03 heading "H23" trunc
Select trunc(first_time) nop,
to_char(first_time, 'Dy DD/MM/YYYY') jour,
count(*) total,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'fm999'),3),' 0',' ') h00,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'fm999'),3),' 0',' ') h01,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'fm999'),3),' 0',' ') h02,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'fm999'),3),' 0',' ') h03,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'fm999'),3),' 0',' ') h04,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'fm999'),3),' 0',' ') h05,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'fm999'),3),' 0',' ') h06,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'fm999'),3),' 0',' ') h07,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'fm999'),3),' 0',' ') h08,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'fm999'),3),' 0',' ') h09,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'fm999'),3),' 0',' ') h10,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'fm999'),3),' 0',' ') h11,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'fm999'),3),' 0',' ') h12,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'fm999'),3),' 0',' ') h13,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'fm999'),3),' 0',' ') h14,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'fm999'),3),' 0',' ') h15,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'fm999'),3),' 0',' ') h16,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'fm999'),3),' 0',' ') h17,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'fm999'),3),' 0',' ') h18,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'fm999'),3),' 0',' ') h19,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'fm999'),3),' 0',' ') h20,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'fm999'),3),' 0',' ') h21,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'fm999'),3),' 0',' ') h22,
replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'fm999'),3),' 0',' ') h23
from v$log_history
where first_time > add_months(sysdate,-1)
group by trunc(first_time), to_char(first_time, 'Dy DD/MM/YYYY')
order by 1
/
But if you really have "in an hour +20 files switches", you should at least double the size of you redo logs.
|
|
|
|
Goto Forum:
Current Time: Thu Nov 28 21:33:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
