Solved

Performance tuning the db_writer_process in Oracle v11g

Posted on 2011-03-21
4
6,681 Views
Last Modified: 2012-05-11
I recently updated my system to Oracle v11g from oracle 9i.  In Oracle 9, the db_writer_process was set by default to 1, but in v11 db_writer_process is set to 4.  

Also with v11 i get a message in my alert logs that reads Private strand flush not complete which i have read as being a normal message for oracle 11.  Oracle also says that i can get rid of this message by increasing the value of db_writer_process.

Does anyone have any information on how performance is affect by increasing or decreasing the db_writer_process parameter?

Any help is greatly appropriated, Thank YOu
0
Comment
Question by:Alex Matzinger
  • 3
4 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 35187209
Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

Default value of DB_WRITER_PROCESSES is 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

0
 
LVL 4

Author Comment

by:Alex Matzinger
ID: 35234551
Thank you for your response, Is there any tool i can use to tune this feature in oracle.  I have two very similair systems, but one has 4 log writers, and the other has 20 log writers, is there a way i can tell the difference.  I don't want to increase the number of log writers, and have it negatively affect my system.  

Adidtion note:  My system has 32 CPU's
0
 
LVL 4

Author Comment

by:Alex Matzinger
ID: 35235320
I would also like to note that my system is using Async IO.
0
 
LVL 4

Author Closing Comment

by:Alex Matzinger
ID: 35750355
Thank You, your info was spot on.  Ended up testing several different db_writer values to find the best fit.  Thank You
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question