What is foregraound and backgroup wait events?:
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 don't make that interpretation because it wasn't 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'.
Ref.:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9525949900346594685
http://shallahamer-orapub.blogspot.com.au/2015/02/what-is-that-light-green-oracle.html
http://blog.orapub.com/20140805/what-is-oracle-db-time-db-cpu-wall-time-and-non-idle-wait-time.html
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 don't make that interpretation because it wasn't 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'.
Ref.:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9525949900346594685
http://shallahamer-orapub.blogspot.com.au/2015/02/what-is-that-light-green-oracle.html
http://blog.orapub.com/20140805/what-is-oracle-db-time-db-cpu-wall-time-and-non-idle-wait-time.html
Foreground and background wait events in Oracle depend on who is doing the waiting like when your session waits on ‘log file sync’ but LGWR handles the actual disk write in the background. Such behind-the-scenes processing directly impacts performance, especially in systems like proton ge where user responsiveness is key.
ReplyDelete