OraFAQ Forum: Performance Tuning » The percentage of time spent by sessions waiting on redo operations

Home » RDBMS Server » Performance Tuning » The percentage of time spent by sessions waiting on redo operations

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The percentage of time spent by sessions waiting on redo operations [message #560416]

Thu, 12 July 2012 11:33

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I am getting the this warning from fogligt, The percentage of time spent by sessions waiting on redo operations. To me this sounds like there is wait to write out the redo log. Does this sound right?

Secondly, is there a way I can tell if my redo logs are too small or if I need more logs?

Any guidance with this issue would be greatly appreciated.

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560417 is a reply to message #560416]

Thu, 12 July 2012 11:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I am getting the this warning from fogligt,
Raise the threshold to stop it from acting like Chicken Little.
http://en.wikipedia.org/wiki/Chicken_little

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560418 is a reply to message #560417]

Thu, 12 July 2012 11:48

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I would like to understand what it means so I posted my thouhts. Ignoring the issue right now is something I dont want to do.

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560419 is a reply to message #560416]

Thu, 12 July 2012 11:50

John Watson
Messages: 8843
Registered: January 2010
Location: Global Village

Senior Member

Hi, man. This may be an example of why the Quest products are not perfect. One has to be careful (you never know, they might buy advertising here one day!) but I think the Quest products often give advice based on general principles that is not applicable to all cases. Best would be to generate a statspack report (or AWR, if you have the licence) covering an hour or two of your busiest time and upload it here. That will be ppure information, no attempted advice. Then see what people say.

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560420 is a reply to message #560419]

Thu, 12 July 2012 11:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.quest.com/communities/

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560421 is a reply to message #560420]

Thu, 12 July 2012 12:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT To_char(first_time, 'YYYY-MM-DD HH24') YYYY_MM_DD_HH, 
       Count(*) 
FROM   v$loghist 
WHERE  first_time > Trunc(SYSDATE - 4) 
GROUP  BY To_char(first_time, 'YYYY-MM-DD HH24') 
ORDER  BY 1 

/

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560423 is a reply to message #560419]

Thu, 12 July 2012 12:13

Michel Cadot
Messages: 68455
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

This may be an example of why the Quest products are not perfect.

This is what is called a litote, isn't it?

Regards
Michel

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560424 is a reply to message #560421]

Thu, 12 July 2012 12:17

BeefStu
Messages: 208
Registered: October 2011

Senior Member


SQL> SELECT To_char(first_time, 'YYYY-MM-DD HH24') YYYY_MM_DD_HH,
       Count(*)
FROM   v$loghist
WHERE  first_time > Trunc(SYSDATE - 4)
GROUP  BY To_char(first_time, 'YYYY-MM-DD HH24')
ORDER  BY 1
  2    3    4    5    6    7
SQL> /

YYYY_MM_DD_HH   COUNT(*)
------------- ----------
2012-07-08 13          9
2012-07-08 22          1
2012-07-09 05          2
2012-07-09 06          3
2012-07-09 07          9
2012-07-09 09          3
2012-07-09 11          1
2012-07-09 12          2
2012-07-09 13          1
2012-07-09 14          1
2012-07-09 15          8

YYYY_MM_DD_HH   COUNT(*)
------------- ----------
2012-07-09 16          1
2012-07-09 19          9
2012-07-09 21          1
2012-07-09 22          2
2012-07-10 06          1
2012-07-10 07          9
2012-07-10 11          1
2012-07-10 12         11
2012-07-10 14          1
2012-07-10 16          1
2012-07-10 17         10

YYYY_MM_DD_HH   COUNT(*)
------------- ----------
2012-07-10 18          8
2012-07-10 19          9
2012-07-10 21          1
2012-07-10 22          1
2012-07-11 00          1
2012-07-11 02          2
2012-07-11 03          2
2012-07-11 07         10
2012-07-11 08         11
2012-07-11 10         26
2012-07-11 11          2

YYYY_MM_DD_HH   COUNT(*)
------------- ----------
2012-07-11 12          3
2012-07-11 14          1
2012-07-11 16          2
2012-07-11 17          1
2012-07-11 19          3
2012-07-11 21          1
2012-07-11 23          1
2012-07-12 06          3
2012-07-12 07          4
2012-07-12 08          1

43 rows selected.

Is this telling me the number of redo logs written to disk by hour?

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560425 is a reply to message #560424]

Thu, 12 July 2012 12:22

BeefStu
Messages: 208
Registered: October 2011

Senior Member

attached is a copy of an AWR report when I got the fog light warning. Based on this report can anybody see why I may have gotten this message.

Attachment: awr.txt
(Size: 107.37KB, Downloaded 1278 times)

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560427 is a reply to message #560424]

Thu, 12 July 2012 12:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is this telling me the number of redo logs written to disk by hour?
YES.

some say the size of REDO should be such that they switch 3 or 4 times per hour.
If you were to DOUBLE the size of REDO, those counts would be cut in half.
Tradeoffs exist.
As REDO size increases, then boot time after crash (applying REDO) can increase.

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560431 is a reply to message #560425]

Thu, 12 July 2012 12:51

John Watson
Messages: 8843
Registered: January 2010
Location: Global Village

Senior Member

Well, I would say that your database is barely ticking over. A large proportion of the activity is actually Foglightr reporting on itself. you have two non-Foglight SQLs that are taking a few seconds, perhaps they need tuning? But basically, I would say you need do nothing. I am of course open to correction here, this "analysis" is rthe result of about 30 seconds.

Report message to a moderator

Re: The percentage of time spent by sessions waiting on redo operations [message #560434 is a reply to message #560431]

Thu, 12 July 2012 13:01

BeefStu
Messages: 208
Registered: October 2011

Senior Member

All thanks for your input.

Report message to a moderator

Previous Topic:	DBA performance tools
Next Topic:	Big database migration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 09 22:43:22 CDT 2023