Database Writer Parameters – What is Wrong with this Quote?

18 06 2010

June 18, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I keep stumbling across interesting sections of the book.  Here is an interesting section – I hope that I did not exclude too much of the surrounding section of the book, causing the paragraph to lose contextual meaning (I am trying to balance how much typing I need to do with how much needs to be quoted to avoid losing the meaning of the material).  From page 219:

“From an Oracle perspective, I start thinking of any instance parameter that may increase Oracle’s IO writing efficiency. For example, I would investigate looking for a way to increase the database writer’s batch write size. As I mentioned, there are version-specific ways to alter the database writer’s batch size. Investigate the _db_block_write_batch and the _db_writer_max_writes parameters. Also considering increasing the instance parameter _db_writer_max_scan_pct (the default may be 40, for 40%) or _db_writer_max_scan_cnt, as they determine how many LRU buffer headers a server process will scan before it signals the database writer to start writing. Increasing these parameters provides more time for the write list to build up, and therefore results in more blocks written per database writer IO request.”

What, if anything, is wrong with the above quote from the book?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.



4 responses

18 06 2010

I don’t think it is useful to recommend investigating 3 hidden parameters that have dubious impact in modern Oracle Database revisions. I understand that investigation is one thing, but to what end? If you start plopping these sorts of tunables in your init.ora and subsequently have any problems at all Support is likely to start by yanking out the settings.

18 06 2010
Charles Hooper

My concerns include the following (this is why I paused at this section of the book):
* I really do not know what those four parameters change. I suspect that the author is correct regarding their description, but I would probably want a second opinion for normal parameter changes, and at least three or four other sources recommending the same changes before I would consider touching a hidden parameter. Other than a very brief description found through an Oracle query (see there really is not a lot of information to be easily found in printed form for these parameters (I have not checked for these parameters, but that seems to be the general rule).
* What is the long term effect of these parameter changes? If we modify the parameters and allow the write list to grow longer, what happens when a lot of dirty blocks in the buffer cache must be flushed to make room for consistent read versions of blocks (with undo applied) during intense activity?
* The answer of how much of a value increase is not specified – is there some sort of formula we can use to find exactly the right value? And if there is a right value, why is it not the Oracle Database default value?
* Where is the warning that changing hidden parameters should only be performed under the guidance of Oracle support? It was mentioned on one page (I cannot recall if the book stated to contact Oracle support), possibly in a footnote.
* Is there another way to improve performance without adjusting the hidden parameters – or is that the only way. Does Exadata use the modified parameters, if not why?
* Does this recommendation only apply to databases in the petabyte range, or is there some lower limit to database size (and rate of change) where the advice does not apply?
* What if a spfile is used rather than a pfile (init.ora) – is it possible that we would make the changes and then not be able to easily tell that the defaults have changed (I believe that changed hidden parameters will show in V$PARAMETER)?

Kevin makes several good points also (I suspect that he has more points to make, and that he is giving other people an opportunity to participate in the discussion).

19 06 2010

I’m with Kevin here: 3 hidden parameters, no one knows for sure what they do nowadays, we start micro-tuning to this extent, what happens when a real performance problem surfaces?
Coming from an wengineering background, I’ve always applied an 80-20 rule to tuning. Diminishing returns becomes a primary concern beyond that.
Sure: academically, it might be interesting to research what-if scenarios at this level of detail. Exactly how many employers have I found interested in paying for academics? Their number approaches the real meaning of NULL….

20 06 2010
Charles Hooper

It is hard to disagree with Kevin’s comment. However, just because the meaning of the _SMALL_TABLE_THRESHOLD hidden parameter (as discussed in another recent blog article) changed between Oracle release versions, does not necessarily mean that the meaning of these parameters will change. For example, if we check Oracle Database, we find the following:
_db_writer_max_writes: Max number of outstanding DB Writer IOs

_db_block_write_batch: Not a valid parameter on

_db_writer_max_scan_pct: Not a valid parameter on

_db_writer_max_scan_cnt: Not a valid parameter on

Someone might be wondering right now if it is safe to change the value of the last three of the four hidden parameters. So, maybe there *is* something wrong with this quote. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: