Home » RDBMS Server » Performance Tuning » The percentage of time spent by sessions waiting on redo operations
The percentage of time spent by sessions waiting on redo operations [message #560416] Thu, 12 July 2012 11:33 Go to next message
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.


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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.quest.com/communities/
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 Go to previous messageGo to next message
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 

/ 
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
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 2187 times)
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
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 Go to previous message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
All thanks for your input.
Previous Topic: DBA performance tools
Next Topic: Big database migration
Goto Forum:
  


Current Time: Thu Mar 28 09:35:25 CDT 2024