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_at_gmail.com> 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_at_gmail.com>
> *Sent:* 13 January 2021 17:51
> *To:* jacques.kostic_at_gmail.com
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *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_at_gmail.com> wrote:
>
> 2 db_writer_processes will be more than enough
>
>
>
> Cheers
>
> jko
>
>
>
> *From:* Pap <oracle.developer35_at_gmail.com>
> *Sent:* 13 January 2021 17:14
> *To:* jacques.kostic_at_gmail.com
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *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_at_gmail.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Pap
> *Sent:* 13 January 2021 15:04
> *To:* Oracle L <oracle-l_at_freelists.org>
> *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
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l