Forground wait class versus background wait class in AWR

All posts relating to Oracle database administration.

Moderator: Tim...

Forground wait class versus background wait class in AWR

Postby shrinika » Tue Dec 24, 2013 4:13 pm

Tim, Can you please help me understand Forground wait class in AWR reports?

My database shows 72% User IO. It means, the DB was waiting on IO long time? How can i interpret this?

Code: Select all
Foreground Wait Class
• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
•Captured Time accounts for 102.1% of Total DB time 46,711.44 (s)
•Total FG Wait Time: 36,072.67 (s) DB CPU time: 11,604.80 (s)

Wait Class

Waits

%Time -outs

Total Wait Time (s)

Avg wait (ms)

%DB time

User I/O 11,530,679 0 33,724 3 72.20
DB CPU     11,605   24.84
Commit 1,437,125 0 1,568 1 3.36
Concurrency 266,667 0 380 1 0.81
Configuration 15,904 0 324 20 0.69
Network 34,166,960 0 42 0 0.09
System I/O 50,742 0 18 0 0.04
Application 175 0 10 56 0.02
Other 15,297 37 7 0 0.01


But when i look at OS statistics, IO wait is too low. I am little confused.. please help

Code: Select all
Operating System Statistics - Detail


Snap Time

Load

%busy

%user

%sys

%idle

%iowait

01-Nov 00:00:27 12.58           
01-Nov 00:30:29 12.08 30.50 23.17 4.11 69.50 21.59
01-Nov 01:00:31 8.53 14.35 11.04 1.89 85.65 18.89


Also what is background wait class?

Any help is highly appreciated. I googled and not able to convince myself.
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Forground wait class versus background wait class in AWR

Postby Tim... » Tue Dec 24, 2013 5:43 pm

Hi.

- Background Wait Events: Those wait events that are caused by background processes while they are doing their work.
- Foreground Wait Events: Those wait events that are caused by user processes. This is the work you are doing sending to the server, like SQL etc.

Regarding the interpretation, it's kind-of hard because I can't read what you ave posted. I suggest in the future you post formatted results. If you use SET TAB OFF in SQL*Plus and define your COLUMN formats properly, it is usually readable. :)

Ignoring what you have posted, which I can't read, you have to be careful about what the percentages mean. Let's say in 1 hour my DB only had a *single* wait event on User I/O and nothing else. That would mean that User I/O is 100% of the wait states during that hour. If I base my analysis solely on the percentage, I will say, "Oh no. User I/O is killing my DB performance!" :) Clearly this is ridiculous, as a single wait even on User I/O is not a problem. Instead, you need to combine a number of factors to see if there is an issue.

- Number of wait events : Does the total load look like something that will stress your CPU or disk subsystem. This will depend on the throughput of your kit, which may not be obvious without asking your hardware people and doing some specific storage performance testing. ( http://www.oracle-base.com/articles/mis ... ystems.php )

- Maximum duration : Maybe you have a few very long wait events making the average look super bad.

- Minimum duration : Tells you what the best you can expect from an event is.

- Average duration

The information has to be looked at as a whole, rather than in isolation. This is where experience comes in to play. Look at a quiet server and you still have "Top 5 Event", but they are all pretty low and not affecting anything.

I'm sorry if this answer sounds rather vague, but without knowing your system, it is hard to say what number mean. It's a bit like me asking, "How long is this piece of string?", without ever showing you the string... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Forground wait class versus background wait class in AWR

Postby shrinika » Thu Dec 26, 2013 5:33 pm

Great explanation! thanks Tim! :-)
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Forground wait class versus background wait class in AWR

Postby shrinika » Thu Dec 26, 2013 5:54 pm

Tim, One last question....

I generated AWR reports during the peak time. The db was super busy that time. My database handle several million cardholders during that time.

In foreground wait class,

Code: Select all
Wait class        %DB time
user I/O           78.79
DB CPU             19.97
Commit             2.52


Based on this info, here is my understanding. 78% of the time(it is 1 hour AWR report) in that hour, DB was waiting for USER I/O. Is that correct?

I really can not say, it is GOOD or BAD. It is okay to be 78% of User I/0 due to peak volume...

Just asking..
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Forground wait class versus background wait class in AWR

Postby Tim... » Thu Dec 26, 2013 6:13 pm

Hi.

That is correct. 79% of the time the DB was doing something, it was doing User I/O.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron