OraFAQ Forum: Performance Tuning » How to deal with 'Free buffer waits' event

Home » RDBMS Server » Performance Tuning » How to deal with 'Free buffer waits' event (Oracle 11g R2 , Linux 64bit)

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

How to deal with 'Free buffer waits' event [message #649112]

Sun, 13 March 2016 23:41

kangfei
Messages: 3
Registered: March 2016

Junior Member

My PL/SQL program has long running time and low CPU utilization. The awr report shows the TOP1 timed foreground events is free buffer waits(60.25%).How can I deal with it? I try some approaches, but seems no effect.

1. Reduce the checkpoint frequency, it seem I can enlarge the redo log size to reduce the checkpoint frequency. This is the checkpoint interval of my system with heavy load(running programs).

TO_CHAR(FIRST_TIME,'DD-MON-
---------------------------
08-MAR-16 01:10 PM
08-MAR-16 01:10 PM
08-MAR-16 01:10 PM
08-MAR-16 01:10 PM
08-MAR-16 01:11 PM
08-MAR-16 01:11 PM
08-MAR-16 01:11 PM
08-MAR-16 01:14 PM
08-MAR-16 01:46 PM
08-MAR-16 01:47 PM
08-MAR-16 01:47 PM

TO_CHAR(FIRST_TIME,'DD-MON-
---------------------------
08-MAR-16 01:47 PM
08-MAR-16 01:47 PM
08-MAR-16 01:48 PM
08-MAR-16 01:48 PM
08-MAR-16 01:50 PM
08-MAR-16 01:59 PM
08-MAR-16 01:59 PM
08-MAR-16 01:59 PM
08-MAR-16 01:59 PM
08-MAR-16 02:00 PM
08-MAR-16 02:00 PM

TO_CHAR(FIRST_TIME,'DD-MON-
---------------------------
08-MAR-16 02:01 PM
08-MAR-16 02:02 PM

Dose it very frequent? Actually, I have ever added the log files to 7 groups. This is the V$log view:

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 3265 52428800 512 1 NO
INACTIVE 43252812 08-MAR-16 43256155 08-MAR-16

2 1 3272 52428800 512 1 NO
CURRENT 43395238 08-MAR-16 2.8147E+14

6 1 3266 262144000 512 1 NO
INACTIVE 43256155 08-MAR-16 43274090 08-MAR-16

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
7 1 3267 262144000 512 1 NO
INACTIVE 43274090 08-MAR-16 43289790 08-MAR-16

8 1 3268 262144000 512 1 NO
INACTIVE 43289790 08-MAR-16 43310841 08-MAR-16

9 1 3269 262144000 512 1 NO
INACTIVE 43310841 08-MAR-16 43329918 08-MAR-16

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
10 1 3270 262144000 512 1 NO
INACTIVE 43329918 08-MAR-16 43362453 08-MAR-16

11 1 3271 262144000 512 1 NO
INACTIVE 43362453 08-MAR-16 43395238 08-MAR-16

In addition, most of my SQL statements manipulate Global Temporary Table(GTT), and it should bypass the redo. Therefore, I'm wondering if it is necessary to enlarge the redo log file size.

2. For adjusting the size of the buffer cache, as the component of SGA, maybe the size of buffer cache is controlled default in ASM(Automatic Storage Management)? It seem actually I had better not to turning it?

3. For the event free butter waits , there is a parameter called "FILESYSTEMIO_OPTIONS". It controls the asynchronous IO and direct IO. I change it from "none" to "setall" but it seems has only slight effect to the performance. And can the ASM control Oracle file system?

4. In Oracle official statement, #DBWR process= #cpu /8 . So the machine of 8 cpu had better set the db_writer_processes to 1 (Now it is the default configuration). Isn't it?

How can I deal with the free buffer waits? I'm really confused.
Thanks for your help.

Report message to a moderator

Re: How to deal with 'Free buffer waits' event [message #649117 is a reply to message #649112]

Mon, 14 March 2016 02:18

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

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Free buffer wait is (sometimes) an indication that your database writer(s) cannot clean the cache fast enough. I would increase the size of the logfiles by a factor of ten, and double th number of database writers.
Note that work on GTTs will generate redo, not for the rows on the GTTs but for the undo segments that protect the transactions.

If you post the AWR or statspack report (or wtever else you are using t investigate this) that would help.

Report message to a moderator

Re: How to deal with 'Free buffer waits' event [message #649120 is a reply to message #649117]

Mon, 14 March 2016 04:23

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

As John says, can you upload the full report?

Also is the host dedicated?

(I've never seen the case where I've needed >1 dbwr, by the way and we have some pretty busy systems)

Report message to a moderator

Re: How to deal with 'Free buffer waits' event [message #649123 is a reply to message #649120]

Mon, 14 March 2016 06:21

kangfei
Messages: 3
Registered: March 2016

Junior Member

Attachment is the AWR report.
Actually, it's only personal workstation. The data I processing is no larger than 2G. I have enlarged the log file and set the db_writer_processes =2, but seem has limited effect.

Attachment: awr_3_3.txt
(Size: 119.97KB, Downloaded 2281 times)

Report message to a moderator

Re: How to deal with 'Free buffer waits' event [message #649124 is a reply to message #649123]

Mon, 14 March 2016 06:23

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

Senior Member

That is unreadable.

Report message to a moderator

Re: How to deal with 'Free buffer waits' event [message #649125 is a reply to message #649123]

Mon, 14 March 2016 06:30

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You've lost the formatting somewhere in there. That awr doesnt have the filesystemio_options set. Was that beforehand?

If the DB isn't able to write asynchronously these waits will happen.

I have seen very nasty things happen with SETALL and storage sometimes, we have to use ODM to get it to work correctly in our stack. I've had better success simply setting the filesystemio_options to asynch in cases where it wasn't playing nicely under setall. Check disk_asynch_io set to true also.

I'd also give yourself a bigger cache and turn ASM off, although if it's just a wee test machine you'll be ok.

Note: ASM is rare in my shop.

Note2: It has been my experience that this wait event "db file async I/O submit" means the database cannot write asynchronously. I've never seen it on a true async system. I think it is reporting that it is trying and is unable to.

[Updated on: Mon, 14 March 2016 06:33]

Report message to a moderator