Skip to Main Content
  • Questions

  • Tune Foreground or Background Part of Wait Events

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: January 30, 2016 - 2:02 pm UTC

Last updated: February 10, 2016 - 12:52 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Good Morning, Gentlemen,

I just realized that v$system_event provides a breakdown of the time spent on background and foreground. I just have to subtract time_waited_micro-time_waited_micro_fg to attain the time spent on the background piece.

select event, time_waited_micro, time_waited_micro_fg from v$system_event;
EVENT TIME_WAITED_MICRO TIME_WAITED_MICRO_FG
---------------------------------------- ----------------- --------------------
log file sync 10444615 8888593
db file sequential read 458338939 99571773
db file scattered read 117470443 6988436

Are you able to breakdown the various activities performed by foreground and background activities for the three wait events listed above?

My guess is that Oracle decided to breakdown a wait event into foreground and background to allow one to focus in on an even smaller target. For instance, if I notice that most time was spent on the foreground "db file sequential read", i would tune X, Y & Z. If most time is spent on the background "db file sequential read", i would tune A,B and C.

Thanks,

John

and Connor said...

I'm not entirely sure I understand your question:

log file sync is anyone waiting for the completion of a commit.

db file sequential read - single block read. Most typically - index access to a table.

db file scattered read - multi block read. Most typically - table full scan.

In all three cases, its either a foreground (user) session waiting or a background session waiting. The latter is *likely* to be of less concern, because typically you dont really care too much if a background session was waiting - unless it had a subsequent impact on a foreground session.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John Cantu, January 31, 2016 - 3:16 pm UTC

Thanks, Connor. The problem why the question may not really make sense is because I don't really understand the "background wait" piece.

I've always heard that pmon, smon, and other "background processes". I recently realized that wait events have both a "background wait event" and a "foreground wait event" like the three examples I listed.

I am going to breakdown the questions into simple questions that will allow me to understand this "background wait" piece.

Are "background sessions waits" only performed by background processes like pmon, smon, dbwr, etc.?

Will every foreground "db file scattered read" wait also have a "background session wait"? In other words, if my foreground session performs an full table scan, will that operation have both a "foreground wait" and a "background wait"? If no, then, does a "background process" perform its own full table scans when I see a "db file scattered read" with a "background wait"?

Why don't we have to be too concerned about the "background session waits"?

Thanks for your help. this topic either was not covered in class or was sped through by the instructor back in 2002 when i took the admin classes.

Thanks,

John
Connor McDonald
February 01, 2016 - 2:40 am UTC

I think of it this way.

Waits are waits. Everything in Oracle is pretty much either doing something (where "doing" means burning CPU) or waiting (where "waiting" might be one of - burning a non-CPU resource, eg, I/O or, *wanting* to do something but cannot, eg locks, or actually doing nothing, eg, waiting for you to enter a command).

So then, its *who* is doing the waiting that determines foreground or background.

*You* run a query that does a full scan... its a *foreground* db file scattered read.

*SMON* runs a query that a full scan... its a *background* db file scattered read.

Then there *might* be some interaction between the two, for example:

*You* do a commit, its foreground wait for 'log file sync'. You've asked LGWR to write some data, so you have a foreground wait but you are waiting on a background process to complete. LGWR dispatches the I/O off to the OS and it is now waiting for the I/O to complete. So it has a background wait of "log file parallel write". You could argue that *you* are also effectively waiting on that as well, but we dont make that interpretation because it wasnt you that issued the I/O. Eventually the IO completes and LGWR is done, so its no longer waiting. Shortly thereafter, LGWR is going to let you know that the commit is done, and you will no longer be waiting on 'log file sync'.

Hope this helps.

Almost there!

John Cantu, February 09, 2016 - 1:56 am UTC

Thanks for the explanation. That makes a whole lot more sense to me.

I have a few more questions.

What is the wait event for a session that is on the CPU run queue, meaning the session wants to use CPU but is waiting for the CPU? Does that time still count toward "CPU Time" or is there a wait event for that?

What is the wait event for a session that is on the I/O queue, meaning the session wants to use I/O but is waiting for its turn?

Thank you
Connor McDonald
February 10, 2016 - 12:52 am UTC

Craig Shallahamer has an excellent blog post on this

http://shallahamer-orapub.blogspot.com.au/2015/02/what-is-that-light-green-oracle.html

but to paraphrase, since we dont explicitly measure "on cpu queue" time, it is inferred by:

"We did "n" seconds as being ON CPU (from our perspective), and when the cpu usage came back from the OS after our call, it was less than "n", so the delta is assumed to be 'waiting for cpu'"

But do not confuse this was the Oracle standard definition of "wait", which is pretty much *anything* non-CPU.

CPU appears to be only one where we make that differential. For I/O, we simply say "we started an I/O, and we got a response back", so this is how long it took. We dont care whether we were queued in storage network, on the controller, etc etc...we just say "Man, that disk read took a while"

But for inside the db, its Total = ON CPU + other, and everything that is other is a wait.


John Cantu, February 09, 2016 - 2:30 am UTC

For instance, if I am waiting to use a bicycle (waiting for a resource to become available), I consider that "waiting" to use the bike. Once I am using the bicycle, that is "using time" not wait time. Along those lines, if I am "waiting" to use I/O, I consider that waiting to use I/O. Once I am using I/O, I consider that "using time" not wait time.

Or does Oracle only care about CPU. If you are using CPU, then you are not waiting. Everything else is considered "waiting" time?

Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.