Re: Someone remind me - SLOW LGWR - buffer flushes prevent new blocks being read in

From: Rodrigo Mufalani <rodrigo_at_mufalani.com.br>
Date: Thu, 18 Oct 2012 17:11:45 -0300
Message-ID: <de12d6adbbaec750a2232ada74d4d8df.squirrel_at_webmail.mufalani.com.br>

Hi Christopher,

  Can you tell us about the complete oracle version running,OS, size and number of redolog groups and the output from this query?

col h0 form 999
col h1 form 999
col h2 form 999
col h3 form 999
col h4 form 999
col h5 form 999
col h6 form 999
col h7 form 999
col h8 form 999
col h9 form 999
col h10 form 999
col h11 form 999
col h12 form 999
col h13 form 999
col h14 form 999
col h15 form 999
col h16 form 999
col h17 form 999
col h18 form 999
col h19 form 999
col h20 form 999
col h21 form 999
col h22 form 999
col h23 form 999

SELECT trunc(first_time) "Date",
 to_char(first_time, 'Dy') "Day",

        count(1) "Total",
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"

FROM V$log_history
where first_time > sysdate -2
group by trunc(first_time), to_char(first_time, 'Dy') Order by 1
/
  Then we will be able to point you in some direction.

Best Regards,

Rodrigo Mufalani
Oracle Ace Member
Tel.: +55 21 88994817
http://www.mufalani.com.br
<img src=http://mufalani.com.br/site/wp-content/uploads/2012/01/logo.png /img>

I was reading somewhere (and I can't put my hands on it now of course) where when LGWR is slow, that it backs stuff up bad enough that blocks can't get into the cache fast enough and will slow the whole database down (that's the gist of it I think). When LGWR is exceptionally slow/hung. Can someone point me in the right direction here?

Chris

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 18 2012 - 22:11:45 CEST