Hi Pap,
Usually, common mistake with big SGA is too few db_writer processes producing
free buffer waits on heavy activity.
Reasons: not enough db_writer processes to deal with all blocks to be written
to disk before using the slot for another block.
I am curious to see if to many db_writers processes could produce the same
effect, this is just a guess…
Cheers
jko
From: Pap <oracle.developer35@xxxxxxxxx>
Sent: 13 January 2021 18:20
To: jacques.kostic@xxxxxxxxx
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Performance issue - high Free buff wait
As i have attached the AWR in my first email, below is the host CPU section
from that.
And CPU_COUNT in the gv$parameter is set as - 64. No resource manager is active.
I am still not able to fully get the point, so can you please help me
understand how these things(say decreasing db_writer_processes) logically help
to get rid of "free buffer waits" and thereby eliminate other contentions/waits
too?
Host Name
Platform
CPUs
Cores
Sockets
Memory (GB)
XXXX
HP-UX IA (64-bit)
64
64
8
511.66
On Wed, Jan 13, 2021 at 10:34 PM <jacques.kostic@xxxxxxxxx
<mailto:jacques.kostic@xxxxxxxxx> > wrote:
You can try.
How about your number of CPU in the host and cpu_count parameter?
Is resource manager active?
Cheers
jko
From: Pap <oracle.developer35@xxxxxxxxx <mailto:oracle.developer35@xxxxxxxxx> >
Sent: 13 January 2021 17:51
To: jacques.kostic@xxxxxxxxx <mailto:jacques.kostic@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >
Subject: Re: Performance issue - high Free buff wait
Thank you.
Do you mean to say decreasing the db_writer_process from current value of 8 to
2 will fix the free buffer wait issue thus removing contention from those
15minutes window? I am not able to relate it logically , how this will do such.
Regards
Pap
On Wed, 13 Jan 2021, 9:48 pm , <jacques.kostic@xxxxxxxxx
<mailto:jacques.kostic@xxxxxxxxx> > wrote:
2 db_writer_processes will be more than enough
Cheers
jko
From: Pap <oracle.developer35@xxxxxxxxx <mailto:oracle.developer35@xxxxxxxxx> >
Sent: 13 January 2021 17:14
To: jacques.kostic@xxxxxxxxx <mailto:jacques.kostic@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >
Subject: Re: Performance issue - high Free buff wait
SGA is ~24GB for this database and total host memory is 500gb+ and its hosting
5 databases.
db_writer_processes is set as "8" for this database.
Don't have access to the DB server right now but I think we are using large
pages, will check and confirm.
Regards
Pap
On Wed, Jan 13, 2021 at 7:52 PM <jacques.kostic@xxxxxxxxx
<mailto:jacques.kostic@xxxxxxxxx> > wrote:
Hi,
What is the size of your SGA?
Are you using large pages?
What is your value for db_writer_processes?
Cheers
jko
From: oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> > On
Behalf Of Pap
Sent: 13 January 2021 15:04
To: Oracle L <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >
Subject: Performance issue - high Free buff wait
Not sure why , but the first email I dropped on this question was bounced back
from oracle-l, so trying again.
Hello experts,
We have a database with Oracle version 11.2.0.4. We are getting complaints of
slowness in one of our jobs mainly for ~15minutes duration on a daily basis.
And there is no plan change or any Undo reads for the underlying sqls and also
there is not one sql which we can singled out for that job duration and that
job consists of many small queries(selects, inserts running many thousand times
in that duration). But while seeing/comparing the AWR for that ~15minutes
period VS another ~15minutes just before that, we are seeing a few odd waits ,
they are a combination of Configuration(free buffer waits) followed by
concurrency(buffer busy, index contention etc) foreground waits. We do see
comparatively higher sessions during this ~15minutes window as compared to
normal time.
In the AWR under section "IOStat by Function summary" the DB writer avg
response time is logged as ~103 ms vs in normal period it stays ~35ms. And then
checking v$iostat_file , we see it has ASYNCH_IO set as OFF for both "data
file" and "temp file" which I have seen in many databases normally set to ON.
Also in dba_hist_active_sess_history the top waits events are showing as below.
I have attached excel with specific sections of AWR during the issue period and
normal period in two different tabs.
1)So , is it correct that by turning this ASYNCH_IO "ON" for data/temp file ,
will cater all these issues because these all seem to be triggering from the
slow DB writer performance?
Or
2)Should we ask the application team to reduce the total number of
sessions(maybe by decreasing the max connection limit) which are submitting to
the Database at that point to reduce contention?
Below is count of waits from Dba_hist_active_sess_history for that ~15minutes
window:-
Top two(log file sync and db file async I/O submit) are not associated with
application user/session.
EVENT
COUNT(*)
log file sync
260
db file async I/O submit
188
free buffer waits
72
66
65
63
62
db file sequential read
56
write complete waits
54
54
db file sequential read
51
Regards
Pap
