Question Bibufs Parameter

[Jack@dba]

Hi All,

Regarding bibufs parameter present value for our database is 300.we are planning to migrate the version from 10.2 to 11.6 Enterprise edition.During this time we are planned for bibufs parameter value for increase.

So any help on how to suggest a value for bibufs or any theory or calculations to assign value.And also how to test this parameter.

Thanks
Jack

 

[Cringer]

Other more experienced folks will no doubt advise more but what problem are you encountering that you want to fix with a change to bibufs?

 

[Rob Fitzpatrick]

It's easier to know if -bibufs is too low than to know if it's too high. And "too high" (or "more than enough") has to be defined: a value that is more than enough for your present transaction workload might be not enough when your workload increases.

That said, I usually see values lower than 300 and I wouldn't increase it without knowing that 300 is insufficient, e.g. by seeing that there are empty BI buffer waits on an otherwise well-tuned system. For example if you aren't running a BIW, you could have empty buffer waits and the appropriate fix in that case is to run a BIW, not to increase -bibufs.

You can see empty buffer waits in promon R&D 2 5 or in _actbilog._bilog-ebuffwaits. You can see how many full BI buffers there are at present in promon R&D 1 9; there is currently no corresponding VST field for that.

 

[Rob Fitzpatrick]

Beyond -bibufs, a version upgrade is a good time to take stock of how the database and application are performing and review the configuration of clients and brokers. Also, 11.6 has new capabilities that are not in 10.2B so you should evaluate the new features and parameters and see which ones make sense for you.

But I think it's wise to take a data-driven approach to tuning. Look at the stats in the current production environment and try to identify bottlenecks (and obvious bad/missing parameter values, if any). Changing -bibufs from 300 to 500 when you already have more than enough bibufs won't help performance and will waste RAM. Making -B larger than the database will waste RAM.

But making -bibufs larger when you can show that there are lots of empty buffer waits, or making -B much larger when you can show that the buffer hit ratio is low, or increasing -omsize when you can show that there is OM latch contention, those are reasonable changes. Don't forget to tune the client side too!

 

[Cringer]

One really nice feature (I think so anyway) in 11.6 is the fact that -Mm is set on the database and the clients negotiate the correct setting. This means you don't need to specify it any more, and can have different -Mm for different databases in the same session. This was something that has held me back from changing this value in the past. Long story. Anyhow, you can get some decent performance enhancements on the client side by upping the -Mm parameter and also tweaking prefretchfactor, prefetchdelay etc.